How to Refresh a Pivot Table in Excel: Step-by-Step Guide 2026

Pivot tables are powerful. But they have one quirk that trips up almost every Excel user: they do not update automatically when your source data changes.

If you edited your spreadsheet and your pivot table still shows old numbers, you need to refresh it. This guide shows you exactly how to refresh a pivot table in Excel, when to do it, and how to make it happen automatically so you never have to think about it again.

The Quick Answer: How to Refresh a Pivot Table

Click anywhere inside your pivot table. Then use one of these methods:

• Right-click and select Refresh
• Go to the PivotTable Analyze tab and click Refresh
• Press the keyboard shortcut Alt + F5

That covers the basic refresh. Keep reading for the full picture, including how to refresh all pivot tables at once, auto-refresh on file open, and what to do when refresh does not work.

Why Pivot Tables Do Not Update on Their Own

Excel pivot tables store a snapshot of your data in something called a pivot cache. When you type new values into your source data, the pivot cache does not know about the change yet.

Think of it like a printed report. You can update the spreadsheet all day, but the printed copy stays the same until you print a new one. Refreshing the pivot table is how you print that new copy.

This design is intentional. It makes large pivot tables faster to work with. But it means you always need to manually trigger a refresh after editing your data, unless you set up an automatic refresh.

Method 1: Right-Click to Refresh

This is the fastest everyday method.

1. Click any cell inside your pivot table
2. Right-click to open the context menu
3. Click Refresh

The pivot table will recalculate instantly. If your data has new rows, new totals, or changed values, you will see them update right away.

Method 2: Use the PivotTable Analyze Tab

This method works well if you want more control.

1. Click anywhere inside your pivot table
2. Look at the top ribbon. A new tab called PivotTable Analyze (or just Analyze in older Excel versions) will appear
3. Click that tab
4. In the Data group, click the Refresh button

You will also see a dropdown arrow next to Refresh. Click that and you get two options:

• Refresh – Updates only the selected pivot table
• Refresh All – Updates every pivot table, query, and data connection in the entire workbook

Note: In Excel 2016 and earlier, this tab may be called Options instead of Analyze.

Method 3: Keyboard Shortcut

If you work fast and prefer keyboard shortcuts, this is the one to memorize.

Action	Shortcut	Works On
Refresh selected pivot table	Alt + F5	Windows only
Refresh all pivot tables in workbook	Ctrl + Alt + F5	Windows only
Open PivotTable Analyze menu	Alt + J + T	Windows only
Refresh on Mac	No default shortcut	Mac (use ribbon instead)

Note: Mac users can assign a custom keyboard shortcut via Tools > Customize Keyboard in Excel for Mac.

How to Refresh All Pivot Tables at Once

If your workbook has multiple pivot tables, refreshing them one at a time is slow. Here is how to refresh everything in one go.

Using the Ribbon

1. Click inside any pivot table
2. Go to PivotTable Analyze
3. Click the dropdown arrow under Refresh
4. Select Refresh All

Using the Data Tab

1. Click the Data tab in the ribbon
2. Click Refresh All in the Queries and Connections group

This method refreshes everything in the workbook: pivot tables, Power Query connections, and external data sources. It is useful when you have a dashboard with multiple data sources.

How to Auto-Refresh a Pivot Table When the File Opens

You can set a pivot table to refresh automatically every time the workbook is opened. This is perfect for reports shared with colleagues who just need to see current data without doing anything.

1. Click inside your pivot table
2. Go to PivotTable Analyze
3. Click PivotTable Options (the small arrow at the bottom left of the PivotTable group)
4. In the dialog that opens, click the Data tab
5. Check the box that says Refresh data when opening the file
6. Click OK

Now every time anyone opens the file, the pivot table will pull the latest data automatically.

How to Auto-Refresh Using VBA (Advanced)

If you want a pivot table to refresh every time you change data in the worksheet, you need a small VBA macro. This is more advanced but very useful for live dashboards.

Step 1: Open the VBA Editor

1. Press Alt + F11 to open the Visual Basic Editor
2. In the Project panel on the left, find your workbook
3. Double-click the sheet that contains your source data (not the pivot table sheet)

Step 2: Add the Macro

Paste this code into the code window:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim pt As PivotTable
    For Each pt In Worksheets("Sheet2").PivotTables
        pt.RefreshTable
    Next pt
End Sub

Replace Sheet2 with the actual name of the sheet containing your pivot table.

1. Press Ctrl + S to save
2. Save the file as a macro-enabled workbook (.xlsm)

Now any time data changes on your source sheet, the pivot table updates immediately.

Why New Rows Are Not Showing After Refresh

This is a common problem. You add rows to your data, refresh the pivot table, but the new rows do not appear.

The reason: your pivot table source range is fixed. It points to a specific range like A1:D100. When you add row 101, the pivot table does not know to include it.

The Fix: Use a Table as Your Data Source

The best solution is to format your source data as an Excel Table before creating the pivot table.

1. Click inside your source data
2. Press Ctrl + T to create a table
3. Make sure My table has headers is checked, then click OK
4. Now create or update your pivot table to use this table as the source

Excel Tables expand automatically when you add new rows. Your pivot table source range will always include every row. Refresh once and everything shows up correctly.

Fix Without Converting to a Table

If you cannot use a table, you can manually update the source range.

1. Click inside the pivot table
2. Go to PivotTable Analyze
3. Click Change Data Source
4. Update the range to include your new rows
5. Click OK, then refresh

Pivot Table Refresh Not Working: Common Fixes

Problem	Likely Cause	Fix
New rows not appearing	Fixed source range	Convert data to Excel Table
Refresh button is greyed out	Pivot table is grouped or protected	Ungroup or unprotect the sheet
Error after refresh	Source data moved or renamed	Update data source via Change Data Source
Numbers look wrong after refresh	Old pivot cache	Clear the cache: uncheck Save source data with file, save, reopen
Refresh takes very long	Large dataset or slow connection	Use Power Pivot or load data via Power Query
Pivot table not refreshing on shared file	Workbook is shared	Remove shared workbook mode or use co-authoring in Microsoft 365

Excel Table vs Named Range: Which to Use as Pivot Source

Both work as pivot table data sources. But they behave differently.

Feature	Excel Table	Named Range
Auto-expands with new rows	Yes	No
Pivot refreshes new data	Yes (after refresh)	Only if range updated manually
Easy to set up	Very easy (Ctrl + T)	Requires formula or manual update
Works with structured references	Yes	No
Best for growing datasets	Yes	Not ideal

For almost every use case, an Excel Table is the better choice. Use named ranges only when you have a specific reason.

Setting a Refresh Schedule for External Data Sources

If your pivot table connects to an external source like SQL Server, SharePoint, or a web query, you can set it to refresh on a timer.

1. Click inside the pivot table
2. Go to PivotTable Analyze
3. Click PivotTable Options
4. Go to the Data tab
5. Check Refresh every X minutes and set your preferred interval
6. Click OK

This keeps your pivot table live while the workbook is open. Useful for monitoring dashboards connected to live databases.

For more detail on connecting Excel to external data sources, the official Microsoft guide at support.microsoft.com covers every connection type including ODBC, OLE DB, and Power Query.

Refreshing a Power Pivot Data Model

If your workbook uses Power Pivot (the Excel data model), refreshing works slightly differently.

• In the Power Pivot window, click Refresh or Refresh All
• In the normal Excel ribbon, go to Data and click Refresh All
• Power Pivot connections can also be refreshed via the Queries and Connections panel

Power Pivot handles much larger datasets than standard pivot tables. If you are regularly working with millions of rows, Power Pivot is worth learning. Microsoft has a solid introduction at learn.microsoft.com if you want to go deeper on data models.

Best Practices for Managing Pivot Table Refreshes

Follow these habits and you will rarely run into pivot table refresh problems.

• Always format source data as an Excel Table before building a pivot table
• Name your tables clearly so pivot sources are easy to identify
• Enable Refresh data when opening the file for any report shared with others
• Use Refresh All from the Data tab when your workbook has multiple pivot tables
• Add new data at the bottom of your table, not in between existing rows
• Avoid merged cells in your source data as they cause pivot table errors
• Save your workbook after refreshing so others get the updated version

All Refresh Methods at a Glance

Method	How To	Best For
Right-click Refresh	Click inside pivot, right-click, Refresh	Quick single refresh
Ribbon Refresh	PivotTable Analyze > Refresh	Standard everyday use
Keyboard Alt + F5	Press Alt + F5	Keyboard users
Refresh All (ribbon)	PivotTable Analyze > Refresh > Refresh All	Multiple pivot tables
Refresh All (Data tab)	Data > Refresh All	Workbook-wide refresh
On file open	PivotTable Options > Data > Refresh on open	Shared reports
VBA auto-refresh	Worksheet_Change macro	Live dashboards
Scheduled refresh	PivotTable Options > Refresh every X min	External data sources

Conclusion

Refreshing a pivot table in Excel is straightforward once you know where to look. The right-click method and Alt + F5 shortcut cover most everyday situations. If you want to avoid manual refreshes entirely, either enable the on-open setting or set up a simple VBA macro.

The single most valuable habit you can build is formatting your source data as an Excel Table. This solves the most common refresh problem (new rows not showing up) before it ever starts.

Whether you are refreshing once a day or building a live dashboard, the techniques in this guide give you full control over when and how your pivot table data updates in 2026.

Frequently Asked Questions

Can I set a pivot table to refresh automatically without VBA?

Yes. Go to PivotTable Options, click the Data tab, and check Refresh data when opening the file. This refreshes the pivot table every time the workbook is opened. For timed refreshes while the file is open, use the Refresh every X minutes option under the same Data tab. Both options work without any VBA or coding.

Why does my pivot table still show old data after I refreshed?

The most likely cause is that your new data falls outside the pivot table source range. This happens when the source is a fixed cell range like A1:D50 and you added rows below row 50. Convert your source data to an Excel Table using Ctrl + T. Tables expand automatically and your pivot will always include the latest rows after a refresh.

What is the keyboard shortcut to refresh a pivot table on Windows?

The shortcut is Alt + F5. This refreshes only the selected pivot table. To refresh all pivot tables and data connections in the workbook at once, use Ctrl + Alt + F5. Both shortcuts work on Windows. Mac users do not have a built-in default shortcut and need to use the ribbon or assign a custom one.

Does refreshing a pivot table affect formulas that reference it?

It can. If you have formulas in other cells that use GETPIVOTDATA or reference pivot table cells directly, those formulas will recalculate after a refresh. If the pivot table layout changes (new rows or columns appear), cell references that point to specific rows may shift and return wrong values. Using GETPIVOTDATA functions makes formulas more stable across refreshes.

How do I refresh a pivot table in Excel for the web or SharePoint?

In Excel for the web, click inside the pivot table, then go to the PivotTable tab in the ribbon and click Refresh. The option is in the same place as the desktop version but some advanced settings like timed refresh and VBA macros are not available in the browser version. For SharePoint-connected workbooks, opening the file and using Refresh All from the Data tab is the most reliable approach.