Purpose
By the end of this lesson, you will be able to design a simple, effective spreadsheet dashboard combining key metrics, charts, and filters.
Lesson Explanation
A dashboard’s job is different from a detailed report: it should let a viewer assess the current state of a business area within seconds, not minutes – which means showing fewer, carefully chosen metrics rather than as many as will fit on the screen.
A well-designed dashboard typically combines a small number of KPI (key performance indicator) figures shown prominently and simply, one or two PivotCharts showing trends or comparisons, and interactive filters (often called slicers in Excel, Insert → Slicer) that let a viewer narrow the view to a specific region, time period, or category without needing to edit any formulas.
Visual hierarchy matters: the single most important number or chart should be the most visually prominent (largest, positioned first), with supporting detail arranged to be genuinely secondary, both visually and in reading order – a dashboard where every element competes equally for attention fails at its core job of fast assessment.
A dashboard built directly on live PivotTables and slicers (rather than static, manually-updated numbers) stays automatically current whenever the underlying data is refreshed, making it genuinely reusable period after period, similar in spirit to the recurring-report principles from the previous lesson.
Practice Questions
1. What is the core difference between a dashboard’s job and a detailed report’s job, according to this lesson?
View Answer
A dashboard should let a viewer assess the current state within seconds, while a detailed report can take longer to read for deeper understanding.
2. A dashboard is built showing 25 different metrics, all displayed with equal visual weight. What problem does this lesson identify with this approach?
View Answer
A dashboard where every element competes equally for attention fails at its core job of fast assessment – fewer, carefully chosen metrics would serve the actual purpose better.
3. What Excel feature lets a dashboard viewer interactively narrow the view to a specific region or category, without editing any formulas directly?
View Answer
Slicers (Insert → Slicer), connected to a PivotTable.
4. A dashboard shows a single large revenue figure prominently at the top, with supporting charts arranged smaller below it. What principle does this reflect?
View Answer
Visual hierarchy – the most important number is the most visually prominent, with supporting detail arranged as genuinely secondary.
5. A dashboard is built with all its values manually typed in and updated by hand each month, rather than connected to live PivotTables. What problem does this create, connecting to the previous lesson’s principles?
View Answer
It requires manual rework each period rather than automatically staying current when data is refreshed, undermining the reusability principle from the recurring-report lesson.
6. A dashboard includes a slicer for “Region.” What does clicking a specific region on this slicer do to the rest of the dashboard, if it’s properly connected?
View Answer
It filters all connected PivotTables, PivotCharts, and KPI figures to reflect only that selected region, updating the whole dashboard view accordingly.
7. A dashboard designer struggles to decide which 3 metrics out of 15 possible options to feature prominently. What does this lesson’s core principle suggest should guide this decision?
View Answer
Prioritizing based on which metrics would actually let a viewer assess the current state fastest and most meaningfully, rather than trying to include everything.
8. Why might a dashboard with too many equally-weighted elements actually be less useful than one with fewer, prioritized elements, even if the larger one technically contains more information?
View Answer
The dashboard’s core value is fast assessment; more equally-weighted information can overwhelm rather than clarify, defeating the fundamental purpose of quick, at-a-glance understanding.
9. A dashboard’s KPI figures are built using formulas referencing structured Excel Tables, and its charts are PivotCharts connected to PivotTables built on that same table data. Why does this design choice support long-term reusability?
View Answer
As new data is added to the underlying table each period, the connected PivotTables, PivotCharts, and KPI formulas can all update automatically, without requiring the dashboard to be manually rebuilt.
10. A viewer looks at a dashboard and immediately understands the single most important figure within a few seconds, thanks to its size and position. What does this reflect, per this lesson’s guidance?
View Answer
Effective visual hierarchy, prioritizing the most important information for immediate visibility.
11. Why does this lesson connect dashboard design back to the recurring-report principles from the previous lesson?
View Answer
A dashboard, like a recurring report, is typically refreshed and viewed repeatedly over time, so the same principles about building on stable, automatically-updating structures apply directly.
12. A dashboard combines 3 prominent KPI numbers, 2 PivotCharts, and a slicer for filtering by time period. What does this combination reflect, based on this lesson’s description of a well-designed dashboard?
View Answer
The typical components this lesson identifies for an effective dashboard: a small number of prominent KPIs, supporting trend/comparison charts, and interactive filtering.