The word dashboard might conjure images of complex Power BI reports or elaborate coded applications. But Excel is capable of producing truly interactive, visually compelling dashboards entirely without writing a single line of VBA. Using pivot tables, slicers, pivot charts, and a handful of smart design techniques, you can build dashboards that let users explore data by clicking buttons — no programming required.
This tutorial uses features available in Microsoft Office 2024 Professional Plus for Windows (€34.99 at GetRenewedTech), though most techniques work in Excel 2019 and later. Mac users can follow along with Office 2024 for macOS (€58.99).
Planning Your Dashboard Layout
Before touching Excel, sketch your dashboard on paper or a whiteboard. Decide:
- What are the three to five key metrics your audience needs to see?
- What dimensions do they need to filter by (date, region, product, team member)?
- What chart types best represent each metric?
- How much space do the filters (slicers) need?
A good dashboard answers a specific question — such as how this month’s sales performance compares to last month by region — rather than trying to show everything at once. Clarity beats completeness.
Setting Up Your Data as a Structured Table
The foundation of any Excel dashboard is clean, structured data. Click anywhere in your data range and press Ctrl+T to format it as a table. Give the table a descriptive name in the Table Design tab (e.g., SalesData). Tables have two major advantages for dashboards: they expand automatically as you add new data, and pivot tables connected to them can be refreshed to include new rows without rebuilding anything.
Building Pivot Tables as Your Data Engine
Pivot tables are the engine behind most Excel dashboards. Go to Insert > PivotTable, select your named table as the source, and place it on a dedicated Calculations sheet rather than the dashboard sheet itself. This keeps the dashboard clean and the calculations hidden from end users.
Create separate pivot tables for each metric your dashboard displays. For example, one pivot table might summarise total sales by month, another by region, and another by product category. Use the Value Field Settings (right-click any value in the pivot table) to switch between sum, count, average, or percentage of total as needed.
Creating Pivot Charts
With a pivot table selected, go to Insert > PivotChart. Choose an appropriate chart type for each metric. Bar charts work well for comparisons, line charts for trends over time, and doughnut or pie charts for proportional breakdowns. Format each chart on the Calculations sheet, then cut and paste it onto your Dashboard sheet.
Once on the dashboard, resize and position the charts so they form a coherent layout. Remove gridlines from the dashboard sheet via View > Gridlines to give it a clean, report-like appearance. Set the sheet tab colour and hide the row and column headers for a more polished look.
Adding Slicers for Interactivity
Slicers are the key to making a dashboard interactive without any code. Click any pivot table, go to Insert > Slicer, and choose which fields to slice by. Common slicer choices are Date (using a Timeline slicer), Region, Product Category, and Salesperson.
The critical next step is connecting each slicer to all the pivot tables on your dashboard. Right-click the slicer and choose Report Connections (called PivotTable Connections in some versions). Tick every pivot table that should respond to this slicer. Now when a user clicks a button on the slicer — say, North West under Region — every chart and summary table on the dashboard updates simultaneously.
Format slicers via the Slicer tab to match your colour scheme. You can set the number of columns in a slicer (useful for months or categories with many items), adjust button size, and apply custom styles.
Using GETPIVOTDATA for KPI Tiles
To display headline numbers — total revenue, number of orders, average order value — as large text tiles at the top of your dashboard, use the GETPIVOTDATA function. This function pulls a specific value from a pivot table, even if the table is on another sheet. The syntax is automatically generated when you click a cell in a pivot table from within a formula.
Format the cells containing these figures with a large, bold font and a contrasting background colour to create KPI tiles. Consider adding conditional formatting to show whether the number is above or below target.
Dynamic Chart Titles with Cell References
Static chart titles are a missed opportunity. To make a chart title reflect the current slicer selection, click the chart title, then type = in the formula bar and click a cell that contains the relevant label. That cell can use a formula like =TEXT(MAX(SalesData[Date]),"MMMM YYYY") to display the latest month in the dataset. Now when the data refreshes, the title updates too.
Protecting the Dashboard
Once your dashboard is complete, protect the dashboard sheet to prevent accidental disruption. Go to Review > Protect Sheet. Slicers will still work for users even when the sheet is protected, so filtering remains fully functional. Hide the Calculations sheet to keep the backend invisible: right-click the sheet tab and select Hide.
Refreshing the Dashboard
When new data arrives, paste it into your source table (it will expand automatically) and then right-click any pivot table and select Refresh All. All pivot tables, charts, and KPI tiles update instantly. For data sourced via Power Query, the refresh can be triggered by the Power Query connection, keeping the entire process automated.
With Office 2024 Professional Plus you also get access to newer dynamic array functions like FILTER, SORT, and UNIQUE, which open up additional dashboard-building techniques beyond pivot tables. These allow you to create live filtered views that update as you type into a search cell — a powerful complement to slicer-based filtering.
