Excel Dashboards

An Excel dashboard is a single sheet that shows the health of something — sales, ops, marketing — at a glance. Every chart and KPI on it is wired to live data, so refreshing the source updates the whole picture. Done well, it replaces a 20-page email; done badly, it confuses everyone.

What a Dashboard Actually Is

Three promises a real dashboard makes:

1. One screen, one story. A user should know in 5 seconds whether things are on track. 2. Live, not pasted. Charts pull from data, never from screenshots. 3. Interactive. Slicers / dropdowns let the viewer ask their own follow-up questions.

If you find yourself manually editing numbers on the dashboard sheet, it has stopped being a dashboard.

The Three-Layer Architecture

Professional dashboards always separate concerns into three sheets:

Sheet	Purpose	Visible?
Data	Raw, unedited source (or Power Query output)	Hidden
Calc	All SUMIFS / XLOOKUP / DAX measures	Hidden
Dashboard	Charts, KPIs, slicers — nothing else	Shown

When the source changes, only the Calc sheet needs touching. Dashboard layout stays untouched.

Beginner Mistakes to Skip

1. Computing on the dashboard sheet. Cluttered, slow, fragile. Push it to Calc. 2. Pasting screenshots of charts. They will go stale. Always use real chart objects. 3. Too many colours. Pick 1 brand colour + grey + 1 accent for alerts. That's it. 4. Pie charts with 8 slices. Use a bar chart — humans compare lengths better than angles. 5. No "as of" date. Always show ="As of "&TEXT(MAX(Data[Date]),"dd-mmm-yyyy"). 6. Hard-coded numbers in titles. Use formula-driven titles: ="Revenue: "&TEXT(SUM(...),"$#,##0").

Intermediate: KPI Cards That Update Themselves

A KPI card is just a big number + a coloured indicator + a comparison to target.

A1: ="Revenue"
A2: =SUM(Sales[Amount])               → the big number, formatted huge
A3: =A2 / Target - 1                  → % vs target
A4: =IF(A2>=Target, "↑ On track", "↓ Behind")

Apply Conditional Formatting → Icon Sets / Colour Scale to the cell so the colour changes automatically.

Intermediate: Slicers + Timelines for Interactivity

If your data is in a Table or Pivot, you can drop a Slicer (Insert → Slicer) and a Timeline (for dates) directly. Each click filters every connected pivot at once — wire them via Report Connections. This single feature replaces 90% of the cases people used to script with VBA.

Intermediate: Form Controls (When You Need More Than Slicers)

Developer tab → Insert → Form Controls:

• Combo Box — dropdown that writes its index to a cell.
• Option Buttons — single-choice radio group.
• Check Boxes — boolean flags.
• Scroll Bar — numeric input as a slider.

Link them to a hidden helper cell, then drive formulas off it:

Linked cell $Z$1   → user picks "India"
=INDEX(RegionList, $Z$1)
=SUMIFS(Sales[Amount], Sales[Region], INDEX(RegionList, $Z$1))

Intermediate: Dynamic Chart Ranges

Charts pointing at Data!A1:A1000 go stale the moment row 1001 arrives. Two clean fixes:

• Convert the source to a Table — charts auto-expand.
• Use OFFSET / INDEX / COUNTA named ranges:

MonthlyRev:= OFFSET(Data!$B$2, 0, 0, COUNTA(Data!$B:$B)-1, 1)

Point the chart series at the named range and forget about it.

Advanced: Conditional Formatting as Visual Code

• Data bars inside cells — instant mini-bar-chart.
• Colour scales for heat-maps over a region × month grid.
• Icon sets for traffic-light KPIs.
• Formula-based rules for anything custom: =AND($D20).

Apply rules to the *whole table column* using $ correctly so they propagate to new rows.

Advanced: Power Query for the Data Layer

Stop manually pasting CSVs. Power Query (Data → Get Data) connects to files, folders, web APIs, SQL, even Excel sheets, and applies a recorded series of cleaning steps. Refresh = re-run the steps end-to-end. This is how dashboards become truly hands-off.

Typical pipeline: Source CSV folder → append → promote headers → change types → trim → unpivot → load to Table.

Advanced: Power Pivot + DAX Measures

For multi-table dashboards (Sales + Customers + Calendar), load each into the Data Model, define relationships once, and write DAX measures:

TotalRevenue := SUM(Sales[Amount])
RevenueLY    := CALCULATE([TotalRevenue], SAMEPERIODLASTYEAR(Calendar[Date]))
YoY%         := DIVIDE([TotalRevenue]-[RevenueLY], [RevenueLY])

The same measures power every pivot, slicer, and KPI card on the dashboard.

Advanced: Dashboard Polish Checklist

• Single page, fits on a 1080p screen with no scrolling.
• Top-left is the most important number (eye starts there).
• 4–6 KPIs max in the header band.
• Chart titles describe the *takeaway*, not the dimensions: "Revenue grew 23% YoY" beats "Revenue by Year".
• Print area set, gridlines off, sheet protected (only slicers/inputs unlocked).
• File size <5MB — disable "Save source data with PivotTable" if needed.

Practice Path

1. Set up Data / Calc / Dashboard sheets and load a sample sales CSV via Power Query. 2. Build 4 KPI cards with formula-driven titles and icon-set conditional formatting. 3. Add 3 charts (trend line, bar, donut) bound to the Calc sheet, plus a Slicer + Timeline filtering all of them. 4. Replace one chart with a Power Pivot DAX measure (SAMEPERIODLASTYEAR) to show YoY growth.