How to Refresh Pivot Table in Excel? (Shortcut + VBA)

A Pivot Table is the simplest data analysis tool that provides a summary of large and complex data. 

Moreover, Pivot Tables are flexible and we can transform the table as per our desire easily. 

Therefore in Excel, we often transform our table into a Pivot Table in order to get a meaningful summary of data like sum, average, count, maximum, minimum, etc. 

However, once we create the Pivot Tables, it does not automatically refresh when we change or alter the source data. 

In this tutorial, I will show you different ways to refresh a Pivot Table in Excel

For demonstration purposes, I will use a sample dataset of sales done by various representatives in different regions (shown below).

Based on the above data, I have created a Pivot Table (shown below) that provides the Grand Total of sales generated by various representatives across different regions.

Now the problem you might face is that Pivot Table does not automatically get updated whenever there is a change in the source data. 

In the source data, let’s say I deleted the sales done by Micheal.

To delete a row, select a row by clicking on the row number and press CTRL + – (minus sign) from the keyboard. 

This will delete the complete row containing sales done by Micheal as shown.

Now. the sales done by Micheal have been deleted from the source, but the data is not updated in the Pivot Table and it is still showing the data of Micheal.

 So let’s see different ways to refresh a Pivot Table in Excel.

Method 1: Refresh Pivot Table Using the Option in the Ribbon

To update the Pivot table using the options in the ribbon follow the below steps.

1. Click anywhere inside the Pivot Table this will show the Pivot Tools in the ribbon. 

2. Now click on the ‘Analyze’ tab in the ribbon

3. Click on the ‘Refresh’ Option (in the Data group)

4. In the options that show in the drop-down, click on the ‘Refresh’ Option

This will refresh all the data in the Pivot Table, which can be seen below, where Micheal’s records have been removed.

Also read: How to Clear Pivot Table Cache?

Method 2: Refresh Pivot Table Using a Keyboard Shortcut

You can also use a simple keyboard shortcut to refresh a pivot table in Excel.

Below are the steps to use a keyboard shortcut to refresh a Pivot Table.

1. This shortcut will work only if the active cell is inside the Pivot Table so, click on any cell inside the Pivot Table.

2. Hold the Alt key and then press the F5 key. This will refresh all the data according to the source in the Pivot Table.

Also read: How to Lock a Pivot Table in Excel

Method 3: Right-Clicking on the Pivot Table

Another easy and quick way to refresh a pivot table is by right-clicking on any cell in the table using the refresh option there.

Below are the steps to do this.

1. Select any cell inside the Pivot Table as shown in the below screenshot

2. Right-click on the cell and choose the Refresh option

That’s it!

This will refresh the pivot table and bring the latest data from the source

All of the above methods work perfectly, but you have to manually refresh the Pivot Table each time when there is a change in the source data, and that could be a bit annoying.

There is also a chance that you forget to refresh the pivot table which can lead to errors in your work.

However,  there are techniques available for automatically refreshing the Pivot Table.

Also read: Copy Excel Pivot Table

Method 4: Automatically Refresh the Pivot Table Whenever Excel File is Opened

It is hard to always refresh the Pivot Table manually in order to get the latest data from the source. Moreover, sometimes the user forgets to refresh and gets incorrect data. 

However, there is a way to automatically refresh a Pivot Table whenever you open the file that has the Pivot Table

Let’s dive into the steps required to refresh the Pivot Table automatically,

1. Select any cell in the Pivot Table

2. Click on the ‘Analyze’ tab in the ribbon.

3. Now click on Options

4. From the dropdown, select ‘Options’ as shown.

5. In the Dialog box that opens, click on the Data tab

6. Under the PivotTable Data options, check the ‘Refresh Data when opening the file’ option and hit Ok.

The above method refreshes the Pivot Table every time you reopen the workbook. 

Method 4: Refresh Pivot Table using VBA Macro

If you want to make it super efficient and automatically refresh the Pivot table when there is any change in the source data, you can do so by using VBA macros. 

If you have multiple Pivot tables in a workbook, then it would be quite tedious to manually refresh each Pivot Table.

In that scenario, macro comes in handy to refresh all Pivot Tables automatically.

To automatically refresh the Pivot Table follow the steps

1. Open the VBA editor by pressing ALT+ F11 on your keyboard (alternatively, you can go to the Developer tab in the ribbon and click on the Visual Basic icon).

2. Here we have two Excel sheets (sheet2 and sheet4). Sheet2 contains the source data while sheet4 contains the Pivot Table.

3. Double-click on the sheet containing source data (sheet4) to open the code module.

4. In the code module, we have to create event macros so click on the object drop-down box and choose Worksheet.

5. Now from the procedure dropdown select Change. This will insert the Worksheet_Change event that will run any time when the user made changes to the sheet.

6. Now highlight and delete the code for the SelectionChange event.

7. Add the following line of code to refresh all the Pivot tables when changes are made in the source data sheet

ThisWorkbook.RefreshAll

8. Now press F5 and hit the Run button.

Now, if you make any changes in source data all the Pivot tables will automatically get refreshed.

How does this code work?

In the above steps, we added a code to the worksheet that contains the source data.

It’s a worksheet change event, which means that when you make any changes to the worksheet, the worksheet event will be triggered and it will run our code. And in our code, we have refreshed the entire workbook, which will also refresh our Pivot Table.

In this tutorial, I covered simple ways to refresh a Pivot table in Excel. I also covered how you can change the settings to always refresh a Pivot Table whenever the Excel file is opened.

And I have also covered how you can refresh a Pivot Table using VBA.

Other articles you may also like:

• Remove Grand Total From Pivot Table in Excel
• How to Move Pivot Table in Excel?
• How to Add Calculated Field to Pivot Table?
• How to Delete a Pivot Table?
• How to Group by Months in Excel Pivot Table?
• Pivot Table Field Name is Not Valid – How to Fix?