When talking about keyboard shortcuts, there is always discussion on what’s the most time-saving shortcuts, what are the top X shortcuts you should learn, what is the most frequently used shortcuts, etc…
Just to name a few, Ctrl+S to save, Ctrl+Z to undo, Ctrl+C to Copy, together with Ctrl+V to paste. I won’t disagree these are popular shortcuts and probably the most frequently used shortcuts for most users. Nevertheless, we always want to learn more and then start searching on the Internet for various shortcuts. But did you know how many shortcuts are there for Excel? I don’t know the exact number, but I know there is a lot! More than I can remember a quarter of them. So it is not practical to try to learn them all.
Then the question is:
Which shortcuts key should I learn?
My answer to this question is: You are asking the wrong question. Your question should be:
When I work with Excel, the most frequently performed actions are A, B, C, D… What are the shortcuts for these actions?
These actions could be totally different for different people. Someone would need Autosum (Alt, =) a lot, while some others need to group rows and columns (Alt+Shift+Right Arrow) frequently; or some people just need to Merge and Centre (No built-in shortcut).
Honestly, it really depends on your job nature is and what you need Excel for. Therefore you should be the one who decides what shortcuts you should learn. It is easy. Simply think about which buttons on the ribbon your click most? Next time when you hover your mouse cursor on that button, wait a bit and observe the tip of using it. If there is a built-in shortcut for that button, you will see. Like below:
Indeed, you could learn a lot of Ctrl+ shortcut simply through observation. There are, however, many buttons without built-in Ctrl+ shortcuts. But…
did you know… you could navigate to any button on the ribbon by using Alt key?
- For example: To insert a row using Alt keys…
Technically, we can assess to any button on the ribbon using Alt keys combination. Nevertheless, some buttons are located deep inside multi-level and we may need 4 to 5 (or even more) keystrokes to get there. Not too convenient… 😦
The good news is… we can put commands on Quick Acess Toolbar (QAT).
Before I proceed, I would like you to pay attention to the QAT when you press the Alt key:
Did you see that there are numbers assigned to the QAT buttons? So for the above QAT, I can press Alt, 2 to Save, Alt, 3 to Undo. Isn’t it super handy? You should know what I am going to tell you next: To assign the buttons you really need frequently to the first 4 buttons on QAT.
For buttons on the ribbon, simply right-click it and then “Add to Quick Access Toolbar“.
For example, to add “Clear All” to QAT,
- Click the drop-down for “Clear…” on the Editing group on Home tab
- Right–click “Clear All”
- Add to Quick Acess Toolbar
There we go:
Another way to modify the QAT is to click the drop-down arrow at the end of the QAT, then select “More Commands…”
It takes us to the “Quick Acess Toolbar” in Excel Option:
- Choose where the command is coming from… (this is just the grouping of commands for easy navigation, you will need to select the command shown underneath)
- Select a command you find under the group selected in step 1, click “Add>>” to add it to the right-hand side (QAT). You may “<<Remove” any existing commands on QAT too
- Move the commands up or down according to your own preference.
- Click OK (not X) when you are done.
This is my QAT (for demonstration).
Now I have my customized shortcuts:
- Alt, 1 –> Clear All (This one is actually my number one QAT button)
- Alt, 2 –> Subscript
- Alt, 3 –> Sort ascending
- Alt, 4 –> Decrease decimal
Why I removed Save, Undo, and Redo? These are with no doubt the most frequently used actions. Yes, they are. That’s why I can tell the shortcuts for these are Ctrl+S, Ctrl+Z, and Ctry+Y respectively with no effort at all. 😛
- If there is a built-in Ctrl+ shortcut for a button, remember the Ctrl+ key combination for that button instead of putting it on QAT
- Put only those without Ctrl+ shortcuts on QAT
- Put your MOST frequently used buttons to the first four positions on QAT. Why? Because you can use your left hand to reach them easily 🙂
- Don’t put too many buttons on QAT… otherwise, it dilutes focus.
- You may also assign Personal Macro to QAT
Note: Once you have customized your QAT, you need to put it into practice. You will find yourself unconsciously going back to the ribbon for the button. This is normal as you have the habit built for a long time. You need to “force” yourself to the new QAT (and Alt 1 to 4 key combinations) consciously until it becomes your new habit. Believe me, if you have carefully chosen your frequently used commands to the QAT 1 to 4, you will soon find it super handy to use Alt 1 to 4 for these four actions, just like Ctrl+C and Ctrl+V. 🙂