How to Refresh a Pivot Table in Excel (+ Auto Refresh)

To refresh a pivot table in Excel, click any cell in the pivot table and press Alt+F5. Or right-click the pivot table and choose Refresh. To refresh every pivot table in the workbook at once, press Ctrl+Alt+F5 (Data > Refresh All).

Here’s the quick version:

1. Click any cell in the pivot table.
2. Go to the PivotTable Analyze tab.
3. Click Refresh in the Data group.

In most versions of Excel, a pivot table doesn’t update by itself when you change the source data. Below, I’ll show you every way to refresh it, how to refresh it automatically, why new rows don’t show up (and how to fix that for good) and how to keep your column widths when you refresh 📄

Download our sample workbook here to practice refreshing a pivot table in Excel 😃

Table of Contents

Why do you need to refresh a pivot table?

A pivot table summarizes, filters and organizes large amounts of data in a few clicks 🔍

When you create a pivot table, Excel stores a copy of the source data in the pivot cache. The pivot table reads from that cache, not directly from your data.

So if you change a value, add a row or delete something in the source data, the pivot table keeps showing the old numbers. When you refresh, Excel reloads the cache from the source range and updates the pivot table 💻

There are several ways to refresh a pivot table, from a single click to fully automatic. Let’s go through them.

Refresh a pivot table from the ribbon

The most common way to refresh a pivot table is the Refresh button on the ribbon.

Say we have the following sample dataset.

Here’s its pivot table. We’ll change some values in the dataset and then refresh the pivot table to see the changes 📑

To refresh it:

Step 1) Click any cell in the pivot table.

Step 2) Go to the PivotTable Analyze tab and click Refresh in the Data group. Or skip the ribbon and press Alt+F5.

Step 3) The pivot table now shows the updated data.

That was simple, no? 😀

Refresh a pivot table with right-click

You can also refresh a pivot table from the right-click menu. We’ll use the same sample data as before 🔼

Step 1) Right-click any cell in the pivot table 🖱️

Step 2) Choose Refresh from the menu.

And it’s done! The pivot table now shows the changes from the dataset.

Cool, no? 🤓

Why doesn’t my pivot table show new rows?

Refresh only reloads the range the pivot table was built on. If your source data is a normal range like A1:F11 and you add new rows below it, those rows are outside the range, so Refresh ignores them 🤔

There are two fixes: change the data source once, or (better) turn the source data into an Excel table.

Fix 1: Change the data source

We’ll add new rows to the dataset of the pivot table above. The pivot table range needs to grow to include them.

To change the data source:

Step 1) Click any cell in the pivot table.

Step 2) Go to the PivotTable Analyze tab.

Step 3) Click Change Data Source in the Data group.

Step 4) Excel opens a dialog box with the current Table/Range. If the data is on another sheet, Excel takes you to that sheet.

Step 5) Select the new range, including the new rows 💻

Step 6) Click OK.

The pivot table updates with the new range:

How cool is that? 😃

Fix 2: Use an Excel table as the source

Changing the data source every time you add rows gets old fast. The better fix is to turn your source data into an Excel table before you create the pivot table:

1. Click any cell in your data and press Ctrl+T.
2. Check that the range is right and click OK.
3. Create the pivot table from the table (or point your existing pivot table to the table name with Change Data Source).

Rows you add to a table are included in the pivot table automatically when you refresh. New columns show up in the PivotTable Fields list too.

Refresh all pivot tables at once (Refresh All)

If your workbook has several pivot tables, refreshing them one by one is a hassle 😩

Luckily, you can refresh all of them in one go. Here we have the same pivot table as before, plus two copies of it.

To refresh them all:

Step 1) Go to the Data tab.

Step 2) Click Refresh All in the Queries & Connections group. Or press Ctrl+Alt+F5.

And it’s done! All the pivot tables in your workbook are refreshed 🔃

You can also click the arrow under Refresh on the PivotTable Analyze tab and choose Refresh All.

Pro tip

Prefer key tips? Press Alt, A, R, A one after another (Data > Refresh All > Refresh All). Or simply press Ctrl+Alt+F5.

Refresh a pivot table automatically when the workbook opens

If you work with the same pivot table often, let Excel refresh it every time you open the file 📖

Step 1) Click any cell in the pivot table. Go to the PivotTable Analyze tab and click Options in the PivotTable group.

Step 2) Go to the Data tab.

Step 3) Under PivotTable Data, check Refresh data when opening the file.

Step 4) Click OK 🆗

And it’s done! Now your pivot table refreshes every time you open the workbook.

Auto refresh a pivot table when the data changes

In newer versions of Excel for Microsoft 365, pivot tables can refresh automatically when their source data in the same workbook changes. At the time of writing, Microsoft lists this Auto Refresh feature for participants in the Microsoft 365 Insider program, so you may not have it yet.

If you do, you’ll find it in two places:

• The Auto Refresh button in the Data group on the PivotTable Analyze tab.
• PivotTable Analyze > Options > Data tab > Automatically refresh when the data source changes.

The setting belongs to the data source. If you turn it on or off, it changes for every pivot table that uses the same source.

Refresh a pivot table every few minutes

You can also make a pivot table refresh on a timer, for example every 10 minutes. This only works when the pivot table has a data connection, like a pivot table built on the Data Model or on external data 🤔

Here’s how to set the timer.

Step 1) Click any cell in the pivot table. Go to the Data tab, click the arrow under Refresh All and choose Connection Properties (or Data > Queries & Connections > Properties, as in the screenshot).

Step 2) The Connection Properties dialog box opens on the Usage tab.

Step 3) Check Refresh every.

Step 4) Enter the number of minutes between refreshes. We set it to 10 minutes.

Step 5) Click OK.

Voilà! The pivot table now refreshes every 10 minutes. How cool is that? 😀

Keep column widths and formatting when you refresh

Does your pivot table squeeze or stretch your columns every time you refresh? That’s the Autofit column widths on update option. To turn it off:

1. Click any cell in the pivot table.
2. Go to PivotTable Analyze > Options.
3. On the Layout & Format tab, uncheck Autofit column widths on update.
4. To keep your own cell formatting too, check Preserve cell formatting on update.
5. Click OK.

Refresh pivot tables with VBA

You can also refresh pivot tables with a short VBA macro. It’s a more advanced method, but handy if you already use macros 😵

Our pivot table looks like this before the refresh:

To refresh all pivot tables with VBA:

Step 1) Press Alt+F11 to open the VBA editor ⌨

Step 2) Click the Insert menu and choose Module.

Step 3) Paste this VBA code into the module window.

Step 4) Close the Visual Basic editor.

Step 5) Press Alt+F8 to open the Macro dialog box.

Step 6) Select the RefreshAllPivotTables macro.

Step 7) Click Run 🏃‍♀️

The pivot tables are refreshed:

How easy is that? 🧐

To keep the macro, save the workbook as an Excel Macro-Enabled Workbook (.xlsm).

Refresh a pivot table in Excel for the web and Mac

Excel for the web: right-click anywhere in the pivot table and select Refresh. To refresh all pivot tables, use Refresh All on the PivotTable tab. Refresh on open and Autofit column widths are in the PivotTable Settings pane.

Excel for Mac: click the pivot table, go to PivotTable Analyze and click Refresh (or Control-click the pivot table and select Refresh). Choose Refresh All from the Refresh arrow to update every pivot table.

Conclusion

Now you know how to refresh pivot tables in Excel: with the ribbon, right-click, Alt+F5 and Refresh All (Ctrl+Alt+F5), when the file opens, automatically and on a timer.

You also know how to make new rows show up with Change Data Source or an Excel table, how to keep your column widths, and how to refresh pivot tables with VBA macros 📄

To learn more about pivot tables, check out these tutorials:

How to Create a Pivot Table in Excel: Step-by-Step

How to Delete a Pivot Table in Excel (With and Without Data)

How to Add Calculated Fields in Excel Pivot Tables

Frequently asked questions

What is the shortcut to refresh a pivot table in Excel?

Click any cell in the pivot table and press Alt+F5. To refresh all pivot tables and data connections in the workbook, press Ctrl+Alt+F5.

What is the difference between Refresh and Refresh All?

Refresh updates the pivot table you selected. Refresh All updates all data in the workbook: every pivot table on every sheet, plus queries and other data connections.

Why is my pivot table not updating when I refresh?

Usually because the new data is outside the pivot table’s source range. Use Change Data Source to include the new rows, or turn the source data into an Excel table so new rows are included every time you refresh.

Can a pivot table refresh automatically?

Yes. Turn on Refresh data when opening the file in PivotTable Options, use Refresh every in Connection Properties for pivot tables with a data connection, or use the newer Auto Refresh button in Microsoft 365 if you have it.

How do I stop a pivot table from changing column widths when I refresh?

Go to PivotTable Analyze > Options > Layout & Format and uncheck Autofit column widths on update.

How do I stop a refresh that takes too long?

Press Esc. You can also click the arrow under Refresh and choose Refresh Status to check on it or Cancel Refresh to stop it.