How to Refresh All Pivot Tables in Excel (2 Suitable Ways)

We often work with large datasets in Excel and use PivotTables to summarize and analyze our data quickly. However, most of the times, these datasets changes periodically. Or, sometimes, we need to update the dataset. And in such situations, our PivotTable does not update automatically. So, to make sure we get the analysis result from the latest dataset, we have to refresh the pivot table.

Suppose you are preparing a monthly sales report for different product categories. However, every time new sales data is added, the data changes, but the PivotTables will remain unchanged. And, manually updating each PivotTable takes longer and can lead to errors. You can refresh all PivotTables together and get error-free yet updated results in just a few seconds.

In this article, we will explain 2 different methods to refresh all PivotTables in Excel, including built-in commands and VBA.

The built-in Refresh All option in Excel allows us to update every PivotTable at once with just a single click.

We will use the dataset and two pivot tables below to explain how you can refresh all the pivot tables at once in Excel.

This is a sales dataset of a company in different regions, which includes some product names, region, month, and sales value.

This is our first pivot table, which lists the count or number of products sold by region.

This is the second pivot table that includes the sales value for each product per month.

Now, we can see that our dataset is updated till March, but our pivot tables are only showing the analysis till February. So, we need to update the pivot tables for March.

Steps:

➤ First, click on the Data tab from the upper ribbon.
➤ Then, go to the Queries & Connections Group and select Refresh All.

➤ Now, you will see that all PivotTables have been updated for recent data.

Here, you can see the data is updated for March, too, after using the Refresh All option. The total number of products updated has increased from 7 to 10 after refreshing, as the number of laptops increased from 2 to 3  in the East region, the number of tablets increased from 2 to 3 in the North region, and in the West region, laptop sales increased to 4 from 3.

Also, in this second pivot table, a new column for March is added automatically after using the Refresh All option.

If you frequently deal with large workbooks containing multiple PivotTables, then refreshing or updating those PivotTables using VBA is the most efficient way for you. This will automate the reporting tasks and save a lot of time for you

We will use the same dataset and the pivot tables to explain this method.

Steps:

➤ First, press  Alt  +  F11  on Windows or  Fn  +  Option   +  F11   on Mac to open the VBA Editor.
➤ Now, double-click on the worksheet name, and you will see a new window open up for writing the code.
➤ Then, copy and paste the following code into the code writing module:

Sub RefreshAllPivots()
    ThisWorkbook.RefreshAll
End Sub

➤ Finally, click the small run green icon and close the VBA Editor.

➤ Now, you can see all the pivot tables refreshed at once for the latest month.

Frequently Asked Questions

Is There Any Shortcut to Refresh All Pivot Tables in Excel?

Yes, there is. First, select any cell in your workbook, and it does not have to be inside the pivot table. Then, press  Alt  +  A  +  R  +  A  for Windows. However, there is no shortcut to refresh all pivot tables at once on Mac. Also, for refreshing the currently selected Pivot tables in Windows, press  Alt  +  F5  . And, to refresh the selected PivotTable on Mac, press  Command  +  Shift  +  F5  .

Can I Use Mouse Command to Refresh All Pivot Tables in Excel?

Yes, you can use the mouse to refresh all PivotTables in Excel. To do this, first, go to any pivot table in your workbook and left-click on any cell inside the table. It will open up a pop-up with a few options. Now, select Refresh from it, and Excel will update that pivot table and any other pivot tables that share the same data source, reflecting all recent changes in your dataset.

Can I Refresh Pivot Tables Automatically After a Certain Time Period?

Yes, you can set a timer to refresh your pivot tables automatically after a specific interval. However, this option is available only when the pivot table is connected to an external data source such as Power Query, SQL, or another database. In that case, you can adjust the connection properties to refresh the data every few minutes, ensuring the PivotTable stays updated without manual effort. However, if your PivotTable is built from a standard Excel range or table, this timed refresh feature is not available, and you would need to rely on manual refresh or a VBA macro to automate the process.

Wrapping Up

In this article, we have learned two different methods to refresh pivot tables in Excel, including the built-in Refresh All option and VBA. We also talked about the keyboard shortcut. Give this method a try, and you can save your time significantly. Also, reach us to us if you have any inquiries or want to share your feedback.