How to Refresh Pivot Table in Excel?
Below are the top four methods to refresh the PivotTable in Excel:
- Refresh PivotTable By Changing Data Source
- Refresh PivotTable using Right Click Option
- Auto Refresh PivotTable Using VBA Code
- Refresh PivotTable When you open the Workbook
Now, let us discuss each method in detail with an example.
Table of contents
#1 Changing the Data Source Refresh Pivot Table
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.
#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.
- 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. 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. 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. 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., 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 ExcelPivot Table From Multiple Sheets In ExcelPivot Table is a basic data analysis tool that calculates, summarizes, & analyses the data of a more extensive table. To create a Pivot Table from Multiple Sheets, you can use a few shortcuts & features as per the specified conditions.
- Pivot Table SlicerPivot Table SlicerPivot Table Slicer is a tool in MS Excel to filter the data present in a pivot table. The data can be presented based on various categories as it offers a way to apply the pivot table filters that dynamically change the view of the pivot table data.
- Filter in a Pivot TableFilter In A Pivot TableBy right-clicking on the pivot table, we can access the pivot table filter option. Another approach is to use the filter options available in the pivot table fields.
- Pivot Table Calculated FieldPivot Table Calculated FieldPivot table calculated fields are formulas with reference to other fields, and calculated values refer to other values within a specific pivot field.