

You probably know this situation: You just quickly want to use a function in Excel, but it takes you way too much time searching it within the menu bar. Admittedly, the ribbon structure of Excel can be quite confusing. But there is a solution: The Quick Access Toolbar. You can save the most important or frequently used buttons in this menu bar on top of the screen. That way, you can easily access your favorite functions. So let’s see how it works and learn some tips and tricks.
Contents
Definition: Ribbon and Quick Access Toolbar.
First of all, what is the Ribbon and what is the Quick Access Toolbar?
Excel is organized within two major menus:
Using these two menus cleverly, you can save a lot of time. Let’s take a close look at it.
The Quick Access Toolbar is always on top of the screen. It doesn’t change when you go to another ribbon. That’s why it’s very useful: You can save your most important functions there and always “quickly” access them.
Add a button to the Quick Access Toolbar by right clicking on it.
Adding and removing buttons from the QAT is very easy: Just right click on any button on the ribbon and click on “Add to Quick Access Toolbar”. Yes, it’s that simple. If the button is greyed out it means that it is already in your Quick Access Toolbar.
Removing buttons from the Quick Access Toolbar works almost the same way.
Removing a button works almost the same way: Right click on the button on the Quick Access Toolbar and click on “Remove from Quick Access Toolbar”. Now it’s gone again.
Organize the Quick Access Toolbar in Excel.
Often, you slowly add more and more button to the Quick Access Toolbar. After some time, you might want to reorganize it or clean it up.
Excel offers a menu for (re-)moving button to the Quick Access Toolbar. You can also add more rare (but still very useful buttons), which are not on any ribbon.
Right click on the Quick Access Toolbar. Then click on “Customize Quick Access Toolbar”. Now you will see the window on the right side. It has to parts: The available commands (orange) and your current Quick Access Toolbar (QAT, green).
You got the following options (the numbers correspond to the picture):
Please take a look at the image above: Importing and exporting customization files can be done by clicking that button. These customization files include your current Quick Access Toolbar settings as will as your personal ribbon settings (if you got some).
How do you get there? Right click on any ribbon or your Quick Access Toolbar and click on “Customize Quick Access Toolbar”.
Attention: If you import such settings, the existing customization will be lost.
Hold on a second. Was this information helpful so far?
Why don't you subscribe to our monthly, free Excel newsletter?
Subscribe now! Your welcome gift: Our big 45 pages keyboard shortcuts package. In case the sign-up form above doesn't work, please use this page. Sorry for the inconvenience.
Of course, I'm also on other networks:
Twitter: Follow @professorexcel
Facebook:
Add a button for your own macro to the Quick Access Toolbar.
Besides normal buttons you can also create buttons for your VBA macros. Excel allows you to add any “Sub” with no arguments. “Private Subs”, “Subs” with input arguments or functions don’t work.
In order to add your individual button, right click on the QAT again. Next, click on “Customize Quick Access Toolbar”. Switch the top drop down menu from “Popular commands” to “Macros”. Now you see the list of your available macros (either in your current document or of your Excel add-in (“xlam”-file). Simply add them with the Add button.
If a new ribbon (like the “Table Tools”) opens, half of the Quick Access Toolbar will be hidden if it is located above the ribbon.
You got two options for the location of the Quick Access Toolbar: Above or under the ribbon. Both positions have advantages and disadvantages:
QAT above the ribbonQAT below the ribbonWon’t be hidden when a new ribbon (like “PivotTable”) opens.Needs more space on the screen than above the ribbon.
DisadvantagesHalf of the ribbon will be hidden when a new (special) ribbon (like “PivotTable”) opens.
Advantages | Needs less space, as the space is otherwise just blank. |
You can easily change the position: Right click on any ribbon or the QAT and click on “Show Quick Access Toolbar above the Ribbon” (or below, depending on the current state).
Access the Quick Access Toolbar with keyboard shortcuts by pressing Alt + 1 (for the first button as shown in the numbers).
All you have to do is press the Alt key on the keyboard. Excel shows you then your options for accessing the QAT with keyboard shortcuts, usually shown with numbers. The first QAT got the number 1, the second one 2 and so on. Just press it and you can use keyboard shortcuts for your individual QAT. Quite cool, isn’t it?
The short answer: It depends. It highly depends on what you need in your daily life in Excel and what steps you already got covered with keyboard shortcuts. If you for example always use Ctrl + c for copying cells, it doesn’t make sense to add the copy button to the QAT.
But still, we got some advice for you:
Excel too slow? Speed it up. Get the book now!
Tired of waiting for Excel? Use the 30 best methods described in this book to speed up Excel calculations!
Learn more or get it on Amazon!
It’s contradicting: In the previous chapter we just said that you should make up your individual Quick Access Toolbar. But nonetheless, we’ve created an example of what buttons might be useful. But of course, it highly depends on your daily work in Excel.
So this is our suggestion:
Suggestions for your Quick Access Toolbar.
Let’s go through it by groups (each group is divided by a separator):
If you like these suggestions, please feel free to download the whole customization file here. You can import it into your Excel as described above. But please be aware, that your current customization will be removed. So please export it first if you want to keep it.
'Professor Excel Tools': Add more than 60 amazing features to Excel!
Professor's Error Helper for Excel: Solve any error in Excel.
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
Download test abc
Comments 4
Harold van Bolhuis
Dear Henrik
Thank you for the introduction.
As I really liked your setup with the automatic and manual calculation options I added those to my QAT as well.
However they are only shown with the green circle used standard for all special commands.
As they are shown in your example as tick boxes, I downloaded your file and compared it to mine.
The show exactly the same instruction. Can you tell me how to get those tick boxes? I am using Excel 2016.
With kind regards,
Harold
Henrik Schiffner
Hi Harold,
I had the same problem. But if I remember correctly, after some updates of Microsoft Office it was gone and instead of those green circle tick boxes, it showed the correct description again. If you click on “File” and “About”, it shows the version number (e.g. “Version 1707”)? Version 1707 should be the latest version as of today.
Does that help you?
Best regards,
Henrik
Sal Veltri
I have added a macro to the QAT. I would like to change the mouseover tip, it currently shows the name of the sub. I would like it to be more descriptive. Can this be done?,
Richard
Dear Henrick; I’m using Excel-2016 running on Windiws-10 (home, 64-bit). My problem is that sometimes Excel does show ALL (50 or so of) my “Quick Access Toolbar” shortcuts and sometimes it will show only some (maybe, 30 or so). Why does this annoyance happen and what can I do about it? When it show only some of the QAT shortcuts, the ones that do show are spread-out across the top of the screen with more blank-space in between the shortcuts. Thank you for any ideas you might have.
—Richard