Refresh Pivot Table in Excel

How to Refresh Pivot Table in Excel?

Below are the top 4 methods to refresh the Pivot Table in Excel

  1. Refresh Pivot Table By Changing Data Source
  2. Refresh Pivot Table using Right Click Option
  3. Auto Refresh Pivot Table Using VBA Code
  4. Refresh Pivot Table 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 data source refresh pivot table –

  1. I have sales data from different countries.

    Refresh Pivot Table in Excel step 1

  2. Let me apply the pivot table to this data.

    Refresh Pivot Table in Excel step 1-1

  3. Now, I am going back to my pivot table data and adding a few more data to the table.

    Refresh Pivot Table in Excel step 1-2

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

    Let me audit what the range of the pivot table data is.

  4. Select the pivot table and go to Options and Change Data Source.

    Refresh Pivot Table in Excel step 1-3..

  5. Once you click on Change Data SourceClick On Change Data SourceBefore creating a pivot table the data range that is to be covered in pivot table report generation is to be selected and this data range may need to be changed from time to time. This can be done through Change Data Source option which is present under Analyze more, this will take you to the datasheet along with the below box.

    Refresh Pivot Table in Excel step 1-4

    Look at the range it has. It has the data range from A1 to B23, whereas my data is there from A1 to B28.

  6. In the same dialogue 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; it will add the new data to the pivot table 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

I am taking the previous data as an example.

  • Step 1: Here, I am not adding any amount of data to the list; rather, I am just changing the existing numbers.
Refresh Pivot Table in Excel step 2
  • Step 2: Here on cell B10, I have to change the value from 677434 to 750000. If I go to the pivot table, it is still showing 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 1638228, 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 on the Refresh button to update the new values.
Refresh Pivot Table in Excel step 2-3

Okay, now our Pivot Table is showing updated values.

Now the question is if I have 10-pivot tables, can I update each one of them by going to each pivot table and update.

We have the shortcut to refresh all the pivot tables in excel in one single shot. Below is the shortcut key to refresh all the pivot tables 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

Ok, we know how to refresh the pivot table to new values. Whenever we change the values, we every time we need to update manually.

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

To come off that danger, we have a VBA code, which can update or refresh as soon as there are any changes.

Follow this section of the article to learn the VBA code that refreshes the pivot tableVBA Code That Refreshes The Pivot TableWhen we insert a pivot table in the sheet, once the data changes, pivot table data does not change itself; we need to do it manually. However, in VBA, there is a statement to refresh the pivot table, expression.refreshtable, by referencing the more in excel automatically.

VBA Code step 1
  • Step 2: Click on Visual Basic. Go to This Workbook and double click on that.
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 drop-down, select Sheet Change. It will insert one more macro for you.
VBA Code step 5
  • Step 6: Now copy and paste the below code the macro that has been created for the second time.
VBA Code step 6

What this code will do is if there is any change in the sheet, it will refresh the pivot table in excel automatically for you.
Note: Once you copy and paste the code, you have to save the workbook as Macro-Enabled Workbook.

#4 Refresh Pivot Table When you Open the Workbook

We can refresh the pivot table while opening the workbook.

Right-Click on any of the pivot table and select the Pivot Table in the ExcelPivot Table In The ExcelA Pivot Table is an Excel tool that allows you to extract data in a preferred format (dashboard/reports) from large data sets contained within a worksheet. It can summarize, sort, group, and reorganize data, as well as execute other complex calculations on it. read more option.

Table 1

Click on this option.

Go to Data and check Refresh Data when opening the file.

Table 2

This will refresh the pivot table in excel whenever you open the excel file.

Things to Remember

Recommended Articles

This has been a guide to Refresh Pivot Table in Excel. Here we discuss how to Refresh Pivot Table using the top 4 methods in excel along with practical examples and a downloadable template. You may learn more about excel from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion