How to auto refresh pivot table data in Excel

In this article, we will learn How to auto refresh pivot table data in Excel.
Scenario:

As we all know, whenever we make changes in a source data of a pivot table, it doesn't reflect immediately in the pivot table. We need to refresh the pivot tables to see the changes whenever opening an excel workbook. And if you send an updated file without refreshing the pivot tables, you may feel embarrassment. So learn here how to find the refresh option when using pivot table

Refresh data when opening a file in Excel

First Create a Pivot table and then right click any pivot table cell.

Go to Pivot table options > Data tab > Tick the box which says Refresh data when opening a file

This will enable auto update data whenever the file is opened.

Example :

All of these might be confusing to understand. Let's understand how to use the function using an example. Here we have some data and we need to create a pivot table first and then find the options to enable auto update pivot tables.

Create a pivot table and then right click any pivot table cell as shown below.

Select the Pivot table Options and this will open a PIvot table Options dialog box.

Select the data tab and then tick the box which says Refresh Data when opening the file. You can perform this without opening and closing the file using VBA.

Using VBA

So here, we will learn how to auto refresh a pivot table using VBA. This way is easier than you imagined.
This is the simple syntax to automatically refresh pivot tables in the workbook.

What are Pivot Caches?

Every pivot table stores the data in pivot cache. This is why pivot is able to show previous data. When we refresh pivot tables, it updates the cache with new source data to reflect the changes on the pivot table.
So we just need a macro to refresh the cache of pivot tables. We will do this using a worksheet event so that we don't have to run macro manually. 

Where to Code To Auto Refresh Pivot Tables?

If your source data and pivot tables are in different sheets, then the VBA code should go in the source data sheet.
Here we will use Worksheet_SelectionChange Event. This will make the code run whenever we switch from the source data sheet to another sheet. I'll explain later why I used this event.

Here, I have source data in sheet2 and pivot tables in sheet1.

Open VBE using CTRL+F11 key. In project explorer, you can see three objects, Sheet1, Sheet2 and the Workbook.

Since Sheet2 contains the source data, double click on sheet2 object.

Now you can see two drop downs at the top of the code area. From the first drop down, select the worksheet. And from the second drop down select Deactivate. This will insert an empty sub name Worksheet_Deactivate. Our code will be written in this sub. Any lines written in this sub, get's executed as soon as the user switches from this sheet to any other sheet.

On sheet1 I have two pivot tables. I want to refresh only one pivot table. For that, I need to know the name of the pivot table. To know the name of any pivot table, select any cell in that pivot table  go to pivot table analyze tab. On the left hand side, you will see the name of the pivot table. You can also change the name of the pivot table here.

Now we know the name of the pivot table, we can write a simple line to refresh the pivot table.

And it is done.

Now whenever you will switch from the source data, this vba code will run to refresh the pivot table1. As you can see in the gif below.

How to Refresh All the Pivot Tables in The Workbook?

In the above example, we only wanted to refresh one specific pivot table. But if you want to refresh all the pivot tables in a workbook, you just need to do slight changes to your code.

In this code we are using a For loop to loop through each pivot caches in the workbook. The ThisWorkbook object contains all the pivot caches. To access them we use ThisWorkbook.PivotCaches.

Why Use Worksheet_Deactivate Event?

If you want to refresh the pivot table as soon as any change is made in source data, you should use Worksheet_Change event. But I don't recommend it. It will make your workbook run the code every time you make any change in the sheet. You may have to do hundreds of changes before you want to see the result. But excel will refresh the pivot table on every change. This will lead to waste of processing time and resources. So if you have pivot tables and data in different sheets, it is better to use Worksheet Deactivate Event. It lets you finnish your work. Once you switch to pivot table sheets to see the changes, it amends the changes.

If you have pivot tables and source data on the same sheet and you want pivot tables to auto refresh it self, you may want to use Worksheet_Change Event.

How to Refresh Everything in Workbooks When A Change is Made in Source Data?

If you want to refresh everything on a workbook (charts, pivot tables, formulas, etc) you can use ThisWorkbook.RefreshAll command.

Note : The code does not change the data source. So if you add data below the source data, this code won't include that data automatically. You can use Excel Tables to store source data. If you don't want to use tables, we can use VBA for including new data too. We will learn it in the next tutorial.

Hope this article about How to auto refresh pivot table data in Excel is explanatory. Find more articles on calculating values and related Excel formulas here. If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook. We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write to us at info@exceltip.com.

Related Articles :

How to Dynamically Update Pivot Table Data Source Range in Excel : To dynamically change the source data range of pivot tables, we use pivot caches. These few lines can dynamically update any pivot table by changing the source data range. In VBA use pivot tables objects as shown below...

Run Macro If Any Change Made on Sheet in Specified Range : In your VBA practices, you would get the need to run macros when a certain range or cell changes. In that case, to run macros when a change is made to a target range, we use the change event.

Run Macro When any Change is Made On Sheet : So to run your macro whenever the sheet updates, we use the Worksheet Events of VBA.

Simplest VBA Code to Highlight Current Row and Column Using : Use this small VBA snippet to highlight the current row and column of the sheet.

The Worksheet Events in Excel VBA : The worksheet event are really useful when you want your macros to run when a specified event occurs on the sheet.

Popular Articles :

50 Excel Shortcuts to Increase Your Productivity : Get faster at your tasks in Excel. These shortcuts will help you increase your work efficiency in Excel.

How to use the VLOOKUP Function in Excel : This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets. 

How to use the IF Function in Excel : The IF statement in Excel checks the condition and returns a specific value if the condition is TRUE or returns another specific value if FALSE.

How to use the SUMIF Function in Excel : This is another dashboard essential function. This helps you sum up values on specific conditions.

How to use the COUNTIF Function in Excel : Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.