To fix an Excel PivotTable refresh error, select the PivotTable and choose PivotTable Analyze > Refresh; to update every connection and PivotTable in the workbook, choose Data > Refresh All. If refreshing fails or the results stay out of date, check the source range, source data structure, and any external connection—the message usually points to one of these rather than to the PivotTable layout itself.
What a refresh does
A PivotTable summarizes data held in a source range, an Excel table, or a connection to another source. Refresh asks Excel to read the source again and update the summary. It does not automatically repair missing columns, invalid source values, broken file paths, or a source range that excludes new rows. That distinction matters: a refresh can complete successfully while still showing incomplete results if Excel is reading the wrong range.
Many PivotTables use a stored cache of source data. Refreshing updates that cache, then recalculates the displayed summary. Workbooks connected to external databases, text files, or the Excel Data Model have additional connection and permission steps, so their errors may not be solved by changing the PivotTable’s visible fields. First identify whether the underlying source is a worksheet range, an Excel table, or an external connection.
Start with a safe troubleshooting order
Before changing the layout or deleting anything, save the workbook. Then try the basic checks in order. This keeps the diagnosis focused and avoids disrupting a report that may already contain custom formatting or filters.
- Select a cell inside the PivotTable and choose PivotTable Analyze > Refresh. If the PivotTable is connected to an external source, watch for a credentials or connection prompt.
- Choose Data > Refresh All if the workbook has multiple PivotTables or connections. A slow refresh may simply be processing several sources.
- Read the full error message and note which query, connection, or field it names. If Excel reports success but numbers seem wrong, inspect the source range rather than repeatedly refreshing.
- Check whether the source has the expected headers, rows, and data types. Then verify the PivotTable’s source through PivotTable Analyze > Change Data Source when that command is available for its source type.
- Refresh again and compare a small, easily checked total in the source with the matching PivotTable result.
On Mac, the relevant PivotTable and Data ribbon tabs provide the same general refresh commands, although their placement and available options can differ by Excel build. In Excel for the web, some connection and data-model management features are more limited than in the desktop app; open the workbook in desktop Excel if the web version does not expose the needed source controls.
Check whether new rows are inside the source
A frequent cause of a refresh that appears to do nothing is an incomplete source range. For example, a PivotTable may use cells A1:D500, while new transactions have been added in rows 501 to 540. Refresh updates the existing range; it does not necessarily expand a fixed range to include those extra rows.
Check the source by selecting the PivotTable and opening PivotTable Analyze > Change Data Source. If the displayed worksheet reference stops before the new records, select the complete range and refresh. A more reliable setup for recurring additions is to convert the source list to an Excel table: select a source cell, choose Insert > Table, confirm whether the table has headers, and use that table as the PivotTable source. Excel tables expand as rows are added directly beneath them, so future refreshes can include those additions.
That does not mean every change is included automatically. The PivotTable still needs to be refreshed, and rows pasted outside the table may not become part of it. Also check that the new records follow the same column structure. A table with headers such as Date, Region, and Sales cannot treat an extra note column or a blank header as a normal matching field.
Repair source headers and inconsistent data
Each source column should have a nonblank, unique header. Blank headers, merged header cells, or duplicate names can prevent Excel from creating or refreshing a PivotTable correctly. Give every column a clear label, unmerge the header row if necessary, and avoid inserting title rows or subtotals within the source list. The first row of the source should describe the fields, not serve as a decorative report heading.
Inconsistent values can cause different symptoms. A date column containing genuine dates alongside text that looks like dates may not group as expected. Numbers stored as text may be counted rather than summed, or may not behave like other numeric values. Blank cells and error values can also complicate calculations or grouping. Inspect the source column, standardize its data type, and correct or deliberately handle invalid entries before refreshing.
For example, if a date field contains one entry typed as 31/02/2026, that is not a valid calendar date. Refreshing cannot turn it into a valid date, and grouping the field by month may fail or produce unexpected categories. Correct the source record, refresh, and then check the PivotTable’s grouping or value-field settings.
Understand common error messages
The wording varies by Excel version and source type, but the message often indicates the class of problem. A source-reference error suggests that the range, table, file, or connection cannot be found. A message about a field name commonly points to a missing or blank source header. An error when grouping a selection often calls for checking that the field contains consistent dates or numbers and does not include problematic text or errors.
If a PivotTable refresh reports that the source data is invalid, inspect the source layout before rebuilding the PivotTable. If a query or connection fails, check its separate connection settings and source availability. If there is no error but the result is stale, verify that the source actually changed, that the changed rows are included, and that the correct PivotTable was refreshed. A filtered-out item can also make a valid result look missing; inspect the field filters and slicers before assuming the data was lost.
Do not clear the PivotTable cache or recreate the report as a first response. Those actions may remove useful configuration or fail to address the root cause. Work from the source outward: validate the data, confirm the source reference, refresh, then investigate the display and filters.
Fix external connection and Data Model problems
A PivotTable based on a database, text file, online service, or another workbook depends on more than the visible worksheet. The source may have moved, a network location may be unavailable, a sign-in may have expired, or the account may no longer have permission. Start with Data > Queries & Connections to identify workbook connections and queries, where available, and inspect the specific item that failed. Confirm that the source is accessible and that any required credentials or permissions are current.
For a query-based source, refresh the query as well as the PivotTable if needed. A PivotTable built on the Data Model may not use the ordinary worksheet-range workflow; its source can depend on model tables and relationships. If the error identifies a query, model table, or connection, fix that component first, then refresh the PivotTable. Some model and connection-management tools vary by platform and Excel edition, so a workbook created with desktop Power Pivot features may require desktop Excel on Windows for full editing.
Use Data > Refresh All thoughtfully in large workbooks. It may update multiple queries and connections, take time, or prompt for access. If one connection fails, other refreshes may still have completed; check each connection’s status rather than assuming the whole workbook remained unchanged.
When the refresh works but the numbers still look wrong
A successful refresh is not proof that the report is summarizing the intended records. Compare the PivotTable source with the source data, check filters and slicers, and verify the field arrangement. In the Values area, a field may be summarized by Count instead of Sum, particularly when Excel detects text values. Right-click a value and choose Summarize Values By to inspect or change the calculation where appropriate.
Also check whether the underlying source contains formulas. If those formulas have not recalculated, the PivotTable may faithfully summarize old formula results. Excel’s calculation mode is on the Formulas tab under Calculation Options; Automatic is the usual choice for routine work, while Manual can leave formula results unchanged until recalculation. Recalculate the source formulas, then refresh the PivotTable.
Finally, remember that a PivotTable can retain field items that no longer appear in the source, depending on its settings and cache. A leftover label does not necessarily mean the source record still exists. Review the PivotTable options for retained items if obsolete labels are the issue, and distinguish those labels from current totals.
Prevent repeat refresh failures
A few design habits make recurring reports easier to maintain:
- Keep one header row with unique, nonblank field names and no merged cells.
- Use an Excel table for a growing worksheet source, and add new records within the table.
- Keep each source column consistent: dates as dates, numbers as numbers, and categories as text.
- Use stable paths and permissions for external sources, and document which connection feeds each report.
- Save a backup before major source or connection changes, especially in a workbook shared with other people.
- After refresh, verify one or two totals against the source instead of relying only on the absence of an error.
For a shared report, agree on who may change the source structure. Renaming or removing a source column can break a PivotTable, query, or downstream report even if the data itself remains intact. Adding records while preserving existing field names is usually less disruptive than changing the schema.
Frequently Asked Questions
Why is my PivotTable not updating after I change the source data?
Select the PivotTable and choose PivotTable Analyze > Refresh. If the changed records still do not appear, check whether they fall inside the source range or Excel table and whether the PivotTable uses a different connection.
Does Refresh All update every PivotTable in a workbook?
Data > Refresh All refreshes workbook connections and refreshable data, including PivotTables tied to those sources. Results depend on the source type and whether its connection can be reached; inspect the individual connection if one item fails.
Why does Excel say the PivotTable field name is not valid?
Check that the source header row has a label in every column and that labels are unique. Remove merged header cells and make sure the selected source range begins with the actual column names.
Why can’t I group dates in a PivotTable?
Check for text entries, blank or error cells, and values that are not valid dates in the source field. Standardize the column, correct invalid entries, refresh, and try grouping again.
Can I fix a PivotTable refresh error by rebuilding the PivotTable?
Sometimes rebuilding helps when the PivotTable itself is damaged or its configuration is unsuitable, but it will not fix a missing file, broken connection, invalid header, or incomplete source range. Diagnose the source and connection first.
Related
- Excel Password Protect to Keep a Workbook from Opening?
- Excel Packing List Template: What to Include and How to Use
- Excel Ordinal Number Format: Show 1st, 2nd, and 3rd
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Speedometer Chart: Build One and Make It WorkAn Excel speedometer chart is a dashboard-style gauge that shows a value on a semicircular scale; Excel does…
- How to Make a Chart in Excel From Your DataTo make a chart in Excel, select the cells containing your data, choose Insert > Recommended Charts, pick…
- Box and Whisker Plot Excel X-Axis Labels ExplainedIn Excel for Microsoft 365 on Windows, box-and-whisker plot x-axis labels usually come from the headers above each…
- Excel Chart Group by Category: How to Set It Up RightTo group Excel chart data by category, first summarize the records so each category has one row, then…
- Microsoft Excel Waterfall Chart: Create and Format OneTo create a waterfall chart in Microsoft Excel for Microsoft 365 on Windows, select a table with category…
- Black Bullseye Chart in Excel: How to Create OneA black bullseye chart is usually a custom target graphic made from concentric circles; it is not a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.