Excel Speed: Hidden Commands and QAT Buttons

Working fast is about knowing features and functions, for sure, but, it is also about how quickly we communicate with Excel. We can set up QAT buttons to activate frequently used commands … and … hidden commands. Hidden commands? Yes … Excel has far more commands than are provided in the standard ribbon tabs. It is easy to discover and use them. This post shows how.

Objective

Let’s say there is a command we use frequently. We can easily create a button in the Quick Access Toolbar (QAT) which, by default, sits above the ribbon tabs. The nice thing about QAT buttons is they are easy to see and click AND they have a simple Alt key shortcut.

But, that is just one thing we can do with QAT buttons. We can also add a QAT button to activate one of Excel’s many hidden commands or a macro.

We’ll talk about the following:

• Add a frequently used command
• Add a hidden command
• Add a macro button

Let’s get to it.

Add a frequently used command

Microsoft makes it easy to add popular commands to the QAT. To do so, just click the drop-down on the right side of the QAT. You’ll see a list of popular commands appear as shown below.

You can click one and a corresponding button will be added to the QAT.

But, what if you frequently use a command that isn’t in this list. No problem. For example, let’s say that you frequently define custom names by using the Formulas > Define Name > Define Name command. It typically takes three mouse clicks to navigate to the command. Or, you could memorize the Alt key sequence, which for my version of Excel is Alt+M, M, D.

Another option is to create a button for this command on the QAT. Doing so is pretty easy. Simply right-click the command icon and select Add to Quick Access Toolbar as shown below.

This adds a new button to the QAT, as shown below.

Now this command can be clicked with the mouse, or, can be activated with a simple keyboard shortcut. Excel automatically assigns sequential numbers to the QAT buttons. To determine which number is assigned to each button, just press the Alt key on your keyboard. The assigned numbers appear, as shown below.

We can now activate the Define Name command with Alt+6.

But, we are just getting warmed up. It is now time to proceed to our hidden commands discussion.

Note: to remove a command from the QAT, just right-click it and select Remove from Quick Access Toolbar.

Add a hidden command

As you know, Excel is big. Really big. There are tons of Excel commands that don’t appear on the default ribbon. These are like little hidden commands just waiting to be discovered.

Here is how to find and add them. Just right-click any QAT button and select Customize Quick Access Toolbar. Alternatively, you can click the QAT drop-down and select More Commands, or, use the Excel Options dialog and select Quick Access Toolbar.

The Excel Options dialog appears, and provides two columns. The column on the left contains the commands that we can add to the QAT. The column on the right includes the commands that are in the QAT. We can just pick any command in the left column and click the Add>> button to add it to the QAT. By default, you’ll notice that the Popular Commands option is selected in the Choose commands from drop-down, as shown below.

The Popular Commands option provides only a subset of the available commands. To see all of them, just select All Commands. Or, to just see the commands that are not already in the ribbon, select Commands Not in the Ribbon, as shown below.

These are what I refer to as the hidden commands. They are hidden because they exist in Excel, but, they aren’t displayed in the Ribbon. You can scroll through the list and add any that look interesting. Play with them and if they are helpful, keep them around. Otherwise, you can just right-click and remove them.

One command I love to add to the QAT is Advanced Document Properties. It makes it easy for me to open this dialog without having to navigate to File, Info, Properties, Advanced Document Properties, or Alt+F, I, QS, Enter.

In addition to adding such hidden commands, we can also add a macro button.

Add a macro button

The easy part is adding a button to activate a macro. The hard part is building the macro 🙂

To add a macro button, just select Macros from the Choose commands from drop-down.

If you’d like to check out a few tutorials that walk through how to create macros, check out these previous posts:

• Center Across Selection Macro
• PivotTable Number Format
• SUBTOTAL Macro

If you have any other fun Excel speed or QAT tips, please share by posting a comment below … thanks!