Excel Refresh Pivot Table Source: How to Update Data

Charts & pivot tables · Updated November 22, 2026

To refresh a pivot table’s source data in Excel, right-click the pivot table and select Refresh, or press Alt+F5 (Windows) or Ctrl+Option+R (Mac). This updates the pivot table with any changes made to the existing source range. If you added or removed rows or columns from the source, you must also update the pivot table’s source range via PivotTable Analyze > Change Data Source.

Why Pivot Tables Don’t Auto-Update

Pivot tables are not live connections to your data—they are snapshots. When you create a pivot table, Excel stores a cache of the source data. Any changes you make to the underlying cells (editing values, adding rows, deleting columns) are not reflected until you refresh. This design improves performance because pivot tables can handle large datasets without recalculating every time you type. However, it also means that if you forget to refresh, your pivot table shows stale numbers, which can lead to incorrect decisions.

How to Refresh a Pivot Table (Step-by-Step)

There are several ways to refresh, depending on how many pivot tables you have and your preference for speed.

  • Refresh a single pivot table: Right-click anywhere inside the pivot table and choose Refresh. Alternatively, select the pivot table and go to PivotTable Analyze > Refresh (on the Ribbon).
  • Refresh all pivot tables in the workbook: Press Ctrl+Alt+F5 (Windows) or Cmd+Option+F5 (Mac). This refreshes every pivot table and data connection in the file.
  • Use the keyboard shortcut: Alt+F5 (Windows) refreshes the selected pivot table; on Mac, it’s Ctrl+Option+R.
  • Refresh on file open: Go to File > Options > Data (or Excel > Preferences > Data & General on Mac) and check Refresh data when opening the file. This ensures your pivot tables are up to date every time you open the workbook.

When You Must Change the Source Range

Refreshing only updates the data within the existing source range. If you added rows below the original range or new columns to the right, those are not included unless you adjust the source. Here’s how:

  1. Click anywhere in the pivot table to activate the PivotTable Analyze tab.
  2. Click Change Data Source (in the Data group).
  3. In the dialog, select the new range (e.g., =Sheet1!$A$1:$D$500) or use the range selector.
  4. Click OK. The pivot table refreshes automatically with the new range.

If your data grows regularly, consider converting your source data to an Excel Table (Ctrl+T) and then creating the pivot table from that table. When you add rows to the table, the pivot table’s source automatically expands on refresh—no manual range changes needed.

Common Problems and Misconceptions

Problem: “I refreshed but my new rows aren’t showing.” This almost always means the source range wasn’t updated. Check Change Data Source to see if the range includes the new rows.

Problem: “My pivot table shows #REF! errors.” This happens when the source range points to a sheet or range that was deleted. Go to Change Data Source and re-select a valid range.

Misconception: “Refreshing will overwrite my formatting.” Refreshing preserves most formatting, but if you have custom conditional formatting or number formats, they may not apply to new fields you add later. That’s a separate issue from refreshing.

Misconception: “I need to refresh every time I filter.” Filtering and refreshing are independent. Filtering only changes what’s displayed, not the underlying data. You only refresh when the source data changes.

Refreshing vs. Rebuilding a Pivot Table

Refreshing is faster and keeps all your layout, filters, and formatting. Rebuilding (creating a new pivot table from scratch) resets everything and is rarely needed unless you want to change the data model or the source fundamentally. For example, if you switch from a range to an external data connection, you might rebuild. But for routine updates, always refresh first.

Using Pivot Tables with External Data Sources

If your pivot table uses data from an external source (e.g., SQL Server, Access, or a text file), refreshing may take longer and might require credentials. You can set up automatic refresh intervals via Data > Queries & Connections (for Power Query) or by right-clicking the pivot table and choosing Connection Properties to set a refresh timer. Be aware that external data refreshes can fail if the source is unavailable or the connection is broken.

Alternatives to Manual Refreshing

If you find yourself refreshing constantly, consider these options:

  • Use Excel Tables as the source—they auto-expand, so you only need to refresh (not change the range).
  • Use Power Query to import and transform data. You can set the query to refresh on file open, and pivot tables built on the query will update with it.
  • Use a macro to refresh on a schedule or when a cell changes. For example, a simple VBA macro that runs when you activate a worksheet:
Private Sub Worksheet_Activate()
    ' Refresh all pivot tables when the sheet is activated
    Dim pt As PivotTable
    For Each pt In Me.PivotTables
        pt.RefreshTable
    Next pt
End Sub

Note: Save a copy of your workbook before adding any macro, as macros cannot be undone with Ctrl+Z.

Frequently Asked Questions

Why is my pivot table not picking up new rows? Because the source range is static. Update it via Change Data Source, or better, convert your data to an Excel Table so the range expands automatically.

What’s the difference between Refresh and Refresh All? Refresh updates only the selected pivot table. Refresh All (Ctrl+Alt+F5) updates all pivot tables, charts, and data connections in the workbook.

Does refreshing a pivot table affect my original data? No. Refreshing only recalculates the pivot table’s cache from the source. It never changes the source data itself.

Can I set a pivot table to refresh automatically every few minutes? Yes, for external data connections. Right-click the pivot table, choose Connection Properties, and under the Usage tab, check Refresh every and set minutes. This does not work for data that lives in the same worksheet.

Why does my pivot table show old values even after I press Ctrl+S? Saving a file does not refresh pivot tables. You must explicitly refresh. Use the shortcut or set the workbook to refresh on open.

How do I refresh a pivot table in Excel for the web? In Excel for the web, right-click the pivot table and choose Refresh. The option is available, but automatic refresh on open is not. You’ll need to refresh manually each time.

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.