Building an Interactive Dashboard¶

A dashboard is a single screen that answers the key questions at a glance. We'll combine everything from this course into one.

A finished one-page business dashboard with KPIs and charts

The Three-Layer Design¶

1. DATA layer    → raw data as an Excel Table  (one sheet)
2. ANALYSIS layer→ PivotTables doing the math  (hidden sheet)
3. DASHBOARD layer→ charts + KPIs + slicers     (the pretty sheet)

Keep these on separate sheets. Never build charts directly on raw data.

KPI Cards¶

Big single numbers grab attention. Build them with functions from earlier chapters:

excel

4 lines

1Total Sales:   =SUM(Sales[Revenue])
2Avg Order:     =AVERAGE(Sales[Revenue])
3Top Region:    =INDEX(Regions, MATCH(MAX(Totals), Totals, 0))
4vs Target:     =SUM(Sales[Revenue]) / Target

Format each in a large font inside a colored box — instant KPI card.

Wiring Up Interactivity¶

1.Create PivotTables for each view (sales by region, by month, by product).
2.Make a PivotChart from each.
3.Insert slicers and a timeline (Chapter 8).
4.Connect each slicer to all pivots: right-click slicer → Report Connections.

Now one click on "North" filters every chart and KPI on the page simultaneously.

Layout Principles¶

+Most important number top-left (we read Z-shaped)

+Group related charts together

+Align everything to a grid; consistent colors

+Leave white space — don't cram

−Avoid more than ~5–6 visuals per screen

Finishing Touches¶

View tab → uncheck Gridlines & Headings   → clean canvas
Insert → Shapes/Text Box                  → title bar
Page Layout → set print area               → shareable PDF

Success

You've now built the full pipeline: clean data → formulas → lookups → PivotTables → charts → an interactive dashboard. That's professional-grade Excel. 🎉