Refresh All Pivot Tables
April 19, 2017 - by Bill Jelen
In Microsoft Excel, can you refresh all pivot tables at once? Read this article to learn how.
- Say that you have two pivot tables from different data.
- The original data changes in both data sets. You want to refresh all pivot tables.
- The Refresh button on the PivotTable Tools | Analyze tab only refreshes one pivot table.
- Use Ctrl + Alt + F5 or Data, Refresh All to refresh all pivot tables in the workbook
Learn Excel from MrExcel Podcast, Episode 2079: Refresh All Pivot Tables
This question from YouTube last week, they have multiple data sets. Each data set is driving a different pivot table. So here we have data that's driving the orange pivot table, here we have data that’s driving the blue pivot table. And if we change the underlying data, and I have a little factor up here, that's going to change both columns D and I. So we’ll change from 4 to 2 and press ENTER. And when I press ENTER watch this column and that column, they're both going to update; but you see the pivot table, of course, didn't update. And you know, the typical way to do this is to select the pivot table, go to the Analyze tab, click Refresh and that refreshes that one but not that one. So then you have to go click the second pivot table and click Refresh. And this is a hassle that, you know, you're never going to get your manager, you’re never going to be able to get your manager to do anything like this.
So, you know, two- three options, three options. First option: come over here to the Data tab where they have awesome Refresh All button which is Ctrl+Alt+F5, Refresh All. So here, we'll change the 2 to 20 and when I click Refresh All we're going to see this change to 450, this to 56,000 like that. Alright, cool.
If you would like, you can right click and say Add this to the Quick Access Toolbar and then becomes an item up there. It also, ironically, if you go back into one of the pivot tables, on the Analyze tab underneath the Refresh, there is a drop-down that offers Refresh All, alright? It's the exact same Refresh All, Ctrl+Alt+F5. It’s just a faster way to refresh that.
Lots of pivot table tricks in my book, Power Excel with MrExcel, The 2017 Edition. Check that out. Click the “i” in the top right-hand corner.
A simple little episode today: You have multiple pivot tables coming from different data. The original data in both data sets changes. You want to Refresh All pivot tables. Well, the Refresh button on the Pivot Table Tools, Analyze tab only refreshes the current pivot table. You can use Ctrl+Alt+F5 or Data, Refresh All or the Analyze tab, open the Refresh drop-down and choose Refresh All to refresh all the pivot tables in the workbook.
Well, I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
Download the sample file here: Podcast2079.xlsm
Title Photo: sign/post/1e930eb4-9a54-444b-ada5 / Pixabay