iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
To make an Excel performance dashboard update, first decide where its data lives: changes to a workbook table, a PivotTable refresh, and a query that retrieves data from an external source are different events. A dashboard can refresh on demand or when the workbook opens; that does not necessarily make it live or real-time. The exact options depend on your Excel platform and version.
What an Excel performance dashboard should show
A performance board, or dashboard, puts the metrics that matter for a decision into one visual view. Microsoft describes a dashboard as “a visual representation of key metrics” that lets you view and analyze data in one place. Its Excel tutorial combines PivotTables, PivotCharts, slicers, and a timeline to make a dashboard users can filter.
Before formatting, define the audience and the decision the board should support. Choose a small set of relevant KPIs—often three to six is a manageable starting point—and write down each metric’s formula, unit, comparison period, and target or status rule. There is no universal target that makes a KPI good; set thresholds for your organization and use case.
Do these 3 things before closing this tab:
1Scan for outdated or missing drivers - takes under a minute2Clear out junk files and repair common Windows errors3Fix the driver behind crashes, sound loss and screen glitchesPrepare a reliable source table
Good summaries depend on a consistent source. Arrange the data as a rectangle with a clear heading for each column and one record per row. Keep dates and category labels consistent, and avoid blank rows or columns within the data range. Microsoft’s dashboard guidance specifically recommends one row per record and no missing rows or columns.
#1 Best Overall
- The Microsoft Office 365 Bible: The Most Updated and Complete Guide to Excel, Word, PowerPoint, Outlook, OneNote, OneDrive, Teams, Access, and Publisher from Beginners to Advanced
- ABIS BOOK
For data maintained in the same workbook, convert the source range to an Excel table. A table provides a structured source for summaries and can expand as records are added. Put new or pasted records in that source table—not in a worksheet populated by a query, which can be overwritten when the query refreshes.
Choose the refresh route for your data
| Situation | Suggested route | What triggers an update | Key qualification |
|---|---|---|---|
| Data is maintained in the workbook | Excel table with PivotTables and dashboard visuals | Refresh the PivotTable or use an available automatic refresh option | PivotTable refresh behavior depends on its settings and Excel version. |
| Data comes from a file, database, or repeatable import and cleanup process | Power Query, loaded to a worksheet table or Data Model, then summarized | Refresh the query, manually or through an available refresh setting | Connectors and refresh support vary by platform and version. |
| You need an on-demand update | Refresh or Refresh All | A person starts the refresh | External connections must be available and permitted to retrieve data. |
| You want current data when opening the workbook | Configure refresh on open where supported | The workbook opens and the refresh runs | This is not continuous updating; the connection must work when opened. |
Power Query can connect to or import external data, shape it, and load it into Excel. Its transformation steps are reapplied when the query refreshes, so a repeatable cleanup process does not have to be repeated manually. Microsoft documents Power Query across Windows, Mac, and the web, but support is not identical: its overview says Power Query is not supported on Excel 2016 or 2019 for Mac, and lists particular Mac refresh sources, including TXT, CSV, XLSX, JSON, XML, SQL Server, and tables or ranges in the current workbook. The same overview says Excel for the web gained refresh from authenticated data sources in 2025. Check the current documentation and your specific Excel build before relying on a connector or refresh behavior. Microsoft: About Power Query in Excel
Build summaries and dashboard visuals
Create PivotTables for the measures you defined
Use PivotTables to summarize the source table into the totals, rates, and comparisons that match your KPI definitions. For example, a time-based measure might be grouped by month, while a category comparison might place categories in rows and a chosen measure in values. Confirm the aggregation is appropriate: a rate or percentage may need a calculated measure rather than a sum of row-level percentages.
The Tool Desk
Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Add charts and interactive filters
Use PivotCharts to make changes over time or differences among categories easier to interpret. Add slicers for categorical filters and a timeline for dates when supported in your Excel version. Microsoft’s sample uses four PivotTables and charts from one source; that is an example, not a required count. Keep labels, units, and the comparison period visible so users can interpret the result without guessing.
Make the dashboard refresh—and know what that means
Refresh a PivotTable manually
When the source has changed, select a PivotTable and use Refresh, or use Refresh All to update workbook connections and summaries. In Excel, these commands are available from the PivotTable Analyze tab or from the right-click menu for a PivotTable; exact labels and placement can vary by platform and build. Microsoft’s instructions cover the supported versions and refresh options. Microsoft: Refresh PivotTable data
Refresh when the workbook opens
For supported PivotTable connections, configure the option to refresh data when the file opens in the PivotTable’s data connection properties. This makes the workbook request an update at opening; it does not keep the dashboard continuously synchronized while it remains open. A failed connection, unavailable source, or authentication requirement can prevent the refresh from retrieving new data.
Understand the newer Auto Refresh option
Microsoft’s current PivotTable refresh documentation describes a newer Auto Refresh option for local workbook data, but says it is currently available to Microsoft 365 Insider participants. Do not assume that every Excel installation has this control or that it applies to every external connection. Availability can change as Microsoft rolls out features, so check the current documentation for your platform and build. Microsoft: Refresh PivotTable data
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Refresh a Power Query import
When a query is the source, refreshing the query retrieves data from its source and reapplies the query’s transformations. Use the query’s refresh control or Refresh All as appropriate. Microsoft’s walkthrough explains how to add data and refresh a query. Microsoft: Add data and then refresh your query
Best Value
These are distinct stages: editing a source table changes the underlying records; refreshing a PivotTable updates its summary from its available source; refreshing an external query connects to the source and retrieves data before downstream summaries can reflect it. A dashboard is only as current as the last successful update in the path it uses.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Validate the board before relying on it
- Add a test record to the real source. For a workbook table, add it to the source table. For Power Query, change the upstream file or source rather than typing into the query output.
- Run the relevant refresh. Refresh the query if data is external, then refresh PivotTables if they summarize the loaded results; Refresh All may handle multiple connections and summaries.
- Check the KPI and chart. Confirm that the new record affects the expected measure, period, and category, and that slicers or the timeline do not hide it.
- Inspect boundary and data-quality cases. Check date cutoffs, blank values, duplicate records, inconsistent labels, and whether totals and rates use the intended calculation.
Microsoft also provides a tutorial workbook for creating and sharing an Excel dashboard. Microsoft: Create and share a Dashboard with Excel and Microsoft Groups
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.
Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Clear out junk files and repair common Windows errorsFree Scan →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →
