In one of the Excel newsgroups last week, someone asked how they could update several pivot tables at the same time. They were tired of selecting each pivot table separately, and clicking the Refresh button.
In an Excel workbook you can refresh all the pivot tables and queries if you click the Refresh All button. That button is on the External Data button, which appears automatically if you click in a cell that's part of an external data range.
However, if you don't have any external ranges in your workbook, you can add the Refresh All button to the PivotTable toolbar, so it's easy to find and click.
The quickest way to add this button is to click the Toolbar Options button at the end of the PivotTable toolbar. If the toolbar is floating, you'll see a large triangle to the left of the Close button.
If the toolbar is docked, there's a small triangle in the bar at the end of the toolbar.
Click the Add or Remove Buttons command, then click PivotTable
You'll see a list of all the standard commands that can be added to the PivotTable toolbar.
Click on Refresh All, to add it to the toolbar.
Now, when you want to update all the pivot tables, just click the Refresh All button.
HI Debra,
An even faster way is:
- make sure both toolbars are visible
- Control+shift+drag your button from toolbar1 to toolbar2.
(drag with just alt to move a bottun)
(Jan Karl - doesn't this need to be done in 'customize' mode? )
This approach is one of my favourites.
It also lets you move items from Menu bars to tool bars. One of my favorites is a single command like "Show Formula Auditing Toolbar". I put this in a toolbar, instead of under its standard dropdown menu, to get a one-click function to show/hide the whole Auditing toolbar. No auto-sense required, which I find aggravating. I also use this for the VBA toolbar.
Alex:
No, no need for customize mode. That is the beauty of this trick!
[...] Refreshing Multiple Pivot Tables with One Click [...]
Thanks Jan Karel -- I can use the Alt key to drag buttons between toolbars, but the Ctrl+Shift trick doesn't work. Maybe it's a European keyboard feature that isn't in the North American ones.
Alex, glad you mentioned your time saving tricks. That Show Formula Auditing Toolbar is buried pretty deep! My current favourite is a button for Show All, which makes it quicker to clear a filtered table.
Good point, Debra. My Filter-Sort grouping includes the buttons for AutoFilter, Show All, and a custom Advanced Filter Macro which puts unique items in a seperate list, along with the Sort button.
Maybe its time to host a Cool Toolbar topic. I'd enjoy seeing other folks' arrays, and showing off mine. It would be really cool if we could make it visual, not just descriptive.
P.S. - I'm finding it really frustrating when I have to sit in front of someone else's Excel without my toolset!
Alex, you'll have to read tomorrow's blog post. ;-)
And I'm sure no one will mind if you rearrange all the toolbars on their machine. Someone should invent a portable toolbar kit that you can plug in temporarily, wherever you're working.
[...] Quickly Customize an Excel Toolbar [...]
[...] further reading, my recommendation is to start with Customized Excel Toolbars and Quickly Customize an Excel Toolbar from Debra Dalgleish's blog. If you like this post, why [...]