How to refresh Pivot Table
In our previous post we learnt about how to create pivot table in excel. Now in this post we will try to understand different ways to refresh pivot table when new data is being added in the data range of pivot table. There are different ways to refresh excel pivot table, but we can categories them in two different ways.
- Manually and
- by VBA code.
How to refresh pivot table manually
1) To refresh a pivot table manually first select any cell on excel pivot table then go to PivotTable Analyze tab then in Data column click on Refresh. Now we ill get 2 options Refersh and Refresh all.
How to refresh Pivot Table
Click on Refresh option,pivot table got refreshed with new data. If we have multiple pivot tables then click on Refresh All to refresh all tables.
Alternatively we can right click on a pivot table and there would find an option Refresh. By clicking on this our pivot table will also get refreshed.
2) To refresh pivot table automatically we will first select a cell on the pivot table. Then right click on it. After that we will choose PivotTable Options as illustrated in the below image.
A new box will come in which we will go to Data tab and then check Refresh data when opening the file. After choosing this option click OK. A warning message will come on which click OK. Now whenever you will open this excel file it will automatically get refreshed.
Refresh pivot table vba
This option helps in automatically update pivot table when source data changes. We would require to use few VBA codes. Lets see how to do it.
First we need to go to Developer Tab then click on Visual Basic in Code column.
Now Project Explorer window of the Visual Basic Editor will open. We will now locate the workbook where we wanted to implement the code. Now in this workbook we will find the sheet which contain the pivot table for which we wanted to refresh data automatically. Now select the sheet containing the source data and then double click on it.
Project Explorer window in excel
If you don’t see the Project Explorer window you can enable it from the View menu (keyboard shortcut: Ctrl+R).
Now after double click a code module will open. In this module we will create and event macro. To do so, we will choose Worksheet in the Object drop-down box on the left.
A code will come in box which we don’t want to use. We will delete this code later but before that we will select change from SelectionChange dropdown which is on the right side. This will add a new event at the top called Worksheet_Change. Now we will highlight and delete the unnecessary code which came earlier when we first created macro event.
The Worksheet_Change event macro will run any time a change is made to cells in that source datasheet. We can add VBA code to the Worksheet_Change event to perform actions when the user edits or append cells. Now in the final step we will use our VBA code ThisWorkbook.RefreshAll just below the Worksheet_Change line as illustrated in the below image.
We also used Application.EnableEvents code in the above code to prevent the recursive loop created by Refresh.
After that save the file in macro-enabled workbook (.xlsm) and/or enabled macros.
That is all for now how to refresh pivot table in excel. Will meet again with some new topic.
You can also watch the below video for more clarity on this topic.
You can read more on refresh pivot table from the below articles.
- https://www.thesmallman.com/vba-refresh-pivot-table
- https://trumpexcel.com/refresh-pivot-table-excel/
- https://support.microsoft.com/en-us/office/refresh-pivottable-data-6d24cece-a038-468a-8176-8b6568ca9be2
- https://www.excel-easy.com/examples/update-pivot-table.html