STA191Data Visualization
Topic 3 · Google Spreadsheets  ›  Week 7

Google Sheets — Build an Interactive Dashboard

Everything from Weeks 5 and 6 comes together on one screen. This week you turn separate charts into an interactive infographic dashboard — with scorecards and slicers, and data pulled from several sheets and even several files.

By the end of this week you can…

  • Explain how an interactive dashboard differs from a static report.
  • Lay out a dashboard sheet with charts, scorecards and a slicer that filter together.
  • Combine data from several sheets in one file, and from separate files using IMPORTRANGE.
  • Summarise a large dataset with pivot tables, including grouping dates by month.
  • Judge whether a dashboard is designed well — or just decorated.
Datasets: ✅ MYMart CLEAN 🍎 Dataset 3 (Fruits) 🏪 Case 1 — 3 shops, 1 file 📄 Case 2 — Shop A 📄 Case 2 — Shop B 📝 Practice (Retail Sales)

7.1  What makes a dashboard interactive

An interactive dashboard is a digital interface that shows data as charts, graphs and numbers — but lets the reader explore, filter and drill down in real time. A static report shows fixed charts and nothing more. A dashboard is a control panel: click an option, and every visual updates to match.

● Picture a sales dashboard

A map shows sales by region. A bar chart shows product sales. A line chart shows revenue over time. The reader selects “Q2 2025” or “Product A” — and instantly all three visuals redraw for only that selection.

Anatomy of a dashboard — one screen, four parts
MYMART SALES DASHBOARD ▼ State: All the slicer TOTAL SALES RM512k INVOICES 9,894 AVG RATING 4.2 scorecards SALES BY CATEGORY SALES TREND BY MONTH

One slicer at the top controls everything below it. Change it, and the scorecards, bar chart and line chart all redraw together.

7.2  The recipe — four steps, every time

Every dashboard in this course is built the same way. Learn the loop once and the rest is repetition.

  1. Summarise — build a pivot table so each chart has clean numbers to draw from.
  2. Chart — create the chart from the pivot output.
  3. Collect — move every chart onto one Dashboard sheet.
  4. Connect — add a slicer so one control filters them all, then test it.
Make a Dashboard sheet first. At the bottom of your file click + and name a new sheet Dashboard. Keep your data on Clean, your pivots on their own sheets, and the Dashboard sheet for display only.

7.3  Build it — MYMart, step by step

Open your cleaned MYMart file from Week 5. We'll build the dashboard in the figure above.

Chart 1 — sales by category (bar)

  1. Insert ▸ Pivot table → Rows = Product Category, Values = SUM of Sales. Sort descending.
  2. Select the pivot output → Insert ▸ Chart → Column chart. Title it “Sales by category”.
  3. Click the chart → ⋮ ▸ Copy chart, then paste it onto the Dashboard sheet.

Chart 2 — sales trend by month (line)

  1. New pivot: Rows = Month, Values = SUM of Sales. Fix the month order as you did in Week 6.
  2. Insert ▸ Chart → Line chart → title “Sales trend by month” → copy onto the Dashboard sheet.

Scorecards — the headline numbers

A scorecard shows one number, big. It's the first thing a reader's eye lands on, so use it for the figure that matters most.

  1. Select your data → Insert ▸ Chart → chart type Scorecard chart.
  2. Set Key value = Sales, and tick Aggregate → SUM.
  3. Optionally set a Baseline value (for example the previous period) so Sheets shows the change.
  4. Copy it to the Dashboard sheet. Repeat for Invoices (COUNT of InvoiceID) and Rating (AVERAGE).

The slicer — what makes it interactive

  1. Select your whole data table on the Clean sheet.
  2. Data ▸ Add a slicer → choose the column to filter by, e.g. State (or Month).
  3. Copy the slicer and paste it onto the Dashboard sheet, near the top where readers will find it.
  4. Test it. Pick one state — every chart and scorecard fed by that data should change together.
● Two things that trip students up

Click a single empty cell before pasting a chart or slicer, otherwise Sheets drops it over your existing work. And a slicer only filters visuals built from the same data range — if a chart doesn't respond, it's reading from somewhere else.

● Try it · Dataset 3 (Fruits)

Open Week 7 Dataset 3 (Fruits) — three months of fruit sales. Build a clustered bar chart (Rows = Fruit, Columns = Month, Values = Sales), a line chart of the sales trend, and a slicer on Month. Put all three on a Dashboard sheet and check that selecting “Feb” updates both charts.

● Session 1 recap

Summarise → chart → collect → connect. Pivot tables feed the charts, scorecards carry the headline numbers, and the slicer is what turns a page of charts into a dashboard. Always paste onto a single empty cell, and always test the slicer before you call it done.

Real data rarely sits in one tidy sheet. Two situations come up constantly — several sheets in one file, and several separate files.

7.4  Case 1 — several sheets, one file

Three shops each record their daily sales on their own sheet inside the same file. You need one table that adds them up.

Open Week 7 Case 1 (Three shops, one file). It has a sheet for Shop A, Shop B and Shop C, each with the same Day and Sale columns.

  1. Create a new sheet and name it Dashboard.
  2. Build a small table: column A = Day, column B = Total Sale.
  3. Pull each shop's figure using sheet references. The pattern is ='Sheet name'!Cell — the quotes matter when the sheet name has a space.
  4. Add them together for each day:
    ='Shop A'!B2 + 'Shop B'!B2 + 'Shop C'!B2
    then drag the formula down the column.
  5. Now build from this table: select it → add a column chart, a scorecard (Key value = Sale, Baseline = Day, Aggregate = SUM), and a slicer on Day.
Why a summary table first? Charts can only read one range. Consolidating into a single table on the Dashboard sheet gives every visual — and the slicer — the same source to work from.

7.5  Case 2 — separate files, with IMPORTRANGE

Now the harder case. A company runs two shops. Manager A keeps Shop-A's daily profit in one file; Manager B keeps Shop-B's in a different file. You need a dashboard tracking both, without asking either of them to change how they work.

Open Week 7 Case 2 — Shop A and Shop B as two separate Google Sheets files, then create a third file for the dashboard.

How IMPORTRANGE connects three files
SHOP-A file Manager A SHOP-B file Manager B IMPORTRANGE DASHBOARD file combined table → chart, scorecard, slicer

Each manager keeps their own file. The dashboard file reads from both — it never edits them.

  1. Open the Shop-A file and copy its URL from the browser address bar.
  2. In the dashboard file, type:
    =IMPORTRANGE("paste-the-URL-here", "Sheet1!A1:B8")
  3. The first time, the cell shows a #REF! error with an Allow access button. Click it — this is a one-off permission, not a mistake.
  4. Repeat for the Shop-B file in another column.
  5. Add a Total column that sums the two shops for each day.
  6. Select roughly A1:B8 → add a bar chart and a slicer. Select the total cell → insert a scorecard with Aggregate = SUM.
  7. Test the slicer and the scorecard together.
● IMPORTRANGE gotchas

The URL and the range both go in quotation marks. The range needs the sheet name and an exclamation mark: "Sheet1!A1:B8". And the import is live — when Manager A updates her file, your dashboard changes too. That's the point, but it also means a typo in her file becomes a wrong number in yours.

7.6  Pivot tables on a bigger dataset

The Retail Sales practice file has one row per sale, with Date, Store, Salesperson, Product, Quantity, Price and Total Sales. Far too much to read raw — which is exactly what pivot tables are for.

  1. Click any cell inside the data → Insert ▸ Pivot table → new sheet.
  2. Set Rows = Store and Values = SUM of Total Sales.
  3. Copy that pivot table and change the Rows field to Salesperson, then Product — one pivot per question.
  4. For a monthly view: Rows = Date, Values = SUM of Total Sales. Then right-click any date in the pivot → Create pivot date group → Month. Daily rows collapse into months.
  5. Build a dashboard from those pivots, with a slicer on Store or Product.
One pivot, one question. Don't try to force every field into a single pivot table. Four small pivots are easier to chart, and easier to fix when something looks wrong.

7.7  Design it like a designer

A working dashboard and a good dashboard are different things. Everything you learned in Chapter 2 applies here — the dashboard is just several charts sharing one page.

✓ Designed
  • Headline number top-left, where the eye starts.
  • Slicer somewhere obvious and consistent.
  • Charts aligned to a grid; equal gaps.
  • One accent colour, used to mean something.
  • Every chart titled in plain language.
  • Gridlines off, chart clutter removed.
✗ Just decorated
  • Charts scattered wherever they landed.
  • Six colours, none of them meaningful.
  • Default titles like “Chart 1”.
  • A 3-D pie squeezed into the corner.
  • Slicer hidden at the bottom.
  • Reader can't tell what to look at first.
● The test

Show your dashboard to someone who has never seen the data. If they can't tell you the main message within ten seconds, the problem is the design, not the reader.

● Try it · Practice (Retail Sales)

Using the Retail Sales file, build a dashboard with: a scorecard for total sales, a bar chart by Store, a bar chart by Product, a line chart of monthly sales, and a slicer on Salesperson. Then apply the design checklist above before you show anyone.

Key terms & definitions

Interactive dashboard
A single screen of visuals the reader can filter and explore in real time, rather than a fixed report.
Dashboard sheet
A sheet used only for display — charts, scorecards and slicers pasted together, with the raw data kept elsewhere.
Scorecard chart
A chart type that displays one aggregated number, optionally compared against a baseline.
Slicer
A filter control on the sheet. It filters every visual built from the same data range at once.
Sheet reference
='Sheet name'!Cell — reads a value from another sheet in the same file.
IMPORTRANGE
=IMPORTRANGE("url","Sheet1!A1:B8") — reads a live range from a different file, after a one-time access approval.
Pivot date group
Collapsing daily dates in a pivot table into months, quarters or years.

✅ Quick self-check

1. What turns a page of charts into an interactive dashboard?

2. Your slicer changes the bar chart but not the line chart. The most likely reason is…

3. To pull daily sales from a colleague's separate Google Sheets file, you use…

4. A scorecard chart is best used for…

Week 8 →