Chapter 3: Custom-Tailoring the Excel Workspace

You needn't settle completely for the way the Microsoft Office Excel 2007 command and control system is organized. Although the Office Excel 2007 command structure is not as universally configurable as previous versions, you can still customize it for the way you work.

Customizing the Quick Access Toolbar

In previous versions of Excel, you could fill the screen with built-in and custom toolbars, docked on all four sides of the screen and floating everywhere in between. In Excel 2007, you get the Quick Access Toolbar-that's it. The good news is that Excel 2007 offers buttons for every command, plus introduces some new buttons for sets of commands called Ribbon groups.

Positioning the Toolbar

When you first start Excel, the Quick Access Toolbar appears above the Ribbon, as shown in Figure 3-1. You can change this so the toolbar appears below the Ribbon. Click the Customize Quick Access Toolbar button (the little arrow on 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-2.

Figure 3-1: The Quick Access Toolbar usually appears above the Ribbon, on the title bar.

Figure 3-2: 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'll 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 it can occupy unused space on the title bar.

Adding Tools to the Toolbar

You get three tools to start with on the Quick Access Toolbar-Save, Undo, and Repeat. These are undeniably heavily used commands, but you perform other tasks often too and might like to have them just a click away. It's easy to customize the toolbar, and you have a couple of ways to do it.

Note 

The Microsoft Office Button is not part of the Quick Access Toolbar-it stays in the upper-left corner, no matter what.

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, as shown in Figure 3-3.

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

In Figure 3-3, 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, so you can always rest the pointer on a button to display a ScreenTip explaining its function, as shown at the bottom of Figure 3-3. If you add a lot of buttons, these ScreenTips are indispensable.

Removing Tools

You can remove tools from the Quick Access Toolbar using the same technique as you use to add them. Right-click any tool on the toolbar, and click Remove From Quick Access Toolbar.

Adding and Organizing Tools

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

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

As you can see in Figure 3-4, the box on the right shows the buttons currently visible on the toolbar, including the Switch Windows command we added earlier. It's pretty easy to figure out how this works-just 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've selected an item, you can also click the Up and Down buttons to reposition the selected 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.

Notice in Figure 3-4 that <Separator> is the first item in the list on the left of the Customize category. This is the first item 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 like commands.

When you first access the Customize category, the Choose Commands From drop-down list displays Popular Commands. Click this drop-down list to see the rest of the available options, as shown in Figure 3-5.

Figure 3-5: Each item in the Choose Commands From drop-down list corresponds to a tab on the Ribbon.

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 a number of other categories that at first glance don't seem to relate to the Ribbon at all. These are categories that contain commands available on contextual tabs, that is, tabs that appear on the Ribbon only when an object is selected. For example, close the Excel Options dialog box, click SmartArt on the Insert tab, and then click OK. An object like the one in Figure 3-6 appears on the worksheet, and you'll see two new tabs on the Ribbon-Design and Format-under a SmartArt Tools heading. Anytime you select a SmartArt object, these tabs appear, offering relevant tools. These two tabs correspond to the two SmartArt items visible in the Choose Commands From drop-down list (shown in Figure 3-5).

Figure 3-6: When you select an object on the worksheet, additional tabs appear that correspond to items in the Choose Commands From drop-down list.

Several of these "phantom" groups appear in the Choose Commands From drop-down list, with headings such as Chart Tools, PivotTable Tools, and Drawing Tools, all of which correspond to tabs that are contextually triggered by selecting particular types of objects.

The last three items in the Choose Commands From drop-down list merit special attention. First, when you select Macros, any available macros appear, and you can assign them to buttons; you'll learn more about this later in "Creating Your Own Buttons" on page 91. Second, Commands Not In The Ribbon is handy and can be a good place to look for buttons with which to populate the Quick Access Toolbar. Finally, All Commands is especially useful if you're not sure which group a command might fall into, and it can save you a lot of clicking and scanning through lists of commands.

Inside Out-Create a Mini-Ribbon

Here is a useful trick you can use if it just seems like too much bother to click those tabs all the way at the top of the Ribbon or you just want to maximize your screen space. Saving a click here and there can make quite a difference if you do a lot of repetitive work in Excel that requires constantly accessing different tabs on the Ribbon. The following illustration shows the Quick Access Toolbar with the Ribbon minimized (double-click any tab to do this) and with buttons that correspond to every group on every default tab on the Ribbon:

The idea here is to make available every command that is usually available on the Ribbon, without actually having to click a Ribbon tab. The toolbar shown here includes only buttons that correspond to the Ribbon groups (not the individual commands) on every 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, you should use the Customize category in 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.

Identifying Items in the Customize Dialog Box

Most of the items you can see in the command list on the left side of the Customize category in the Excel Options dialog box are buttons. That is, clicking the command button on the toolbar executes the associated command immediately. You'll notice that some items also have a small icon to the right of the command name in the dialog box. These are called command modifiers, and they tell you that although the item may still be a button, it is also something more, as detailed in Table 3-1.

Table 3-1: Command Modifiers
Open table as spreadsheet

Item	Description	Image
Drop-down list	A control that displays a menu or palette from which you select an option, such as the Conditional Formatting button on the Home tab
Split button	A two-part item- one side looks and acts like a button, and the other side has a small arrow that displays a drop-down list, such as the Font Color button on the Home tab
Edit control	A control you can type into-for example, the Font drop-down list on the Home tab
Ribbon group	A control that displays a palette of items, such as the "boxed" group of Font controls on the Home tab

If you want a little more information about the commands in the command list, rest the pointer on any command to display a ScreenTip like the one shown in Figure 3-7.

Figure 3-7: ScreenTips help you identify commands in the list.

The first part of the ScreenTip tells you where on the Ribbon the command appears. In Figure 3-7, the Accounting Number Formats button (which, as you know because of the icon to the right of the command, is a split button) appears in the Number group on the Home tab. The portion of the ScreenTip in parentheses indicates the name used to refer to the command programmatically using Visual Basic for Applications (VBA).

For more information about VBA and creating macros, see Chapter 26, "Recording Macros."

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-8, clicking More Controls displays the hidden controls on a drop-down toolbar. If you'd rather have some these overflowing controls appear on the main part of the toolbar, select the Customize category in the Excel Options dialog box, and rearrange the controls in the list using the Up and Down buttons.

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

Note that we collapsed the Ribbon in Figure 3-8. To do this, double-click any tab, or click Minimize The Ribbon on the Customize Quick Access Toolbar menu (refer to Figure 3-2). Click any tab again to restore the Ribbon. Also 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 bar.

Creating Your Own Buttons

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 right 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-9 shows the Customize Quick Access Toolbar list containing a single macro that has been added to the toolbar.

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

You might notice that for the first time the Modify button below the list is active. 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 were 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, as shown in Figure 3-10, lets you select a different image.

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

For more information about macro-enabled workbooks, see "Saving Files" on page 51 and also see Chapter 26.

Inside Out-A Word About RibbonX

Microsoft made a lot of changes in the 2007 Microsoft Office system, not least of which are the Ribbon and the new XML-based file format. Put 'em together, and what do you get? RibbonX! OK, it's not really that simple, but if you want to do some extreme customization of the new user interface (UI) that Microsoft spent so much time and energy to change, you'll need to get acquainted with the XML extensibility model called RibbonX that "controls the controls" of Excel 2007.

In previous versions of Excel, toolbars and menus ruled. You could easily change them, and you could create your own toolbars and menus for special purposes. But real-world observation showed that UI anarchy was the rule, not the exception. Researchers found stray toolbars littering the screens of a huge percentage of those surveyed. So, Microsoft removed most of the up-front UI-customizing tools. Now, manipulating the Ribbon is a task you can accomplish only in VBA. So if you can't make do with customizing the Quick Access Toolbar, you'll need some basic programming skills. To take the first step, you can find a bit more information in Chapter 26.

Attaching Custom Toolbar Configurations to Workbooks

Excel saves the configuration of the Quick Access Toolbar when you exit the program. You can also attach a custom configuration for the Quick Access Toolbar to a workbook. The exact configuration you specify is activated only when you open the corresponding workbook. That way, you can repurpose the Quick Access Toolbar for specific tasks that apply to specific workbooks. You can even send copies of workbooks to co-workers, and the attached toolbar configuration appears-only when that workbook is active.

If you have two workbooks open, only one of which has an attached toolbar configuration, the Quick Access Toolbar switches between configurations when you switch between workbooks. If you created attached toolbars in previous versions of Excel, this is a welcome change, because attached toolbars would persist forever unless you specifically removed them. This old behavior was part of the UI clutter problem identified by usability studies that eventually resulted in the creation of the Ribbon.

To attach a custom toolbar configuration to a workbook, 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-11.

Figure 3-11: 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. (Despite that the list starts out blank, the default tools-Save, Undo, Redo, and Quick Print-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 will be saved with the workbook, and they will 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.

Inside Out-What Happened to My Custom Toolbar?

If you've grown accustomed to modifying toolbars and menus to suit your working preferences, many modifications are unfortunately not possible in Excel 2007 without employing VBA programming and the XML implementation called RibbonX. The good news is that any custom toolbars you created and attached to workbooks in previous versions of Excel are still accessible, though you might not think so at first glance. If you open an existing workbook with an attached toolbar, the toolbar doesn'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 it is-your custom toolbar, which will look something like the one shown here:

So, all your hard work creating custom toolbars is not lost-provided you attached them to workbooks. (Or find an older version of Excel, re-create them, and attach them!) After you open a workbook containing an attached toolbar, the toolbar continues to appear on the Add-Ins tab each time you start Excel. To get rid of it, right-click it, and close it 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. Unfortunately, you can't modify attached toolbars in Excel without working in VBA. For more information, see the sidebar "A Word About RibbonX" earlier in this chapter on page 92.

Restoring the Toolbar

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.

• Restoring the toolbar Select Customize in the Excel Options dialog box, and click the Reset button. Click OK to confirm the restoration.

• Removing individual buttons Right-click the button on the Quick Access Toolbar you want to remove, and then click the Remove From Quick Access Toolbar command, as shown in Figure 3-12.

Figure 3-12: You can easily remove any button added to the Quick Access Toolbar.

Exploring Other Toolbar and Ribbon Options

The following are a few more customization options that are quite helpful. Unless otherwise noted, you can find these options by clicking the Microsoft Office Button, clicking the Excel Options button, and then selecting the Popular category.

• Show Mini Toolbar On Selection-the first option in the Popular category in the Excel Options dialog box-controls the display of the "mini-bar" whenever you right-click a cell or object where its formatting tools are applicable, as shown in Figure 3-13.

• You can select a ScreenTip scheme to customize the label that appears when you rest the pointer on any item on the toolbar or the Ribbon. You can choose to see only a small label with the name of the item, a larger ScreenTip that includes a description, or no label at all. (For more information, see "Enhancing Accessibility" on page 100.)

• By selecting the Enable Live Preview option in the Popular category, you can simply rest the pointer on many palette items to show what the effect would look like, without even clicking the item.

• Also in the Popular category, the Show Developer Tab In The Ribbon option adds a new tab with controls for creating macros and editing VBA code.

Figure 3-13: The Mini toolbar appears when you right-click cells or relevant objects.

For more information about fonts and formatting and Live Preview, see Chapter 9, "Worksheet Formatting Techniques." For more information about macros and VBA, see Chapter 26.