Customizing Quick Access Tool Bar (QAT) in MS-Excel– The Efficiency Booster

By default only one seventh of the commands are visible on the ribbon at any given point in time due to which you need to switch between tabs to use multiple options which consumes time. The solution to overcome this problem is to have all the commands which are used frequently at one place. Is it possible to have all our frequently used options at one place for an easy access?

This can be done using the QAT. This feature is available from Office 2007 and above. In this post, Let us see what QAT is and how to customize it to improve our efficiency by customizing the QAT.

What is a QAT

The Quick Access Tool Bar (QAT) is a customizable tool bar located at the left most corner of the window on the top of the ribbon in Office applications. This tool bar contains a set of commands that are independent of tab on the ribbon that is being displayed currently. By default save, undo and redo commands along with an arrow pointing downward are available on the QAT. Adding up all the required/ most frequently used commands to the QAT helps to do your work much quicker.

Positioning the QAT

You can change the position of the QAT which is on the top of the ribbon to below the ribbon i.e. above the formula bar in case if you want to use it more frequently. To do this Right Click on the Quick Access Tool bar and select the option “Show below the Ribbon”. (See below image)

Customizing Quick Access tool bar in excel
Customizing Quick Access tool bar in excel

Customizing the QAT – Adding/ Removing Commands

The QAT provides you with the option of adding other features/ options/ commands which you use frequently to it apart from the default options of save, undo and redo which helps to reduce the time to switch between the tabs in the ribbons.

To add an option/ feature/ command to the QAT click on the drop down/ arrow pointing down wards and you can see the most frequently used commands. Click on any of them to add it to the Quick Access Tool bar.

If you want to add other features/ commands that are not there in the frequently used list,
Right click on the QAT and select the “Customize Quick Access tool Bar”. A new window opens up listing all the features/ options/ commands that are available. Alternatively you can also click on more commands option in the drop down to display the Excel Options window.

In the Excel Options window, from the list of commands available choose the command which you want on the Quick Access Toolbar and click on Add button to get the command added to QAT.

Customizing Quick Access tool bar in excel

Excel Options Window

The Excel Options window provides with various options to customize your Quick Access Tool Bar.

Customizing Quick Access tool bar in excel

  1. This window provides you the option of using the customized QAT only for the current workbook or for all other documents as well.
  2. Icons on the QAT can be arranged by using the arrows available in the right pane along with adjusting the position of the QAT above/ below the ribbon.
  3. You can access more than 2000+ commands from different tabs along with the commands that are not available on the ribbon through the dropdown available on the left.
  4. Icons added to the QAT can be separated used by using the separator option. This helps to group the icons.
  5. The settings can be exported and used on a different system to replicate the QAT. You can reset to default QAT by clicking on the Reset button available at the bottom.

Adding/ Assigning Macros

To add/ assign a macro to QAT choose “Macros” option from the drop down on the right and you can see a list of all available Macros. Click on the Macro you needed and then click on “Add” button to add it to the QAT.

Every Macro added to the QAT gets an identical icon. However, you can select an icon in the Customize Quick Access Toolbar list box and click the Modify button which helps you choose an icon from the 55 set icons which are available. These icons are easy to identify and are related to the task they do. For Example Save icon is well known and has a meaning.

You can also type a text for a display name which can be see when you hover your mouse on the icon.

Customizing Quick Access tool bar in excel

Removing Icons from the QAT

Commands can be removed from QAT from the same window. Select the command which you don’t need on the QAT from the right side block and click on the remove button. The same can also be done by right clicking on the icon on the QAT and selecting “Remove from Quick Access Tool Bar”

Shortcut to Access icons in QAT

The QAT can be accessed by pressing the ALT key. Press and release the Alt key and you can see the numbers appearing below each icon on the QAT.

ALT+{the icon number} will be short cut for the command.  

To remember the shortcut numbers add the icons in the frequency you use them. The highly used command should be placed in the 1st position the net command in the 2nd position and so on.

See the image below to create a Table for the data using the Shortcut. The icon should be added to the QAT before you use the shortcut.

Customizing Quick Access Toolbar in Excel

Please Note

  1. Quick Access Tool Bar cannot be in displayed/ seen in multiple lines. It is always in a single line.
  2. The size of the buttons on the QAT is fixed. It cannot be increased/ decreased. To see bigger icons the screen resolution need to be adjusted.
  3. Only commands can be added to the Quick Access Toolbar. Contents of lists, such as indent and spacing values and individual styles, which appear on the ribbon, cannot be added to the Quick Access Toolbar.

What Commands/Icons do you use on Quick Access Tool Bar?

Please do share what commands you use on the QAT and also which commands do you feel it necessary to be added on the QAT by any user.

7 Replies to “Customizing Quick Access Tool Bar (QAT) in MS-Excel– The Efficiency Booster”

  1. Never knew that the small ribbon above there, has got this much utility. Thanks for sharing!

    1. Hi Ananth,

      Thanks for dropping by. You are right. QAT makes work much simpler but many of us just ignore the small ribbon.

  2. […] Quick Access Tool Bar (QAT): This is on the top-left corner of the screen. By default it has only 3 options save, redo and undo. You can add any feature of excel to this Quick Access Tool Bar to access the feature quickly and easily. Read more about how you can improve efficiency/ productivity using QAT. […]

  3. […] the option is not available in the ribbon. However, this can be added to the Quick Access Tool Bar. (In case if you are not sure of adding features to Quick Access Tool Bar, Please read about the […]

  4. […] Filter by Selection is not available in the ribbon. We need to enable the function and add it to Quick Access Tool bar before using […]

  5. […] stands for Quick Access tool bar. Follow the below steps, to add the close all option to […]

  6. […] There will be situations where we need more screen space to view data/ perform operations. This can be achieved with toggling the ribbon on and off. To toggle the ribbon on/ off just double click on the active tab of the ribbon. (Know more about tabs, ribbons and QAT). […]

Leave a Reply