• Excel Book Excerpt

Excel Make Your Most-Used Icons Always Visible

This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Make Your Most-Used Icons
Always Visible

Problem: With the ribbon, I can only see one-seventh of the icons at any one time. I find that I spend a lot of time on the Data tab, but I annoyingly have to keep switching back to the Home tab. Does Microsoft really think this is better?

Strategy: Microsoft provided the Quick Access toolbar to address this problem. You can add your favorite icons to the Quick Access toolbar and then, because the Quick Access toolbar is always visible, you can invoke your most-used icons without having to switch ribbon tabs so frequently.

The Quick Access toolbar (QAT) starts out as a small bar with the icons Save, Undo, and Redo. It initially appears above the ribbon, above the File menu, as shown here.


Figure 32 The QAT starts with three icons.

If you right-click the Quick Access toolbar, you can choose to move it below the ribbon. This gets your most used icons closer to the grid and provides room for a few more icons.

A dropdown at the end of the QAT offers several popular commands that you can add to the toolbar. Two of those were added due to outcry over an extra mouse click.

It used to be that opening the File menu would show a list of recent files. You could use a shortcut of Alt+F+2 to open the second most recent file. In Excel 2010, once a file is open, you have an extra click to get to the recent file list. You have to use Alt+F then R for Recent, then select the file. During the Excel 2010 beta, customers complained loudly about that one extra click. By adding the Open Recent File command to the QAT, you can get to the improved recent file list in one click. This is worth it; I find that I use the QAT icon the most.


Figure 33 Add these popular commands to the QAT.

For twenty years, Ctrl+P would print your document. Starting in Excel 2010, Ctrl+P takes you to the Print panel in the Backstage view. You then have to click the Printer icon to actually print. Again, this is one more mouse click than previous versions. Add the new Quick Print icon to the QAT if the extra step annoys you.

When you find yourself using a ribbon icon frequently, you can right-click the icon and choose Add to Quick Access Toolbar, as shown below. This is the easiest way to customize the Quick Access toolbar.


Figure 34 Right-click a command and add to QAT

Gotcha: Some of the icons look similar to one another when moved to the Quick Access toolbar. For example, Goal Seek, Go To Special and Scenario Manager use a green crystal ball icon. Unless you are a fortune teller, hover over each icon to see its ToolTip and tell which is which.


Figure 35 The green crystal ball is used for too many commands.

Additional Details: You can right-click the Quick Access toolbar and choose Customize Quick Access Toolbar to reach the full-featured dialog shown in Figure 36. The dialog offers a dropdown of categories on the left. Below this dropdown is a list of icons from the category. Here’s how you use this dialog:

  • You can select an icon on the left and click the Add button to add the icon to the Quick Access toolbar.
  • You can select an icon on the right and click the Up or Down buttons to re-sequence the icons on the Quick Access toolbar.
  • You can click the Reset button near the bottom to undo all your customizations and restore the Quick Access toolbar to the initial three buttons.
  • You can use the top-right dropdown to say that certain icons should be assigned to the current workbook. Most Quick Access toolbar icons apply to every workbook. However, you can have 10 icons for every workbook and then add 3 additional icons for each specific workbook. The 10 global icons appear first, followed by the 3 local icons.
  • You can organize your icons into logical groups and then add a separator between groups. To do this, you click the <Separator> item at the top of the left list and then click Add to add a vertical line between icons.
  • You should pay particular attention to the category Commands Not in the Ribbon. If one of your favorite Excel 2003 or earlier commands is in this category, Microsoft completely left it out of the ribbon. The only way to access the command is by adding it to the Quick Access toolbar.


Figure 36 You can find all commands here.

<-Previous Topic Next Topic->

For more resources for Microsoft Excel: