Save Your Excel Ribbon Customizations

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, use shortcuts to run the commands, and how to save your QAT and Excel Ribbon Customizations, so you can use them again later.

Using the Ribbon

Do you have a favourite command on the Excel Ribbon, that’s hard to find, or takes a few clicks to use?

For example, it takes 4 steps to change  a pivot table to Tabular layout, if you use the Ribbon commands:

  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 “Show in Tabular Form”.

Add to the QAT

If you add that command to the QAT (follow the steps below), it only takes 2 steps to change to Tabular layout.

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

quickaccesstoolbarpivotlayout02

Add a Ribbon Command to the QAT

It’s easy to add a Ribbon command to the QAT. For example, here’s how to add the Tabular Layout command:

  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.

qataddcommand

QAT Shortcuts

After you add commands to the QAT, you can use keyboard shortcuts to run those commands.

  • Count from the left, to find a command’s position number.
  • Then press Alt+[QAT position number], to run a command.

On my QAT, the Clear All command is first, so I can press Alt+1 to clear all the filters in the selected pivot table or Excel table.

If you put your favourite commands at the left end of the QAT, you won’t have to do too much counting, to figure out what position they’re in!

quickaccesstoolbarshortcut01

Export Your QAT and Excel Ribbon Customizations

If you make changes to the QAT or the Excel Ribbon, it’s a good idea to export those customizations. Later, you can import that file, if you need to install Excel again, or want the same customizations on another computer.

To export your QAT and Excel Ribbon 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 Export all customizations
  • Leave the default file name as is, or change the file name, then click Savequickaccesstoolbarexport01

To import your saved QAT and Excel Ribbon 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

More QAT Tips

See more examples on how to customize the QAT on my Contextures website.

And, if you’re setting up Excel on a new computer, there are more tips for setting up Excel.

Video: Customize the Quick Access Toolbar

Watch this short video to see how to customize the Quick Access Toolbar.

___________________

Save

You may also like...

5 Responses

  1. Shair says:

    Great post Debra!
    I always recommend to my users to be sure to backup the Personal.xlsb file as well. I have users that add macro shortcuts to the QAT only to find out the macro is not part of the the QAT shortcut they have created, but basically a link to a macro in the personal workbook.

Leave a Reply to Peter Buyze Cancel reply

Your email address will not be published. Required fields are marked *