Excel Advanced - Building an Excel Dashboard

Learning outcomes

Plan the dashboardDefine the audience, questions, KPIs and visual hierarchy before formatting.

Build reliable KPIsLink summary cells to PivotTables and preserve currency and percentage formats.

Coordinate interactionConnect slicers and a timeline to compatible PivotTables and PivotCharts.

Deliver one clear pageAlign objects, reduce clutter, test filters and write an evidence-based conclusion.

Download the practice dataset

This lesson continues with the same 120 fictional transactions used in Lessons 8 and 9. The generated workbook contains Sales Data, Dashboard Plan and Controls sheets.

120orders

RM146,236revenue

RM56,646profit

38.7%profit margin

Recommended workflow: convert Sales Data into an Excel Table named tblSales. Keep raw data, PivotTables and the dashboard on separate worksheets so the presentation layer stays clean.

1. Define the management questions

ACTIVITY 1

Write a dashboard brief

Assume the audience is a sales manager who needs a monthly performance overview. Write the questions before choosing charts:

How much revenue and profit have we generated?
What is the overall profit margin and average order value?
Which region contributes the most revenue?
How does revenue change by month?
Which product category produces more profit?
How do results change by Channel and reporting period?

Dashboard discipline: a dashboard is not a collection of every available chart. Each object must answer a named business question.

I wrote the audience, reporting period and six management questions.

2. Audit and prepare the source

ACTIVITY 2

Validate the dashboard foundation

Open Sales Data and confirm one header row and 120 uninterrupted records.
Check that Date values are real dates and financial fields are numbers.
Press Ctrl+T, confirm My table has headers and name the Table tblSales.
Create a worksheet named Dashboard and another named Pivot Support.
Build all supporting PivotTables on Pivot Support, not on the dashboard.

Control	Expected
Orders	120
Revenue	RM146,236
Profit	RM56,646
Average order value	RM1,218.63
Profit margin	38.7%

My source passes every control and is named tblSales.

3. Build KPI cards

ACTIVITY 3

Create five executive measures

Create a one-cell PivotTable value for Total Revenue and another for Total Profit.
Create an Order Count using Sale ID in Values and set it to Count.
Calculate Average Order Value as Revenue ÷ Orders.
Calculate Profit Margin as Profit ÷ Revenue.
On Dashboard, link each card to its supporting value. Excel may create GETPIVOTDATA automatically.
Apply RM, whole-number and percentage formats appropriately.

RM146,236Total Revenue

RM56,646Total Profit

120Orders

RM1,218.63Average Order Value

38.7%Profit Margin

Example formulas

If Revenue is in B4, Profit in B5 and Orders in B6, use =B4/B6 for average order value and =IFERROR(B5/B4,0) for margin. Prefer references or GETPIVOTDATA rather than typing totals into the dashboard.

All five KPI cards link to calculations and match the controls.

4. Create the supporting visuals

ACTIVITY 4

Build three PivotCharts

Revenue by Region: Region in Rows, Revenue in Values, Clustered Column chart.
Monthly Revenue Trend: Date grouped by Month in Rows, Revenue in Values, Line with Markers chart.
Profit by Category: Category in Rows, Profit in Values, Clustered Bar chart.
Give every chart an informative title and apply RM formatting to value axes.
Remove legends from single-series charts and hide PivotChart field buttons for presentation.

Validation: North revenue is RM59,679; Furniture profit is RM34,668; Accessories profit is RM21,978.

Keep the connection: move or copy the PivotCharts to Dashboard, but retain their supporting PivotTables on Pivot Support.

My three PivotCharts reconcile to the supporting PivotTables.

5. Add slicers and a timeline

ACTIVITY 5

Coordinate dashboard filters

Select a PivotTable and insert a Channel slicer.
Insert a Date timeline and set its level to Months or Quarters.
Open Report Connections or PivotTable Connections for each control.
Select every compatible PivotTable used by the KPIs and charts.
Test Corporate, Online, Retail, multi-select and Clear Filter.
Test several date periods and confirm every connected object updates.

Unfiltered channel totals: Corporate RM56,728; Online RM50,154; Retail RM39,354.

If a connection is missing: the PivotTables may use different sources or caches. Recreate them from the same tblSales Table.

The Channel slicer and Date timeline update all intended objects.

6. Arrange the one-page layout

ACTIVITY 6

Build a visual hierarchy

Set View → uncheck Gridlines, then use this layout as a starting point:

Dashboard title and reporting period

Revenue KPI

Profit KPI

Orders KPI

Margin KPI

Monthly Revenue Trend

Revenue by Region

Profit by Category

Channel slicer

Date timeline

Management conclusion

Use one consistent outer margin and spacing rhythm.
Make KPI cards equal in size and align chart edges.
Keep slicers away from titles, labels and plotted data.
Use Selection Pane to rename and manage overlapping objects.

My dashboard fits on one screen without overlapping objects.

7. Apply dashboard design standards

ACTIVITY 7

Make the page presentation-ready

Use
One font family, one accent colour, short titles, aligned objects and consistent RM formats.

Avoid
3-D charts, decorative gauges, excessive borders, rainbow colours and duplicated legends.

Use a white or very light background with dark text.
Reserve one accent colour for emphasis and filter selection.
Keep chart scales honest; do not truncate axes to exaggerate differences.
Add Alt Text to charts and ensure colour is not the only meaning cue.
Set the dashboard print area and choose Landscape orientation with Fit Sheet on One Page.

My dashboard is readable, restrained, accessible and print-ready.

8. Test, conclude and hand over

ACTIVITY 8

Complete the dashboard acceptance test

Use Data → Refresh All and confirm all totals still reconcile.
Apply each Channel selection and at least three date periods.
Clear every filter and confirm the unfiltered KPIs return.
Add one new test transaction to tblSales, refresh, confirm inclusion, then remove the test row.
Check the dashboard at 100% zoom and in Print Preview.
Write a concise management conclusion and a recommended follow-up question.

Example conclusion: North is the strongest region by revenue, while Furniture contributes more profit than Accessories. Management should filter by Channel and month to determine whether these results are broad-based or concentrated in a few periods.

Handover note: record the source Table name, refresh procedure, reporting period, control totals and Excel-version limitations.

My dashboard passed refresh, interaction, reconciliation and print tests.

Troubleshooting

KPI does not change
Link it to the PivotTable result or GETPIVOTDATA and connect that PivotTable to the filter.

Timeline will not appear
Confirm the source field contains valid dates. Timelines require Excel 2013 or later.

New rows are missing
Use tblSales as the source, then choose Refresh All.

Objects move or resize
Use Size and Properties to control placement and align objects from the Shape Format tab.

Knowledge check

1. Why keep PivotTables away from the dashboard sheet?

It separates calculations from presentation, reduces clutter and makes maintenance safer.

2. Why should KPI cards link to cells instead of containing typed totals?

Linked values update after refresh and filtering; typed totals become stale.

3. What must be true for one slicer to control several PivotTables?

The PivotTables must use a compatible shared source or PivotCache and be selected in Report Connections.

4. What is the final dashboard quality test?

It must reconcile, refresh, respond correctly to controls, remain readable and support a clear business conclusion.

Completion checklist

Source: tblSales with 120 records
KPI cards: Revenue, Profit, Orders, Average Order Value and Margin
Charts: regional comparison, monthly trend and category profit
Controls: Channel slicer and Date timeline
Layout: one-page management dashboard
Quality: refresh-tested, reconciled, accessible and print-ready
Insight: written conclusion and follow-up question

Extension complete: Lesson 10 turns the analytical components from Lessons 8 and 9 into a maintainable executive dashboard.