In this tutorial, we will have an in-depth look at how to use and customize Quick Access Toolbar in Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021 and Excel 365.
Getting to the commands you use most often should be easy. And it is exactly what the Quick Access Toolbar is designed for. Add your favorite commands to the QAT so they are only a click away no matter what ribbon tab you currently have open.
The Quick Access Toolbar (QAT) is a small customizable toolbar at the top of the Office application window that contains a set of frequently used commands. These commands can be accessed from almost any part of the application, independent of the ribbon tab that is currently opened.
The Quick Access Toolbar has a drop-down menu containing a predefined set of the default commands, which may be displayed or hidden. Additionally, it includes an option to add your own commands.
There is no limit to a maximum number of commands on the QAT, although not all the commands may be visible depending on the size of your screen.
By default, the Quick Access Toolbar is located in the upper left corner of the Excel window, above the ribbon. If you want QAT to be closer to the worksheet area, you can move it below the ribbon.
By default, the Excel Quick Access Toolbar contains only 3 buttons: Save, Undo and Redo. If there are a few other commands that you use frequently, you can add them to the Quick Access Toolbar too.
Below, we will show you how to customize the Quick Access Toolbar in Excel, but the instructions are the same for other Office applications such as Outlook, Word, PowerPoint, etc.
Microsoft provides many customization options for the QAT, but still there are certain things that cannot be done.
You are free to personalize the Quick Access Toolbar with things like:
Here's a list of things that cannot be changed:
Most customizations to the QAT are done in the Customize Quick Access Toolbar window, which is part of the Excel Options dialog box. You can open this window in one of the following ways:
Whatever way you go, the Customize Quick Access Toolbar dialog window will open, where you can add, remove, and reorder the QAT commands. Below, you will find the detailed steps to do all the customizations. The guidelines are the same for all versions of Excel 2019, Excel 2016, Excel 2013 and Excel 2010.
Depending on what kind of command you'd like to add, this can be done in 3 different ways.
To enable a currently hidden command from the predefined list, this is what you need to do:
For example, to be able to create a new worksheet with a mouse click, select the New command in the list, and the corresponding button will immediately appear in the Quick Access Toolbar:
The fastest way to add to the QAT a command that appears on the ribbon is this:
To add a button that is not available on the ribbon, carry out these steps:
For example, to have the ability to close all open Excel windows with a single mouse click, you can add the Close All button to the Quick Access Toolbar.
To remove either a default or custom command from the Quick Access Toolbar, right-click it and pick Remove from Quick Access Toolbar from the pop-up menu:
Or select the command in the Customize the Quick Access Toolbar window, and then click the Remove button.
To change the order of the QAT commands, do the following:
For example, to move the New File button to the far-right end of the QAT, select it and click the Move Down arrow.
If your QAT contains quite a lot of commands, you may want to sub-divide them into logical groups, for instance, separating the default and custom commands.
Though the Quick Access Toolbar does not allow creating groups like on the Excel ribbon, you can group commands by adding a separator. Here's how:
As the result, the QAT appears to have two sections:
To have your favorite macros at your fingertips, you can add them to the QAT too. To have it done, please follow these steps:
As an example, we are adding a custom macro that unhides all sheets in the current workbook:
Optionally, you can put a separator before the macro like shown in the screenshot below:
By default, the Quick Access Toolbar in Excel is customized for all workbooks.
If you'd like to make certain customizations for the active workbook only, select the current saved workbook from the Customize Quick Access Toolbar drop-down list, and then add the commands you want.
Please note that the customizations made for the current workbook do not replace the existing QAT commands but are added to them.
For example, the Conditional Formatting button that we have added for the current workbook appears after all other commands on the Quick Access Toolbar:
The default location of the Quick Access Toolbar is at the top of the Excel window, above the ribbon. If you find it more convenient to have the QAT below the ribbon, here's how you can move it:
To get the QAT back to the default location, click the Customize Quick Access Toolbar button again, and then click Show Above the Ribbon.
If you wish to discard all your customizations and revert the QAT back to its original setup, you can reset it in this way:
Microsoft Excel allows saving your Quick Access Toolbar and ribbon customizations into a file that can be imported later. This can help you keep your Excel interface looking the same on all the computers that you use as well as share your customizations with your colleagues.
In the Customize the Quick Access Toolbar window, click Import/Export, then click Export all customizations, and save the customizations file to some folder.
In the Customize the Quick Access Toolbar window, click Import/Export, select Import customization file, and browse for the customizations file that you saved earlier.
That's how you customize and use the Quick Access Toolbar in Excel. I thank you for reading and hope to see you on our blog next week!
Table of contents