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.
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.
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:
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.
To add the Tabular Layout command to the QAT:
The "Show in Tabular Form" command is added to the right end of the 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.
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.
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.
The macro icon now appears on the QAT, and you can click it to run the macro.
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:
The command appears on the QAT when the Project file is active, and disappears when a different workbook is active.
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.
The icon is removed immediately, without any confirmation message.
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:
To import your saved Ribbon and QAT customizations, follow these steps:
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.
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.
Last updated: July 10, 2021 7:48 PM