Putting "all our charts on one tab" doesn't make a dashboard. A dashboard is a decision instrument. At a glance and on a single screen it answers the two or three questions the decision-maker actually asks, and it lets them slice those answers by the dimensions they care about (region, product, month). The one-screen, no-scrolling constraint is what forces the hard choices. You can't fit everything, so you have to choose the vital few. Anything that goes on the dashboard has to answer a top question, and everything else goes on a supporting tab.
What are the building blocks of a KPI dashboard?
The building blocks:
- KPI tiles. A tile is a big number with context. The headline metric (Revenue, Gross Margin %, YoY growth) goes in large type, with a tiny comparison beneath it against last period or against target. On its own the number is meaningless, and "₹6.83 crore" says nothing until it's next to "+18% YoY". A tile often carries a sparkline showing the trend as well. Three to six tiles across the top is the standard "at a glance" row. Build the numbers with the aggregation functions you already know (
SUMIFS,XLOOKUP, simple ratios), and display them in merged, large-font cells or linked text boxes. - Slicers for interactive filtering. A slicer is a set of clickable buttons that filters one or more PivotTables/Tables live. Go to Insert ▸ Slicer and pick the field (e.g., Region). Click a button and every connected PivotTable refilters instantly, along with every chart built on it. One slicer can drive several PivotTables at once via Slicer ▸ Report Connections, where you tick every PivotTable it should control. That's how one "Region" click updates the whole dashboard coherently. A Timeline slicer (Insert ▸ Timeline) does the same for dates with a drag-able period selector. Slicers are what turn a static picture into a tool the user operates.
- Small multiples. One cluttered chart with eight regions' lines tangled together is a "spaghetti chart". Draw eight small, identical charts instead, one per region, laid out in a grid with the same axes and the same scale. The eye compares them like frames of a filmstrip, and the outlier region jumps out. Sparklines are the most compact small multiple. A grid of tiny, identically scaled line charts is the fuller version.
- A headline exhibit. One larger chart usually carries the main story, often the revenue-and-margin combo chart. The tiles summarise it and the small multiples break it down.
Advertisement
How do you assemble a KPI dashboard?
To assemble it, put the raw data in Tables on a hidden "Data" sheet. Build the PivotTables and measures on a "Calc" sheet, and keep the "Dashboard" sheet for presentation only. Set View ▸ Gridlines off and Headings off on the Dashboard sheet for a clean canvas. Lay the tiles and charts on a consistent grid. Holding Alt while you drag a chart snaps it to cell borders. To line several objects up, select them with Ctrl+click and use Shape Format ▸ Align. Add slicers, connect them to every relevant PivotTable via Report Connections, and style them (Slicer ▸ Buttons/Columns) to fit the tile grid. Freeze the layout. Then test it by clicking through the slicers to confirm every element refilters together.
The finished sheet should let a manager answer "how are we doing, and where's the problem?" without asking you a single question.