The Quick Access Toolbar (QAT) in Excel 2007 is extremely useful for creating keyboard shortcuts for items that don’t have a defined keyboard shortcut. For example, there is no defined keyboard shortcut to Paste Values but you can easily set it up on the QAT.
The QAT is accessed through the keyboard by pressing the Alt key. Press and release the Alt key and you will see numbers appear next to each icon on the QAT.
So Alt + [the icon number] is the keyboard shortcut for each item. In this example, the keyboard shortcut for paste values is Alt+1. The keyboard shortcut for the format painter is Alt+2.
There are two ways to use the keyboard shortcut.
I tend to move the icons around based on the task I’m working on in Excel. If I’m going to be using the format painter a lot, I will move it to the “1” position (farthest left) while I’m using it. This is mainly because it is easier to Press Alt+1, Alt+2, and Alt+3 on the keyboard by placing your right thumb on Alt and right index finger on the number. The other numbers can be a bit of a stretch, depending on your keyboard.
See my post on how to setup the QAT for more details.
The Quick Access Toolbar (QAT) in Excel 2007 is a great option for shortcuts to your most used commands. It’s faster to click on an icon in the QAT (one click) versus clicking on the ribbon tab and then the command (two clicks + mouse navigation). And you can use keyboard shortcuts for the QAT to make things even easier.
Setting up the QAT is simple and consists of two steps: Adding commands and arranging the icons.
The easiest way to add commands to the toolbar is to:
The command icon will be added to the end of the QAT.
To arrange the icons:
The order of the icons becomes important when you are using keyboard shortcuts to access the commands. And I highly recommend this as a much faster way to execute commands in Excel, versus using the mouse. You can also use the Options window (above) to add and remove commands from the toolbar.
This will depend on what you use Excel for and which commands you use most often. If you are doing lots of data entry and manipulation you might want to have the “Paste Values” and “Format Painter” commands at the top of your list. Reporting and analyzing, you may want some charting and page layout commands. The key is to have the commands you use most often located closest to the left because the keyboard shortcuts are easier to press with your thumb and index finger.
The toolbar can be place above or below the ribbon by clicking the down arrow to the right of the QAT and selecting Show Below the Ribbon or Show Above the Ribbon.
I prefer to have it above because it reduces the total vertical height of the top toolbars, which gives you more spreadsheet real estate. But you may find that you have so many icons that it fits better below the ribbon. If you are a heavy mouse user (vs. keyboard shortcuts) then it is also easier to navigate to the QAT if it is below the ribbon.
Checkout my recommendations on commonly used commands for the Quick Access Toolbar.