📊 Lesson 8.3: Capstone — Build a Complete Interactive Dashboard

The Brief & the Plan

A dashboard is a single screen that answers "how are things going?" at a glance. Before touching a cell, choose a real subject and sketch the dashboard on paper. Good capstone datasets:

• Sales — orders with date, region, product, amount (great for KPIs and trends).
• Personal finance — the budget data from Lesson 7.1, elevated to a full dashboard.
• Fitness / habits — daily logs you want to see trends in.
• A small business — inventory, bookings, or expenses.

Then decide the architecture — the same three-stage pattern you've used all along, now as the backbone of a polished product:

Step 1 — The Data Foundation

Everything rests on clean data, so start there (Modules 1–3):

• Put your records in a Data tab as one tidy table with clear headers — real dates, numbers as numbers, consistent categories.
• If the source is messy, run it through the cleanup workflow from Lesson 7.3 (raw → staging → clean) first.
• Put option lists on a Lists tab and add data validation so the data stays consistent.
• Create a couple of named ranges (e.g. the data table, a key list) to keep later formulas readable.
• Freeze the header row and keep this tab pristine.

Step 2 — The Calculation Engine

On a hidden or side Calc area (or the Dashboard tab's working region), build the numbers that will feed the display, pulling in the power-tier skills from Module 4 and the analysis tools from Module 5:

• Lookups (Lessons 4.1–4.2) — if your data has codes, use XLOOKUP or INDEX/MATCH to pull in friendly names, prices, or categories from a lookup table.
• Conditional aggregation (Lesson 4.3) — SUMIFS/COUNTIFS/AVERAGEIFS to compute your key metrics by category, region, or period. Wrap in IFERROR for clean output.
• A QUERY summary (Lesson 4.4) — a live "top categories" or "totals by group" table that a chart can read.
• A pivot table (Lesson 5.1) — for a cross-tab (e.g. category by month) you can chart.

Example KPI (total for the region chosen in the dropdown at Dashboard!B1):
=SUMIFS(Data!D:D, Data!B:B, Dashboard!$B$1)

Example live summary:
=QUERY(Data!A1:D, "select C, sum(D) where B = '"&Dashboard!$B$1&"' group by C order by sum(D) desc", 1)

Step 3 — Make It Interactive

This is what turns a static report into a dashboard. Add a control cell — a dropdown (data validation) where the user picks, say, a region, month, or category — and feed that cell into your engine formulas.

You've already seen the key move (Lesson 4.4): reference the control cell inside SUMIFS criteria or a QUERY string, so every metric and chart recalculates the instant the user changes the dropdown:

Dashboard!B1 holds the chosen region (a dropdown).
Every KPI reads it:  =SUMIFS(Data!D:D, Data!B:B, Dashboard!$B$1)
The summary reads it: ... where B = '"&Dashboard!$B$1&"' ...
Even titles react:   ="Sales Dashboard — "&Dashboard!$B$1

Now one dropdown drives the whole screen. Add a second control (e.g. a month) and you have a genuinely interactive tool. Charts built on the QUERY results redraw automatically because they read the same live cells. (A pivot table doesn't read a cell — its filters live in the pivot editor — so a chart built on a pivot keeps showing everything. Use pivots for fixed cross-tabs, and QUERY or SUMIFS for anything the dropdown should drive.)

Step 4 — The Visual Dashboard & Sharing

Now assemble the Dashboard tab — the one screen people actually look at (Modules 5–6):

• KPI tiles across the top — a few big numbers (total, average, count, % change) in large, bold cells with labels. These read from your engine.
• Charts (Lesson 5.2) — a comparison column chart and a trend line chart, reading your QUERY/pivot summaries, sized to sit neatly side by side.
• A sparkline column (Lesson 5.2) in a small table for per-item trends.
• Conditional formatting (Lesson 5.3) to highlight standouts — best/worst, over/under target.
• The control(s) placed prominently so users know they can interact.

Polish it: hide gridlines on the dashboard (View → Show → uncheck Gridlines) for a clean look, use consistent colors (your green accent), align everything to a tidy grid, and give it a clear title. Protect the formula/engine areas (Lessons 3.3 and 6.1), then share it (Lesson 6.1) with the right people and the right access — or export a PDF snapshot for a report.

⚠️ Important Note: Before you call it done, run the Lesson 8.2 audit: bounded ranges, no unhandled errors, references locked correctly, a Notes tab documenting how it works. A dashboard that is beautiful and solid is the real goal — and now you know how to achieve both.

🎯 Project: Build Your Dashboard

This is your capstone. Take your time — 90 minutes is a guide, not a race — and build something you'd be proud to show. Every step maps to skills you've already practiced.

Tabs: Data | Lists | Calc | Dashboard | Notes

Control cell (Dashboard!B1): a dropdown of regions/categories.

KPI (Calc): =SUMIFS(Data!D:D, Data!B:B, Dashboard!$B$1)
% share:  =IFERROR(KPI_cell / SUM(Data!D:D), 0)      (format %)
Summary:  =QUERY(Data!A1:D,
            "select C, sum(D) where B = '"&Dashboard!$B$1&"' group by C order by sum(D) desc", 1)
Title (on Dashboard): ="Dashboard — "&$B$1

Charts that read the QUERY output follow B1, so change B1
and the dashboard updates (pivot-based charts don't). Hide gridlines for the clean look.

Best Practices for Dashboards

✅ Do's

• Build in layers — data, engine, interactivity, presentation — verifying each.
• Drive it with a control and add a dynamic title.
• Keep the engine separate, then audit for speed and errors before sharing.

❌ Don'ts

• Don't cram everything on one messy tab — separate data, calc, and display.
• Don't over-decorate — clarity beats clutter; every element earns its place.
• Don't skip the audit — beautiful but broken helps no one.

📓 Learning Journal

📝 Lesson Summary

🎉 What You've Accomplished

You did it. You started this course typing your first number into a cell, and you're finishing it by building a complete, interactive, shareable dashboard from scratch. Along the way you mastered formulas and references, lookups and QUERY, pivots and charts, collaboration and cleanup, performance and best practices. That's not "knowing a few spreadsheet tricks" — that's genuine, transferable capability, and it carries straight over to Excel too. Be proud of this.

❓ Common Questions at This Stage

🔭 Looking Ahead

This is the last lesson — but not the end of your journey. You now have a spreadsheet skill set most people never build, and a dashboard to prove it. Keep it, share it, extend it, and reach for Sheets the next time life hands you a problem a spreadsheet could solve. That's the real graduation.

✅ Before You Go

• Finish and save your capstone dashboard — it's your portfolio piece
• Write your final Learning Journal entry and re-read your first one
• Share your dashboard with someone, or export a PDF to show off

📚 Additional Resources

• Google Sheets — keep building
• Google Sheets function list
• Ray's House of Fun — more free courses