Pivot Table Not Updating with New Data - Intro
If your pivot table is not updating with new data, the problem may not be the Refresh button. In many cases, your PivotTable is still connected to an older source data range that does not include the newly added rows or columns. The solution is to check the source range first and then refresh the report.
In Microsoft Excel, you may need to refresh the PivotTable or expand its original data source. In Google Sheets, pivot tables refresh automatically when the source cells they use change, but newly added data outside the selected range must be included manually. This guide explains how to identify the problem, fix it step by step, and prevent it from happening again.
Why Is Your Pivot Table Not Updating With New Data?
If your PivotTable is not updating with new data in Excel, refreshing it may not be enough. The underlying problem is often an outdated source range that excludes newly added rows or columns. Other possible causes include an inactive data connection, incorrect source headers, hidden filter selections, or an outdated PivotTable cache. The quickest way to diagnose the problem is to refresh the report first. If the missing records still do not appear, inspect the source range through PivotTable Analyze > Change Data Source and expand it to include the complete dataset. For workbooks that receive frequent updates, converting the source data into an Excel Table provides a more reliable long-term solution. This guide walks you through seven practical fixes, including the exact Excel ribbon navigation, a real sales-data example, and separate instructions for Google Sheets.
Understand Why Your Excel PivotTable Is Not Updating
A PivotTable is designed to summarize data from a specified source, such as a worksheet range, Excel Table, Power Query connection, or external database. Unlike a standard worksheet formula, its report does not necessarily display every change to the source immediately.
Excel maintains PivotTable data separately through a mechanism called the PivotTable cache. Refreshing the report updates the cached information from the applicable data source. However, refreshing a PivotTable does not automatically solve every source-range problem.
For example, imagine creating a PivotTable using the following source:
Sheet1!$A$17:$E$31
You later append five additional sales transactions in rows 32 through 36. The existing PivotTable still references the original range, so those transactions remain outside its source. You can refresh the report repeatedly, but the missing records will not be included until the source range is expanded.
The most common causes can be grouped into the following categories:
- Refresh problem: Existing source values have changed, but the PivotTable has not been refreshed.
- Source-range problem: Newly added rows or columns fall outside the original data range.
- Field problem: A newly added column is unavailable or has not been added to the report layout.
- Filtering problem: The PivotTable contains the updated records, but filters or slicers hide them.
- Source-structure problem: Blank headers, inconsistent columns, or an incorrect source selection prevent the expected results.
- Connection problem: Power Query or an external data connection has not retrieved the latest information.
Identifying the correct cause is important because each situation requires a different solution.
Fix 1: Refresh Your PivotTable to Update Existing Data
Start with a manual refresh before changing the source range. This is the appropriate solution when you have modified existing values or added records within the range that the PivotTable already uses.
Method 1: Refresh Using the Right-Click Menu
- Open your Excel workbook.
- Navigate to the worksheet containing the PivotTable.
- Right-click any cell inside the PivotTable.
- Select Refresh from the context menu.
- Wait for Excel to finish updating the report.
- Compare the refreshed results with the original source data.
Method 2: Refresh From the PivotTable Analyze Ribbon
- Select any cell inside your PivotTable.
- Open the PivotTable Analyze tab.
- Find the Refresh command in the Data group.
- Click Refresh to update the selected report.
You can also press Alt + F5 in supported Windows desktop versions of Excel to refresh the selected PivotTable.
When your workbook contains multiple PivotTables or external data connections, use Data > Refresh All to update the applicable workbook data and reports.
Important distinction: Refresh updates data from the source currently assigned to the PivotTable. If your new transactions are outside a fixed source range, you must expand the range first. Refresh alone will not include those excluded records.
Fix 2: Change the PivotTable Data Source to Include New Rows
If your PivotTable still does not include recently added records after refreshing, check its source range. This is one of the most important troubleshooting steps when a PivotTable is not updating with new rows.
Excel provides a built-in Change Data Source command that lets you expand the existing worksheet range without unnecessarily rebuilding the entire report.
Step-by-Step: Change Data Source in Excel
- Open the worksheet containing your existing PivotTable.
- Click any cell inside the PivotTable to activate its contextual ribbon.
- Select the PivotTable Analyze tab.
- Locate the Change Data Source command in the Data group.
- Click its dropdown arrow.
- Select Change Data Source... from the menu.
- In the Change PivotTable Data Source dialog box, locate the Table/Range field.
- Replace the existing source reference with the updated range that includes all required records.
- Click OK to apply the new source.
- Refresh the PivotTable and verify that the missing records now appear.
In the supplied Excel screenshot, the navigation is marked with three steps:
- 1: Open the PivotTable Analyze tab.
- 2: Click the Change Data Source dropdown.
- 3: Select Change Data Source... from the dropdown menu.
This opens the dialog box where you can inspect and modify the PivotTable's current source reference.
Practical Example: Expand the Existing Source Range
Suppose your original sales worksheet contains the following data:
Original source range: Sheet1!$A$17:$E$31
After adding five more sales transactions, your complete dataset extends to row 36.
Updated source range: Sheet1!$A$17:$E$36
Replace the old reference with the updated one in the Table/Range field. Click OK and refresh the PivotTable.
Make sure the new reference includes the original header row, every existing transaction, and all additional records. Do not select only the newly added rows, because the PivotTable needs the complete source dataset to produce an accurate summary.
Worked Example Using Your Attached Sales Workbook
Your supplied workbook, pivot table not updating with new data.xlsx, contains a product-sales dataset with customer names, cities, product names, sales amounts, and sales representatives. The PivotTable summarizes sales by product.
The following calculation illustrates what happens when a PivotTable source range excludes the five additional transactions.
| Dataset | Transactions | Sales Total |
|---|---|---|
| Original records (rows 18–31) | 14 | $23,271.99 |
| Additional records (rows 32–36) | 5 | $17,529.05 |
| Complete dataset (rows 18–36) | 19 | $40,801.04 |
The example demonstrates why an incomplete source range can produce an incorrect Grand Total even when the original records refresh successfully.
If your PivotTable originally referenced Sheet1!$A$17:$E$31, expanding it to Sheet1!$A$17:$E$36 would include the additional transactions. The complete workbook's displayed PivotTable Grand Total is $40,801.04.
Verification tip: After changing the source, compare the PivotTable Grand Total with the total of the complete source dataset. This is a practical way to confirm that the missing records have actually been included.
Fix 3: Resolve a PivotTable Not Updating With a New Column
A PivotTable that does not recognize a newly added column has a slightly different problem from one that ignores additional rows.
For example, imagine that your original sales worksheet contains these fields:
- Customer
- City
- Product
- Sales (USD)
- Sales Rep
Now suppose you add another column named Sales Region. If the original PivotTable source ends at column E, the newly added field in column F is not included in the source definition.
To make the new column available:
- Select the existing PivotTable.
- Open PivotTable Analyze > Change Data Source > Change Data Source.
- Expand the source reference to include the additional column.
- Ensure the new column has a meaningful, non-blank header.
- Click OK and refresh the PivotTable.
- Open the PivotTable Fields list.
- Find the newly available field.
- Drag it into Rows, Columns, Values, or Filters according to your reporting requirements.
For example, if the previous reference was:
Sheet1!$A$17:$E$36
And the new Sales Region field is in column F, update the reference to:
Sheet1!$A$17:$F$36
One important detail: making a field available does not automatically place it into your PivotTable report. You must also add it to the appropriate field area.
If the source structure has changed substantially, including major column additions or removals, creating a new PivotTable from the revised dataset may be cleaner than modifying the existing one.
Fix 4: Convert Your Source Data Into an Excel Table
If you frequently append new transactions, manually expanding the PivotTable source range every time is inefficient. A better long-term solution is to convert the original dataset into an Excel Table and use that table as the PivotTable source.
Unlike a fixed cell reference, an Excel Table can automatically expand when you add records immediately below its existing rows or add adjacent columns. The PivotTable can then retrieve those additions when refreshed.
How to Create an Excel Table
- Open the worksheet containing your original source data.
- Click a cell inside the dataset.
- Press Ctrl + T on your Windows keyboard. Alternatively, use Insert > Table.
- Check that the selected range includes all required data.
- Enable My table has headers if the first row contains column names.
- Click OK to create the Excel Table.
- Select any cell inside the newly created table.
- Open Table Design and assign a meaningful table name, such as SalesData.
Use the Excel Table as Your PivotTable Source
- Select the existing PivotTable.
- Go to PivotTable Analyze > Change Data Source > Change Data Source.
- Choose the table or range source option, where available.
- Enter the name of your Excel Table, such as SalesData, in the source field.
- Click OK and refresh the PivotTable.
- Save the workbook after confirming the updated report.
When you add new records, enter them directly below the table or paste them into the first row below the table so Excel can expand it. Check that the new records are actually inside the table. If they are not, use Table Design > Resize Table to expand the table manually.
Best long-term practice: Use a named Excel Table as the source for regularly updated reports. New rows within the expanded table can be included through a normal PivotTable refresh, avoiding repeated manual cell-range adjustments.
Fix 5: Check PivotTable Filters, Headers, and Source Data
Sometimes a PivotTable has already retrieved the latest data, but the report still looks incomplete. Before changing its source again, examine the filters and the structure of the original dataset.
Check Filters and Slicers
PivotTable filters and slicers can hide otherwise valid records. A new category may exist in the source data but remain invisible because a field is restricted to selected values.
To investigate:
- Refresh the PivotTable after confirming its source range.
- Open the dropdown menu for the relevant row, column, or report filter.
- Review which values are currently selected.
- Clear the filter temporarily to inspect all available records.
- Check connected slicers and timelines for restricted selections.
- Restore only the filters required for your report.
Validate Your Source Data Structure
A PivotTable works most reliably when its source is organized as a clean, consistent table.
- Use one header row with unique column names.
- Do not leave required column headers blank.
- Avoid merged cells within the source dataset.
- Keep each record on a separate row.
- Maintain consistent data types within each column.
- Avoid blank rows and columns inside the dataset.
- Ensure newly added records follow the original column structure.
For example, a Sales (USD) column should contain numeric values rather than a mixture of numbers and manually entered text. Inconsistent source values can affect how Excel summarizes the field.
If you have corrected the source structure, refresh the PivotTable again and review the resulting field list and calculations.
Fix 6: Troubleshoot Power Query, External Connections, and Data Models
Not every PivotTable is connected directly to a worksheet range. Some reports are based on Power Query, external databases, data feeds, or the Excel Data Model.
When the source uses one of these connections, refreshing the PivotTable may not be sufficient if the underlying query or connection is not retrieving the latest information.
Check Power Query and External Connections
- Open the Excel workbook.
- Go to the Data tab.
- Open Queries & Connections, where available.
- Check whether the relevant query or connection is present and available.
- Verify that its source file, database, or connection is accessible.
- Review query filters and transformation steps that could exclude the new records.
- Use Refresh All to update the applicable connections and reports.
- Check the PivotTable again after the refresh process finishes.
If your source file has moved, been renamed, or requires updated credentials, correct the connection before refreshing.
Understand the Excel Data Model Limitation
PivotTables based on the Workbook Data Model have different source-management rules from ordinary worksheet-based PivotTables. In particular, Microsoft documents that the source of a PivotTable based on the Workbook Data Model cannot be changed through the normal Change Data Source workflow.
In this situation, investigate the underlying Data Model tables, Power Query queries, and connections instead of repeatedly trying to replace the PivotTable's worksheet range.
For additional technical guidance, refer to Microsoft's instructions for changing PivotTable source data.
Fix 7: Resolve a Google Sheets Pivot Table Not Updating With New Data
Google Sheets handles PivotTable updates differently from desktop Excel. According to Google's documentation, a Google Sheets pivot table refreshes whenever its existing source data cells change.
However, newly added rows or columns outside the original source range may not be included automatically. This is why a Google Sheets pivot table may appear outdated even though you have entered additional records in the source worksheet.
How to Change the Google Sheets Pivot Table Source Range
- Open your spreadsheet in Google Sheets.
- Click anywhere inside the existing pivot table.
- Click the pop-up Edit button underneath the pivot table.
- Locate the Pivot table editor on the right side of the screen.
- Click Select data range.
- Review the currently selected source range.
- Extend the range to include all newly added rows or columns.
- Confirm the change and review the available fields.
- Add any new fields to Rows, Columns, Values, or Filters as required.
For example, if your original source range is:
Sheet1!A1:E100
And the dataset has grown to row 150, update the reference to:
Sheet1!A1:E150
Also review any active filters that might exclude newly added categories. The records may already be included in the source range but hidden from the report because of the current filter selection.
For the official interface instructions, see Google's guide to creating and using pivot tables.
Enable Automatic PivotTable Refresh Where Available
If you regularly modify the source data, automatic refreshing can make your workflow more convenient. However, it is important to distinguish automatic refreshing from automatically expanding a fixed source range.
Microsoft's current documentation describes a PivotTable Auto Refresh feature for compatible Microsoft 365 builds. Its availability depends on the Excel version and release channel; Microsoft's documentation identifies it as an Insider-program feature.
Where the feature is available:
- Select your PivotTable.
- Open PivotTable Analyze.
- Look for Auto Refresh in the Data group.
- Enable it if the option is available in your installation.
- Alternatively, check the relevant automatic-refresh setting in PivotTable Options under the Data tab.
Auto Refresh is associated with the data source, so changing its setting may affect other PivotTables connected to the same source.
Do not confuse these two features: Auto Refresh updates data from the applicable source. It does not make an incomplete fixed source range include records that are outside that range. Your source range or Excel Table must still cover the required data.
Complete PivotTable Troubleshooting Reference
Use this reference to identify the most appropriate fix without repeatedly changing settings that are unrelated to the actual problem.
| Observed problem | Likely cause | Recommended action |
|---|---|---|
| Existing values remain outdated | PivotTable has not been refreshed | Use Refresh or Refresh All. |
| New rows are missing | Fixed source range | Expand the source using Change Data Source. |
| New columns are missing | Additional fields are outside the source | Expand the range, refresh, and check the Field List. |
| New categories are invisible | Filters or slicers | Review the selected filter values. |
| New records are outside the Excel Table | Table did not expand | Resize the table or add records within its boundary. |
| External data is outdated | Connection or query issue | Check the connection and use Refresh All. |
| Data Model source cannot be changed | Workbook Data Model limitation | Update the underlying model or query. |
| Google Sheets records are missing | Incomplete pivot source range | Use Select data range in the Pivot table editor. |
Pivot Tables Not Updating With New Data - FAQs
Even after following the main troubleshooting steps, certain PivotTable situations can be confusing. The following answers address common questions about missing records, source-range changes, new fields, automatic refresh behavior, and the differences between Excel and Google Sheets.
Why is my PivotTable not updating even after refreshing the data?
Your PivotTable may be using an incomplete source range that excludes the new records. Select the PivotTable and open PivotTable Analyze > Change Data Source > Change Data Source. Check the Table/Range field, expand it to include all required rows and columns, and refresh the report again. Also check filters and external data connections if the expected results remain missing.
How do I change the source data range of an existing Excel PivotTable?
Select a cell inside the PivotTable, open the PivotTable Analyze tab, and choose Change Data Source > Change Data Source. Enter the updated worksheet reference in the Table/Range field, including the header row and all required records. Click OK and refresh the PivotTable to apply the change.
How can I automatically include new rows in an Excel PivotTable?
Convert your source data into an Excel Table using Ctrl + T, confirm that it has headers, and use its table name as the PivotTable source. Excel Tables can expand when new records are added directly below the existing data. Refresh the PivotTable after adding the records to update the report. Confirm that the new rows are actually included within the table.
Why is my PivotTable not showing a newly added column?
The newly added column may be outside the original PivotTable source range. Expand the source through Change Data Source, making sure the new column has a valid header. Refresh the report and open the PivotTable Fields list. Once the field becomes available, add it to the required Rows, Columns, Values, or Filters area.
Why is my Google Sheets pivot table not updating with new rows?
Google Sheets automatically updates pivot tables when their existing source cells change. However, new records outside the original source range are not necessarily included. Click the pivot table, select its Edit option, open the Pivot table editor, and use Select data range to include the additional records. Review any filters that may be hiding new values.
Does Excel automatically refresh PivotTables when source data changes?
Traditional PivotTable workflows generally require a manual refresh or an enabled refresh-on-open setting. Microsoft also documents an Auto Refresh feature for compatible Microsoft 365 builds, currently identified as an Insider-program feature. Its availability depends on the Excel release you use. In every case, the source must include the data you want to analyze.
Why does my PivotTable show the wrong Grand Total after adding new data?
An incorrect Grand Total can indicate that some records are missing from the source range or that filters are excluding particular transactions. Compare the PivotTable's source reference with the complete dataset, expand it when necessary, refresh the report, and temporarily clear filters. Also verify that the source values are correctly formatted as numbers rather than text.
Still experiencing missing records, unexpected totals, or unavailable fields in your spreadsheet? Share your workbook scenario and the steps you have already tried in the comments section, where commenting is enabled. For a more specific Excel or Google Sheets question, you can also reach out through the website's Contact Us page. A clear description of your source range and the expected result will make it easier to understand the issue.
Our Final Thoughts On Pivot Table Not Updating with New Data
A PivotTable that does not update with new data is often a source-management problem rather than a damaged workbook. Understanding the difference between refreshing existing records and expanding the source range is the key to resolving most issues.
Start with a manual refresh. If new rows or columns remain missing, inspect the existing source using PivotTable Analyze > Change Data Source. For regularly updated Excel workbooks, convert the original dataset into a properly maintained Excel Table. When working with Google Sheets, verify the selected source range in the Pivot table editor.
Finally, compare your PivotTable totals with the complete source dataset. This simple verification step helps identify missing transactions and incorrect summaries before they affect your reports.
By applying the right fix and using a suitable data structure, you can keep your spreadsheet reports consistent, accurate, and easier to maintain as your dataset grows.
