Refresh Pivot Table in Excel

Publication Date :

13 Dec, 2018

Blog Author :

Edited by :

Reviewed by :

Download FREE Refresh Pivot Table Excel Template and Follow Along!

Refresh Pivot Table Excel Template.xlsx

Table Of Contents

How to Refresh Pivot Table in Excel?

Below are the top four methods to refresh the PivotTable in Excel:

Now, let us discuss each method in detail with an example.

Below are the steps for changing the data source refresh PivotTable.

We have sales data from different countries.
Let us apply the PivotTable to this data.
Now, we are returning to the PivotTable data and adding a few more data to the table.

We have added five lines of the data. If we go and see the table, it is not showing the updated values.

Let me audit the range of the PivotTable data.
Select the PivotTable and go to "Options" and "Change Data Source."
Once we click "Change Data Source," this will take you to the datasheet and the box below.

Look at the range. For example, it has the data range from A1 to B23, whereas our data is from A1 to B28.
In the same dialog box, change the source data from A1 to B23 to A1 to B28.
Now, click on the "OK" button. It will add the new data to the PivotTable and refresh it.

We have taken the previous data as an example.

Step 1: We are not adding any data to the list. Rather, we are just changing the existing numbers.

Step 2: Here, on cell B10, we have to change the value from 677,434 to 750,000. If we go to the PivotTable, it shows the old values for the country France.

Step 3: The new value for the country France is supposed to be 1,638,228, but it is showing the old value only. We need to do one simple thing here. Right-click on the "Refresh."

Now, our PivotTable is showing updated values.

Now, the question is, if we have 10 PivotTables, can we update each one by going to each PivotTable?

We have the shortcut to refresh all the PivotTables in Excel in one single shot. Below is the shortcut key to refresh all the PivotTables at once.

We can find this option under the Data tab.

Now, we know how to refresh the PivotTable to new values. But unfortunately, whenever we change the values, we always need to update them manually.

If the data changes frequently, updating each PivotTable is not easy. We are humans, and we tend to forget at times.

To overcome that danger, we have a VBA code, which can update or refresh as soon as any changes occur.

Follow this article section to learn the VBA code that automatically refreshes the PivotTable in excel.

Step 2: Click on "Visual Basic." Go to ThisWorkbook and double-click on that.

Step 4: Once you have selected the workbook, it will automatically create one macro for you. Ignore that.

Step 5: From the right-hand side dropdown, select SheetChange. It will insert one more macro for you.

Step 6: Now, copy and paste the below code of the macro that has been created for the second time.

If there is any change in the sheet, this code will automatically refresh the PivotTable in Excel for you.

Note: Once you copy and paste the code, you must save the workbook as "Macro-Enabled Workbook.

We can refresh the PivotTable while opening the workbook.

Right-click on any PivotTable and select the PivotTable in the Excel option.

Click on this option.

Go to the "Data" tab and check "Refresh data when opening the file."

It will refresh the PivotTable in Excel whenever you open the Excel file.

We can choose any of the above methods to refresh the PivotTables.
In the case of VBA code, we must save the workbook as the macro-enabled workbook.
The easiest for me to refresh is the shortcut "ALT + A + R +A."
We can rename the PivotTable under "PivotTable Options.
In the VBA code, you need to mention each PivotTable name in the case of multiple PivotTables.