Section B.1. The Customize Dialog Box | Excel 2010: The Missing Manual

B.1. The Customize Dialog Box

The central hub for customizing Excel is the Customize dialog box. To show this box, just select Tools Customize. There are three tabs here: Toolbars, Commands, and Options. You use the Toolbars tab to hide, show, or create toolbars. You use the Commands tab to add commands to an existing menu or toolbar. Finally, you use the Options tab to tweak a few miscellaneous settings that determine how menus and toolbars are displayed.

For the most part, the only option you'll want to use on the Options tab is "Always show full menus." If you turn on this checkbox, you'll be saved from Excel's perpetually changing personalized menus (as described in Chapter 1). Other options, like the ability to turn off the helpful tooltips (which appear on toolbar buttons ) or a setting that changes the type of animation used to show menus, are less helpful.

B.1.1. Managing Toolbars

The Toolbars tab lists all the available Excel toolbars, whether they're visible or not (see Figure B-1).

Figure B-1. The Toolbars tab shows a list of all the available Excel toolbars. To show a toolbar, just place a checkmark next to its name . To hide a toolbar, remove the checkmark. You can accomplish the same task by selecting the toolbar from the View Toolbars menu.

The Toolbar tab's most important feature is its ability to create new toolbars. To create a custom toolbar of your own, just follow these steps:

Select the Toolbars tab in the Customize dialog box.
Click New.

Excel shows the New Toolbar dialog box, which has a single text box where you enter the toolbar's name.
Supply a name for your toolbar.

Typically, this name is one or two words. Spaces are allowed, as are all the standard characters on your keyboard. But for clarity's sake, it usually makes sense to keep toolbar names simple and descriptive.
Click OK to create the toolbar.

A new, empty floating toolbar appears. You can dock this toolbar to any side of the main Excel window if you want, or you can let it float freely over your worksheet. You can also hide it by removing the checkmark next to its name. Adding commands to the toolbar is covered in the next section.

If you create a new toolbar and later decide you don't want it, simply select it in the list and click Delete. Similarly, you can select a toolbar and use the Rename button to give it a new name. (You can rename or delete the standard Excel toolbars.)

B.1.2. Adding, Removing, and Rearranging Commands

As soon as the Customize dialog box appears, Excel switches into a special customization mode . While Excel is in this customization mode, all the toolbar buttons and menu commands are inactive. In other words, if you select File Save, nothing happens.

More important, you'll also notice that toolbar buttons and menu commands are no longer locked in place. That means you can freely drag them from one place to another to change how Excel is organized. Figure B-2 shows an example.

Figure B-2. Once the Customize Dialog box appears (as shown in Figure B-3), you can start rearranging toolbars and menus to your heart's content. In this example, the Cells menu command is being dragged to a new place on the Insert menu, between the Columns and Worksheet commands. You can drag an item from one menu to another, and you can even drag an item from a toolbar into a menu, or from a menu onto a toolbar.

Rearranging menu commands is useful, but even more important is the ability to add or remove commands. To remove a menu command or toolbar button, just drag it off the surface of the menu or toolbar. Once the mouse pointer changes to an X symbol, release the mouse button to send the command into oblivion.

To add a command, you need to use the Commands tab of the Customize dialog box (shown in Figure B-3).

Figure B-3. The Commands tab shows all the commands that you can add to a menu or a toolbar. The commands are divided into multiple categories (shown on the left side of the window). You'll notice that many of the categories match top-level menus, like File, Edit, View, and so on. However, if you look at the menu commands in these categories, you'll find not only the familiar choices, but also some specialized commands that aren't used in the standard menussimply because they would make them too crowded. For example, in the Format menu, you'll find commands like Calculate Now (which replaces the currently selected formula with the formula result), and the mysterious "Hangul Hanja Conversion" (which allows you to work with the Chinese and Korean alphabets, assuming you have the appropriate version of Windows installed).

Note: Many of the commands duplicate or overlap the services provided by other commands. For example, in the Format menu you'll find a slew of font- related commands like Cells (which opens the Format Cells dialog box), Increase Font Size and Decrease Font Size (which automatically change the font to the next size up or down), and Font Size (which lets you choose a font size from a drop-down list). Unless you're regularly tweaking font sizes, you won't want to see all of these similar options at once.

To add a new command to a toolbar or menu, follow these steps:

Select the Commands tab in the Customize dialog box.
From the Categories list on the left, choose a category.
Scroll through the Commands list until you find the command you want to add.
Drag the command you want to add to the toolbar or menu where you want to place it.

Tip: There isn't any significant difference between toolbars and menus in Excel. You can add the exact same command to a menu as you add to a toolbar. It looks a little different in the toolbar (typically, you'll see just the icon, not the descriptive text), but it functions in exactly the same way.

When you modify menus and toolbars, Excel makes your changes automatically. There's no way to cancel or undo your changes (although you can reset menus and toolbars, as described in the Section B.1.4 a little later in this appendix). When you are finished making your changes, click Close.

B.1.3. Using Special Commands

The Categories list also includes a couple of specialized categories at the bottom of the list. These include:

Macros . This category includes two commands, both of which let you add buttons and menu entries that trigger a macro. For more information on how to set this up, see Chapter 25.
Built-in Menus . This category includes the standard Excel menus with the standard options. For example, you'll see File, Edit, View, Insert, and so on. If you drag one of these items to your menu bar, Excel creates the standard Excel menu, without any missing entries or extra details. This category is primarily useful to restore a top-level menu if you accidentally remove it in a fit of wild customization.
New Menu . This category includes one command, named New Menu. You can drag this menu onto an existing menu or toolbar to create a submenu . The new submenu starts out blank, but you can add other commands to this submenu in the same way you add commands to an existing menu.

Occasionally, you might want to modify the text or image of a menu item. For example, you might want to add a new item that represents a custom macro because the standard text and smiley-face icon aren't very informative. To change these details, right-click the menu command or toolbar button after you've added it. A menu appears with several options for changing various aspects of the command. These options include:

Name . In a menu item, the name is the text that's displayed on the menu, and in a toolbar button, it's the text that appears when you hover over the button. You can use the ampersand (&) to indicate the quick access key in a menu. (The quick access key is the letter that appears underlined in most menu commands; you can press that letter when the menu is open to activate that command quickly.) For example, by entering the name Finan&ce Macro , you make the menu display "Finan c e Macro" and set the quick access key to the "c" in "Finance."
Change Button Image . This option lets you choose an icon for the command. In a menu, this icon appears next to the text. In a typical toolbar button, only the icon appears (although you can use the menu options described in the next bullet point to show text alongside or instead of the image). If you want to get a little more sophisticated, you can copy the icon from another menu item or toolbar button. To perform this task, just right-click the icon you want to copy and select Copy Button Image. Then, right-click your custom command and choose Paste Button Image. You can even select Edit Button Image to open a small paint program where you can change the icon in painstaking pixel-by-pixel detail.
Default Style, Text Only, or Image and Text . These menu commands let you tweak the appearance of menu items and toolbar buttons by controlling whether text is displayed along with an image. (In menus, the text is always included.) However, the image portion is optionalsome menu commands include it (like File Open), while others don't (like File Close). When you add a toolbar button, the text isn't shown. If you want to make the function of the toolbar button more obvious, you might want to display the command's text. To take this step, right-click the command, and then choose Image and Text.

B.1.4. Returning to Normal

Sooner or later, if you do enough customizing, you'll accidentally remove a button or menu item you need. In fact, it's not uncommon for over-zealous customizers to accidentally remove entire top-level menus. Fortunately, such blunders are easy enough to correct.

To repair a toolbar, switch to the Toolbar tab, select the toolbar in the list, and then click the Reset button. Excel prompts you to make sure you want to reset the toolbar, and once you click OK, the toolbar reverts to its original state.
To repair a menu, right-click the menu heading (like File), and select Reset. Excel doesn't show any warningit simply changes the menu back to its original state.
If you've accidentally removed an entire top-level menu, you can't reset it because it isn't there! Fortunately, there's an easy solution. Select the Commands tab, and scroll down to the Built-in Menus category. Select the appropriate category, and then find the missing top-level menu in the Categories list. Finally, drag the top-level menu from the Categories list back to where it belongs on the menu bar.

B.1.5. Attaching Toolbars to a Workbook

Excel stores your custom toolbars and menu information in your Windows user profile (a collection of user-specific settings for each person who has an account on your computer). As a result, if another person opens your workbooks on a different computer, or if another person logs onto your computer with a different user name, the custom toolbar and menu settings don't take affect. Most of the time, this arrangement makes sense because you want personalization settings to be firmly attached to your computer and your user identity. However, sometimes you want to attach toolbars to specific workbooks so that someone else can use them.

To attach a custom toolbar to a workbook, follow these steps:

Select the Toolbars tab in the Customize dialog box.

Click the Attach button.

The Attach Toolbars dialog box appears, with two lists (as shown in Figure B-4). On the left is the "Custom toolbars" list, which shows all the toolbars you've created. On the right is the "Toolbars in workbook" list, which shows all the toolbars that are specifically attached to this workbook.

Figure B-4. Using the Attach Toolbars dialog box, you can copy the Custom Group toolbar into the current workbook. Just select the toolbar and click Copy. You can also remove a toolbar from the workbook. Just select the toolbar from the "Toolbars in workbook" list. The Copy button changes to a Delete button.

Select the workbook you want to attach from the "Custom toolbars" list, and click Copy.

Excel moves a copy of the toolbar into the workbook file. The next time you save the file, the toolbar information gets saved along with it. When someone else opens the workbook, they'll have access to your custom toolbar.
Click OK to return to the Customize dialog box.

You can also use this technique to copy a toolbar into a template file. That way, the toolbar will be available to everyone who creates a workbook based on that template.

Tip: There's no way to attach a custom menu to a workbook. Menus are stored only in your Windows user profile, and there's no way to copy or transfer them. That's one of the many reasons that extensive menu customization is a bad idea.