Section A.1. The Quick Access Toolbar | Excel 2007[c] The Missing Manual

A.1. The Quick Access Toolbar

You've already seen the Quick Access toolbar (known to Excel nerds as the QAT). It's the micro- size toolbar that sits above the ribbon. The Quick Access toolbar has only icons, but you can hover over a button to get the full command text.

When you first start out with Excel, the Quick Access toolbar is a lonely place, with buttons for quickly saving your workbook and undoing or redoing the last action (Section 2.2.5). However, Excel gives you complete control over this space, including the ability to add new buttons . You can most quickly add stuff by clicking the down-pointing arrow at the far right side. Figure A-1 shows how it works.

Figure A-1. When you click the drop-down arrow on the Quick Access toolbar, Excel shows a list of often-used commands that you can add just by clicking them. These commands include some for creating a new workbook, opening an existing workbook, sending it to the printer with no questions asked, and firing up the spell checker. But to see all your possibilities, you need to choose More Commands.

Note: If you don't like Quick Access toolbar's placement, Excel gives you one other option. Click the drop-down arrow, and then choose "Show Below the Ribbon" to move your toolbar under the ribbon so your mouse has less distance to travel.

You might add buttons to the Quick Access toolbar for two reasons:

To make it easier to get to a command you use frequently . If it's in the Quick Access toolbar, you don't need to memorize a keyboard shortcut or change the current ribbon tab.
To get to a command that the ribbon doesn't provide . Excel has a small set of unpopular commands that it lets you use, but that it doesn't keep in the ribbon. Many of these commands are holdovers from previous versions of Excel. If you have a long-lost favorite Excel feature that's missing, it just might be available via the Quick Access toolbar's extra buttons.

Keyboard lovers can also trigger the commands in the Quick Access toolbar with lightning speed thanks to Excel's KeyTips feature (Section 3.2.1). When you press the Alt key, Excel displays a number superimposed over every command in the Quick Access toolbar (starting at 1 and going up from there). You can then press the number to trigger the command. So in the Quick Access toolbar shown in Figure A-1, Alt+1 saves the workbook, Alt+2 opens the Undo list, and so on.

You've already seen how to customize the Quick Access toolbar in a few places in this book. You used it to get the Data Form in Chapter 14 and to use the "Compare and Merge Workbooks" feature in Chapter 23. You also used it to get quick access to macros in Chapter 27.

Tip: If you want to add a command that duplicates something that's already in the ribbon, here's a shortcut. Find the command in the ribbon, right-click it, and then choose Add to Quick Access Toolbar.

A.1.1. Adding Buttons

To add a button to the Quick Access toolbar, you follow these steps:

Click the drop-down arrow on the Quick Access toolbar, and then choose More Commands .

The Excel Options dialog box opens, and positions you at the Customize section where you need to be (Figure A-2).

Choose a category from the "Choose commands from" list .

The library of commands that you can add to the Quick Access toolbar is enormous . To make it easier to find what you want, it makes sense to choose a specific category. Many of the categories overlapExcel simply provides them to make finding what you want easier. Here are the top choices:

Popular Commands gives you a short list of commands that Excel jockeys love. If you're trying to get quick access to a commonly used feature, you just might find it here.
Commands Not in the Ribbon provides all the leftoverscommands that Microsoft didn't consider useful enough to include in the ribbon. This list holds some commands that are superseded or partially duplicated by other commands, commands that are included in other dialog boxes, and commands that were used in previous versions of Excel and put out to pasture in this release.
All Commands includes the full list of choices. As with the other categories, it's ordered alphabetically .

Macros shows all the macros in the currently open workbooks, including the personal macro workbook. Be careful about adding a macro that's not from the personal macro workbookif you move the file, Excel can't run the macro when you click the button.

Figure A-2. The Customize section of the Excel Options window has two areas. The list on the left lets you choose the command you want to add. The list on the right shows the commands that currently appear in the Quick Access toolbar.

Under these categories are several additional categories that correspond to the Office menu and various tabs in the ribbon. For example, you can choose the Insert tab to see all the commands that appear in the ribbon's Insert tab.

Once you've chosen the category you want, pick the command from the list below, and then click Add .

The command moves from the list on the left to the list on the right, placing it on the Quick Access toolbar (Figure A-3).
You can repeat this process (starting at step 2) to add more commands .

Optionally, you can rearrange the order of items in the Quick Access toolbar. Just pick a command, and then use the up and down arrow buttons to move it. The topmost commands in the list are displayed to the left on the Quick Access toolbar.

Tip: If you've customized the heck out of your Quick Access toolbar and want to go back to a simpler way of life, then click the Reset button.

When you're finished, click OK to return to Excel with the revamped Quick Access toolbar .

Figure A-3. In this example, the Calculator command being added to the Quick Access toolbar, so you can pop open the handy Windows calculator whenever you need it, without leaving Excel.

Adding a Quick Access toolbar isn't a lifetime commitment. To get rid of a command you don't want anymore, right-click it, and then choose Remove from Quick Access Toolbar.

Note: You might notice the tempting Modify button, which lets you change a command's name and picture. Unfortunately, it works only for macro commands.

A.1.2. Customizing Specific Workbooks

Do you have a button or two that you're using incessantly, but just for a specific workbook? In this situation, it may not make sense to customize the Quick Access toolbar in the normal way. If you do, you'll get your extra buttons in every workbook you use, including those in which the extras aren't useful.

Excel has a great feature to help you out in this situation. You can customize the Quick Access toolbar for an individual workbook. That way, whenever you open that workbook, the buttons you need appear in the Quick Access toolbar. When you close it (or open another workbook in a separate window), the buttons disappear.

Note: Customizing individual workbooks has advantages and disadvantages. The disadvantage is that you need to perform this task separately for every workbook, which can take a lot of time. The advantage is that your customizations are recorded right in your workbook file. As a result, they stick around if you open the workbook on someone else's computer.

To customize the toolbar for a single workbook, you follow the same steps that you used in the previous section. Start by clicking the Quick Access toolbar's drop-down arrow, and then choosing More Commands. However, before you add any commands, change the selection in the "Customize Quick Access Toolbar" list, which appears just above the list of commands in the Quick Access toolbar. Instead of using "For all documents (default)", choose your workbook's name (as in "For SecretSanta.xlsx"). This list starts off empty. Then, follow the normal steps to add buttons.

When Excel displays the Quick Access toolbar, it combines the standard buttons (as configured in the previous section) with any buttons you've defined for the current workbook (Figure A-4).

Figure A-4. The workbook-specific buttons (circled) always appear after standard buttons and have a slightly different appearance darker background). You can define the same button as a standard button and a workbook-specific button, in which case it appears twice.

Customizing the Quick Access toolbar for a specific workbook is a handy trick. It makes great sense with macros (Chapter 27) because it lets you create a single workbook that has a useful set of macros and handy buttons for running them. It's also great with templates (Chapter 16). If you customize the Quick Access toolbar for a specific template, every workbook that you create using that template gets the buttons you've added.

NOSTALGIA CORNER
What Happens to My Custom Toolbars ?

Using the customization features in previous versions of Excel, you might have designed your own specialized toolbars (which you could then attach to a workbook file). The bad news is that Excel no longer provides this feature, so you can't enhance or modify your existing toolbars. The good news is that you can still access your custom toolbar, just in a slightly different way.

All the buttons from custom toolbars and custom menus end up on a separate tab, named Add-Ins. This tab appears only if there's a custom toolbar or menu in the workbook. When it appears, it ends up at the every end of the series of tabs. This arrangement may not be the prettiest, but it makes sure that the solutions Excel gurus have already created will keep working in Excel 2007.

If you want to keep your custom commands closer at hand, you can add them to the Quick Access toolbar. There are three items you need to look for (in the All Commands list): Custom Toolbars, Menu Commands, and Toolbar Commands. When you add these to the Quick Access toolbar, they appear as drop-down menus that, when clicked, show all the custom commands in your current workbook.