Adding commands that are otherwise not available
In previous versions of Excel, it was relatively easy for end users to make changes to the user interface. They could create custom toolbars that contained frequently used commands, and they could even remove menu items that they never used. Users could display any number of toolbars and move them wherever they liked. Those days are over.
With the introduction of the new Ribbon-based user interface in Office 2007, end-user customization is severely curtailed. Although you can modify the Ribbon, it’s a rather complicated process, and it’s not something a casual user would do. In Office 2007, user-interface customization is limited to the Quick Access Toolbar, or QAT, which is the topic of this chapter.
The QAT is always visible, regardless of which Ribbon tab is selected. After you customize the QAT, your frequently used commands will always be one click away.
Note
The only situation in which the QAT is not visible is in Full Screen mode, which is enabled by choosing View 
Workbook Views Full Screen. To cancel Full Screen mode, right-click any cell and select Close Full Screen or press Escape.
By default, the QAT is located on the left side of Excel’s title bar (see Figure 23.1). It includes three tools:
Save: Saves the active workbook.
Undo: Reverses the effect of the last action.
Redo: Reverses the effect of the last undo.
If you prefer, you can move the QAT below the Ribbon. To do so, right-click the QAT and select Show Quick Access Toolbar Below The Ribbon. Moving the QAT below the Ribbon uses additional vertical space on your screen. In other words, you’ll be able to see one less row of your worksheet if you move the QAT from its default location.
Commands on the QAT always appear as a small icon, with no text. When you hover your mouse pointer over an icon, you see the name of the command and a brief description.
Customizing the QAT consists of adding new commands to it. If you find that you use some Excel commands frequently, you can make these commands easily accessible by adding them to your QAT.
As far as I can tell, there is no limit to the number of commands that you can add. But regardless of the number of icons, the QAT always displays a single line of icons. If the number of icons exceeds Excel’s window width, it displays an additional icon at the end: More Controls. Click the More Controls icon, and the hidden QAT icons appear in a pop-up window.
You can add a new command to the QAT in three ways:
Click the QAT drop-down control, which is located on the right side of the QAT (see Figure 23.2). The list contains a few commonly used commands. Select a command from the list, and Excel adds it to your QAT.
Right-click any control on the Ribbon and choose Add To Quick Access Toolbar. The control is added to your QAT, after the last control.
Use the Customization tab in the Excel Options dialog box. A quick way to access this dialog box is to right-click any Ribbon control and select Customize Quick Access Toolbar.
The remainder of this section discusses the Customization tab in the Excel Options dialog box, shown in Figure 23.3.
The left side of the dialog box displays a list of Excel commands, and the right side shows the commands that are currently in your QAT. Above the command list on the left is a drop-down control that lets you filter the list. Select an item for the drop-down, and the list displays only the commands for that item.
Some of the items in the drop-down are
Popular Commands: Displays commands that Excel users commonly use.
Commands Not In The Ribbon: Displays a list of commands that you cannot access from the Ribbon.
All Commands: Displays a complete list of Excel commands.
Macros: Displays a list of all available macros.
Office Menu: Displays the commands available in the Office menu.
Home Tab: Displays all commands that are available when the Home tab is active.
In addition, the drop-down contains an item for every other tab.
To add an item to your QAT, select it from the list on the right, and click Add. Notice some of the commands in the list on the left display an icon. The icon tells you what form the command will take: A drop-down, a split button, and edit control, or a Ribbon group. If the command doesn’t display an icon, it’s a button control.
If you add a macro to your QAT, you can click the Modify button to change the text and choose a different icon for the macro.
Tip
The only times you ever need to use the Customize tab in the Excel Options dialog box is when you want to add a command that’s not in the Ribbon, or add a command that executes a macro. In all other situations, it’s much easier to locate the command in the Ribbon, right-click the command, and choose Add To Quick Access Toolbar.
Other QAT actions include the following:
Rearranging the QAT icons: If you want to change the order of your QAT icons, you can do so in the Customization tab in the Excel Options dialog box. Select the command and then use the Up and Down arrow buttons on the right to move the icon.
Removing QAT icons: The easiest way to remove an icon from your QAT is to right-click the icon and select Remove From Quick Access Toolbar. You can also use the Customization tab in the Excel Options dialog box. Just select the command in the list on the right and click Remove.
Resetting the QAT: If you want to return the QAT to its default state, display the Customization tab in the Excel Options dialog box and click the Reset button. All your customizations disappear, and the QAT then displays its three default commands.
You may be curious about how Excel keeps track of your customized QAT. The information is stored in an XML file named excel.qat. The file is located here:
C:Documents and Settings<username>Local SettingsApplication DataMicrosoftOffice
You can view this file with a text editor or an XML viewer. If you make a copy of the file and rename it with an XML extension, you can even open it with Excel (when prompted for how to open the file, specify as an XML table). However, you cannot modify the excel.qat file using Excel.
If you create the ultimate QAT, you can share your excel.qat file with your colleagues.