• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Quick Access Toolbar

You are here: Home / Excel / Excel Quick Access Toolbar
QAT
June 2, 2021 by Mynda Treacy

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:

Excel Quick Access Toolbar above ribbon

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:

Excel Quick Access Toolbar below ribbon

You can change the location of the QAT via the drop down:

Excel Quick Access Toolbar drop down menu

Watch the Video

Subscribe YouTube

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:

add icons 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:

Quick Access Toolbar More Commands

The More Commands dialog box allows you to search through all icons available and add them to the QAT:

Customize the Quick Access Toolbar

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:

Excel Quick Access Toolbar keyboard shortcuts

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:

  1. 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.
 
  1. 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:

    Clear All icon

 
  1. 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:

Mynda's Quick Access Toolbar

You might be wondering why I have some icons I expressly said not to include, so I’ll explain:

  1. Paste Values – although there’s a couple of keyboard shortcuts for this, none are as short as ALT > 1
  2. 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.
  3. Clear All Filters is super handy when working with Excel Tables, which I do a lot.
  4. 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.
  5. 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.
  6. Insert PivotTable – at least a couple of clicks or a cumbersome keyboard shortcut ALT > N > V > T
  7. 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.
  8. Select Objects – I use this all the time and it bugs me having to click so many times to get to it.
  9. Manage Conditional Formatting Rules – at least two clicks and the option is at the bottom of a long list, which bugs me!
  10. 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:

    Undo Icon

  11. Redo – same as for Redo
  12. 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!
QAT

More Excel Posts

tips for working in multiple excel files

Hacks for Working in Multiple Excel Files

Awesome tips for navigating, arranging and working in multiple Excel files. Guaranteed to streamline your workflow and increase productivity.
chatgpt for excel

ChatGPT for Excel

Using ChatGPT for Excel can be hit and miss. Learn the best uses for ChatGPT to make your Excel life easier and what to avoid using it for.
excel templates

Where to Find Free Excel Templates

Where to find free Excel templates and how to create your own Excel templates. Using templates saves time and effort.
Easily Remove Password Protection from Excel Files

Easily Remove Excel Password Protection

How to remove Excel password protection when you’ve forgotten the password. Works for sheets, workbooks and read only files.
Import data from a picture to Excel

Import Data from a Picture to Excel

Import data from a picture to Excel. Works with pictures from a file or the clipboard and loads it to the spreadsheet.
excel online

5 Excel Online Features Better than Desktop

5 Excel Online Features Better than Desktop including searchable data validation, track changes, single line ribbon and more.

10 Common Excel Mistakes to Avoid

10 common Excel mistakes to avoid, including merge cells, external links, formatting entire rows/columns and more.
new Excel features

Cool New Features in Excel for Microsoft 365

Cool New Features in Excel for Microsoft 365 including the navigation pane, smooth scroling, unhide multiple sheets and more.
dynamic dependent data validation

Dynamic Dependent Data Validation

Dynamic Dependent Data Validation with dynamic array formulas like FILTER make it quick and easy to set up.

Share and Collaborate in Excel

Share and Collaborate in Excel just like Google Sheets! Show changes, custom views, threaded comments with @ mentions and more.


Category: Excel
Previous Post:handling http errors in power query and power biHandling HTTP Errors in Power Query and Power BI
Next Post:Fuzzy Matching in Power Queryfuzzy matching in power query

Reader Interactions

Comments

  1. Random User

    November 5, 2022 at 10:51 am

    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.

    Reply
  2. Random User

    November 5, 2022 at 10:42 am

    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.

    Reply
  3. Random User

    November 5, 2022 at 10:41 am

    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 🙂

    Reply
  4. Krist

    July 1, 2021 at 7:36 am

    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)

    Reply
    • Mynda Treacy

      July 1, 2021 at 9:35 am

      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

      Reply
  5. jim

    June 4, 2021 at 12:03 am

    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)

    Reply
    • Mynda Treacy

      June 4, 2021 at 3:58 pm

      Thanks for sharing, Jim!

      Reply
    • Random User

      November 5, 2022 at 10:49 am

      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.

      Reply
  6. Andy

    June 3, 2021 at 10:44 pm

    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 …

    Reply
    • Mynda Treacy

      June 4, 2021 at 6:48 pm

      What a shame, Andy! I suppose it’s their loss, but still very frustrating for you.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.