Refresh Pivot Table in Excel

Last Updated :

21 Aug, 2024

Blog Author :

N/A

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya

Table Of Contents

arrow

How to Refresh Pivot Table in Excel?

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

  1. Refresh PivotTable By Changing Data Source
  2. Refresh PivotTable using Right Click Option
  3. Auto Refresh PivotTable Using VBA Code
  4. Refresh PivotTable When you open the Workbook

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

#1 Changing the Data Source Refresh Pivot Table

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

  1. We have sales data from different countries.


    Refresh Pivot Table in Excel step 1

  2. Let us apply the PivotTable to this data.


    Refresh Pivot Table in Excel step 1-1

  3. Now, we are returning to the PivotTable data and adding a few more data to the table.


    Refresh Pivot Table in Excel step 1-2

    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.

  4. Select the PivotTable and go to "Options" and "Change Data Source."


    Refresh Pivot Table in Excel step 1-3..

  5. Once we click "Change Data Source," this will take you to the datasheet and the box below.


    Refresh Pivot Table in Excel step 1-4

    Look at the range. For example, it has the data range from A1 to B23, whereas our data is from A1 to B28.

  6. In the same dialog box, change the source data from A1 to B23 to A1 to B28.


    Refresh Pivot Table in Excel step 1-5

  7. Now, click on the "OK" button. It will add the new data to the PivotTable and refresh it.


    Refresh Pivot Table in Excel step 1-6..

    Refresh Pivot Table in Excel step 1-6

#2 Right Click on the Pivot Table and Refresh Pivot Table Excel

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.
Refresh Pivot Table in Excel step 2
  • 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.
Refresh Pivot Table in Excel step 2-1
  • 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."
Refresh Pivot Table in Excel step 2-2gif
  • Step 4: Click the "Refresh" button to update the new values.
Refresh Pivot Table in Excel step 2-3

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.

Refresh Pivot Table in Excel step 2-4

We can find this option under the Data tab.

Refresh Pivot Table in Excel step 2-5

#3 Using Auto Refresh Pivot Table Excel VBA Code

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 1: Go to the Developer tab and click on "Visual Basic.".
VBA Code step 1
  • Step 2: Click on "Visual Basic." Go to ThisWorkbook and double-click on that.
Refresh-Pivot-Table-Using-VBA-Code-step-2
  • Step 3: From the dropdown list, select Workbook.
VBA Code step 3
  • Step 4: Once you have selected the workbook, it will automatically create one macro for you. Ignore that.
VBA Code step 4
  • Step 5: From the right-hand side dropdown, select SheetChange. It will insert one more macro for you.
VBA Code step 5
  • Step 6: Now, copy and paste the below code of the macro that has been created for the second time.
VBA Code step 6

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.

#4 Refresh Pivot Table When you Open the Workbook

We can refresh the PivotTable while opening the workbook.

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

Table 1

Click on this option.

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

Table 2

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

Things to Remember

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

This article has been a guide to Refresh Pivot Table in Excel. Here, we discuss how to refresh PivotTable using the top four Excel methods, practical examples, and a downloadable template. You may learn more about Excel from the following articles: -

  • Pivot Table From Multiple Sheets in Excel
  • Pivot Table Slicer
  • Filter in a Pivot Table
  • Pivot Table Calculated Field