How to Customizing the Quick Access Toolbar in MS Excel

If you find that you continually need to switch Ribbon tabs because a frequently used command never seems to be on the Ribbon that’s displayed, this tip is for you. The Quick Access toolbar is always visible, regardless of which Ribbon tab is selected. After you customize the Quick Access toolbar, your frequently used commands will always be one click away.

By default, the Quick Access toolbar is located on the left side of the Excel title bar, and it includes three tools:

1. Save : Saves the active workbook.
2. Undo : Reverses the effect of the last action.
3. Redo : Reverses the effect of the last undo.

Commands on the Quick Access toolbar always appear as small icons, with no text. When you hover your mouse pointer over an icon, you see the name of the command and a brief description. As far as I can tell, the number of icons that you can add to your Quick Access toolbar is limitless. But regardless of the number of icons, the Quick Access toolbar always displays a single line of icons. If the number of icons exceeds the Excel window width, it displays an additional icon at the end: More Controls. Click the More Controls icon, and the hidden Quick Access toolbar icons appear in a pop up window. You can add a new command to the Quick Access toolbar in three ways:

1. Click the Quick Access toolbar drop-down control, which displays a down-pointing arrow and is located on the right side of the Quick Access toolbar. The list contains several commonly used commands. Select a command from the list, and Excel adds it to your Quick Access toolbar.
2. Right-click any control on the Ribbon and choose Add to Quick Access Toolbar. The control is added to your Quick Access toolbar, positioned after the last control.
3. Use the Quick Access Toolbar tab of the Excel Options dialog box. A quick way to access this dialog box is to right-click any Quick Access toolbar or Ribbon control and choose Customize Quick Access Toolbar.

The Quick Access Toolbar tab of the Excel Options dialog box. The left side of the dialog box displays a list of Excel commands, and the right side shows the commands that are now on the Quick Access toolbar. Above the command list on the left is a drop-down control that lets you filter the list. Select an item from the drop-down list, and the list displays only the commands for that item.

Some of the items in the drop-down list are described here:

1. Popular Commands: Displays commands that Excel users commonly use.
2. Commands Not in the Ribbon: Displays a list of commands that you cannot access from the Ribbon.
3. All Commands: Displays a complete list of Excel commands.
4. Macros: Displays a list of all available macros.
5. File Tab: Displays the commands available in the back stage window.
6. Home Tab: Displays all commands that are available when the Home tab is active.

In addition, the drop-down list contains an item for every other tab. Sometimes, you need to do some guessing to find a particular command. For example, if you want to  add the command that displays the Excel Options dialog box, you can find it listed as Options, not Excel Options. To add an item to your Quick Access toolbar, select it from the list on the left and click Add. If you add a macro to your Quick Access toolbar, you can click the Modify button to change the text and choose a different icon for the macro. Notice the drop-down control above the list on the right. This lets you create a Quick Access toolbar that’s specific to a particular workbook, which is most useful when you add workbook-specific macro commands to the Quick Access toolbar. Most of the time, you’ll use the setting labeled For All Documents (Default). The only time you ever need to use the Quick Access Toolbar tab of the Excel Options dialog box is when you want to add a command that’s not on the Ribbon or add a command that executes a macro. In all other situations, it’s much easier to locate the command on the Ribbon, right-click the command, and choose Add to Quick Access Toolbar. Only you can decide which commands to put on your Quick Access toolbar. In general, if you find that you use a particular command frequently, it should probably be on your Quick Access toolbar.