Dashboard Basics

A dashboard is not a collection of charts. It is a communication tool with a specific audience, a specific set of questions to answer, and a visual hierarchy that guides the reader to the most important information first. Most Excel dashboards fail not because of bad formulas or wrong data — they fail because of poor layout decisions, chart type mismatches, and the absence of a clear design intention. This lesson covers the principles that turn a page of charts into a dashboard that actually works.

The Dashboard Design Process

Before touching Excel, the most important work happens on paper — or in your head. Every good dashboard starts with three questions answered clearly: Who is the audience? What decisions does this dashboard support? What are the three most important things the reader needs to see first? The answers to these questions determine layout, chart types, level of detail, and what to leave out.

Dashboard Layout Zones

A well-structured dashboard is divided into zones with a clear reading hierarchy. The most critical information occupies the top-left position because that is where Western readers naturally look first. Supporting context lives below or to the right. Interactive controls (slicers, date pickers) sit at the top or along a side panel where they are easy to find but do not compete with the data.

Choosing the Right Chart Type

Chart type selection is one of the highest-impact decisions in dashboard design. The wrong chart type does not just look bad — it actively misleads or confuses the reader. The right chart makes the insight obvious at a glance.

Two specific rules worth memorising: pie charts only work with two to five segments where the proportions are significantly different from each other. With more segments or similar-sized slices, even experienced readers cannot correctly compare the areas. Use a sorted bar chart instead — it is always more readable. And avoid 3D charts entirely: the perspective distortion makes accurate comparison impossible, which is the exact opposite of what a chart is supposed to do.

Excel Dashboard Setup — Practical Configuration

Setting up Excel properly before building a dashboard saves significant rework later. A few configuration decisions made at the start determine whether the dashboard is easy or painful to maintain.

Sheet structure — recommended setup:

  Data sheet(s)     — Raw source data or Power Query output tables
                      Hidden from users (right-click tab → Hide)
  Model sheet       — Power Pivot calculations (no user-facing content)
  Dashboard sheet   — The visible output: charts, KPI cards, slicers
  Instructions tab  — (optional) notes for users on how to use filters

Dashboard sheet setup:
  Remove gridlines:   View → untick Gridlines
  Remove row/column headers: View → untick Headings
  Set zoom level:     View → Zoom → 75% or 80% (fits more on screen)
  Freeze pane at top: View → Freeze Panes → Freeze Top Row (for slicer bar)
  Set tab colour:     Right-click tab → Tab Color → match brand colour
  Lock the sheet:     Review → Protect Sheet (allow: Select cells, Use slicers)
                      Prevents accidental chart or formula deletion

Background colour:
  Select all cells (Ctrl+A) → fill with a neutral background
  Recommended: #F1F5F9 (light cool grey) or #F8FAFC (near-white)
  Avoid white — charts float better on a subtle background

Cell alignment grid:
  Use row height and column width as a consistent grid unit
  e.g. row height = 15pt, chart heights = multiples of this unit
  Everything snaps to the same grid → professional, aligned appearance

The single most important setup decision is hiding the data sheets. Dashboard users should never be able to accidentally click onto a raw data tab and see the underlying query output. Right-click any sheet tab and choose Hide to remove it from the tab bar — the data is still there and all formulas and PivotTables that reference it still work perfectly.

KPI Cards — Building Them Without a Plugin

KPI cards — the coloured tiles showing a single headline number with a trend indicator — are the most recognisable element of modern dashboards. In Excel, they are built from formatted cells, not charts. No special plugin needed.

Building a KPI card from cells:

1. Merge a block of cells (e.g. B2:E5) → right-click → Format Cells → Alignment → Merge
2. Fill the background with a brand colour
3. Type the metric label in a small font (white, bold, uppercase)
4. In the cell below, reference the measure output cell from the PivotTable
5. Format the number (currency, %, etc.) in white bold large font
6. Add a trend indicator in a smaller cell below:
   =IF(YoY_Growth>0, "▲ " & TEXT(YoY_Growth,"0.0%") & " YoY",
                     "▼ " & TEXT(ABS(YoY_Growth),"0.0%") & " YoY")
7. Colour the trend cell green (positive) or red (negative)
   using Conditional Formatting → Icon Sets or custom rules

Tip: remove the chart border:
  Right-click the merged cell range → Format Cells → Border → None
  This makes the card look like a clean coloured tile, not a cell block

A critical tip for KPI cards: do not link the number directly to a PivotTable cell. Instead, link to a helper cell on a hidden sheet that uses GETPIVOTDATA() or a simple formula to pull the specific measure. PivotTable cells move when the layout changes, breaking direct references. A helper cell reference stays stable regardless of PivotTable rearrangement.

Next up — Charts in Excel, where we go hands-on with every major chart type — building, formatting, and customising line charts, bar charts, combo charts, and sparklines to professional dashboard quality.