Your Customized Excel Toolbars

Yesterday I posted a tip for adding the Refresh All button to the PivotTable toolbar, so you can quickly update all the pivot tables and queries in a workbook. In the comments, Jan Karel Pieterse mentioned that he can press Ctrl+Shift, or hold the Alt key, then drag toolbar buttons to a different toolbar. I can't get that Ctrl+Shift shortcut to work, but often use the Alt key to adjust toolbars on the fly.
In another comment, AlexJ described how he moves well-hidden menu commands, such as Show Formula Auditing Toolbar, to a toolbar, so they're easier to use. Great idea!
Besides the Refresh All button, there are a few others that I add to my Excel toolbars. For instance, when I'm creating data entry forms it helps to know if a cell is locked or unlocked. I copy the Lock Cell button from the Protection toolbar to my Standard toolbar (Ctrl+Alt, then drag the button).

When I click on a cell, the Lock Cell button shows the cell's current setting, and I can click that button to lock or unlock the cell. Very handy!
I also add the Record Macro and Visual Basic Editor buttons, copied from the Visual Basic toolbar, and Toggle Grid from the Forms toolbar.

What Buttons Do You Add or Remove?

  • Are there any standard command buttons that you always add to your toolbars when you install Excel?
  • Any standard command buttons that you always remove, because you never use them?

If you'd like to share a screen shot of your toolbars, upload it somewhere like TinyPic.com, and paste a link in your comment. Or, if you're feeling adventurous, you can open this Google Spreadsheet, choose Insert>Picture, and paste the picture link in there. I have no idea how well this will work, but it might be an interesting experiment!

0 thoughts on “Your Customized Excel Toolbars”

  1. Jan Karel, no problem! I just thought you had some fancy new keyboard.
    Bryony, it's my first attempt at a shared Google spreadsheet, so maybe I need to adjust the settings. I've added your screenshot, and thanks for sharing it.
    Now I have to go read Jon's response...

  2. The one additon I use a lot and often add to others' Excel toolbar is the Paste Values button.
    I know it's there on the drop-down of the standard Paste button, but I'm lazy and want a single click, and almost without exception, other users that I have helped out in the past do not look at the additional commands that sit behind those little down-arrows on toolbar buttons. (sad, but true)

  3. Thkx, Debra. My favourite of the group is [Name In Range] for its function (which Name Manager, I recently discovered, addresses by the highlight function).
    I "stole" the [Name In Range] function from AppsPro Excel Utilities by starting the Add-In, and then copying the command button to my own tool bar from the Excel Utilities menu. After that, I unchecked the Excel Utilities Add In so that it does not load on startup, but when I select the [Name In Range] button, it LOADS the Add In and engages the function.
    Not sure if this is a reliable deployment technique for all functions of this type, but it works for me. (And was a surprise, too).

  4. Debra, you are right of course - it IS efficiency that drives our fine-tuning of the Excel toolbar.
    In the same way, and because I exceed Excel's list of 9 most recent documents, I keep Ken Puls' XLG Favourites installed to facilitate access to my most commonly used files and folders.
    http://www.excelguru.ca/node/64

  5. Thanks Sam, looks like you've got everything you need in Excel 2007 in one handy Ribbon add-in! And I hope you have a backup of your customized toolbar file for Excel 2003 -- it must have taken a long time to set that up.

  6. Pingback: Useful toolbar buttons you don't see every day in Excel 2003, Part 2 - JP Software Technologies

Leave a Reply to Debra Dalgleish Cancel reply

Your email address will not be published. Required fields are marked *

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