26.1 Dashboard Design Principles

Before building a single chart or Slicer, planning the layout and purpose of the dashboard will save hours of rework.

The Three Dashboard Questions

• Who is the audience? A CEO wants high-level KPIs; a sales manager wants granular rep-level detail; an accounts team wants aged debt. Design for the reader's decisions, not for your data.
• What decision does it support? Every element on the dashboard should answer a question or trigger an action. Remove anything that is "interesting" but does not support a decision.
• How often does it update? A daily operational dashboard needs simple, auto-refreshing data. A monthly management dashboard may be manually refreshed and more elaborate.

Layout Principles

• Top-left is prime real estate: readers' eyes start at the top-left. Put your most important KPI or summary number there.
• Flow from summary to detail: headline KPI numbers at the top, supporting charts in the middle, detailed tables or drill-downs at the bottom.
• Group related information: cluster controls (Slicers) together, cluster charts together, cluster KPI cards together.
• Consistent spacing and alignment: use cell gridlines as guides — align all chart edges to the same cell boundaries. Alt+drag snaps objects to cell gridlines.
• Limit the colour palette: two to three colours maximum. One primary (corporate colour), one accent (for highlights), one neutral (grey for secondary information).
• Remove chart clutter: no gridlines, no chart borders, no 3D effects, no legend if data labels are used instead. Every pixel should earn its place.

The Sheet Structure

Professional Excel dashboards use a minimum of three sheets:

26.2 Setting Up the Dashboard Sheet

Visual Preparation

1. Select the entire sheet (Ctrl+A) → set row height to a uniform 18–20pt (Home → Format → Row Height)
2. Set background: fill the dashboard area with a dark or neutral colour to give the appearance of a designed layout rather than a spreadsheet grid
3. Hide gridlines: View tab → Show group → untick Gridlines
4. Hide row and column headers: View → Show → untick Headings
5. Hide the formula bar for a cleaner look (optional): View → Show → untick Formula Bar
6. Freeze panes (if the dashboard has a header area that should stay fixed): View → Freeze Panes
7. Set zoom level: View → Zoom → choose a percentage that fits the entire dashboard on one screen (typically 80–90%)
8. Protect the sheet once complete so users cannot accidentally edit the layout: Review → Protect Sheet

Creating a Header Banner

1. Select rows 1–4 (or whatever depth you want for the header)
2. Fill with the corporate dark colour (e.g., #1A4D1A for dark green)
3. In a merged cell spanning the width: type the dashboard title in white bold text
4. In a smaller merged cell on the right: add ="Last updated: "&TEXT(TODAY(),"dd mmmm yyyy") as a live update indicator
5. Add the company logo using Insert → Pictures in the left area of the header

Creating KPI Card Areas

KPI cards are small highlighted boxes showing a single key number with a label. A typical dashboard has 4–6 KPI cards in a row below the header:

1. Merge a group of cells (e.g., B6:D9) for the first card
2. Fill the merged area with a colour (white or a light accent colour)
3. In the merged cell: enter a formula referencing the KPI value from the Calculations sheet (e.g., =Calculations!B2)
4. Format the number in the KPI cell (large font, bold, Rand currency or %)
5. In the row below the KPI cell: add a label in smaller text (e.g., "Total Revenue Q1")
6. Repeat for each KPI: Total Revenue, YTD Growth %, Active Customers, Avg Order Value, etc.

26.3 Slicers — Advanced Dashboard Controls

Slicers are the interactive controls of the dashboard. When a user clicks a Slicer button, all connected Pivot Tables and their Pivot Charts update simultaneously.

Inserting and Positioning Slicers

1. Click inside any Pivot Table in the workbook
2. PivotTable Analyse → Filter → Insert Slicer
3. Select all fields you need Slicers for (e.g., Region, Product Category, Year) → OK
4. Each slicer appears as a floating panel → drag and position them on the Dashboard sheet (not on the Calculations sheet)
5. Resize: drag corners for proportional resize, or Slicer tab → Size for precise dimensions
6. Align: select multiple Slicers (Ctrl+click) → Slicer tab → Arrange → Align → Align Top / Align Left / Distribute Horizontally for pixel-perfect alignment

Connecting One Slicer to All Pivot Tables

1. Click the Slicer
2. Slicer tab → Slicer group → Report Connections
3. Tick every Pivot Table on the Calculations sheet that this Slicer should control
4. Click OK
5. Now one Slicer click simultaneously filters all connected Pivot Tables — and all Pivot Charts linked to those Pivot Tables update too

Customising Slicer Appearance

The Timeline Slicer for Date Filtering

1. Click inside a Pivot Table that has a date field
2. PivotTable Analyse → Filter → Insert Timeline → select the date field → OK
3. The Timeline appears as a scrollable time bar
4. Use the time unit dropdown (top-right of the Timeline): Days, Months, Quarters, Years
5. Drag the selection handles to define the date range — the Pivot Tables and Charts update instantly
6. Timeline tab: connect to multiple Pivot Tables via Report Connections — same as regular Slicers
7. Style: Timeline tab → Timeline Styles for colour matching

26.4 Charts on Dashboards

Chart Placement Principles

• Position charts on the Dashboard sheet by dragging them from the Calculations sheet, or by copying and pasting them onto the Dashboard sheet
• Hold Alt while dragging or resizing charts to snap their edges precisely to cell gridlines — essential for a clean, aligned layout
• Make all charts the same height for visual consistency: select multiple charts (Ctrl+click each) → Chart Format tab → Size group → set identical Height value

Removing Chart Clutter for Dashboards

Charts on dashboards need to be stripped of unnecessary elements to keep the visual clean:

• Right-click the chart → Format Chart Area → Border: No line (removes chart border)
• Click the Plot Area → Format Plot Area → Fill: No fill (transparent plot area)
• Remove gridlines: click any gridline → Delete key
• Remove legend if data labels are shown on the chart
• Remove chart title if the dashboard context makes it redundant (a nearby KPI label serves the same purpose)
• Set chart background to match dashboard background: Format Chart Area → Fill: Solid fill → same colour as dashboard

Linked Pictures (Camera Tool) for Seamless Layout

When chart placement alongside KPI cards is difficult, use a Linked Picture to embed a live snapshot of the Calculations sheet's chart or table directly into the Dashboard layout:

1. On the Calculations sheet: select the chart or range you want to embed
2. Copy (Ctrl+C)
3. Navigate to the Dashboard sheet
4. Home → Clipboard → Paste dropdown ▼ → Linked Picture
5. The image appears on the Dashboard and updates automatically whenever the source changes
6. Resize and position like any object

26.5 KPI Formulas for Dashboard Cards

Dashboard KPI cards pull their values from the Calculations sheet. These are the most common KPI formulas used in SA business dashboards.

Common SA Dashboard KPIs

GETPIVOTDATA — Extracting Specific Values from a Pivot Table

GETPIVOTDATA extracts a specific cell value from a Pivot Table by field name rather than by cell address. This makes dashboard KPI formulas robust — they still work even if the Pivot Table is rearranged or rows are added above it.

To generate GETPIVOTDATA automatically: with a Pivot Table active, click any value cell in the Pivot Table → type = in another cell → click the Pivot Table value cell. Excel inserts GETPIVOTDATA automatically with the correct field/value syntax.

26.6 Building a Complete Dashboard — Step by Step

A practical walkthrough for a Regional Sales Performance Dashboard for a South African retail company.

Phase 1: Prepare the Data

1. Ensure source data is on a sheet named "Data" formatted as an Excel Table named "SalesData"
2. Columns: Date | Region | Salesperson | Product | Category | Revenue | Units | Cost
3. Add helper columns if needed: Month (=TEXT(Date,"mmmm")), Quarter (=SWITCH(MONTH(Date),1,"Q1",2,"Q1",3,"Q1",4,"Q2",5,"Q2",6,"Q2",7,"Q3",8,"Q3",9,"Q3",10,"Q4",11,"Q4",12,"Q4")), Year (=YEAR(Date))

Phase 2: Build the Calculations Sheet

1. Insert a new sheet named "Calculations"
2. Create Pivot Table 1: Revenue by Region × Month (grouped from Date)
3. Create Pivot Table 2: Revenue by Category × Quarter
4. Create Pivot Table 3: Top 10 Salespersons by Revenue
5. Create Pivot Table 4: Product mix (pie chart data)
6. For each Pivot Table, apply number formatting (Rand currency), add calculated fields where needed (Profit = Revenue - Cost)
7. Build Pivot Charts linked to each Pivot Table on this same sheet
8. Add SUMIF/SUMIFS formulas for the KPI card values (Current Month, YTD, Target)

Phase 3: Design the Dashboard Sheet

1. Insert a new sheet named "Dashboard" → move it to be the first tab
2. Hide gridlines and headings (View → Show)
3. Set uniform row heights and a background colour
4. Create the header banner (rows 1–4): title, date stamp, logo
5. Rows 5–10: KPI card row — 5 to 6 KPI boxes with live formulas referencing Calculations sheet
6. Rows 11–30: primary chart row — Revenue trend line chart on the left, Category bar chart on the right
7. Rows 31–50: secondary chart row — Regional map or column chart on the left, Top 10 Salesperson bar chart on the right
8. Move or copy Pivot Charts from Calculations sheet to Dashboard sheet (right-click chart → Move Chart → Object In → Dashboard)

Phase 4: Add Slicers and Timeline

1. From any Pivot Table on the Calculations sheet: PivotTable Analyse → Insert Slicer → tick Region, Category, Year
2. Move each Slicer to the Dashboard sheet
3. Connect each Slicer to ALL Pivot Tables via Report Connections
4. Insert Timeline → Date field → move to Dashboard sheet → connect to all Pivot Tables
5. Position Slicers in a control panel area (top-right or left sidebar)
6. Apply custom Slicer styles matching dashboard colours

Phase 5: Polish and Protect

1. Align all charts to the same row boundaries (Alt+drag)
2. Test all Slicer combinations — verify all charts update correctly
3. Add a "Reset All Filters" macro or a shape with a hyperlink to clear all Slicers
4. Right-click the Calculations and Data sheet tabs → Hide (so users see only the Dashboard sheet)
5. Review → Protect Sheet (allow only selecting unlocked cells — prevents accidental editing)
6. File → Properties → set the opening sheet to Dashboard

26.7 Clearing Slicers & Resetting the Dashboard

Clearing Individual Slicers

• Click the × Clear Filter button at the top-right of the Slicer panel — or —
• Click the Slicer → press Alt+C — or —
• Right-click the Slicer → Clear Filter

Reset All Slicers with a Macro Button

Adding a "Reset All Filters" button improves usability for non-technical users. Create a simple VBA macro:

Controlling What Users Can Do

26.8 Quick Self-Check

Q1: You have two Pivot Charts on a dashboard — one showing revenue by region and one showing revenue by product category. A single Region Slicer should control both charts. How do you configure this?

✓ Each Pivot Chart is linked to its own Pivot Table on the Calculations sheet. To connect one Slicer to both: click the Region Slicer → Slicer tab → Slicer group → Report Connections → tick BOTH Pivot Tables (the one feeding the region chart AND the one feeding the category chart) → OK. Now clicking "Gauteng" on the Slicer simultaneously filters the region Pivot Table (and its chart) to Gauteng data only AND the category Pivot Table (and its chart) to show only Gauteng's category breakdown. This is the fundamental mechanism of multi-chart interactive dashboards in Excel.

Q2: Your dashboard has a KPI card showing "Current Month Revenue" that is linked to a Pivot Table cell by formula. When you rearrange the Pivot Table (add a new row field), the formula breaks and shows the wrong number. How do you make it robust?

✓ Replace the direct cell reference with a GETPIVOTDATA formula. GETPIVOTDATA extracts values from a Pivot Table by field name and value, not by cell address — so it is immune to Pivot Table restructuring. Example: instead of =Calculations!C5, use =GETPIVOTDATA("Revenue",$B$3,"Month",TEXT(TODAY(),"mmmm")) where $B$3 is any cell inside the Pivot Table. Even if the layout changes (rows move, columns are added), GETPIVOTDATA always finds the correct Revenue value for the current month. To generate it automatically: type = in the KPI cell → click the target cell in the Pivot Table → Excel automatically creates the GETPIVOTDATA formula.

Q3: A dashboard user says all the Slicer buttons work except the region buttons are greyed out after they click "Q1" on the Quarter Slicer. What does this mean and should you fix it?

✓ This is the intended behaviour of connected Slicers, not a bug. When the Quarter Slicer is filtered to Q1, the Region Slicer checks which regions have data in Q1. Any region with no transactions in Q1 shows its button greyed out (but still visible) indicating there is no data matching that combination. This is a useful data integrity indicator — it tells the user which filter combinations are valid. If you want to completely hide the greyed buttons instead of showing them dimmed: right-click the Slicer → Slicer Settings → tick "Hide items with no data". The greyed-out buttons disappear entirely when no data exists for that combination.

Q4: When you build a dashboard, why should the Dashboard sheet contain no complex formulas or raw data, only references to the Calculations sheet?

✓ Separating layers serves several critical purposes: (1) Maintainability: if the calculation logic needs to change, you update it in one place (Calculations sheet) not scattered across the Dashboard; (2) Performance: complex formulas and large Pivot Tables on a hidden sheet calculate in the background; the Dashboard sheet loads quickly because it only contains references and display objects; (3) Protection: the Calculations and Data sheets can be password-protected or hidden, while the Dashboard sheet is unprotected for Slicer interaction; (4) Clarity: any formula on the Dashboard that a developer opens is a simple reference like =Calculations!B5, not a nested SUMIFS or GETPIVOTDATA that must be deciphered; (5) Debugging: if a KPI shows the wrong number, the issue is either in the source data (Data sheet) or the calculation logic (Calculations sheet), narrowing the diagnostic scope immediately.

Q5: You want to add a "Reset All Filters" button on the Dashboard so users can clear all Slicers with one click without having to click the × button on each Slicer individually. Describe how to implement this.

✓ Step 1: Enable the Developer tab (File → Options → Customise Ribbon → tick Developer). Step 2: Developer → Code → Visual Basic (or Alt+F11) → Insert → Module → type the macro: Sub ResetAllSlicers() / Dim sc As SlicerCache / For Each sc In ThisWorkbook.SlicerCaches / sc.ClearManualFilter / Next sc / End Sub. Step 3: Close the VBA editor. Step 4: On the Dashboard sheet: Insert → Shapes → draw a rectangle → type "Reset All Filters" inside it → format to match the dashboard style. Step 5: Right-click the shape → Assign Macro → select ResetAllSlicers → OK. Now clicking the shape runs the macro which clears all Slicer selections simultaneously. Save the file as .xlsm (macro-enabled workbook) to preserve the macro.

Q6: Describe three specific formatting changes you would make to a Pivot Chart before placing it on a dashboard, and explain why each change improves the dashboard.

✓ (1) Remove the chart border: right-click Chart Area → Format Chart Area → Border: No line. A chart with a visible border looks like a box inserted on the dashboard; removing the border makes it appear to float seamlessly on the dashboard background, creating a more cohesive design. (2) Set the chart background to match the dashboard background colour: Format Chart Area → Fill: Solid fill → same colour as dashboard. This blends the chart into the dashboard rather than making it look like a separate embedded object. (3) Remove gridlines from the plot area and remove the legend if data labels are shown: click any gridline → Delete; click the legend → Delete. Gridlines and legends are cognitive overhead — they require the reader to cross-reference between the legend and the data. Direct data labels on bars/lines remove this effort and make the chart faster to read. The dashboard should communicate instantly; every unnecessary element slows comprehension.

✓ Module 26 Complete — You Have Learned:

• Dashboard design principles — the three dashboard questions (audience, decision, update frequency); layout principles (top-left prime real estate, summary-to-detail flow, grouped controls, consistent alignment, limited colour palette, no chart clutter); three-sheet structure (Data, Calculations, Dashboard)
• Dashboard sheet setup — hide gridlines and headings (View → Show); uniform row heights; background colour; header banner with logo, title, and live date stamp; KPI card design (merged cells, large number, label below); Freeze Panes; Zoom; sheet protection
• Slicers in depth — inserting from Pivot Table; moving to Dashboard sheet; Report Connections (connecting one Slicer to multiple Pivot Tables); customising appearance (Slicer Styles, Columns for grid layout, Caption/header, Sort order, Hide items with no data); Timeline for date fields (Days/Months/Quarters/Years, Report Connections)
• Charts on dashboards — Alt+drag to snap to cell gridlines; matching chart heights; removing chart clutter (border, plot area fill, gridlines, legend); background colour matching dashboard; Linked Picture / Camera tool for seamless cross-sheet embedding
• KPI formulas — SUMIF/SUMIFS for current month, YTD, target; GETPIVOTDATA (extracts specific Pivot Table values by field name, immune to layout changes; auto-generate by clicking Pivot Table cell after typing =); GETPIVOTDATA syntax (field name, pivot ref, field/value pairs)
• Complete dashboard build — 5-phase workflow: Phase 1 (prepare data as Table with helper columns); Phase 2 (Calculations sheet: 4 Pivot Tables, Pivot Charts, KPI formulas); Phase 3 (Dashboard sheet: header, KPI cards, chart layout); Phase 4 (Slicers and Timeline: insert, move, connect, style); Phase 5 (polish: align, test, hide sheets, protect, opening sheet)
• Clearing and resetting — individual Slicer clear (× button, Alt+C); VBA ResetAllSlicers macro; assigning macro to a shape button; saving as .xlsm
• User access control — Review → Protect Sheet (allow PivotTable/Slicer interaction while blocking edits); Protect Workbook Structure (prevents unhiding sheets); hiding Data and Calculations tabs; VBA Workbook_Open for auto-activation of Dashboard