Excel has many outstanding hidden features. Here are a few that I use frequently…
- In Excel 2013 and above, when you close Excel’s last open workbook you also close Excel. But you easily can keep Excel open even when all workbooks are closed.
- You easily can nudge charts into position.
- You easily can assign Windows-searchable keywords, categories, and other properties to your workbooks.
- You can click and drag to select multiple charts and drawing objects on a worksheet.
- You can close all workbooks with one command.
- And you can do a whole lot more.
The key to finding and using these features is to experiment with Excel’s Quick Access Toolbar (QAT).
Excel’s Quick Access Toolbar (QAT)
By default, Excel installs your QAT at the top-left of your Excel window. Here’s what my QAT looks like, but your QAT will be different:
The QAT gives you quick access to Excel features that could be hidden behind many clicks in your Ribbon, or not appear in your Ribbon at all.
To change your QAT, right-click on any icon in the QAT and then, in the dropdown menu, choose Customize Quick Access Toolbar. This launches the Excel Options dialog with the Quick Access Toolbar tab active:
By default, the Choose commands from dropdown list box is set to Popular Commands. This list is worth exploring at some point. But to explore Excel’s hidden features, choose Commands Not in the Ribbon in the dropdown list box.
To add a command to your QAT, select it in the first list shown in the figure above, and then click the Add button.
To change the order of a command in the QAT, select it in the second list, then click the up- or down-arrow keys at the far right to shift its position.
And to remove a command from the QAT, select it in the second list and then click the Remove button, which is below the Add button.
Now let’s take a look at my QAT to give you an idea of how it can be used…
The Default QAT Commands
The first three icons are the QAT’s default commands. The first saves the active file. The second launches the Undo command (Ctrl+Z). And the third launches the Redo command (Ctrl+Y).
The Copy As Picture Tool
Because I copy a lot of Excel content as pictures, I added the Copy As Picture command to my QAT. Unfortunately, Excel doesn’t have an icon for this command, so it uses the circle that I’ve underlined in red. Fortunately, this is the only iconless command I use, so I don’t have a bunch of circles in my QAT.
You’ll find this command in the list of Commands Not in the Ribbon.
The Select Objects Tool
The Select Objects tool allows you to select multiple charts or other objects with your mouse so you can format them, move them, delete them, or whatever.
To use this tool, you first click on the underlined icon, which turns your mouse pointer into an arrow that looks like the icon. Then you use your mouse to select the objects you want…usually by clicking and dragging. To return your mouse pointer to its regular mode, press Esc.
You’ll find this command in the All Commands list in the dropdown list box.
The Paste and Keep Text Only Tool
This is a command that I add to every Office QAT that I can, because I always find myself copying and pasting text from the Web or some other place with heavily formatted text.
You’ll find this command in the list of Commands Not in the Ribbon.
The New (File) Tool
When you click on this icon, Excel launches a new workbook. You’ll find the New command listed in the Popular Commands list.
The Close and Close All Tools
The first icon closes the active workbook. The second icon closes all workbooks. It’s unfortunate that the icons are the same.
You might be wondering why I need a Close command on my QAT, because we have other easy ways to close our workbooks.
It started with the Close All command, which I added first. I needed it because I was working with a lot of open workbooks, and I got tired of closing them individually. But when I used this command for the first time, I noticed that Excel 2013 didn’t close after the last workbook was closed. Instead, Excel gave me an empty area below the Ribbon, kind of like we used to have in Excel 2010.
So I wondered whether the Close command for the QAT would do the same thing, and it does. That is, when you close the last open workbook using the Close command on the QAT, Excel stays open in Excel 2013 and above.
The Advanced Document Properties Tool
I tend to lose workbooks on my hard drive. I can sometimes spend ten minutes or more using File Explorer (formerly known as File Manager, and then Windows Explorer) to search my drive for workbooks that I KNOW are there…somewhere.
Those lost workbooks are my own fault, because Windows gives us the ability to assign various properties to our files, properties that we can search for quickly with File Explorer. But it’s always a hassle to assign those properties; so I seldom do so.
This is why I was delighted to discover the Advanced Document Properties command in the QAT’s list of Commands Not in the Ribbon.
So now, with one click, I can launch a dialog that lets me quickly add searchable information to my workbook.
Here, I’ve circled the properties that you can enter in the Properties dialog for your active workbook, and then search for in File Explorer.
For example, if you enter a title of My Title in this dialog, you can search for it in File Explorer. To do so…
1. Open the folder, library, or drive that you want to search.
2. Click in the search box and enter:
Title:”My Title”
(Microsoft’s Advanced Query Syntax page has many other search tips, by the way.)
To search for Subject, Manager, Category, and the other items circled above, use the same approach as I showed with Title.
Also, when you want to search for keywords, you can use either Keywords or Tags to specify the property that you want to search.
Nudging Charts and Other Objects with the Nudge Commands
If you select a drawing object in a worksheet, and then tap on one of the arrow keys on your keyboard, you can “nudge” the object up, down, left, or right, depending on which arrow key you tap. By tapping several times you can position the object precisely on your worksheet.
Unfortunately, the same technique doesn’t work with charts.
But the four Nudge commands that you can add to your QAT do work with charts! To my knowledge, the nudge icons that I’ve underlined above offer the only way to nudge charts. (These commands also work with drawing objects, of course.)
You’ll find these commands in the list of Commands Not in the Ribbon.
Adding Your Own QAT Commands
Because you do different things in Excel than I do, you’ll probably find some of these commands useful, and others a total waste of space on your QAT. And that’s okay.
But I’m sure you can find some other QAT commands that would be very useful for the work you do. So the next time you have a few minutes to play with Excel, search the list of Commands Not in the Ribbon for likely candidates for your own QAT. And then, when you have time, you might browse through the other two lists as well.
