There’s no denying that the Pivot Table is one of the most powerful tools in Excel to analyze a larger dataset quickly and extract necessary data efficiently. Unfortunately, you may get troubled while working with the Pivot Table as it lacks the feature of auto-updating. However, we can easily update if we know the efficient methods. In this article, I’ll demonstrate how to update the Pivot Table using 5 suitable methods range with the necessary explanation.
This is today’s dataset where the Product Category is given based on states. Also, the Quantity and Sales are provided.
Before going into the methods of updating, certainly, you certainly have the ability to create a Pivot Table. However, if you want to know the process, you may visit the How to create Pivot Table article.
I’ve made a Pivot Table for the above dataset, check the following screenshot.
More importantly, assume that we have added 3 rows (new data) which will be updated in the Pivot Table range manually and automatically.
Let’s dive into the efficient methods
1. Updating the Pivot Table Range Manually by Changing the Data Source
First of all, we’ll see the process of updating the Pivot Table range manually which means by changing the data source.
Just follow the steps below.
⏩ Select a cell within the created Pivot Table.
⏩ Click on the Change Data Source… by from Change Data Source in the PivotTable Analyze tab.
⏩ Then, you’ll get a dialog box namely Move PivotTable. Also, fix the new range of source data as $B$4:$E$15, and press OK.
⏩ Eventually, the output will look as follows where a new column of states namely Arizona is created, and also the data is updated.
Read more: Automatically Update a Pivot Table When Source Data Changes in Excel
2. Updating Pivot Table Range by Clicking the Refresh Button
Actually, it is a simple method. To use the method, you have to select a cell within the Pivot Table and then right-click on the mouse or press ALT+F5 (keyboard shortcut for refreshing Pivot Table).
The following screenshot describes the process sequentially.
After doing that, the Pivot Table will update automatically like the following figure.
Read more: How to Refresh All Pivot Tables in Excel
3. Updating Pivot Table Range by Converting to Excel Table
Another way to update the Pivot Table by creating an Excel table will be discussed step by step.
Steps:
⏩ Select a cell within the dataset and insert a table by clicking the Insert tab > Table.
⏩ Also, you may press CTRL+T (keyboard shortcut for creating a table).
⏩ Then you’ll see the dialog box namely Create Table.
⏩ Fix the location of the table (here: $B$4:$E$12) and also check the box before My table has headers option.
⏩ If you do that, you’ll see the following table.
⏩ Now we have to insert a Pivot Table for the above table.
⏩ Make sure the Table/Range is Table1 and press OK.
⏩ Thus, we have created a dynamic Pivot Table range.
⏩ If we input any new data, the above Pivot Table will update automatically including the data.
For example, we want to add the following 3 rows.
⏩ Then if you select a cell within the Pivot Table, and do right-click on the mouse, and then choose the Refresh option (or press ALT+F5).
⏩ So, the output will be as follows with the new column Arizona state.
Read more: How to Auto Refresh Pivot Table without VBA in Excel
4. Updating Pivot Table Range Utilizing the OFFSET Function
More significantly, if we want to create a dynamic range to update the Pivot Table range automatically, we may utilize the Name Manager and the combination of OFFSET and COUNTA functions.
Steps:
⏩ Click on the Formulas tab > Name Manager option from the Defined Names ribbon.
⏩ Then you’ll see a dialog box namely Name Manager
⏩ Press on the New option.
⏩ Then fix the Name as Source_Data and insert the following formula in the Refers to section
=Dynamic_Range!$B$4:$E$15_Range!$B$4,0,0,COUNTA(Dynamic_Range!$B:$B),COUNTA(Dynamic_Range!$4:$4))Here, the current sheet name is Dynamic_Range, $B$4:$E$15 is the raw data, B4 is the starting cell of the data, $B:$B is for column B and $4:$4 is for row 4.
⏩ Provided that the source data is ready, we can move to insert a Pivot Table.
In the following dialog box, be careful about the Table/Range as Source_Data.
Note: If the Table/Range option is not correct, Excel will show you “the data source is not valid” and surely no Pivot Table will be created.
⏩ Finally, we have the dynamic Pivot Table range as shown in the following figure.
⏩ Now, if we input new data into the raw data, the Pivot Table will update automatically.
⏩ Assuming that we want to add the following new data into the table.
⏩ After adding those new data with the raw data, press ALT+F5 in the Pivot Table.
⏩ And you’ll see the updated data in the following screenshot.
5. Updating Pivot Table Range Using VBA Code
Now, let’s see how you can apply the VBA code to get the list of sheet names in Excel.
Step 1:
Firstly, open a module by clicking Developer > Visual Basic.
Secondly, double-click on the Sheet10.
Step 2:
Private Sub Worksheet_Deactivate()
Sheet9.PivotTables("PivotTable10").PivotCache.Refresh
End Sub
Step 3:
Finally, run the code.
Notes:
Be careful about the following facts while using the VBA code.
- Select the Project as Sheet10 (Dataset_VBA) where the raw data is available.
- The worksheet before the PivotTables is Sheet9 (PivotTable_VBA) where the PivotTable is available.
- Copy the default name of Pivot Table eg PivotTable10 (also you can also adjust the default name).
Then if you change the sales of Vegetables from $1500 to $2000000, what will be the change in the Pivot Table?
After changing the sales, you’ll see the Pivot Table updates data automatically (red rectangular box in the following figure).
Things to Remember
Notably, the Macro cannot retrieve undo history data. In that case, you can extract the undo data and the Pivot Table if you turn on the option of Refresh the data when opening the file.
To do this, you have to click PivotTable Analyze > Options.
Then check the box before Refresh the data when opening the file option.
Download Practice Workbook
Conclusion
In the above article, I tried to cover the methods of how to update the Pivot Table range. Obviously, I believe these methods might be beneficial for you. Anyway, if you have any queries or suggestions, please don’t forget to share them in the following comments section.
