How to Refresh Pivot Table in Excel (Manually + Auto-Refresh with VBA)

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.

Refresh Pivot Table

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:

  • Right-click on any cell in the Pivot Table.
  • Select Refresh.

Refresh Pivot Table in Excel - 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).

Update Pivot Table by Changing the Data Source

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.
  • Go to Analyze –> Data –> Change Data Source. This will select the data source that you have used and will open the ‘Change PivotTable Data Source’ dialog box.Refresh Pivot Table in Excel - Analyze Change Data Source
  • In the Change PivotTable Data Source dialog box, update the range to include new data.Refresh Pivot Table in Excel - Change Data Source
  • Click OK.

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.

Autorefresh Pivot Table Using a VBA Macro

While refreshing a Pivot table is as easy as two clicks, you still need to do this every time there is a change.

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:

Refresh Pivot Table in Excel - Macro Code

  • “Sheet1” – change this part of the code with the name of the sheet that has the Pivot Table.
  • “PivotTable1” – change this to the name of your Pivot Table. To know the name, click anywhere in the Pivot Table and click the Analyze Tab. The name would be visible in the left part of the ribbon under the ‘PivotTable Name’ header.

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.Refresh Pivot Table in Excel - VBA Project Explorer
  • In the Project Explorer, double-click on the sheet name that contains the Pivot Table.
  • In the code window on the right, copy paste the given code.Refresh Pivot Table in Excel - Autorefresh Code in window
  • Close the VB Editor.

Now when you change anything in the data source, the Pivot Table would automatically get refreshed.

Click here to download the example file.

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:

Excel Ebook Subscribe

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Sumit Bansal
Hello there! I'm Sumit Bansal, founder of trumpexcel.com and an Excel MVP. I started this website in 2013 with a simple goal: to share my love for Excel through easy to follow tips, tutorials and videos. I'm here to help you get the best out of MS Excel to save time and boost your productivity.

Leave a Comment

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Free Excel Tips EBook Sumit Bansal

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster