Create an interactive Excel dashboard by preparing a clean data table, summarizing it with PivotTables, visualizing those summaries with PivotCharts, and adding slicers to filter the reports. The essential extra step is connecting each slicer to every PivotTable it should control; a slicer does not automatically filter every report in the workbook.
1. Prepare the data for a dashboard
Start with a source table that Excel can summarize reliably. Microsoft recommends one header row, one record per row, and no missing rows or columns in the data range. Format the range as an Excel Table so it can serve as the basis for the reports. See Microsoft’s dashboard walkthrough.
As an Amazon Associate I earn from qualifying purchases.
- Give each column a clear heading, such as Date, Category, Customer, and Sales.
- Keep each row to one record and avoid blank rows or columns within the source.
- Format the source range as a Table before creating the summaries.
2. Create PivotTables for the views you need
A PivotTable supplies the summarized data behind a dashboard view. Microsoft describes it as “an interactive way to quickly summarize large amounts of data” in its PivotTables and PivotCharts overview.
PC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minute- Select a cell in the source table.
- Insert a PivotTable and place it on a new worksheet.
- Arrange the fields to create the first useful summary—for example, sales by category or by month.
- Format the summary, then create additional PivotTables for other metrics or perspectives. Copying the initial PivotTable can be a convenient starting point when the reports use the same source.
Name the PivotTables meaningfully so they are easier to identify when you connect filters. Leave open space around each one: PivotTables can grow or shrink when filters or source data change, and they cannot overlap. A single PivotTable may be enough for a simple dashboard; use multiple summaries when readers need several different metrics or views.
#1 Best Overall
3. Add charts that match the summaries
Create a PivotChart from each summary that benefits from a visual representation. Select the chart type that suits the data, then size and format the chart for the dashboard. Microsoft’s walkthrough illustrates sales as clustered columns and percentage of total as a line on a secondary axis; that is an example, not a rule for every dataset. For setup details, see Microsoft’s PivotChart guidance.
PivotCharts are useful when the chart should follow the PivotTable’s filtering and pivot-field behavior. A standard chart may be appropriate when that interaction is not needed. On Mac, Microsoft’s documented PivotChart workflow starts by creating a PivotTable and supports a more limited set of chart types than the general workflow. Check the current guidance for your Excel edition and platform before relying on a particular chart type.
Rank #2
4. Insert slicers for the filters readers need
Add slicers for fields people should be able to filter, such as category or customer. A slicer is a visible set of buttons: selecting a button filters its connected report, and the control displays the current filter state. Microsoft documents slicer behavior and platform support in Use slicers to filter data.
Arrange and resize slicers near the charts they affect. Use only the controls that help readers answer the dashboard’s intended questions; a dense cluster of filters can make the view harder to use.
5. Connect each slicer to the intended reports
When you create a slicer, it initially controls the PivotTable from which it was created. To make it filter other reports, open the slicer’s report or PivotTable connections and select the additional PivotTables. Those PivotTables must share the same data source. A slicer can connect to PivotTables on other worksheets, including hidden worksheets.
- Select the slicer.
- Open its report or PivotTable connections.
- Select each compatible PivotTable that should respond to that slicer.
- Test the slicer buttons and confirm that every intended chart and summary changes.
If a PivotTable is missing from the connection list, first check whether it uses the same data source as the report that created the slicer. A workbook with multiple summaries is not automatically a workbook with synchronized filters; the connections must be configured.
Rank #4
6. Add a timeline when date filtering helps
If users need to explore results by date, insert a timeline from a date field and connect it to the PivotTables it should filter. Keep timelines and slicers visibly associated with the charts they control so users can understand which filters apply to which views.
7. Arrange, refresh, and share the dashboard
Place the charts and controls on a dashboard worksheet, and keep the underlying PivotTables in their own report area or worksheets if that makes the dashboard clearer. Allow room for PivotTables to expand or contract. After adding or changing source data, refresh the dashboard so the summaries and charts reflect the updates. Microsoft’s dashboard article also describes sharing through a Microsoft Group; the actual sharing setup depends on the user’s environment.
Best Value
Excel desktop and web: what to check
Microsoft’s dashboard and PivotChart documentation lists Excel for Microsoft 365, Excel 2024, Excel 2021, Excel 2019, and Excel 2016 for the covered workflows. Feature availability is not identical across platforms: Microsoft says Excel for the web supports creating slicers only for local PivotTables. Creating slicers for tables, Data Model PivotTables, or Power BI PivotTables requires Excel for Windows or Mac. Review the current platform-specific instructions before building a workbook that must behave identically for every user.
Choose the simplest setup that meets the need
| Choice | Use it when | Important consideration |
|---|---|---|
| One PivotTable and chart | A single summary answers the dashboard’s main question. | Fewer reports mean fewer slicer connections to configure. |
| Multiple PivotTables and PivotCharts | Readers need different metrics or perspectives in one view. | For a slicer to control multiple reports, the PivotTables must share a data source and be selected in its connections. |
| PivotChart | The chart should respond to PivotTable filters and field changes. | Chart workflow and supported types can vary by platform, particularly on Mac. |
| Standard chart | You do not need PivotChart-specific filtering or field behavior. | Choose charting behavior based on the workbook’s requirements. |
| Excel for the web | The workbook’s needed slicer is for a local PivotTable. | For slicers on tables, Data Model PivotTables, or Power BI PivotTables, use Excel for Windows or Mac. |
| Excel for Windows or Mac | You need the desktop slicer workflows described by Microsoft. | On Mac, create the PivotTable first for the documented PivotChart workflow and check chart-type support. |
For hands-on practice, Microsoft provides an interactive Excel Dashboard tutorial workbook through its dashboard guide.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →
