How To Refresh Pivot Table In Excel Using Top 4 Methods

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.

#1 Changing the Data Source Refresh Pivot Table

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

We have taken the previous data as an example.

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

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

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.”
Step 4: Click the “Refresh” button to update the new values.

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.

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

Step 1: Go to the Developer tabDeveloper TabEnabling the developer tab in excel can help the user perform various functions for VBA, Macros and Add-ins like importing and exporting XML, designing forms, etc. This tab is disabled by default on excel; thus, the user needs to enable it first from the options menu.read more and click on “Visual Basic.”.
Step 2: Click on “Visual Basic.” Go to ThisWorkbook and double-click on that.
Step 3: From the dropdown list, select Workbook.
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.

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

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.

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 codeVBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more, you need to mention each PivotTable name in the case of multiple PivotTables.