Refresh Pivot Table in Excel

Updated on January 2, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

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

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

#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 PivotTableVBA Code That Automatically 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 worksheet.read more in excel.

VBA Code step 1
  • Step 2: Click on “Visual Basic.” Go to ThisWorkbook and double-click on that.
  • 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 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 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

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: –