How to Create a Dashboard in Excel That Updates Automatically

You add this week's sales, switch to the dashboard and nothing has changed. Or the chart still stops at last month. It's one of the most common complaints about Excel dashboards, and it nearly always has one of a handful of causes.

This guide shows how to create a dashboard in Excel that updates automatically: which parts of Excel update by themselves, which need a refresh, and how to set up each part so new data flows through to every number and chart. Menu names are from Microsoft 365; Excel 2016 and 2019 use older names for some tabs.

What updates automatically in Excel, and what doesn't

An Excel dashboard is a chain: data, then calculations, then charts. Some links in the chain update the moment something changes. Others wait to be refreshed.

So there are two ways to get an automatic dashboard: build it from parts that update on their own, or make the refresh happen without anyone having to remember it. Most dashboards use a bit of both.

Step 1: Keep your data in an Excel table

Everything else depends on this. Click any cell in your data and choose Insert > Table (Ctrl+T, or Cmd+T on a Mac), then give the table a name such as Sales on the Table Design tab (the Table tab on a Mac). We cover the full setup in our guide on how to create a dashboard in Excel.

Once your data is a table:

• It grows as you add data. Type in the row directly below the last row and the table expands to include it.
• Formulas fill down by themselves. A formula typed in a table column becomes a calculated column and copies itself into every new row.
• References follow the table. A formula that uses Sales[Amount] always covers the whole column, however many rows it has.
• Charts can follow it too. A chart made from a table's columns picks up new rows as the table grows.

Two habits keep it working: add new rows directly under the last row, with no blank row in between, and paste new data into the first empty row of the table rather than further down the sheet.

Step 2: Calculate with formulas that recalculate

Formulas are the simplest way to get an automatic dashboard, because Excel recalculates them as soon as the data changes. There's no refresh to remember.

Add a helper column to the table called Month that turns each date into the first day of its month:

=EOMONTH([@Date],-1)+1

Format the column as mmm yyyy so it shows months rather than date serial numbers. Then, on a calculation sheet with the first day of each month in column A, monthly revenue is:

=SUMIFS(Sales[Amount],Sales[Month],A5)

Add a row to the table and the total for that month changes immediately, along with any chart that plots it.

In Microsoft 365 and Excel 2021 or later, dynamic array formulas go a step further and grow their own lists. This formula spills a sorted list of every channel in the data:

=SORT(UNIQUE(Sales[Channel]))

If it sits in E5, revenue for each channel goes next to it with a spill reference (the # sign means "the whole spilled list"):

=SUMIFS(Sales[Amount],Sales[Channel],E5#)

When a new channel turns up in the data, it appears in the list and gets its own total, with nothing to copy down.

One setting to check: Formulas > Calculation Options should be set to Automatic, which is the default. If it's on Manual, nothing recalculates until you press F9.

Step 3: Refresh PivotTables automatically

PivotTables are fast and flexible, but they keep a copy of the data from their last refresh. They don't update when the data changes, and neither do the PivotCharts and slicers built on them. You have four options.

Refresh by hand

On Windows, Data > Refresh All (Ctrl+Alt+F5) refreshes every PivotTable and query in the workbook, and Alt+F5 refreshes just the selected PivotTable. On a Mac, use Data > Refresh All, or PivotTable Analyze > Refresh Data. If the PivotTable's source is a table, new rows are included in the refresh automatically, so you never need to change the source range.

Refresh every time the file opens

Click in the PivotTable, choose PivotTable Analyze > Options, open the Data tab and tick Refresh data when opening the file. Anyone who opens the dashboard sees current numbers, even if the last person forgot to refresh.

Refresh when you leave the data sheet (macro)

For a fully automatic refresh in desktop Excel, add a short macro. Right-click the tab of the sheet that holds your data, choose View Code, and paste:

Private Sub Worksheet_Deactivate()
    ThisWorkbook.RefreshAll
End Sub

Every time you switch from the data sheet to another sheet, such as the dashboard, every PivotTable refreshes. Save the file as a macro-enabled workbook (.xlsm). Macros don't run in Excel for the web. On Windows, people opening the file click Enable Content; if the file arrived by email or download, they may first need to unblock it (right-click the file, choose Properties and tick Unblock).

Keep an eye out for Auto Refresh

Microsoft is testing an Auto Refresh option for PivotTables with members of the Microsoft 365 Insider program. Until it reaches your version of Excel, use one of the options above.

Step 4: Pull in outside data with Power Query

If your data starts life outside the workbook, such as a CSV export from your store, booking system or accounting software, don't copy and paste it every week. Connect to it with Power Query once, then refresh.

1. Choose Data > Get Data and pick the source, for example From File > From Text/CSV.
2. Tidy the data in the Power Query Editor if needed (remove columns, fix data types), then click Close & Load. It lands in the workbook as a table.
3. Build your formulas or PivotTables from that table.
4. Set the refresh: choose Data > Queries & Connections, right-click the query, choose Properties and tick Refresh data when opening the file, Refresh every __ minutes, or both.

Two tips make this work better:

• Use a folder for repeated exports. Data > Get Data > From File > From Folder combines every file in a folder, as long as they all have the same columns. Save each month's export into that folder, refresh, and the new month is added to the rest.
• Refresh the query before the PivotTables. If PivotTables are built on a query's output, untick Enable background refresh in the query's properties. The query then runs in the foreground, so the PivotTables are less likely to read old data. If one lags behind, refresh it again.

Power Query is built into Excel for Windows. Microsoft 365 for Mac can also import, shape and refresh data with Power Query, though it supports fewer data sources.

Step 5: Make date ranges move by themselves

A dashboard that shows "this month" or "the last 12 months" shouldn't need its dates edited every month. Build the dates from TODAY(), which returns the current date every time the workbook recalculates.

Revenue for the month to date:

=SUMIFS(Sales[Amount],Sales[Date],">="&EOMONTH(TODAY(),-1)+1,Sales[Date],"<="&TODAY())

For a rolling 12-month chart, put the first month in A5 and step forward from there:

=EOMONTH(TODAY(),-12)+1 in A5, then =EDATE(A5,1) in A6, filled down to A16.

Any chart that plots those 12 rows always shows the latest 12 months. If you'd rather choose the period yourself, for example to look back at a past month, replace TODAY() with a "report date" input cell.

Why isn't my Excel dashboard updating?

Work through these in order. The first two cause most problems.

1. The PivotTables haven't been refreshed. Press Ctrl+Alt+F5 (Data > Refresh All). If the numbers change, set up one of the automatic options in step 3.
2. The new rows aren't in the table. Click the last row you added. If the Table Design tab (Table on a Mac) doesn't appear, the row is outside the table. Usually there's a blank row in between. Delete the blank row, then drag the table's resize handle (bottom-right corner) down to include the new rows.
3. Calculation is set to Manual. Check Formulas > Calculation Options and set it to Automatic.
4. A formula or chart uses a fixed range. A range like A2:A500 ignores row 501. Point formulas at table columns, and rebuild charts from the table.
5. Dates or numbers are stored as text. SUMIFS skips text that only looks like a number. Text is usually left-aligned and may show a small green triangle. Convert it with Data > Text to Columns (click Finish), or fix it in Power Query.
6. A slicer still shows old items. In PivotTable Analyze > Options > Data, set Number of items to retain per field to None, then refresh.

Frequently asked questions

How do I make my Excel dashboard update automatically?

Store the data in an Excel table, build the numbers with formulas that reference table columns, and use TODAY() for date ranges. If you use PivotTables, turn on "Refresh data when opening the file" or add a short macro that refreshes them when you leave the data sheet.

Why doesn't my PivotTable update when I add new data?

PivotTables work from a saved copy of the data called the PivotTable cache, which is only updated when you refresh. Press Alt+F5 to refresh one PivotTable or Ctrl+Alt+F5 to refresh them all. If new rows still don't appear, check that they're inside the table the PivotTable is built from.

How do I create a chart in Excel that updates automatically?

Build the chart from an Excel table, or from formulas that read from one. When you add rows to the table, the chart picks them up. For a chart that always shows a moving period, such as the last 12 months, chart a short range of formulas built from TODAY().

Can an Excel dashboard update from live data?

Partly. Power Query can refresh imported data every few minutes while the workbook is open, and when it opens. Excel can't refresh a workbook that nobody has open. If you need refreshes on a fixed schedule with no one involved, that's a job for a tool like Power BI.

Does an automatic dashboard work in Excel for the web?

Formulas recalculate in Excel for the web just as they do on the desktop. Macros don't run there, so if people use the browser version, rely on formulas rather than the macro method, and refresh PivotTables by right-clicking one and choosing Refresh.

Sources

• Microsoft Support: Resize a table by adding or removing rows and columns
• Microsoft Support: Refresh PivotTable data
• Microsoft Support: Refresh an external data connection in Excel
• Microsoft Support: Import and shape data in Excel for Mac (Power Query)
• Microsoft Support: Change formula recalculation, iteration or precision
• Microsoft Support: UNIQUE function
• Microsoft Learn: Use defined names to automatically update a chart range