How to Update Pivot Table Range (5 Suitable Methods)

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.


Download Practice Workbook


5 Suitable Methods to Update Pivot Table Range in Excel

This is our today’s dataset where the Product Category is given based on states. Also, the Quantity and Sales are provided.

Dataset

Before going into the methods of updating, certainly, you 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.

Pivot Table of Existing Dataset

More importantly, assume that we have added 3 rows (new data) which will be updated in the Pivot Table range manually and automatically.

Dataset with New Data

Let’s dive into the efficient methods


1. Updating Pivot Table Range Manually by Changing the Data Source

First of all, we’ll see the process of updating the Pivot Table range manually that 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.

Updating Pivot Table Range Manually

⏩ 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.

Updating Pivot Table Range Manually

⏩ Eventually, the output will look as follows where a new column of states namely Arizona is created, and also the data is updated.

Updating Pivot Table Range Manually

Read more: Pivot Table is Not Picking up Data in Excel


2. Updating Pivot Table Range by Clicking the Refresh Button

Actually, it is a simple method. For using 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.

Updating Pivot Table Range by Clicking the Refresh Button

After doing that, the Pivot Table will update automatically like the following figure.

Updating Pivot Table Range by Clicking the Refresh Button

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.

How to insert a table

⏩ Also, you may press CTRL+T (keyboard shortcut for creating a table).

⏩ Then you’ll see the dialog box namely Create Table.

Creating a table in Excel

⏩ 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.

Creating a table in Excel

⏩ Now we have to insert a Pivot Table for the above table.

⏩ Make sure the Table/Range is Table1 and press OK.

Creating a Pivot Table in Excel

⏩ 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.

New Data to Update

⏩ 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).

Updating Pivot Table Range by Converting to Excel Table

⏩ So, the output will be as follows with the new column Arizona state.

Updating Pivot Table Range by Converting to Excel Table

Read more: Convert Range to Table in Excel


Similar Readings


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 & 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.

Updating Pivot Table Range Utilizing the OFFSET Function

⏩ 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.

Updating Pivot Table Range Utilizing the OFFSET Function

⏩ 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.

Updating Pivot Table Range Utilizing the OFFSET Function

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.

Updating Pivot Table Range Utilizing the OFFSET Function

⏩ 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.

Updating Pivot Table Range Utilizing the OFFSET Function

⏩ After adding those new data with the raw data, press ALT+F5 in the Pivot Table.

⏩  And you’ll see the updated data like the following screenshot.

Updating Pivot Table Range Utilizing the OFFSET Function

Read more: What is the Difference between a Table and a Range in Excel?


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.

How to Insert VBA Code

Secondly, double-click on the Sheet10.

Step 2: 

Private Sub Worksheet_Deactivate()
 Sheet9.PivotTables("PivotTable10").PivotCache.Refresh
End Sub

VBA Code for Updating Pivot Table Range

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?

Updating Pivot Table Using VBA code

After changing the sales, you’ll see the Pivot Table updates data automatically (red rectangular box in the following figure).

Updating Pivot Table Range Using VBA Code

Read more: How to Use an Excel Table with VBA


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.

For doing this, you have to click PivotTable Analyze > Options.

Pivot Table option

Then check the box before Refresh the data when opening the file option.

Pivot Table Options


Conclusion

In the above article, I tried to cover the methods how to update the Pivot Table range. Obviously, I believe these methods might be beneficial for you. Anyway, if you have any queries and suggestions, please don’t forget to share them in the following comments section.


Further Readings

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo