Quickly Customize an Excel Toolbar

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.

Refresh All Button for Pivot Tables

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.

Refresh All Button External Data Toolbar
Refresh All Button External Data Toolbar

Add to PivotTable Toolbar

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.

Floating Toolbar

If the toolbar is floating, you’ll see a large triangle to the left of the Close button.

  • Click that triangle, then click the Add or Remove Buttons command
  • In the pop-up menu, click on PivotTable
ToolbarAddButtonsDocked Toolbar

If the toolbar is docked, there’s a small triangle in the bar at the end of the toolbar.

  • Click that triangle, then click the Add or Remove Buttons command
  • In the pop-up menu, click on PivotTable

ToolbarOptionsAdd the Button

After you click the PivotTable command, 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.

ToolbarRefAll
Now, when you want to update all the pivot tables, just click the Refresh All button.

10 thoughts on “Quickly Customize an Excel Toolbar”

  1. (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.

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

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

  4. P.S. – I’m finding it really frustrating when I have to sit in front of someone else’s Excel without my toolset!

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

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.