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.

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.

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

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

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: Convert Range to Table in Excel

Similar Readings

• How to Auto Refresh Pivot Table in Excel (2 Methods)
• Pivot Table Not Refreshing (5 Issues & Solutions)
• Edit a Pivot Table in Excel (5 Methods)
• How to Convert Table to List in Excel (3 Quick Ways)

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.

⏩ Then fix the Name as Source_Data and insert the following formula in the Refers to section

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 like the following screenshot.

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.

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

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.

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

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

• Pivot Table Custom Grouping: With 3 Criteria
• How to Refresh Pivot Table in Excel (4 Effective Ways)
• Does TABLE Function Exist in Excel?
• Create Table in Excel Using Shortcut (8 Methods)