Home > Skills > Shortcuts > QAT Customize Quick Access Toolbar The Quick Access Toolbar (QAT) is at the top of the Excel window, and comes with a few default commands installed. See how to customize the QAT, and add macros or favorite commands.

Introduction

Add commands to the Quick Access Toolbar (QAT) so they are easy to use. There are instructions and examples below.

• If your favourite Ribbon commands are buried in submenus, add those the QAT.
• Or, add your favourit macros, or commands that aren't on the Excel Ribbon.

Also, be sure to export your QAT customizations, so you can use them on other computers, or when installing a new version of Excel.

Add Ribbon Command to QAT

Some Excel commands are on the Ribbon, but in submenus, where it takes a few clicks to get to them. For example, to it normally takes 4 steps to change the report layout of a pivot table:

1. Select a cell in the pivot table
2. On the Ribbon, click the Design tab
3. Click the arrow on the Report Layout button
4. Click on one of the Report Layout options.

If you usually change the pivot table to Tabular Layout, add that command to the QAT (follow the steps below). Then, it only takes 2 steps to change the report layout of a pivot table.

1. Select a cell in the pivot table
2. On the QAT, click the "Show in Tabular Form" command

Add a Ribbon Command to the QAT

To add the Tabular Layout command to the QAT:

1. Select a cell in the pivot table
2. On the Ribbon, click the Design tab
3. Click the arrow on the Report Layout button
4. Right-click on the "Show in Tabular Form" command.
5. Click Add to Quick Access Toolbar.

The "Show in Tabular Form" command is added to the right end of the QAT.

Add Close All and Exit to QAT

In Excel 2013, each file opens in its own window, and has its own Close button. To make it easier to close multiple windows quickly, you can add the Close All and Exit commands to the QAT.

Watch this video to see the steps.

Add a Macro to the Quick Access Toolbar

If you use an Excel command frequently, or if an Excel command isn't available on the Excel Ribbon, you can add its icon to the Quick Access Toolbar (QAT). This short video shows the steps, and the written instructions are below the video.

How to Add a Macro to the QAT

For example, here are the steps to add a macro to the QAT. The macro is named ToggleR1C1 and is stored in the Personal Macro Workbook.

• Note: For a macro to run, it must be in a workbook that is currently open. The Personal Macro Workbook opens automatically, when Excel opens, so that is a good place to store your frequently used macros.

To add a macro to the QAT, follow these steps:

• At the right end of the QAT, click the drop down arrow
• Click More Commands

  

• In the Choose commands from drop down, click Macros

  

• In the list of macros, click the PERSONAL.XLS!ToggleR1C1 macro
• Click Add, to move it to the Quick Access Toolbar

  

• In the QAT list, click the PERSONAL.XLS!ToggleR1C1 macro
• Click Modify, and click on an icon for the macro (I use the 8-ball), then click OK

  

• Click OK, to close the Excel Options window.

The macro icon now appears on the QAT, and you can click it to run the macro.

Add Command for Specific Workbook

You can also add commands to the QAT for a specific workbook only. For example, if you frequently add comments in a specific workbook, add the Insert a Comment button for that workbook only.

Watch this video to see the steps, and the written instructions are below the video.

To add a QAT command for a specific workbook, follow these steps:

• Open the workbook where you want the QAT command to appear
• At the right end of the QAT, click the drop down arrow
• Click More Commands
• In the Excel Options window, choose your workbook name, from the drop down at the top right



• In the list of commands at the left, find the command that you want to use, and add it to the QAT.
• NOTE: You can also add buttons for Macros from the selected workbook to the QAT.
• Click OK, to close the Excel Options window.

The command appears on the QAT when the Project file is active, and disappears when a different workbook is active.

• 

Remove a Command from the QAT

If you no longer need an Excel command on the QAT, you can remove it. For example, here are the steps to remove a macro from the QAT.

• On the QAT, right-click the command that you want to remove.
• Click Remove from QAT

  

The icon is removed immediately, without any confirmation message.

Export and Import QAT Customizations

After you add or remove commands on the QAT or the Ribbon, you should export the current settings. Then, if you want to set up a new computer, or if you have to re-install Excel, you can import those saved settings.

To export your Ribbon and QAT settings, follow these steps:

• Right-click anywhere on the QAT, and click "Customize Quick Access Toolbar"
• At the bottom right, click the Import/Export button
• Click Export all customizations



• Leave the default file name as is, or change the file name, then click Save

To import your saved Ribbon and QAT customizations, follow these steps:

• Right-click anywhere on the QAT, and click "Customize Quick Access Toolbar"
• At the bottom right, click the Import/Export button
• Click Import customizations
• Select the customizations file that you want to import, then click Open

QAT Keyboard Shortcuts

To quickly use the commands on the QAT, use the keyboard shortcut, Alt + [QAT button number]

For example, if Clear Filters is the first command at the left of the QAT, press Alt + 1

That will clear the filters in the selected pivot table or Excel table.

Customize Excel Ribbon Tab

You can also customize the Excel Ribbon, by moving or removing the groups on a default Ribbon tab. In this video, see how to move groups on the Data tab, so the tools that are used most often are in the middle of the tab, and easier to reach.

Related Links

Add a Clear All Command to QAT

Show Calculation Mode on QAT

Excel Setup Tips

Adding Macro Code to a Workbook

Create an Excel UserForm

Excel UserForm With ComboBoxes

Excel VBA ComboBox Lists