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).

Dataset for Pivot Table

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.

Pivot table created using the data

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. 

Delete the row

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

One record has been deleted

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.

The data still shows in the Pivot table

 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. 
Select a cell in the Pivot table
  1. Now click on the ‘Analyze’ tab in the ribbon
Click the Analyze tab
  1. Click on the ‘Refresh’ Option (in the Data group)
Click on Refresh
  1. In the options that show in the drop-down, click on the ‘Refresh’ Option
Click on Refresh

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

Pivot table has been refreshed
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.
Select a cell in the Pivot table
  1. Hold the Alt key and then press the F5 key. This will refresh all the data according to the source in the Pivot Table.
Pivot table has been refreshed
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
Select any cell inside the Pivot Table
  1. Right-click on the cell and choose the Refresh option
Right-click and refresh

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
Select any cell in the Pivot Table
  1. Click on the ‘Analyze’ tab in the ribbon.
Click on Analyze
  1. Now click on Options
Click on Options
  1. From the dropdown, select ‘Options’ as shown.
Click on Options
  1. In the Dialog box that opens, click on the Data tab
Click on the Data tab
  1. Under the PivotTable Data options, check the ‘Refresh Data when opening the file’ option and hit Ok.
check the ‘Refresh Data when opening the file’ option

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).
VB Editor
  1. Here we have two Excel sheets (sheet2 and sheet4). Sheet2 contains the source data while sheet4 contains the Pivot Table.
Select the sheet that has the source data
  1. Double-click on the sheet containing source data (sheet4) to open the code module.
Code window for the selected sheet
  1. In the code module, we have to create event macros so click on the object drop-down box and choose Worksheet.
Select worksheet from drop down
  1. 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.
Click on change
  1. Now highlight and delete the code for the SelectionChange event.
Delete the code that was already there
  1. Add the following line of code to refresh all the Pivot tables when changes are made in the source data sheet
Copy paste the code line in worksheet event
  1. Now press F5 and hit the Run button.
Run the macro

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:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment