Excel 2007: Customize Your Quick Access Toolbar

By David H. Ringstrom

The Quick Access Toolbar is a new feature in Excel 2007 that keeps frequently used features just a click away at all times. In this article I’ll make some suggestions on how you can best customize your Quick Access Toolbar, including an undocumented use for AutoFilter. Plus, if you’re still using an earlier version of Excel, I’ll explain how you use dormant space on your Excel screen to create your own version of the Excel 2007 Quick Access Toolbar.

 

Try it now:
A free 60-day trial of Office 2007 is available for immediate download. Note that you can instruct the installation program to leave your present version of Office intact so that you’re free to work in either version. Or, try the online test drive.

Transition tools:
Office Online offers an online command reference that shows where your favorite Excel 2003 commands now reside in Excel 2007. You can also download and install the command reference tool on your computer. Yet another option is to add the Get Started tab to your Excel 2007 ribbon — this puts numerous training aids right at your fingertips.

 

Understand the Quick Access Toolbar
The Quick Access Toolbar puts frequently used commands at your fingertips. It’s a particularly helpful aid when you first migrate to Excel 2007, so that you don’t have to constantly search the ribbon to find commands. By default, this toolbar resides adjacent to the Office button in Excel 2007, but you can keep it closer at hand by placing it below the ribbon. To do so, click the arrow at the end of the Quick Access Toolbar and choose Show Below the Ribbon. Moving the ribbon back is simple: simply click the arrow and choose Show Above the Ribbon.
Always Visible: You can right-click on the ribbon or Quick Access Toolbar and choose Minimize the Ribbon. This puts the ribbon in an on-demand mode, where it expands when you click a tab on the ribbon, but collapses when you click on the worksheet. Conversely, the Quick Access Toolbar is always visible, so some users find it helpful to place the toolbar below the ribbon, load it up with icons, and keep the ribbon minimized. This provides instant access to frequently used commands without losing so several worksheet rows to the ribbon.

Customize the Quick Access Toolbar
By default, the Quick Access Toolbar contains three commands: Save, Undo, and Redo. When you click the drop-down arrow on the toolbar, you can click and enable these additional commands:

• New
• Open
• E-Mail
• Quick Print
• Print Preview
• Spelling
• Sort Ascending
• Sort Descending

Figure 1: The Quick Access Toolbar can provide one-click access to ribbon sections that appear on tabs other than Home.

 

Maximize Your Screen: Placing frequently used command sections, even those on the Home tab, allows you to keep the ribbon minimized, yet still have one or two click access to the commands you regularly use, as shown in Figure 2.

Figure 2: A carefully crafted Quick Access Toolbar can offset a minimized ribbon.

You can truly leverage your Quick Access Toolbar by adding commands that don’t appear in the ribbon, such as AutoFilter:

1. Right-click on the Quick Access Toolbar and choose Customize Quick Access Toolbar (or choose More Commands from the drop-down arrow at the end of the toolbar).
2. Choose Commands Not in the Ribbon from the Choose Commands From list, as shown in Figure 3.
   
   

Figure 3: Useful hidden features reside in the Commands Not in the Ribbon section.

     3. Select AutoFilter, and then click Add.
     4. Choose Data tab from the Choose Commands From List.
     5.Select Clear, and then click Add.
     6.Click OK to save these changes to your Quick Access Toolbar.

Easy Reset: You can easily reset your toolbar back to its default choices. Right-click on the toolbar, choose Customize Quick Access Toolbar, and then click the Reset button.
Although similarly named, the AutoFilter command functions different from the Filter command in the ribbon. By adding AutoFilter to your toolbar, you can click on any entry in a worksheet list, and then click the AutoFilter icon on your Quick Access toolbar. Excel will automatically filter the list for that item with a single click. Conversely, the Filter command in the ribbon requires several steps:

1. Choose Sort & Filter from the Editing section of the Home tab on the ribbon.
2. Choose Filter.
3. Click the filter arrow for the column you wish to filter, and then clear the Select All checkbox.
4. If necessary, scroll down the list to find the item in question, click its checkbox, and then click OK.

Document Specific Toolbar
Historically, any changes to Excel’s toolbars have applied on a global basis. Excel 2007 introduces an exciting new capability, whereby you can create a custom Quick Access Toolbar for specific documents:

1. Right-click on the Quick Access Toolbar and choose Customize Quick Access Toolbar (or choose More Commands from the drop-down arrow at the end of the toolbar).
2. Choose an open document name from the Customize Quick Access Toolbar list, which typically displays For All Documents (Default), as shown in Figure 4.

Figure 4: You can create document-specific Quick Access Toolbars.

 

 

      3.Add or remove commands to the Quick Access Toolbar, and then click Save.

This custom toolbar appears whenever you open or activate the specified document (otherwise your default toolbar appears).
Caveat: Customizations to a document-specific Quick Access Toolbar document are discarded when you save the document in any file format other than these:

• Excel Workbook (.xlsx)
• Macro-Enabled Excel Workbook (.xlsm)
• Excel Binary Workbook (.xlsb)

Keyboard shortcuts: Another interesting benefit to the Quick Access Toolbar is that Excel 2007 automatically assigns shortcut keys to each command on the toolbar. Press the Alt key to see the shortcut keys, as shown in Figure 5.

Figure 5: Press the Alt key to view the keyboard shortcut for every command..

1. Equivalent for Excel 2003 and Earlier
   Just about everyone using Excel 2003 or earlier has a broad expanse of unused screen space in the Excel menu bar to the right of the Help menu and to the left of the Search box in Excel 2003, or the edge of the screen in earlier versions. Many users don’t realize that Excel’s menu bar is highly customizable. To take advantage of this feature:

   1. Choose Tools, Customize, and then click the Commands tab.
   2. Select a menu, such as Data, and then drag the AutoFilter icon from the dialog box up to the top of your Excel screen, to the right of the Help menu.

   Undocumented tip: Adding AutoFilter and Show All provides one-click access to filtering in the same fashion as I described for Excel 2007.

   3. Repeat this process for the Show All command that appears below AutoFilter in the Customize dialog box.
   4. Add any additional commands you find helpful, such as these:

   
   Format menu: Merge Across - enables you to merge multiple rows at once without combining into a single cell like the Merge and Center command
   
   Format menu: Strikethrough - helpful for marking items as completed on a “to do” list
   
   Edit menu: Paste values - easier than choosing Paste Special, and then Values
   
   Tools menu: Protect Sheet - enables you to toggle sheet protection on or off with a single click, instead of having to navigate to the Protection submenu under Tools
   
   Autoshapes menu: Text box – easily insert text boxes for adding notes or narrative to spreadsheets

   
   

 

The views and opinions expressed in this column are those of the author and do not necessarily reflect the opinions of Microsoft.