Excel dashboards often look perfect on the first day, then quietly become incorrect a month later. The cause is rarely complex formulas. Usually, the source data increases, while the formulas, PivotTables, or charts above still point to the old cell range. The numbers still appear neat, but they are no longer complete.
Regular cell ranges do not grow
Formulas like =SUM(C2:C500) only know up to row 500. According to Microsoft documentation, explicit cell references like =C2*D2 also do not adjust: if you insert a column between C and D, that formula must be manually revised. In monthly dashboards, such errors easily go unnoticed because they do not display any error messages.
Convert data to Excel Table
Select a cell within the data, then go to Insert > Table. Microsoft itself recommends that PivotTable sources be in the form of Excel tables with one header row, each column uniquely labeled and not empty, and avoiding duplicate headers and merged cells.
The main benefit lies in structured references. Microsoft explains that names in structured references adjust every time data is added or removed from the table. So =SUM(Penjualan[Nilai]) remains correct after new rows are added, while =SUM(C2:C7) does not. In a table, [@Nilai] refers to the cell in the same row.
Calculated columns also help: write a formula once in one cell, and Excel applies it to the entire column without the need for copy commands.
PivotTable: new data does not automatically enter
This is the part that is most often misunderstood. According to Microsoft’s guidelines, new data does not automatically appear in the PivotTable. After the source expands, you need to press Refresh (right-click on the PivotTable, or Alt+F5), or Refresh All if there are multiple PivotTables. The guidelines do not explicitly state whether the source range expands automatically, so test it by adding a trial row before trusting the dashboard.
If the source is still a regular range, it can be expanded through PivotTable Analyze > Change Data Source. Microsoft also notes that if the number of columns changes significantly, it is better to create a new PivotTable. One other limitation: Data Model-based PivotTables cannot change their data source through this route.
Two settings in Options are worth checking:
- Refresh data when opening the file (Data tab), to ensure numbers are updated every time the file is opened.
- Autofit column widths on update and Preserve cell formatting on update (Layout & Format tab), to keep the layout intact after refresh.
Microsoft mentions an Auto Refresh feature in newer versions of Excel, but that page notes it is only available to participants in the Microsoft 365 Insider program. Do not rely on it.
Slicer connected to the same source
A slicer is a filter button for tables or PivotTables. One slicer can be connected to another PivotTable through Report Connections, provided both are based on the same data source. Therefore, if your dashboard contains multiple PivotTables, make all of them derived from one table so that one slicer controls them all.
Checklist before sharing the dashboard
- The source data is already in a named table, not a plain range.
- Formulas outside the PivotTable use table names, not cell addresses.
- Add a test row, press Refresh All, and ensure the total increases by that row.
- Remove the test row, refresh again, and ensure the total returns.
- Check that the refresh option when opening the file is set according to your needs.
The habit of testing with an additional row is inexpensive and catches most cases where the dashboard is quietly incomplete.
Sources
- Using structured references with Excel tables, Microsoft Support
- Create a PivotTable to analyze worksheet data, Microsoft Support
- Change the source data for a PivotTable, Microsoft Support
- Refresh PivotTable data, Microsoft Support
- Use slicers to filter data, Microsoft Support
- Use calculated columns in an Excel table, Microsoft Support