Contextures

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.

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

Show in Tabular Layout command on QAT

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.
  6. Show in Tabular Layout command on Ribbon

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

Show in Tabular Layout command on 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.

Close All and Exit on 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.xlsb file.

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

    QAT More Commands

  • In the Choose commands from drop down, click Macros

    QAT choose commands

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

    QAT add macro

  • 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

    QAT modify button

  • 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.

macro icon on the QAT

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

    QAT remove

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
  • Export all customiations

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

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.

keyboard shortcut Alt + QAT number

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

Search Contextures Sites

 

Excel Tools Add-in

 

 

Excel UserForms for Data Entry

 

 

 

 

Excel Data Entry Popup List

 

Last updated: June 27, 2017 10:05 AM
Contextures RSS Feed