You are reading the article How To Refresh Pivot Table In Excel (Manually + Auto updated in October 2023 on the website Phuhoabeautyspa.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested November 2023 How To Refresh Pivot Table In Excel (Manually + Auto
Once you have created a Pivot Table, it doesn’t automatically refresh when you add new data or change the existing data.
Since your Pivot Table is created using the Pivot Cache, when the existing data changes or when you add new rows/columns to the data, the Pivot Cache does not update itself automatically, and hence, the Pivot Table also does not update.
You need to force a refresh every time there are changes. Once you force a refresh, the Pivot Cache gets updated, which is reflected in the Pivot Table.
This tutorial covers a couple of ways to do this.
This option is best suited when there are changes in the existing data source and you want to refresh the pivot table to reflect these changes.
Here are the steps to refresh a Pivot Table:
Select Refresh.
This will instantly refresh the Pivot Table.
You can also by selecting any cell in the Pivot Table and use the keyboard shortcut ALT + F5.
Quick Tip: It’s a good practice to convert the data source into an Excel Table, and use this Excel Table to create the Pivot Table. If you do this, you can also use the refresh technique to update the Pivot Table even when new data (rows/columns) are added to the data source (since an Excel Table automatically accounts for new rows/columns that are added).
If you’ve added new rows/columns to the data source, you need to change the data source to make sure new rows/columns are a part of the dataset.
To do this:
Select any cell in the Pivot Table.
In the Change PivotTable Data Source dialog box, update the range to include new data.
Note that if you change the data source into an Excel Table and then use the Excel table to create the Pivot Table, you don’t need to use the change data source option. You can simply refresh the Pivot Table and it’ll account for the new rows/columns.
To make it more efficient and auto-refresh the Pivot Table whenever there is a change in the data source, you can use a simple one-line VBA macro code.
Here is the VBA code:
Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("Sheet1").PivotTables("PivotTable1").PivotCache.Refresh
End Sub
Decoding the Code: This is a change event which gets triggered whenever there is a change in the sheet that contains the source data. As soon as there is a change, the code refreshes the Pivot Cache of the Pivot Table with the name PivotTable1.
You need to modify this code to make it work for your workbook:
“Sheet1” – change this part of the code with the name of the sheet that has the Pivot Table.
Where to put this VBA code:
Press Alt + F11. It will open the VB Editor window.
In the VB Editor, there would be Project explorer on the left (that has the names of all the worksheets). If it’s not there, press Control + R to make it visible.
In the code window on the right, copy paste the given code.
Close the VB Editor.
Now when you change anything in the data source, the Pivot Table would automatically get refreshed.
Note: Since there is a macro in the workbook, save this with .xls or .xlsm extension.
You May Also Like the Following Pivot Table Tutorials:
You're reading How To Refresh Pivot Table In Excel (Manually + Auto
Update the detailed information about How To Refresh Pivot Table In Excel (Manually + Auto on the Phuhoabeautyspa.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!