Make better use of the Quick Access Toolbar (QAT)
We all know that shortcuts could save us lots of time when working with Excel. Excel has many built-in shortcuts. Ctrl+C, Ctrl+V should be the most popular and commonly used ones as most of us are doing copy and paste daily, or even hourly. ;p Just to name a few more, Ctrl+1 to format cells, Ctrl+S to Save, Ctrl+F to Find are some other commonly used shortcuts using Ctrl+ Key combinations.
However, there are some actions you would not find the Ctrl+ shortcut for it, e.g. Format Painter, Sort A to Z, Align (selected objects) Top, etc…
But did you know that we can access to almost all buttons on Ribbon and QAT by using Alt key combinations?
In this post, I am going to show you how to find out the Alt keys combination for actions you find on Ribbon, and more importantly, how to put your favorite (most frequently used) actions onto QAT for enhanced productivity.
Using the Alt Key
When you press the Alt key, you will see the keyboard keys for each accessible tab / button on QAT, like the screenshot you see below:
Format Painter (Alt, H, F, P)
To access to “Format Painter” for example, press Alt, H, then you will see the corresponding keys for each button under Home tab:
Now press F, P (one by one, i.e. F followed by P; no need to press coma). Then the “Format Painter” is activated.
==> Alt, H, F, P to activate “Format Painter”
Sort A to Z (Alt, H, S, S)
Not all buttons are residing right under a tab. Some buttons require more drill downs, e.g. “Sort A to Z” which is under “Sort & Filter”
In this case, we will see the list of actions under “Sort & Filter” (with corresponding keys) after pressing Alt, H, S
Now, simply press S (to “Sort A to Z”)
==> Alt, H, S, S to sort A to Z
Alight (selected objects) Top (Alt, J, D, A, A, T)
Let’s see one more example. Some tab is only visible when you have certain object selected. You will see the “Drawing Tools Format” tab when object(s) is/are selected.
So when object(s) is/are selected, pressing Alt, J, D would take you to the tab:
Now press A, A, then you will see the list below:
Press T to Align (selected objects) Top
==> Alt, J, D, A, A, T to align selected objects to Top
By deploying the same routine, we should be able to access to any buttons on Ribbon. BUT as you see, there are some buttons requiring multiple levels of drill down and thus making the “shortcuts” not really short enough. Take the last example as reference, it required six key strokes… not to mention it is difficult to memorize… 😦
So why not putting it onto QAT for easier and super quick access?
First of all, let’s see what’s on the QAT by default:
There are only three buttons there… they are Save, Undo, and Redo.
When we press Alt, we will see that there will be number assigned to each button on QAT. We can access to them by Alt, 1 / 2 / 3 / etc.
However, for these three commonly used buttons with very easily accessible built-in Ctrl+ shortcuts, when you need to call for them by keyboard, would you prefer Alt, 1 / 2 / 3 or using Ctrl+ key combinations?
I would absolutely prefer the latter.
So why not putting the buttons that you used most frequently but without a built-in Ctrl+ key shortcut onto QAT?
Simply go to the button –> right-click –> Add to Quick Access Toolbar
The following is the QAT after adding the three buttons we used above.
Now you should have expected that we could press Alt, 4 / 5 / 6 to call for these actions.
Rearranging the order
Well, you want your number 1 used action to be put on the 1st position on QAT, not the 4th, so that you could press Alt, 1 to call for that action. Let’s achieve it by rearranging the order of buttons on QAT.
1) Click the drop down arrow on the right most of QAT
2) Select “More Commands…”
3) Select the button on the QAT you want to move up / down
(Tip: From this window, you may indeed add any commands that you don’t see on Ribbon)
4) Click OK when you are done
5) There you go…
Try your new customized Shortcuts
Alt, 1 to activate Format Painter (vs. Alt, H, F, P)
Alt, 2 to Sort A to Z (vs. Alt, H, S, S)
Alt, 3 to Align (selected objects) Top (vs. Alt, J, D, A, A, T)
See? Before we needed 4-6 keystrokes that are difficult to remember; after, we need only 2 key strokes which are easily remembered. Isn’t it much more efficient? 🙂
Now you are ready to create your own Alt Shortcuts.
- Use this trick to those buttons without built-in Ctrl+ key
- Pick up to 5 actions that you used most frequently for
- the Alt 1 to 5 can be easily pressed by one hand;
- putting too many buttons on QAT can be confusing;
- it is difficult to memorize too many (for me)
What keys you plan to put to the first three positions on QAT? Please share with us in comments.
Pl share “Create your own favorite keyboard shortcuts in #Excel with VBA”.
Have you ever tried Skitch for screen annotation? Might be easier than drawing your own arrows.
No I didn’t. Thanks for your suggestion. Will have a look.