What Is Update Pivot Table In Excel?
Pivot Table update in Excel is a manual method to refresh the Pivot Table data to reflect the changes made to the source dataset. The updates could be one or more value changes, new data additions or deletions in the source dataset.
Users can use the Pivot Table update option for an auto data range update for Pivot Table with an Excel Table and to update multiple Pivot Tables in one go.
For example, the following image shows a source dataset in the cell range A1:C16 and a Excel Pivot Table in the cell range E1:I8 based on the source dataset.
The source dataset contains students’ Physics, Chemistry, and Biology test scores.
Let us assume we change the Chemistry test score of a student, Jean Rogers, from 90 to 92 in the source data. And the requirement is to update the new value in the Pivot Table.
Then, though we can use the Pivot Table update VBA option, the more straightforward method is to use the Refresh option from the context menu.
It is not possible to achieve the Pivot Table update automatically.
However, we can right-click a cell in the Pivot Table and select the Refresh option from the contextual menu. It will change the required Chemistry test score from 90 to 92 in the Pivot Table.
Table of contents
Key Takeaways
- The Pivot Table update methods help us manually refresh a Pivot Table based on the changes made to the source dataset. The changes can be value updates and data additions or deletions.
- Users can update the Pivot Table to ensure they work with the latest one without unrefreshed data.
- We can update the Pivot Table for auto data range update of a Pivot Table based on changes in the source Excel Table and to refresh multiple Pivot Tables in one go.
- Excel offers an option in the PivotTable Options window to update the Pivot Tables automatically, but only when we open the specific Excel file.
How To Update Pivot Table?
While Pivot Table update VBA is feasible, we shall see how to update a Pivot Table using the Refresh option.
- Ensure the new values updated in the source dataset are correct.
- Click a cell in the Pivot Table, created based on the source dataset, to enable the Analyze tab in the ribbon. Next, select the Refresh command down arrow → the Refresh option in the Analyze tab. Otherwise, press Alt + F5.
[ Alternatively, right-click a cell in the Pivot Table and choose the Refresh option from the contextual menu.]
Once we click the Refresh option, we will see the new values updated in the Pivot Table according to the changes made to the source dataset.
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
Basic Example
We shall see an example of the Pivot Table update range.
The following image shows a dataset listing fruits, grades and order quantities.
Here is how to create a Pivot Table based on the source dataset.
- Select the source data range A1:C13 and choose the Insert tab → the PivotTable option.
The Create PivotTable window will open, showing the chosen data range in the Table/Range field as the data to review.
- Select the Existing Worksheet option and update the target cell reference in the Location field to place the Pivot Table in the current sheet.
Clicking OK in the Create PivotTable window will show the target location in the active sheet and the PivotTable Fields window with the fields required to insert the Pivot Table.
- Drag and drop the fields in the specific areas in the PivotTable Fields window to create the required Pivot Table in the target cell.
- Choose cells A18 and B17 to update the row and column labels, one by one, as shown in the image below:
Let us consider changing the Grade B fruits’ order quantities in the cells C6:C9, highlighted in the image below.
The expectation is that the Pivot Table update automatically reflects the changed values. However, we must follow the steps below to manually update the source dataset changes in the Pivot Table. - Click a cell in the Pivot Table to enable the Analyze tab in the ribbon.
Next, choose the Refresh option down arrow → Refresh option in the Analyze tab.
Once we select the Refresh option, the new values, highlighted in the source dataset, get updated in the Pivot Table.
[ Alternatively, we can right-click a cell in the Pivot Table to enable the context menu.
Next, choose Refresh from the context menu, as shown in the above image.
The above step will also help us with the required Pivot Table update range.]
Update Pivot Table If Any Addition To The Existing Data Set
Sometimes, we create a Pivot Table based on the given source dataset. But, due to changes in the requirements, we may append new data to the existing source dataset.
Then, here is how to update the Pivot Table to show the newly added data:
- Ensure the newly added data to the existing source dataset is correct and valid.
- Click a cell in the Pivot Table to enable the Analyze tab. Next, choose the Change Data Source command down arrow → Change Data Source option.
The option will open the Change PivotTable Data Source window to perform the required Pivot Table update data source range changes.
- Update the new source dataset range in the Table/Range field in the Change PivotTable Data Source window and click OK to achieve the updated Pivot Table.
Basic Example
The following image shows a source dataset in the range A1:C11 and the Pivot Table in the range E1:L13 based on the source dataset.
The source dataset lists ten US cities, their states and population statistics.
Assume we add five more US cities and their corresponding data to the existing source dataset in the range A12:C16.
So, the aim is to update the Pivot Table to ensure it shows the data newly added to the source dataset.
Then, the Refresh option will not help in the Pivot Table update data source range changes. Instead, we must use the following steps to achieve the desired output.
- Step 1: Click a cell in the Pivot Table to view the Analyze tab in the ribbon.
Next, choose the Change Data Source function down arrow → the Change Data Source option.
The Change PivotTable Data Source window opens, showing the initial source dataset range in the Table/Range field.
- Step 2: Click in the Table/Range field and then select the new source dataset range in the sheet to update the range in the field.
We will see the window name change to Move PivotTable.
Finally, clicking OK in the Move PivotTable window will update the data we added to the existing source dataset in the Pivot Table.
Please note that the Pivot Table created using the initial source dataset lists the US cities in alphabetic order. However, the newly added data is not sorted in the Pivot Table.
Auto Data Range For Pivot Table With Excel Tables
We can create a Pivot Table based on an Excel Table. But when we edit the source Excel Table, the Pivot Table is not updated automatically to accommodate the changes.
However, we can manually update the Pivot Table using the following steps:
- Ensure the new data we add to the source Excel Table is correct. But, if the new data does not appear in the same format as the Excel Table. Then, we must choose the File tab and select Options.
The Excel Options window will open, where we must click the Proofing tab to open it and choose AutoCorrect Options.
The AutoCorrect window will open, where we will click the AutoFormat As You Type tab to open it. Next, check the option to include new rows and columns in the table provided under the Apply as you work heading.
Click OK to exit the window.
Finally, click OK in the Excel Options window to apply the setting in Excel.
So, now we can add new rows and columns of data to the existing Excel Table.
- Right-click a cell in the Pivot Table, created based on the initial source Excel Table, and choose the Refresh option from the contextual menu.
We will see the updated Pivot Table, showing the data we added to the existing source Excel Table.
Basic Example
The image below shows an Excel Table in the range A1:C6 and a Pivot Table in the range E1:F5 based on the Excel Table.
The existing Excel Table contains details of five employees.
The aim is to add details of five more employees to the Excel Table and update the Pivot Table to show the newly added data. Then, the steps are as follows:
- Step 1: Assume the option to add rows and columns in a table in the Excel Options window is checked. Then, we can add new rows of required data to the existing Excel Table in the same way as we would to a dataset.
- Step 2: Right-click a cell in the Pivot Table to select the Refresh option in the context menu.
The Pivot Table will appear updated, with column F data confirming the same.
Update Multiple Pivot Table Results Using Shortcut Keys
We can use the shortcut keys to update multiple Pivot Table results in a workbook simultaneously, with the steps being as follows:
- Ensure the changes made to the source data are correct and valid.
- Press Alt + A + R + A or Alt + JT + F + A to refresh all the Pivot Tables in the Excel file in one go.
Basic Example
The following image shows a source dataset in the range A1:E9 and three Pivot Tables in the same sheet based on the source dataset.
The source dataset contains a list of branch offices, their heads and monthly revenues from Jan-Mar.
Consider that we edit the revenue values in cells C4, D7, and E6, highlighted in the below image.
Then, here is how to update the source data changes in the Pivot Tables in one go.
- Step 1: Assume the active worksheet is open and press Alt + A + R + A or Alt + JT + F + A.
All the Pivot Tables in the workbook will get updated simultaneously, as shown above.
Important Things To Note
- By default,thePivot Table update does not happen automatically. We must manually refresh the Pivot Table or change the data source to update it.
- When we update a Pivot Table to include data newly added to the source dataset, the Pivot Table does not display the new data in a sorted order. The new data in the Pivot Table will be in the same order in which it was added to the source dataset.
- If the source dataset is an Excel Table, we do not need to change the data source range for the Pivot Table to refresh it. Instead, we can use the Refresh option to update it.
Frequently Asked Questions (FAQs)
We can update Pivot Table VBA using the following steps, explained with an example.
The following image shows a source dataset and a Pivot Table based on it.
Consider we change the values of two cells in the source dataset, as depicted in the image below.
Here is how to update the new values in the Pivot Table using Excel VBA.
• Step 1: With the active sheet containing the given source dataset and Pivot Table open, press Alt + F11 to access the VBA Editor.
• Step 2: Choose the applicable VBAProject and the Module option under the Insert tab to open a new module.
• Step 3: Enter the VBA code in the module to refresh the required Pivot Table.
• Step 4: Press the play icon to run the code.
Pivot Tables do not update automatically. We must manually update it using the Refresh option from the contextual menu or the Analyze tab.
However, we can set the settings to update Pivot Tables automatically whenever we open the Excel file.
1) Click anywhere in the Pivot Table to enable the Analyze tab.
2) Choose the Options command down arrow → the Options option in the Analyze tab.
3) The PivotTable Options window opens, where we must click the Data tab to open it.
4) Check the checkbox for the Refresh data when opening the file option of the PivotTable Data section in the Data tab.
5) Click OK in the PivotTable Options window.
We can update Pivot Table in Google Sheets using the following methods.
Method 1 – Using The Refresh Icon
1) Open the Google Sheets application.
2) Click the Refresh icon at the top of the window.
The page will reload, updating the unrefreshed data in the Google Sheets, including the Pivot Table.
Method 2 – Using The Editor Option
1) Open the Google Sheets program.
2) Select the sheet where we must refresh the data.
3) The selected Google Sheet will open.
4) Consider entries existing outside the Pivot Table range.
5) Click on a cell for the Editor icon to appear below the table.
6) Click the Edit icon. The Pivot Table Editor window will appear on the right of the screen.
7) Locate the Data Range information in the Pivot Table Editor window.
8) Click the Select Data Range icon on the right end of the data range information.
9) A box appears where we can enter the new data range, which includes the new data.
10) Once we enter the new data range, click OK in the box.
Download Template
This article must be helpful to understand the Pivot Table Update, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to What Is Pivot Table Update. We learn to update Excel Pivot Tables in different scenarios with examples and points to remember. You can learn more from the following articles –
Leave a Reply