The Excel Quick Access Toolbar or QAT as it’s also known is not only a handy place for commonly used icons, but it also enables some super easy keyboard shortcuts for your favourite tools.
The QAT sits either above or below the ribbon. I prefer to place it above the ribbon as this takes up less space because it sits in the green header bar:
Whereas below the ribbon adds an extra row to my header area, although the upside is you get nice, coloured icons which are easier to see at a glance:
You can change the location of the QAT via the drop down:
Watch the Video
Quick Access Toolbar Keyboard Shortcuts
Adding an icon to the QAT is as easy as right-clicking on the icon > Add to Quick Access Toolbar:
Alternatively, you can click on the drop down at the end of the QAT and choose from popular commands, or open the ‘More Commands’ dialog box as shown with the star in the list in the image below:
The More Commands dialog box allows you to search through all icons available and add them to the QAT:
QAT Keyboard Shortcuts
The first 9 positions in the QAT are the most prime real estate in Excel because with the click of two keys you have a very handy set of keyboard shortcuts. Pressing the ALT key and then a number from 1 to 9 is the equivalent of clicking the icons with your mouse. In the image below you can see the number for each icon which appears after pressing ALT:
It’s as simple as learning the number for each icon and you’re off and running!
Note: you can also access the icons after position 9 by entering their number code e.g. the undo icon is number 09 so the shortcut is ALT > 0 > 9, it’s just not quite as nice as entering a single number. And of course, in the case of Undo, it’s easier to press CTRL+Z.
Which Icons to put in the Quick Access Toolbar
There’s no perfect list of icons to include in the Quick Access Toolbar, but for such a prime piece of Excel real estate it’s important to consider what gets a spot based on some strict criteria.
For me that criteria are as follows:
- There must not already be a super easy keyboard shortcut. e.g. don’t put copy, paste or undo up there. Everyone knows CTRL+C, CTRL+V and CTRL+Z are the keyboard shortcuts for these most commonly used commands.
- It should save me multiple clicks. Most of the time the Home tab of the ribbon is visible, so putting commands in the QAT that are available here with a single click is probably a waste. One of the icons in my QAT is the Clear All button which requires a click on the drop down menu to expose it and then a second click on the icon, and there’s no easy keyboard shortcut already available:
- And of course, it should be something I use regularly. Just because I have an icon in my QAT doesn’t mean you should. If you don’t use Power Pivot, then don’t waste a spot in your QAT with the Power Pivot icon. And remember to update it regularly. Work habits change, so be sure to update your QAT in line with your current needs.
Mynda’s QAT
In case you’re wondering what’s in my QAT, below is a list of the icons:
You might be wondering why I have some icons I expressly said not to include, so I’ll explain:
- Paste Values – although there’s a couple of keyboard shortcuts for this, none are as short as ALT > 1
- Clear All requires two clicks to get to the button and it’s something I use regularly to clear PivotTables, which the DELETE key simply can’t handle.
- Clear All Filters is super handy when working with Excel Tables, which I do a lot.
- Refresh All is great for refreshing all queries and PivotTables in a workbook. There’s a keyboard shortcut, CTRL+ALT+F5 but I find it cumbersome.
- Launch Power Query Editor – there’s no practical keyboard shortcut for getting to this and it’s at least a two click task depending on the route you take.
- Insert PivotTable – at least a couple of clicks or a cumbersome keyboard shortcut ALT > N > V > T
- Open Power Pivot Window – at least a couple of clicks unless you’re lucky enough to be on the correct tab of the ribbon when you need it.
- Select Objects – I use this all the time and it bugs me having to click so many times to get to it.
- Manage Conditional Formatting Rules – at least two clicks and the option is at the bottom of a long list, which bugs me!
- Undo – this is outside the top 9 so it’s not taking up a prime position. I keep it in the QAT so that I can see the undo stack from the drop down:
- Redo – same as for Redo
- Save – I have autosave always turned on these days, but sometimes I like the satisfaction of clicking the button and knowing that the file is actually saving. Call me old fashioned!
Handling HTTP Errors in Power Query and Power BI
Random User
I have enjoyed and hugely benefited from your YouTubes. I didn’t know of this site; I just stumbled onto here from using Google for a specific Excel issue. You do excellent work on the site as well as the videos. Thank you hugely for your unselfish contributions to all of us.
Random User
The “trick” for more than 9 with the ASCII number code is brilliant, as well as shrewd comprehension and explanation of the QAT 9 slot scarcity and value of economizing the 9 assignments. But here’s an approach to put, oh, even 30 operations if you want to, on one single QAT icon. It requires use of VBA.
First I should note that I write many VBA macros, always available in PERSONAL, and some I use SO commonly that I assign them to QAT slots. But inevitably I run out of slots. So the “trick” is to have one macro that serves as an interface to many macros, by dropdown or a Form or however you wish, given your VBA capabilities. And note that each “sub macro”, as you might describe them, might just do 1 simple Application.Run. Even if you don’t do VBA, or want faster execution as Application.Run might yield, the learning curve to get just that far is small.
As to a dropdown, here’s a technique. For brevity let’s consider only 3 “sub macros” instead of 30 or 300. Then make the VBA-accessed dropdown be assigned as a user macro on alt-4, and have in the dropdown
A Launch Power Query Editor
B Insert PivotTable
C Select Objects
The payoff is you get to go alt-4, C, enter. Not exactly hideous. Maybe even alt-4, C if there’s a dropdown option to “select and perform” an item (I can’t recall if Excel VBA accessed dropdowns have that property).
I’d love to hear if anyone tries or benefits from this. You do need to set it up in VBA and put in PERSONAL, but if you’re fluent in VBA this might pay off a thousand fold, who knows.
Random User
Your article is valuable and clear. I like it. Bookmarked it. Here I’ll offer some hopefully useful additions.
Microsoft is a cruel monster, blending being forced to accept insanely stupid design decisions (with no option to decline), but sometimes you can blunt the damage by “making compromises” such as finding ways to do things in 3 strokes that never required more than 1 before. You might choose to make the compromises below, since, as you astutely explain, the “prime” collection is limited to a scarce 9. (Funny, before the murderously productivity assassinating 2007 ribbon I used to have 36 alt- shortcuts with icons or text atop the window, but that’s another story. MS, making the world a better place.)
Manage conditional formats is not tragic; you can still go alt-O-D. More expensive than alt-9, but just by one stroke, and you gain a precious “prime slot”, a “small compromise” to gain a “slot”.
Clear all – I go alt-E-A zillions of times daily on cells. If that’s no good for pivot tables, get comfy with this shortcut anyway. Consider this compromise to regain the alt-2 slot?
Clear filters – again it MIGHT be insufficient in some categories (Tables?) but alt-D-F-S rules. That will unfilter all regular (or advanced) cell filters. Note that this is generally superior to alt-D-F-F which removes the filtering entirely when you toggle it off, meaning to reinstitute the filtering later, you must rebuild (if you can remember!) which columns were filtered, as well as the top and the bottom of the filter area! alt-D-F-S remove the “filtering”, not the “filter”.
Paste values – no doubt alt-1 is superior. But note alt-E-S-V is close, so evaluate “prime real estate” as before. But at any rate be sure you investigate Control-Shift-V, depending on your Excel version. Hard to type, but only one stroke!
I chose the above only because they are QAT choices made in the article. Much more could be said about optimal shortcuts usage. MS catastrophically made it painful to find shortcuts now but there are many out there and even if slightly more work than “alt-4”, sometimes the compromise is worth it. I use shortcuts more than any human in the solar system. Despite Microsoft’s clue-clue-clueless damage to their productive use with the ribbon (because before you could browse every menu item and SEE the shortcut strokes – unfathomable that the designers couldn’t grasp that they ruined that, and shattered productivity) – I still use them to an inhumanly great degree. I hope this little discussion of shortcuts makes someone faster and more accurate and productive 🙂
Krist
Mynda, I really appreciate your excellent tips with Excel! I also use the quick access toolbar and find it frustrating when I use different PCs that do not have my favorite tools saved. Often, if your IT department needs to update (or reformat) your work PC, many customizations are reset. Thanks for sharing the tools you use and I have made a copy of my own toolbar in case I need to create on a new PC.
I know you are so wired with the latest Excel updates – have you ever done a command toolbar options comparison between versions. Do new toolbar options get noticed between updates, or do Microsoft add new buttons and we just need to discover them haphazardly?
Thank you for helping the world with Excel You are a hero. -Krist (kristwalicky.com)
Mynda Treacy
Hi Krist,
Great to hear you enjoyed my tutorial! Microsoft do announce changes, but they’re not easy to find. I haven’t done a toolbar options comparison. I typically find out about new features via my Microsoft MVP email list when other MVPs email about them L0(
Mynda
jim
I’m still amazed at the number of experienced Office users who are unaware of this
here’s mine, which has been largely unchanged for a decade, so I often confuse myself on someone else’s set up when I try to use what I think is a standard shortcut
1. freeze pane toggle, because no-one seems to do this with spreadsheets they send me!
2. autofilter on current value
3. autofit column width on current cell, used more than any other QAT, except 5
4. back, often followed by paste values
5. paste values
6-9. commonly used border commands (eg alt-9,8,7 gets me a thick RH border)
and then many higher ones, often for the same reasons (eg Undo, so I can see the stack), some just for the visual (eg wrap text which shows if the current cell is wrapped or not)
Mynda Treacy
Thanks for sharing, Jim!
Random User
Jim, since like everyone you’re plagued by the limit of 9, maybe consider memorizing these shortcuts to free up slots:
alt-W-F-F to toggle Freeze. The Microsoft Deproductivity department is very proud of this one, because they diabolically recognized that alt-W-F (which has been there forever) was too easy.
alt-O-C-A for the autofit. I too go alt-O-C-A often, easily 60 times per day; going alt-3 wouldbe nicer, but, it’s a tradeoff for the limit of 9 slots, you know?
Ctrl-shift-V for paste by value. This is I believe a relatively recent addition so may be Excel version dependent.
I like you scheme. Just offering some hopefully helpful tweaks.
Andy
My company has a Group policy setting that deletes my custom QAT every few minutes. It must cost the company a lot in staff time, but they keep saying I’m the only person who has complained!
I used to keep a copy to import on my Desktop, but if you import it you have to minimise and maximise to make it visible. By which time the policy may have reset it anyway. So it is back to finding things on the Ribbon… Adios QAT, you are much missed …
Mynda Treacy
What a shame, Andy! I suppose it’s their loss, but still very frustrating for you.