When you first start Excel, the Quick Access Toolbar appears above the ribbon, as shown in Figure 3-10. You can change this so that the toolbar appears below the ribbon. Click the Customize Quick Access Toolbar button (the little arrow at the right end of the toolbar), and click Show Below The Ribbon. When you do, the name of the command changes appropriately, as shown in Figure 3-11.

Figure 3-10. The Quick Access Toolbar usually appears above the ribbon on the title bar.

Figure 3-11. You can move the Quick Access Toolbar below the ribbon.

Moving the Quick Access Toolbar below the ribbon offers you a couple of advantages. First, it’s closer to the action on the worksheet. Second, more space is available for additional tools, which we discuss in depth in Adding Tools to the Toolbar. The drawback to placing the toolbar below the ribbon is that it takes space away from the worksheet; conversely, the advantage of leaving it at the top of the screen is that it can occupy unused space on the title bar.

You get three tools to start with on the Quick Access Toolbar—Save, Undo, and Redo. These are undeniably heavily used commands, but you perform other tasks often too and might like to have them just a click away, readily available regardless of which ribbon tab is currently visible. It’s easy to customize the toolbar, and you have a couple of ways to do it.

Adding Tools as You Work

If you find yourself continually returning to the same tab on the ribbon and using a particular command, you might consider adding it to the Quick Access Toolbar. The easiest way to do this is to right-click the command to display the shortcut menu shown in Figure 3-12.

In Figure 3-12, we added the Switch Windows command to the Quick Access Toolbar by right-clicking the command and clicking Add To Quick Access Toolbar. A button then appears on the toolbar that looks similar to the command on the ribbon. Each new button you add appears to the right of the previous button. Not all toolbar buttons are easy to identify, but you can always rest the pointer on a button to display a ScreenTip explaining its function, as shown at the bottom of Figure 3-12. If you add a lot of buttons, these ScreenTips are indispensable.

Figure 3-12. Right-click any command or group, and you can add it to the Quick Access Toolbar.

Adding and Organizing Tools

You can add virtually any command or group to the Quick Access Toolbar by using the right-click technique, but if you want to dig in and really create an organized toolbar, you should use the Excel Options dialog box. Right-click the toolbar, and click Customize Quick Access Toolbar to open the Quick Access Toolbar category in the Excel Options dialog box, shown in Figure 3-13.

As you can see in Figure 3-13, the list on the right shows the buttons currently visible on the toolbar. The controls here work just like the Customize Ribbon category—select any item on the left side, and then click the Add button to add it to the list on the right. Select any item on the right side, and click the Remove button to get rid of it; when you select an item, you can click the up and down arrows to reposition the item in the list. You can always click the Reset button if you want to discard all your changes and return to the original configuration.

Figure 3-13. The Quick Access Toolbar category in the Excel Options dialog box is the command center for the Quick Access Toolbar.

Notice in Figure 3-13 that Separator is the first item in the list on the left. This item is first in each command category, and with it you can insert small vertical bars on the Quick Access Toolbar to visually separate groups of related commands. You can add separators and move them up and down the list on the right side of the dialog box, just as you do with commands. (This item is not available for ribbon customization because you create groups first instead of using separators.)

The items in the Choose Commands From drop-down list include an eponymous item for each of the command tabs visible on the ribbon, plus all the other contextual tabs that appear on the ribbon only when an object is selected, as shown in Figure 3-14. Choose any item from the drop-down menu, and the corresponding list of commands appears in the list below.

Figure 3-14. Each item in the Choose Commands From drop-down list corresponds to a tab on the ribbon.

Tip

INSIDE OUT Create a Mini-Ribbon

Here is a trick you can use if it just seems like too much bother to click those tabs at the top of the ribbon, or you just want to maximize screen space. Saving a click here and there can make quite a difference if you do a lot of repetitive work. The following illustration shows the Quick Access Toolbar loaded with buttons that correspond to every group on every default tab on the ribbon, which is minimized (double-click any ribbon tab to toggle its display):

The toolbar shown here includes only buttons that correspond to the command groups (not individual commands) on every default tab. Notice that clicking the Page Setup button displays a drop-down list of commands identical to those in the ribbon group of the same name on the Page Layout tab. To customize the toolbar this way, use the Excel Options dialog box. Although you can build a similar toolbar using the right-click approach, using the dialog box offers the advantage of being able to insert separators between groups of buttons.

Note

You’ll find the Ribbon Group Toolbar.xlsx file with the other examples on the companion Web site.

Too Many Tools?

It is certainly possible to load more buttons onto the Quick Access Toolbar than can fit across the screen, even if you move the toolbar below the ribbon. If this happens, a More Controls button appears at the right end of the toolbar, looking like a fast-forward button (>>). As shown in Figure 3-15, clicking More Controls displays the hidden controls on a drop-down toolbar.

Figure 3-15. If you add more buttons than can be displayed, click the More Controls button.

Note that when the More Controls button appears, the Customize Quick Access Toolbar button (the downward-pointing arrow) that usually appears at the right side of the toolbar moves to the “overflow” area of the toolbar.

Right-click any button on the ribbon, click Customize Quick Access Toolbar, and click the Choose Commands From drop-down list. You’ll see a special option listed there: Macros. Macros are sequences of commands you can create to help perform repetitive tasks. When you select the Macros option, nothing appears on the left side of the dialog box unless a macro-enabled workbook is open and the workbook actually contains macros. All the macros available appear here. Figure 3-16 shows the Customize Quick Access Toolbar list containing a single macro that has been added to the toolbar.

Figure 3-16. You can add custom buttons to run macros in macro-enabled workbooks.

You might notice that the Modify button below the list is active when you add a macro to the list on the right side of the dialog box. By clicking it, you can modify the button image displayed on the toolbar if the default image doesn’t do it for you. In case you are wondering, you can modify only custom macro buttons, which is why the Modify button is not available when you select built-in commands. When you click the Modify button, the Modify Button dialog box appears, as shown in Figure 3-17, and allows you to select a different image.

Figure 3-17. You can change the default button image for your custom macro-driven buttons.

In the Quick Access Toolbar category of the Excel Options dialog box, the default setting in the Customize The Quick Access Toolbar drop-down list is For All Documents. In this case, Excel saves the configuration of the Quick Access Toolbar when you exit the program. If you want to go back to the way things were, click the Reset menu and choose Reset Only The Quick Access Toolbar or Reset All Customizations (which resets both the ribbon and the Quick Access Toolbar).

If you want to save your custom toolbar so that you can use it again later, there are two ways to do it. The Import/Export button allows you to save or retrieve a special file containing all your customizations (both toolbar and ribbon). Figure 3-9 on page 92 shows a version of the File Open dialog box that appears when you click Import Customization File from the Import/Export menu, displaying files with the extension .exportedUI.

The second way to save a custom toolbar is to attach it to a workbook. To do so, right-click any button or tab, and click Customize Quick Access Toolbar. In the Excel Options dialog box, click the Customize Quick Access Toolbar drop-down list, as shown in Figure 3-18.

You’ll find both a customizations file—MiniRibbon.exportedUI—and a file with an attached toolbar—Ribbon Group Toolbar.xlsx— with the other examples on the companion Web site.

Figure 3-18. You can configure a custom version of the Quick Access Toolbar that travels with a workbook.

The drop-down list shows two items: For All Documents and For <the active workbook name>. If you select the active workbook, the command list starts out blank, and you can begin adding items from the list on the left. (Even though the list starts out blank, the default tools—Save, Undo, and Redo—always appear at the left end of your custom bar.) Any commands you add to the active workbook’s toolbar are relevant only to the active workbook; they are saved with the workbook and reappear on the toolbar the next time you open the workbook. To remove a custom toolbar configuration from a workbook, select its name in the Customize Quick Access Toolbar drop-down list, and click the Reset button.

Now that you’ve thoroughly scrambled the Quick Access Toolbar, perhaps you’re experiencing a bit of remorse. Don’t worry—it’s easy to return it to normal.

Figure 3-19. You can easily remove any button from the Quick Access Toolbar.

Tip

INSIDE OUT What Happened to My Custom Toolbars?

Custom toolbars you created and attached to workbooks in versions of Excel prior to 2007 are still accessible, though you might not think so at first glance. If you open an old workbook with attached toolbars, the toolbars won’t appear on the screen. But look at the Add-Ins tab on the ribbon. You might not have seen the Add-Ins tab before, but click this tab, and there they are—your custom toolbars. The following graphic shows an example of way too many custom toolbars on the Add-Ins tab:

So, all your hard work creating custom toolbars is not lost—provided you attached them to workbooks. After you open a workbook containing attached toolbars, the toolbars continue to appear on the Add-Ins tab each time you start Excel. To get rid of them, right-click any toolbar (each toolbar is a group on the Add-Ins tab), and close it by using the Delete Custom Toolbar command. If the custom toolbar is the only item on the Add-Ins tab, the Add-Ins tab disappears as well.

The following are a few more customization options that are quite helpful. Unless otherwise noted, you can find these options by clicking the File menu, clicking Excel Options, and then selecting the General category.

Figure 3-20. You can control whether the Mini toolbar appears when you right-click cells or relevant objects.