Day 20: Excel Dashboards

An Excel dashboard is a single-screen summary that presents key metrics and trends from a larger dataset, designed for quick comprehension without requiring the viewer to understand the underlying data. Key principles: (1) One screen — no scrolling. (2) Answer a specific question — not "all the data", but "how is performance vs target?". (3) Hierarchy — most important metric largest and top-left. (4) Consistency — same colour meaning throughout. (5) Minimal decoration — remove gridlines, borders, and chart junk that do not carry information.

Create all Pivot Tables from the same data source (same Table or same Power Query connection). Insert one Slicer via PivotTable Analyze → Insert Slicer. Right-click the Slicer → Report Connections → check all the Pivot Tables you want connected. Now clicking a Slicer item filters all connected Pivot Tables and their charts simultaneously. For this to work, all Pivot Tables must share the same pivot cache — they must be created from the same data source.

A KPI card shows one metric prominently with context (vs target, vs last period, trend indicator). Build one by: selecting a cell range (e.g. 3 wide × 4 tall), removing borders and fill, entering the metric value large and bold, adding a small label below, adding a coloured indicator (green/red text or conditional formatting icon) for vs target. Use shapes or merged cells to create the card boundary. The card is not a chart — it is carefully formatted cells. Group the cells to move them as a unit.

Page Layout → set Orientation to Landscape for wide dashboards. Set scaling to "Fit to 1 page wide, 1 page tall" for single-page output. Set Print Area (Page Layout → Print Area → Set Print Area) to exactly the dashboard range. Check Print Preview before sending. Turn off gridlines: Page Layout → uncheck Print under Gridlines. For professional PDF output: File → Export → Create PDF — preserves exact formatting that print-to-printer may not.

The goal is to prevent accidental edits while keeping filters, slicers, and dropdowns functional. Unprotect the interactive elements: click each Slicer → right-click → Slicer Settings and check "Locked" is unchecked. For cells with input areas (filter dropdowns), unlock those cells (Format Cells → Protection → uncheck Locked). Then protect the sheet (Review → Protect Sheet) and ensure "Use PivotTable and PivotChart" is checked so viewers can still interact with them.