How to Refresh Excel Pivot Tables to Include New Source Data
Question details
Ensure that newly added data rows in a master worksheet correctly populate inside related pivot tables after a manual refresh.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Appending new data rows to an existing dataset that is actively being summarized by a pivot table.
- Observed behavior
- Newly added rows do not appear in the pivot table after clicking refresh because the original source range remains static and does not encompass the new rows.
Verify that your newly added data rows are adjacent to the existing dataset without completely blank rows or columns separating them.
Convert Source Data to an Excel Table for Automatic Expansion
Converting your raw data into a dynamic Excel Table ensures any newly added rows are automatically included in the pivot table source range without manual adjustments.
This is the most reliable long-term solution. By using an Excel Table as the data source, the range automatically expands whenever you append new data, meaning you only need to click 'Refresh' going forward.
Highlight your entire original dataset including the headers.
Press the keyboard shortcut Ctrl+T, ensure 'My table has headers' is checked, and click OK.
Click inside your existing pivot table, navigate to the 'PivotTable Analyze' tab, and click 'Change Data Source'. Enter the new Table name (e.g., Table1) and click OK.
Add your new rows immediately below the table. Then, right-click the pivot table and select 'Refresh' to see the updated figures.
Manually Update the Pivot Table Data Source Range
Adjust the pivot table settings to manually redefine the range of cells it captures, ensuring the new rows are included.
Easily Manage Dynamic Pivot Tables with WPS Spreadsheet
WPS Spreadsheet provides robust, easy-to-use data analysis tools including full support for dynamic tables and pivot tables. Easily update your data sources and refresh your reports in seconds.
- 1. Open Your Data File: Launch WPS Spreadsheet and open your existing workbook containing the raw data.
- 2. Format as Table: Select the data range and press Ctrl+T to convert it into a dynamic table.
- 3. Insert Pivot Table: Navigate to the Insert tab and click PivotTable. Choose to place it on a new or existing worksheet.
- 4. Refresh Easily: Whenever you append new data to the bottom of your table, simply right-click the pivot table and choose 'Refresh'.
Frequently Asked Questions
Why does my pivot table not update automatically when I change the data?
Pivot tables do not auto-refresh by default because continuously recalculating large datasets can severely slow down your computer's performance. You must manually trigger a refresh after altering the source data.
Can I make my pivot table refresh automatically when opening the file?
Yes. Right-click the pivot table, select 'PivotTable Options', navigate to the 'Data' tab, and check the box that says 'Refresh data when opening the file'. Click OK.
What happens to the pivot table if I delete rows from the original source data?
The deleted rows will still be reflected in the pivot table's calculations until you perform a refresh. Once you right-click and select 'Refresh', the removed data will instantly disappear from the pivot view.
