OmniDocHow to update the data source and refresh the data in the pivot table
Explain the difference and operation steps between refreshing and changing the data source area of a pivot table, covering how to make the pivot table contain new data after adding rows and columns, as well as common situations such as automatic refresh when opening a file and batch processing of multiple pivot tables.
Key points of this article
- If you only change the value of an existing cell, you can refresh it directly; if a new row or column is added to the data table, you must first change the data source area and then refresh it.
- After converting the source data into a table (Ctrl+T), the newly added rows will be automatically included, and there is usually no need to repeatedly modify the area.
- The command to change the data source is in the data grouping of the pivot table analysis tab, enter the new area and confirm.
- Refreshing data when opening a file can automatically update when opening a workbook, but ordinary pivot tables do not support scheduled automatic refresh.
When making reports, a common problem is: hundreds of new rows have been added to the source data table, but the totals in the pivot table have not changed. The reason is usually that the pivot table is remembering an old data range, or the data is simply not being reread. This article explains clearly the difference between "refresh" and "change data source" of a PivotTable report, and which one should be used under what circumstances.
After reading this article, you can determine which situation you belong to, follow the steps to make the pivot table include new data, and check whether it is really updated after getting the results. The following takes the common desktop version of Excel as an example. The interface location of other versions may be slightly different, but the idea is the same.
How to update the data source in a PivotTable? One sentence conclusion
If you only modify the value of an existing cell, refreshing it directly is enough. If new rows or columns are added to the source data, the original area of the pivot table will not be automatically expanded, and the data source area must be changed first.
| your situation | Recommended practices | reason |
|---|---|---|
| Only the value of existing cells is modified | Right-click the pivot table and select Refresh, or press Alt+F5 | The pivot table will re-read the data in the original area |
| Source data has new rows in the normal range | First change the data source to expand the area, then refresh | The range of normal areas does not expand as data increases |
| The source data has been converted into a table (Ctrl+T) | Refresh directly, generally no need to change the region | After the table is expanded, the table name referenced by the pivot table will automatically include new rows |
| See new results every time you open the file | Set refresh data when opening a file | No need to refresh manually, but it will be slower to open when the file is larger. |
| There are multiple pivot tables in the workbook | Check data sources one by one, or process them in batches with macros | Different pivot tables may use separate data caches |
Before you start: Confirm these points first
- Whether the source data is continuous and has a header row. The empty rows or columns in the middle will cause Excel to misidentify the area, and the pivot table will only read part of it.
- Whether the source data is already a table. If it is a table, subsequent new data will be automatically included, and the number of area changes will be greatly reduced. If not, it is recommended to convert it first and then build the pivot table.
- Whether the column structure of the new data is consistent with the original one. If the column names are changed or the column order is changed, the pivot table fields may be lost or an error will be reported. You need to align the table headers first.
- Whether the data source is PDF. If the table is originally in PDF, you can first use the OmniDoc desktop client to export the PDF to Excel format, and then put it into the source data of the pivot table. After exporting, the numbers must still be checked item by item to avoid identification errors being brought into the report.
- Whether to keep a backup. Changing the data source will directly change the calculation range of the pivot table. Saving a copy can facilitate comparison of the results before and after.
Steps to change the PivotTable data source range in Excel
- Click any cell in the pivot table to let the analysis tab related to the pivot table appear in the ribbon.
- Switch to the Analysis tab of the pivot table, find the command to change data source in the data grouping, and click it.
- In the pop-up dialog box, enter directly in the area input box or select a new area, such as the range from A1 to F500 in Sheet1.
- Verify that the range contains the header row and all new data rows, and click OK.
- View the field list. If a new column is added, check it in the field list and add it to the row, column or value area.
- Press Alt+F5 to refresh once and confirm that the total and detail row numbers have changed.
Check each item after completion
After refreshing or changing the area, do not use the results directly. It is recommended to check the following items one by one.
| Check items | How to see | What to do if there is a problem |
|---|---|---|
| Number of data rows | Compare the number of valid rows in the source table with the perspective table count | If it is less than the source table, check whether the last line is missed in the area, and re-select it. |
| Total amount | Calculate it once with the source table SUM function and compare it with the total amount of the PivotTable | If it is inconsistent, check if there is a text number, which needs to be converted to a numerical value. |
| New field | Check to see if a new column appears in the field list | When it does not appear, check whether the header of the new data is empty or duplicated. |
| Date grouping | Check that the date field is correctly identified | The date in text format needs to be converted before refreshing. |
| Old data residue | Compare options for filters and slicers | Delete invalid items, or refresh after clearing the filter |
# Core comparison
Refresh, change data source and automatic refresh solve different problems, and it is easy to miss updates when mixed.
| Operation | Action | Applicable scenario |
|---|---|---|
| Refresh | Re-read the data in the current area | The content of the source data has changed, and the range of rows and rows has not changed. |
| Change the data source | Modify the region or table name referenced by the PivotTable | New rows or columns have been added to the source data, and the scope needs to be expanded |
| Refresh when opening a file | Refresh automatically every time you open the workbook | The data is updated frequently, and the new results are seen every time. |
# frequently asked questions (FAQ)
1. How to set automatic update for PivotTable?
In the option settings of the PivotTable, find the section related to the data, and check the box to refresh the data when you open the file, which can be refreshed automatically every time you open the workbook. Ordinary PivotTable does not have the option of regular automatic refresh, if you need regular execution, it is usually achieved with the help of macros and other means.
2. PivotTable batch changes to the data source how to do?
If multiple PivotTables are created based on the same data cache, just change the data source of one of them, and the other PivotTables will be updated together. If they use separate caches, you need to change them one by one, or write macros to handle them uniformly. It is recommended that you save the backup before batch operation.
3. What if the PivotTable field is missing after changing the data source?
In most cases, the column name of the new data is inconsistent with the original, or the column is deleted or moved. Change the header back to its original name, refresh it, and the field usually reappears.
4. What is the difference between the PivotTable "Refresh data" and "Update data Source"?
Refresh is to re-read the data, which is applicable to the situation where the content changes; to update the data source is to modify the scope, which is applicable to the case of new rows and rows. Add a new row but only click to refresh, the result will not change, you need to change the area first.
# Summary
The key to how to update the data source of the PivotTable is to judge whether a new column has been added: refresh after changing the value, change the data source area when the data is added, and convert the source data into a table as far as possible. Follow the steps and checklist operations in this article so that the totals and details in the report can be consistent with the source data.
