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 Save is Alt+1. The keyboard shortcut to undo is Alt+2.
There are two ways to use the keyboard shortcut.
- Press and release Alt to view the shortcut numbers next to each icon. Then press the number of the QAT item you want to use.
- Better Option: Press and hold Alt then press the number of the QAT item. This is much faster once you have your QAT shortcut numbers memorized. You will notice that the numbers don't appear when you press and hold Alt, so you have to memorize them.
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 left thumb on Alt and left 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.
Hi,
Thanks for your post – very useful.
I use the Fill Color a lot, in particular for using the most recently used color. I am looking to find the keyboard shortcut for the most recently used color… Any idea?
Thanks!
PH
Hi PH,
Good question. I’m doing some research but not sure that any direct keyboard shortcuts are available. Are you open to using a macro with a keyboard shortcut?
One possible solution is to use the F4 key. The F4 key duplicates your previous action to the currently selected cell or range. The previous action could be anything from deleting a row to shading a cell.
This might work for you, but I’m assuming you are doing other actions in your spreadsheet between needing a color fill from your Active Fill Color on the palette. Using F4 would only work if your last action was filling a cell with the active color.
Also give me an example of your workflow where you need the shortcut. Are you entering some data or formulas, and then needing to shade cells with the active fill color?
Let me know if this makes sense, and I will keep looking for a solution. I’ve found other forums where people are asking the same question, but no good results for Excel 2007+. But I don’t give up easy… 🙂
Slightly off the QAT topic, but I tend to use three regular fill colours in my spreadsheets, so created a simple macro for each colour, assigning each colour it’s own shortcut code. i.e. Ctrl + Y, for yellow, Ctrl + G for green.
I do have the colour fill option in my QAT, so to clear the colours from cells with colour fill, I can you use Alt (to active QAT), then 0 K N (0 & K) being the shortcut to access the colour fill, and N for “no colour).
Hope that helps and makes sense.
Steve
Hi Jon,
Thanks for your post. What if I want to add more than ten shortcuts to my QAT?
I’ve tried using the e.g. Alt+ 02, Alt+ 03 shortcuts showing up for them but they don’t work as for the first ten.
Any hints? Or are we confined to using only ten shortcuts?
Thanks
SHL
Hi SHL,
Yes, you can use more than ten keyboard shortcuts with the QAT. When the shortcut is more than one character long (02, 03, etc.), you must press and RELEASE the zero 0 Key (zero), then press the 2 key.
It will NOT work if you hold down the 0 key while pressing the 2 key. You do NOT have to hold down the Alt key the entire time either. You can press and release each key in the shortcut combination.
For Alt+02, you would press and release Alt, then press and release 0, then press and release 2.
This is a great question. Please let me know if this helps.
Thanks,
Jon
Very helpful indeed! Thanks so much for your quick reply!
Have a great day
I see these posts are old, but thought I’d try. If you press and release alt then press and release 1 then press and release 2 for alt12. Wouldn’t whatever you have in position alt1 activate before you press 2?
the 10th icon has shortcut alt-09, then -08 down to -01, then -0A, -0B etc
you should see them if you press and release alt
Thank you!!
Just wanted to say for the other French keyboard users here that we need to also press maj : ALT + MAJ + [the icon number]
and to not use the pad numbers but the one one the top of the others keys, otherwise you get those : ☺☻♥♦♣â™
Hi Eve,
Thanks for the comment! Although those are some fun icons, I can see how that would be confusing… 🙂
Is there any way to change this because i find this extra step ruins the whole point of it being a quick shortcut.
hi, is there a recently used action toolbar? I know F4 does the last again, but what about the last few to be readily available. I’m not referring to recently used formulas…
thx
Nice post. I was checking continuously this weblog and I am inspired!
Extremely useful info particularly the closing section 🙂 I care for such info much.
I used to be looking for this particular info for a very lengthy time.
Thanks and best of luck.
THANK YOU! I have spent an hour looking for ways to change the defaults, then it occured to me to look for an existing shortcut. Using the mouse is so cumbersome when one is copying between two programs.
I have a question about adding the List Search to the Quick Access toolbar. Is such a thing possible? I was unable to find the List Search in the “All Commands” section of the Quick Access toolbar.
This guide and your List Search tool are both PHENOMENAL resources that have saved me loads of time.
I use a button box (Streamdeck) which beats the QAT for convenience every time once set up.