Building a Simple Excel Dashboard From Scratch

What You Will Learn

• The four building blocks of an Excel dashboard
• How to lay out a dashboard that reads in 10 seconds
• How to connect slicers so one click filters every chart
• Five design principles that separate good dashboards from bad ones

Why This Topic Matters

A dashboard is the final output of most spreadsheet analysis. If you can build a clean one, you can communicate findings to non-technical stakeholders. If you cannot, your analysis stays trapped in tables nobody reads. This lesson gives you the full workflow from raw data to a one-screen dashboard.

The Sample Data

We reuse the 12-row orders dataset from lesson 15. Convert it to an Excel Table (Ctrl+T, name it Orders) so all pivots and slicers auto-extend when new data arrives.

The Four Building Blocks

Step 1 — Decide the Question First

Before opening Excel, write down the question the dashboard answers. Example: "Which cities and products are driving our revenue, and how is that changing week by week?"

If you cannot state the question in one sentence, you are not ready to build. The question dictates which KPIs, charts, and slicers belong on the dashboard.

Step 2 — Compute the KPIs

On a new sheet called "Dashboard", reserve the top row for three KPI cards. Each card is just a labeled number:

• Total Revenue: =SUM(Orders[amount])
• Orders: =COUNTA(Orders[order_id])
• Average Order Value: =AVERAGE(Orders[amount])

For our 12-row sample: ₹2,75,500 / 12 orders / ₹22,958 AOV. Format each cell large and bold so they read as headlines.

Step 3 — Build the Trend Chart

1. Build a pivot on a "Pivots" sheet: date in Rows (grouped by week), amount (Sum) in Values.
2. Click inside the pivot → PivotTable Analyze → PivotChart.
3. Choose Line chart.
4. Cut and paste the chart onto your Dashboard sheet.

This shows weekly revenue trend. Even with 12 rows, you can see which week was strongest.

Step 4 — Build the Breakdown Chart

1. Build a second pivot: city in Rows, amount in Values.
2. Insert a Bar chart (horizontal bars are easier to read than vertical for long labels).
3. Move it to the Dashboard sheet, below the trend chart.

You can repeat this for product category if you want a third chart. Two charts is plenty for a starter dashboard.

Step 5 — Add Slicers

1. Click any pivot → PivotTable Analyze → Insert Slicer.
2. Check city and product. Two slicer panels appear.
3. Right-click each → Report Connections (or "PivotTable Connections" in some versions).
4. Check all pivots so the slicer drives every chart and KPI.
5. Move the slicers to the right side of the dashboard.

Now click "Mumbai" in the city slicer. Every chart and KPI updates instantly to show only Mumbai. That interactivity is what makes a dashboard.

Step 6 — Apply the Five Design Principles

1. One screen, one question

Do not cram five questions onto one dashboard. Build five dashboards instead. A dashboard that tries to answer everything answers nothing.

2. Top-left to bottom-right reading order

People read dashboards like they read text. Put the most important KPI in the top-left. Put the trend chart below or beside it. Put breakdowns further down. Put slicers on the right where they do not distract.

3. Strip non-data ink

Remove gridlines, chart borders, redundant legends, and 3D effects. Every pixel that is not data is noise. Use Excel's "Chart Elements" button to remove gridlines, legends (when obvious), and axis titles when the chart title says it all.

4. One accent color

Use one color for the data (e.g., dark blue bars) and grey for everything else. Highlight the single most important number in a contrasting color (e.g., orange) so the eye lands on it first. Rainbow charts look impressive but confuse readers.

5. Label with meaning, not jargon

"Revenue (INR), last 4 weeks" beats "Sum of amount". Every chart title and KPI label should be readable by someone who has never seen your data.

The Resulting Layout

+--------------------------------------------------+
| TOTAL REVENUE    ORDERS    AVG ORDER VALUE       |
|   ₹2,75,500       12          ₹22,958            |
+--------------------------------------------------+
|  Weekly revenue trend                            |
|  [Line chart]                                   |
|                                                 |
+----------------------------+--------------------+
| Revenue by city            | Revenue by product |
| [Bar chart]                | [Bar chart]        |
|                            |                    |
+----------------------------+--------------------+
| City:   [All] [Mumbai] [Chennai] [Kolkata]      |
| Product:[All] [Laptop] [Mouse] [Keyboard]       |
+--------------------------------------------------+

Common Mistakes

1. Too many charts. Three charts is the maximum for a one-screen dashboard. More than that and nobody reads any of them.
2. No slicer connections. Adding a slicer but forgetting Report Connections means the slicer filters only one pivot. Always check the connections.
3. Forgetting to refresh. New source data does not appear until you refresh every pivot. Use Data → Refresh All.
4. Putting raw data on the dashboard sheet. Keep raw data, pivots, and the dashboard on three separate sheets. The dashboard sheet should contain only final outputs.
5. Using pie charts for many categories. Pie charts are unreadable past 4–5 slices. Use bar charts instead — see lesson 33.

Practical Exercise (30 minutes)

1. Recreate the 12-row sample data as an Excel Table named "Orders".
2. Build the dashboard above on a sheet called "Dashboard".
3. Add a city slicer and a product slicer, both connected to all pivots.
4. Click through slicer combinations and confirm every chart and KPI updates.
5. Add a fourth KPI: Top City using =INDEX(pivot_city_names, MATCH(MAX(pivot_city_amounts), pivot_city_amounts, 0)). This requires referencing your city pivot's data range — a small but real Excel challenge.

Mini Challenge

Take any real spreadsheet you have access to — a personal budget, a small business sales log, anything with at least 30 rows. Build a one-screen dashboard for it. Show it to someone who has never seen the data and ask: "What does this tell you?" If they cannot answer in 10 seconds, your dashboard needs simplification.

Key Takeaways