5.3 Building a dashboard

What you'll be able to do

Build a dashboard that fits on one screen and answers the reader's five standing questions at once. It is driven by slicers, and built so that it survives next month's data.

Before you start

Open the dashboard starter workbook. Its data sheets are ready and its canvas is empty.

The work

1. Same either way: write the five questions first, before any layout. They are the five things the reader always asks. What is the total this month? How does it compare with last month? Which store leads? Which product is slipping? Is anything overdue? A dashboard is answers arranged on one screen. Start anywhere else and you get decoration.
2. Same either way: use the three-layer structure that keeps a dashboard alive. A data sheet holds the raw data, or the output of a Power Query. A calculation sheet holds the PivotTables and measures that work out the answers. The dashboard sheet holds presentation only: charts and linked numbers, with no raw data and no scattered formulas. When next month's data lands, a refresh flows data → calc → dashboard with nothing rebuilt. That separation is most of what makes a dashboard maintainable.
3. Same either way: build each answer as its own PivotTable on the calculation sheet, because one PivotTable per question keeps them independent. Chart the ones that need a shape, stripped to the bone as the charts lesson taught. Then place the charts and headline numbers on the dashboard sheet.
4. Same either way: headline numbers go at the top, in a few large cells. The total, the comparison with last month, and the one worrying count. Each reads from the calculation layer, and GETPIVOTDATA from the pivot lesson keeps them honest when the PivotTables rearrange. Readers take a number from large text faster than from any chart.
5. No documented keyboard shortcut for this — use the menu: wire up the interaction. Insert slicers (PivotTable Analyze → Insert Slicer) for the store, and a timeline for the months. Then use Report Connections on each slicer so that it drives all the PivotTables at once. One click filters the whole screen, and that is the moment it starts to feel like a product.
6. Same either way: lay everything out on the grid. Hold Alt while dragging or resizing any chart or slicer, and it snaps to the cell edges. That gives instant alignment with no judging by eye. The restraint rules from the design half of this subject apply here: three colours, generous space, and everything visible without scrolling. One screen is the promise in the word dashboard.
7. Same either way: use this test before you hand it over. A colleague answers all five questions in sixty seconds without asking you anything, then clicks a slicer and watches the whole screen obey. If it passes, it is ready.

Try it

Build the starter workbook's dashboard. Five questions written down, three sheets, one PivotTable per question. Two stripped charts and three headline numbers, slicers driving everything, snapped to the grid, and all on one screen.

Check yourself