How to automatically refresh Pivot Tables in Excel

I share a way to refresh Pivot Table automatically in Excel. Credit to Excel Campus

Problem description

I was surprised that the data in my Pivot Table in Excel was not automatically refreshed when the data was changed.

This isn't a bug but a feature though I would appreciate if it could be done automatically.

Manual Solution

First workaround is to trigger the refresh manually.

Right-click on your pivot table and select refresh.

Or go in the Ribbon PivotTable Analyze-> (Data) Refresh

Semi-automatic

In your Excel Workbook you can configure to refresh the data when the File is opened

Automatic Solution

This requires the use of a VBA macro (save the file as .xlsm)

In the ThisWorkbook add the Workbook SheetChange Event macro

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

ThisWorksheet.RefreshAll

End Sub

References

Refresh Pivot Tables Automatically When Source Data Changes - Excel Campus