I use eight great tools in my Quick Action Toolbar (QAT), and you might want to consider using them yourself.
Before I get into the tools, let’s start with a quick look at how to add tools to your QAT, and how to remove them.
How to Modify Your QAT
Your QAT is the toolbar that’s at the very top of your screen by default. Here’s what my QAT looks like:
To modify the QAT, you first need to click the Quick Access Toolbar tab of your Excel Options dialog. I can think of at least three ways to do that:
- Right-click any tool in your QAT and choose Customize Quick Access Toolbar…
- Left-click the right-most icon in your QAT—the one with the tiny down-pointing arrow. And then choose More Commands…
- Choose File, Options and then click the Quick Access Toolbar tab.
Any of these actions will display a large dialog that includes this area:
Area 1 is a dropdown list box that allows you to filter the commands you see in area 2. The second choice for the list of filters is intriguing: Commands Not in the Ribbon. When you get the time, you might want to examine that list for the fun of it.
However, the choice that I use most often is the third choice: All Commands. By having access to them all, I can just search alphabetically for the commands I want to add.
Once you choose a command in area 2, click Add in area 3, which adds them to area 4. Then you can move the selected command up or down by using the controls in area 6.
To remove a command from your QAT, just select it in area 4 and then click Remove in area 3.
Area 5 allows you to add the command to all documents in Excel or just to the active workbook. Honestly, I’ve never needed a workbook-specific QAT command, but you should know that the option is available.
The QAT’s Default Tools
The first four icons in my QAT…
…are the default tools for Excel 365.
The first command is self-explanatory. I always keep AutoSave off because I tend to make many blunders while I’m working in Excel, and I don’t want those blunders saved automatically. By not saving the file automatically, I always can close a file without saving it, then reopen the last version of my file.
The second command is File Save, which performs the same task as Ctrl + S.
The third and fourth commands are Undo and Redo. With one exception, they perform the same tasks as Ctrl + Z and Ctrl + Y, respectively. The exceptions are the tiny down-arrows to the right of each icon. When you click one of these arrows, you see a long list of actions you could undo or redo in one step by clicking on one of the actions in the list.
Now let’s look at the eight commands I’ve added.
Custom QAT Commands 1 and 2: New File and View Gridlines
I typically think of the QAT commands 1 and 2, shown here as two steps in the same process.
To launch a new workbook, I click on #1, which is the same command as Ctrl + N. That is, it launches a new workbook. And then I immediately click on #2, which removes its gridlines. After I click #2, the QAT looks like this:
I remove the gridlines because I care a lot about the appearance of my workbooks, and the gridlines are ugly. The only time I leave the gridlines in a workbook is when I’m testing something that I don’t intend to save. By leaving the gridlines in place, I’m telling myself that appearance doesn’t matter; be as sloppy as you want; you’re not going to save the workbook anyway.
To add these two commands, find these two tools in the All Commands list in your Quick Access Toolbar tab:
#1: New File
#2: View Gridlines
Custom QAT Command 3: Copy as a Picture
Between my online articles and my training programs, I create a lot of documentation. And that documentation requires many images, as you can see on this page. I capture about two-thirds of those images using #3, the Copy as a Picture command. (When I need to include row and column headers, I use Techsmith’s Snagit add-in.)
To use the Copy as a Picture command, I select the area of my worksheet that I want to capture, and then click the #3 icon.
Excel then pops up this dialog. I always accept the As shown on screen option, and usually choose the Bitmap option.
Years ago, the Picture option produced much better-looking results than Bitmap. And then in other generations of Excel, it produced much worse-looking results. And now, in the past six or eight years, it produces good results again. However, the Bitmap version does, as well.
If you plan to resize the image significantly, and you want the best possible result, try both Formats and choose the one that looks the best to you.
To add the tool, find the Copy as Picture… tool in the All Commands list in your Quick Access Toolbar tab.
Custom QAT Command 4: Advanced Document Properties
If you choose File, Info, you’ll see the Properties section at the right of your screen, as shown here:
If you then click the Advanced Properties option, you’ll see this dialog.
From my perspective, the most valuable part of this dialog is its Summary tab, shown here.
Primarily, in this tab, I rely on Category, Keywords, and Comments. Anything you enter in those fields is searchable. That means that you can enter key words for ideas, or categories, or VBA routines that we can’t normally search for.
I have no idea how many characters the Comments field can contain, but I’ve tested it with 10,000 characters, and everything was saved with the workbook and was searchable.
This is a very useful feature of Excel that’s hidden deep beneath the File tab. But you can get to this dialog easily if you add icon #4, the Advanced Document Properties icon, to your QAT.
To add the tool, find the Advanced Document Properties tool in the All Commands list in your Quick Access Toolbar tab.
Custom QAT Command 5: Select Objects
I often have several objects on a worksheet. Charts. Shapes. Images. Text boxes. Icons. Maps. SmartArt. Etc. And often, I want to align them, or group them, or delete them, or whatever.
Not all of them. Just some of them.
So to select them quickly and easily, I use the Select Objects tool. To do so, I click on the icon in my QAT and then click and drag with it, like this:
And when I release my mouse button, all of the objects that are completely within the bounding box are selected. And then, I can group them, or delete them, or whatever I have in mind.
To add the tool, find the Select Objects tool in the All Commands list in your Quick Access Toolbar tab.
Custom QAT Command 6: Paste Values
This is short and simple. After you copy a cell or a range and you want to paste its value(s)—without the source’s formatting at all—just click the Paste Values icon.
To add the tool, find the Paste Values tool in the All Commands list in your Quick Access Toolbar tab.
Custom QAT Command 7: Launch Power Query Editor
Excel offers several ways to launch Power Query. But all of them require several clicks. And when I’m thinking about a query that I’m trying to debug, I can’t seem to think of any of those methods.
But now, since I added this tool to my QAT, my Power Query editor is just a click away.
To add the tool, find the Launch Power Query Editor tool in the All Commands list in your Quick Access Toolbar tab.
Custom QAT Command 8: Clear All Filters
When I’m working with an Excel Table or a Pivot Table, I’ll often add several filters, and perhaps sort on several columns. And then, I’ll usually want to clear away all those settings and return to the normal table.
That used to be a hassle. But since I added this tool to my QAT, that’s easy to do. I just click on the Clear All Filters tool.
To add the tool, find the Clear All Filters tool in the All Commands list in your Quick Access Toolbar tab.
