In previous versions of Excel, you could fill the screen with built-in and custom
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
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.
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
The Microsoft Office Button is not part of the Quick Access Toolbar-it stays in the
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
Figure 3-3: Right-click any command or
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
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.
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
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
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
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
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
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.
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.
A control that displays a menu or palette from which you select an option, such as the Conditional Formatting button on the Home tab
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
A control you can type into-for example, the Font drop-down list on the Home tab
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
For more information about VBA and creating macros, see Chapter 26 , "Recording Macros."
Figure 3-8: If you add more buttons than can be displayed, click the More Controls button.
Note that we
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
In previous versions of Excel, toolbars and
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
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
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
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
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
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.
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.
The following are a few more customization options that are quite helpful. Unless
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
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
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 .