Excel Pivot Table Not Refreshing? Fix the Source and Cache

Charts & pivot tables · Updated November 18, 2026

If an Excel PivotTable is not refreshing, select a cell inside it and choose PivotTable Analyze > Refresh; for every PivotTable and connection in the workbook, choose Data > Refresh All. If the results still look old, check that the PivotTable’s source includes the latest rows, that the source data itself is current, and that filters or grouping are not hiding the change.

How a PivotTable gets its data

A PivotTable summarizes records from a source such as a worksheet range, an Excel table, an external data connection, or the Excel Data Model. It does not behave like a live view that recalculates every time you edit a source cell. Excel uses a PivotTable cache or connection-backed data to produce the summary, so a change in the source may not appear until the PivotTable is refreshed.

That distinction explains a frequent surprise: typing a new sale into the row below a data range does not necessarily make it part of the PivotTable. Refreshing updates the summary from its defined source; it does not automatically redefine every kind of source to include new records. Similarly, refreshing cannot retrieve data that has not been saved, recalculated, or successfully loaded from an external system.

In Excel for Microsoft 365 on Windows, click inside the PivotTable to reveal the PivotTable Analyze tab. Use Refresh for that PivotTable. To update multiple PivotTables and workbook connections, use Data > Refresh All. You can also right-click inside a PivotTable and choose Refresh.

Check whether new rows are in the source

First determine whether the changed record is actually inside the data source. Click the PivotTable, then choose PivotTable Analyze > Change Data Source to inspect the range or table name. If the source is a fixed range such as $A$1:$D$100, a new record in row 101 is outside it. Refreshing that PivotTable will not include the record.

For a fixed range, update the source through PivotTable Analyze > Change Data Source, select the complete range including its headers, and confirm. A more reliable option for regularly growing data is to format the source as an Excel table: select a cell in the records and choose Insert > Table, or press Ctrl+T on Windows. Confirm that the table has headers. When new records are added directly below the table, Excel normally expands the table, and the PivotTable can use the table as its source.

For example, suppose a sales list occupies A1:D250 and a new order is entered on row 251. A PivotTable based on the fixed range A1:D250 will omit that order, even after refresh. If its source is an Excel table that expands to include the new row, refreshing can bring the order into the summary. Check the table boundary rather than assuming that a nearby row became part of it.

  • Confirm that the source range reaches the last record and includes all required columns.
  • Look for a blank row or column that might split the data into separate blocks.
  • Verify that the new record uses the same columns and field layout as existing records.

Look for source-data and header problems

A refresh can fail or produce unexpected results when the source is not a clean rectangular list. Each column should have a header, and headers should be distinct. Blank headers, merged header cells, or a partially selected range can prevent Excel from interpreting the source as intended. If a column was renamed or removed, a PivotTable may report that a field name is invalid or retain a field that no longer exists.

Check the source rows for inconsistent values, too. A date stored as text may not group or sort like a real Excel date. Numbers stored as text can behave differently from numeric values, while empty cells may appear as a blank category in the PivotTable. If the source contains formulas, confirm that those formulas have calculated their latest results before refreshing. When calculation is set to manual, press F9 to calculate the workbook, then refresh the PivotTable. On many Mac keyboards, use Fn+F9 if the function keys are assigned to system controls.

Do not confuse a repeated value with an omitted row. A PivotTable groups identical labels together by design. If two source rows have the same customer and product, their amounts may be combined into one displayed item. Compare the source records with the summary’s value field and aggregation, such as Sum or Count, before concluding that a refresh lost data.

Check filters, grouping, and layout

The data can be present in the PivotTable cache but hidden from view. Inspect report filters, field drop-downs, slicers, timelines, and label or value filters. A filter such as “Top 10” may exclude a newly added category; a slicer may still be selecting only one region. Clear or adjust filters to test whether the missing item appears.

Grouping can also make a change hard to spot. Dates may be grouped into months or years, and numeric values can be grouped into ranges. A new date may appear in an existing month rather than as a separate row. Expand the relevant group or temporarily remove the grouping to inspect the underlying items. Right-clicking a grouped field provides grouping options, though the available commands depend on the field and source.

If a new source column does not appear in the PivotTable Fields list, confirm that it is inside the source range and refresh. If the source column was added after the PivotTable was created, the field list may not reflect it until the source is updated and refreshed. A missing field is different from a hidden item: the first suggests a source or field-list issue, while the second usually points to a filter or layout choice.

Refresh external data and connections

When a PivotTable uses a database, text file, Power Query output, or another external connection, Data > Refresh All may update both the connection and PivotTable. The connection can nevertheless fail because the file moved, a server is unavailable, credentials expired, or the account lacks access. Read any refresh message carefully; a PivotTable cannot show updated records if its upstream query did not load them.

For a Power Query source, inspect the query’s load result and refresh the query before diagnosing the PivotTable itself. In Microsoft 365 for Windows, query controls are available through Data > Queries & Connections. If the query reports an error, fix that first. If it loads successfully but the PivotTable remains unchanged, refresh the PivotTable or use Refresh All again, then check that the PivotTable is built from the expected table or Data Model.

Some external sources ask for credentials or connection settings, and organizational policies may restrict access. Do not bypass a security prompt or share passwords in a workbook. If a connection is managed by an employer or service provider, ask its administrator whether the source is available and whether your account is authorized. A successful refresh also depends on the connection’s configured update behavior; not every source updates automatically when the workbook opens.

When Refresh or Change Data Source is unavailable

If the refresh command is disabled, confirm that a cell inside the PivotTable is selected and that Excel is not currently editing a cell. Press Enter or Esc to finish editing, then try again. Also check whether the worksheet or workbook is protected; protection can restrict changes to PivotTables or their source configuration. If the workbook is opened read-only, you may be able to inspect data but not save changes.

The source type matters. A PivotTable built from the Excel Data Model or an OLAP connection is managed differently from one based on a simple worksheet range. For these sources, changing the range through the usual Change Data Source dialog may not be available. Refresh the underlying query, connection, or model instead, and confirm that it completed successfully. If the workbook was created in another Excel version or uses a specialized connection, some commands may also differ.

On Excel for Mac, the core process is similar: select the PivotTable and use its PivotTable or Analyze controls to refresh, or use the Data tab’s refresh command where available. Ribbon labels and the arrangement of commands can differ by Excel version. In Excel for the web, PivotTable capabilities and connection support can also differ from the Windows desktop app; if a connection or model cannot be refreshed there, open the workbook in desktop Excel and use Data > Refresh All.

Set refresh behavior and avoid stale results

If a workbook should update when opened, select the PivotTable and open PivotTable Analyze > Options (or right-click and choose PivotTable Options). On the Data tab, look for Refresh data when opening the file. The setting is useful when the source is accessible at opening, but it does not fix an incorrect source range or a failed connection. The workbook must also be saved after changing the setting.

For routine work, make the source an Excel table, keep a single header row, and add records within the table rather than in a disconnected block. After importing or editing data, refresh the query or connection first when applicable, then refresh the PivotTable. If several PivotTables share a cache or connection, Refresh All is a practical way to update the workbook consistently, though it may take longer than refreshing one summary.

After refreshing, verify a known total against the source. For example, filter the source to a small category with a handful of records and compare its sum with the corresponding PivotTable total. Also check the refresh status or error message for external data. This simple reconciliation helps distinguish a successful update from a summary that is current but filtered, grouped, or using a different source than expected.

Frequently Asked Questions

Why does my PivotTable not include new rows?
Its source may be a fixed range that ends before those rows. Use PivotTable Analyze > Change Data Source to extend the range, or use an Excel table that expands as records are added. Then refresh the PivotTable.

How do I refresh every PivotTable in a workbook?
Choose Data > Refresh All in Excel for Microsoft 365 on Windows. This updates workbook connections as well as supported PivotTables. If a particular external connection errors, resolve that connection problem before expecting its PivotTable to update.

Why is Refresh All not updating my PivotTable?
Check whether the source range includes the new data, whether an external query or connection completed successfully, and whether filters hide the changed item. If the PivotTable source is the Data Model, refresh the model or its underlying query and confirm the PivotTable uses that model.

Can I make a PivotTable refresh automatically?
You can enable Refresh data when opening the file in the PivotTable Options dialog’s Data tab. This refreshes on opening when the source is available; it does not guarantee continuous updates while the workbook remains open.

Will refreshing remove my PivotTable formatting?
Refresh normally updates the summary without requiring you to rebuild it, but layout and formatting options can affect how the result is displayed. If formatting changes unexpectedly, open PivotTable Options and review its layout and format settings. Keep a copy of important workbooks before making broad source or layout changes.

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.