Excel Trying to Refresh Pivot Table? Fix It Now

Charts & pivot tables · Updated November 22, 2026

If Excel is trying to refresh a pivot table and nothing happens, or it shows an error, the fix is usually to update the data source range, check for blank rows/columns, or use the Refresh All command after ensuring the source data is properly formatted. Below is a complete breakdown of why refresh fails, how to force it, and how to prevent it from happening again.

Why Pivot Table Refresh Fails

A pivot table is a snapshot of your data at the moment you created it. When you change the underlying data, Excel does not automatically update the pivot unless you tell it to. The most common reasons a refresh fails are:

  • New rows or columns added outside the original data range – Excel only looks at the range you selected when creating the pivot.
  • Blank cells in the source data – Excel may misinterpret a blank row or column as the end of the data.
  • The source workbook is closed or the file path is broken – if you linked to an external file, refresh cannot find it.
  • Data type mismatches – e.g., numbers stored as text won’t aggregate correctly.
  • Protected sheets or workbooks – refresh is blocked if the sheet is protected without allowing it.

How to Manually Refresh a Pivot Table

The simplest method is to right-click anywhere inside the pivot table and choose Refresh. Alternatively, use the Ribbon:

  1. Click anywhere in the pivot table.
  2. Go to PivotTable Analyze (or Options in older versions) on the Ribbon.
  3. Click Refresh in the Data group.
  4. To refresh all pivot tables in the workbook, click Refresh All instead.

Keyboard shortcut: Alt+F5 refreshes the current pivot; Ctrl+Alt+F5 refreshes all (on Mac, use Cmd+Option+R for refresh all).

Setting Up Automatic Refresh on File Open

You can make Excel refresh the pivot automatically whenever you open the workbook. This is useful if you receive updated data files regularly.

  1. Right-click a pivot table and choose PivotTable Options.
  2. Go to the Data tab.
  3. Check Refresh data when opening the file.
  4. Click OK.

Note: This only refreshes when the file is opened, not when you type new data. For live updates while working, you need a macro or a dynamic data source.

Fixing the “Data Source Reference is Not Valid” Error

This is the most common error when refreshing. It usually means Excel cannot find the range you originally specified, often because you deleted rows or columns. To fix it:

  1. Click anywhere in the pivot table.
  2. Go to PivotTable Analyze > Change Data Source.
  3. In the dialog, select the new range that includes all your data (including new rows/columns).
  4. Click OK, then refresh.

If you have many pivot tables, consider converting your source data into an Excel Table (Ctrl+T). Tables automatically expand when you add rows, and pivot tables based on tables refresh correctly every time.

Using a Dynamic Range for Automatic Expansion

If you don’t want to use an Excel Table, you can define a named range with the OFFSET function that expands automatically. For example, with data in A1:C100, create a named range called DataRange using:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))

Then set the pivot table’s source to DataRange. This works but is less reliable than an Excel Table because it counts non-empty cells, so blank rows in the middle break it. For most users, an Excel Table is the better choice.

Refreshing Pivot Tables with VBA (for Repetitive Tasks)

If you need to refresh multiple pivot tables or do it automatically when a cell changes, a simple macro helps. Before running any macro, save a copy of your workbook (macros cannot be undone with Ctrl+Z).

Sub RefreshAllPivots()
Dim ws As Worksheet
Dim pt As PivotTable
For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.RefreshTable
Next pt
Next ws
End Sub

To run it, press Alt+F8, select the macro, and click Run. You can also assign it to a button or trigger it on workbook open.

Common Misconceptions About Pivot Refresh

One big misconception is that pressing Ctrl+S saves the refreshed data. It doesn’t – it saves the file, but the pivot’s cached data is updated only when you refresh. Another is that changing a cell in the source automatically updates the pivot – it does not. Even if you have automatic refresh on open, you must explicitly refresh during a session.

Also, if you have multiple pivot tables based on the same data, refreshing one does not refresh the others unless you use Refresh All. And if you have a pivot chart, it refreshes together with its pivot table.

Frequently Asked Questions

Why does my pivot table not pick up new rows I added?

Because the pivot’s source range is fixed. If you added rows below the original range, Excel doesn’t know they exist. Change the data source to include them, or better, convert your source to an Excel Table (Ctrl+T) – then new rows are automatically included on refresh.

How do I refresh a pivot table when the source file is closed?

You can’t refresh from a closed external file. Open the source workbook first, or use a connection that stores the data locally. If you have a Power Pivot model, you can refresh from a closed file if the connection is set to use the cached data, but that’s not the default.

What does “Refresh All” do differently from “Refresh”?

Refresh updates only the selected pivot table. Refresh All updates every pivot table, query, and data connection in the workbook. Use it when you have multiple pivots from the same data to avoid missing one.

Can I make a pivot table refresh automatically every minute?

Yes, but only with VBA or Power Query. A simple VBA timer can run a refresh every 60 seconds, but it’s rarely needed. Alternatively, use Power Query to import data and set the refresh interval in the connection properties.

Why does my pivot table show old data even after refresh?

Check if you have multiple pivot tables and only refreshed one. Also, if the source data has formulas that recalculate, ensure the workbook calculation is set to Automatic (Formulas > Calculation Options). If you have a data model pivot, you may need to refresh the underlying connections first.

Is there a way to refresh a pivot table without losing formatting?

Yes, refresh does not change formatting. However, if you change the data source range, you may lose some custom styles. To preserve formatting, use the same range or an Excel Table, and avoid deleting columns that are used in the layout.

Related

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.