Theory
Meera's one-screen question
Meera does not have time to read reports. She wants one screen that answers, in a glance: how are total sales? Which category leads? Which shop is slipping? And she wants to click 'June' and see it all update for that month.
That one screen is a dashboard, and it is the payoff of everything in this unit: pivots, charts, conditional formatting, slicers, assembled into a single, interactive, glance-able report.
Building a dashboard is the skill that turns a BCA graduate into 'the person who makes sense of the data'. Let us assemble one.
Theory
A car dashboard
A car's dashboard does not make you read the engine manual, it shows speed, fuel and warnings at a glance, exactly what a driver needs, right now. A data dashboard is the same: the headline numbers big and clear, the trends as small charts, warnings in red, and controls to change the view. You should understand the situation in three seconds, without hunting. Design for the glance, not the deep read.
At a glance
Dashboard components
| Component | Shows | Built from |
|---|---|---|
| KPI cards | Headline numbers (total, growth %) | Big formatted cells / pivots |
| Pivot charts | Category / shop breakdowns | PivotTables |
| Sparklines | Tiny in-cell trend per row | Insert > Sparklines |
| Slicers | One-click filters for the whole board | Connected to multiple pivots |
Theory
The magic: one slicer, everything moves
The heart of an interactive dashboard is a slicer connected to several pivots at once. Via Report Connections, one 'Month' slicer controls the KPI pivots, the category chart, and the shop chart together. Click 'June' and the entire dashboard re-filters in sync, one action, whole-screen update.
Add sparklines (Insert > Sparklines) for a tiny line chart inside a single cell, one per shop row, so a 12-month trend fits beside the shop's name. KPI cards up top (big total-sales number), charts in the middle, sparklines in a column. That is a dashboard.
Quiz
Aryan connects one 'Month' slicer to all three pivots on his dashboard. Meera clicks 'June'. What happens?
- All three pivots (and their charts) re-filter to June together, the whole dashboard updates in sync
- Only the first pivot updates
- Nothing, slicers only work on one pivot
- The month column is deleted
Show the answer
All three pivots (and their charts) re-filter to June together, the whole dashboard updates in sync
A slicer connected to multiple pivots (via Report Connections) filters them all at once, so one click on 'June' updates every pivot and chart together, the whole dashboard moves in sync. This shared-slicer mechanism is what makes a dashboard interactive rather than a static collection of separate reports. It is the single most important dashboard-building fact.
Think first
What goes top-left?
Dashboard design has a rule about where the most important information goes. Where should Aryan put Meera's single most important number (total sales), and why?
Show the answer
Top-left, as a big, bold KPI card. Because most people read a screen top-to-bottom, left-to-right, the top-left is where the eye lands first, so the headline number belongs there. Supporting details (breakdowns, trends) flow below and to the right. Good dashboard design is about visual priority: the most important thing, most prominent, first. Cluttering everything equally defeats the glance-ability that makes a dashboard useful.
Watch out
Where marks leak
Not knowing a dashboard combines KPIs + charts + sparklines on one screen. Missing the key mechanism: a slicer connected to multiple pivots updates them all together (interactivity). Confusing sparklines (tiny in-cell trend charts) with normal charts. And ignoring design principles (glance-ability, headline first/top-left, minimal clutter), examiners of a real Excel elective reward design thinking, not just feature-listing.
Theory
Unit 2's grand payoff
Look back: references, functions, VLOOKUP, conditional formatting, pivots, charts, every tool in this unit converges here, in a dashboard Meera actually uses to run her business. This is what data analysis produces. It is also a stellar portfolio piece for a BCA graduate. One tool remains in Unit 2, for looking forwards instead of back: What-If analysis, Goal Seek and Scenario Manager, next. Then Unit 3's automation.
Summary
Key takeaways
- A dashboard is a single screen answering a decision-maker's questions at a glance.
- Components: KPI cards (headline numbers), pivot charts (breakdowns), sparklines (tiny in-cell trends), slicers (filters).
- The key mechanism: one slicer connected to MULTIPLE pivots updates the whole dashboard in sync.
- Sparklines are tiny charts inside a single cell (Insert > Sparklines).
- Design for the glance: most important number top-left, minimal clutter, consistent formatting.
- Memory hook: a car dashboard, understand the situation in three seconds.