Building Dashboards in Excel

What Makes a Good Dashboard?

A dashboard is a visual display of key metrics and trends that allows a decision-maker to understand performance at a glance. The best dashboards are:

Simple: Show only the most important information. Every element must earn its place. Accurate: Built on clean, validated data with a clear refresh process. Relevant: Tailored to the specific audience and their decisions. Scannable: Designed so the viewer can understand the key message in under 30 seconds. Actionable: The metrics shown connect clearly to decisions the audience can make.

A common mistake is trying to show everything. A dashboard that displays 40 charts tells nobody anything. Choose 5-8 key metrics and present them well.

Dashboard Planning

Before building, answer these questions:

1. Who is the audience? An operations manager, a CFO, and a sales director need completely different dashboards.
2. What decisions does this dashboard support? Identify the specific choices the audience makes based on this data.
3. What are the 5-8 most important metrics? Prioritise ruthlessly.
4. What is the data source and how often does it refresh?
5. What is the time period shown? Current month? Rolling 12 months? Year-to-date?

Sketch your layout on paper before opening Excel.

Excel Dashboard Structure

Organise your Excel dashboard workbook into three types of sheets:

Data sheet(s): Contains raw or imported data. Never referenced directly by the dashboard -- only by the calculation sheets. Protected from accidental editing.

Calculation sheets: Contain PivotTables, SUMIFS, and other formulas that aggregate the raw data. The dashboard pulls from these, not from raw data directly.

Dashboard sheet: The visual output. Contains charts, key metric displays, slicers, and no raw data. This is the only sheet users typically see.

Key Metric Cards (KPI Cards)

The most important numbers on a dashboard should be displayed prominently as large, clear numbers with a label and trend indicator.

To create a KPI card:

1. Type the metric label in one cell and format it small (12pt, grey)
2. Type or reference the metric value in the cell below and format it large (28-36pt, bold, dark)
3. Add an up/down arrow or percentage change indicator in a third cell
4. Add a border or background colour to create a card effect (Home > Borders or Fill Color)
5. Group these cells visually for clarity

Example layout:

[Monthly Revenue]
[$842,500]
[ up 12% vs last month ]

Use conditional formatting on the trend indicator to make positive changes green and negative changes red.

Choosing the Right Chart Type

Avoid: 3D charts (distort perception of values), pie charts with more than 5 segments (hard to compare), dual-axis charts (confusing to most audiences).

Building Charts for Dashboards

Charts on dashboards should be clean and minimal. Remove visual clutter:

1. Delete the chart title if the section has its own heading
2. Remove gridlines or reduce their opacity (Format gridlines > lighter colour)
3. Remove chart borders (Format Chart Area > No border)
4. Use a consistent colour palette (typically 1-2 primary colours with variations)
5. Use direct labels on data points instead of legends where possible
6. Remove unnecessary axis labels

To move a chart onto your dashboard sheet without the underlying data:

• Right-click the chart > Move Chart > Object in > select dashboard sheet

Slicers for Interactive Filtering

Connect slicers to all PivotTables on your dashboard to create interactive filtering. When the user clicks a slicer button, all connected PivotTables (and their linked PivotCharts) update simultaneously.

To connect a slicer to multiple PivotTables:

1. Right-click the slicer > Report Connections
2. Check all PivotTables that should respond to this slicer
3. Click OK

To style slicers to match your dashboard:

• Click the slicer > Slicer tab > choose or create a slicer style
• Adjust the number of columns in the Buttons section

Dynamic Chart Titles

Make chart titles update automatically based on the selected filter:

1. Create a cell elsewhere that concatenates the title text with the current filter value using formulas
2. Click the chart title
3. In the formula bar, type = and then click the cell with the dynamic title

Example formula in a cell:

= "Monthly Revenue: " & TEXT(B1, "MMM YYYY")

Where B1 contains the currently selected month from a slicer.

Conditional Formatting for Visual Impact

Apply conditional formatting to tables on your dashboard:

Data bars: Home > Conditional Formatting > Data Bars. Adds in-cell bar charts. Colour scales: Shows gradients from low to high values. Icon sets: Adds traffic lights, arrows, or other icons based on thresholds.

These make tables scannable without requiring a separate chart.

Dashboard Polish and Formatting

Colour: Use a maximum of 3 colours. A dark header, a primary accent colour (your company's brand colour or a professional choice like a medium blue), and a light background.

Typography: Use one font throughout. Calibri, Segoe UI, or Arial are clean and professional. Use size hierarchy to distinguish headings from labels from values.

Alignment: Align all elements to a grid. Use Alt + drag to snap to cell boundaries. Nothing looks less professional than misaligned charts.

White space: Leave breathing room between elements. Crowded dashboards are harder to read.

Remove Excel defaults: Hide the gridlines (View > untick Gridlines), the row and column headers (View > untick Headings), and the sheet tabs (protect the workbook structure) from the dashboard view.

Key Takeaways

• An effective dashboard shows 5-8 key metrics for a specific audience making specific decisions -- not everything available in the data.
• Structure your workbook into separate data, calculation, and dashboard sheets to keep the visual output clean and the data organised.
• Match chart types to data types: line charts for trends, bar/column for comparisons, donut for composition, scatter for relationships.
• Connect all PivotTables to shared slicers so filters update the entire dashboard simultaneously.
• Polish matters: remove gridlines, align elements to a grid, use consistent colours and fonts, and leave adequate white space.

Practice Exercise

Build a sales performance dashboard with the following components:

1. Three KPI cards: Total Revenue, Number of Orders, Average Order Value
2. A line chart showing monthly revenue trend over the last 12 months
3. A bar chart showing revenue by top 10 products
4. A donut chart showing revenue split by region
5. Slicers for Year and Product Category that filter all charts simultaneously

Format the dashboard sheet to hide gridlines and headings. Use a professional colour palette with no more than three colours.