Put Excel 2016’s Quick Access Toolbar in Your Excel Arsenal

Excel is everyone’s favorite productivity tool and for good reason. It is so efficient at managing and analyzing numbers that it is the de facto standard for desktop data analysis. However, most people don’t take advantage of one of Excel’s most potent efficiency weapons: the Quick Access Toolbar (QAT for short). 

Yes, that’s the QAT just above. For most people, the QAT sits forever in default mode, showing the nearly useless “save”, “undo” and “redo” icons. I think most folks have learned to use “cntrl-Z” (undo) and “cntrl-Y” (redo) pretty reflexively by now, so this placement is superfluous. And Excel a) autosaves, and b) uses the “ctrl-s” keyboard shortcut, so showing the now ancient ‘floppy disc’ icon probably mystefies more people than it helps.

Happily, it’s easy to do better. Much better. In fact, I would argue that having a well thought out QAT will save you as much time as many of the most useful keyboard shortcuts available – and that’s saying something! Let’s use my QAT setup as an example, and along the way I’ll show you how easy it it to set up  your own favorites.

Above is a sample useful QAT setup. To be sure, YMMV (Your Mileage May Vary) with this, so by all means set up your own preferences, but let’s do a quick walkthrough of these, from left to right:

• Email as attachment. Nothing is handier than zapping a copy of what you’re working on immediately to a colleague. Clicking this will attach your file to a new email, leaving you to address and send it – all in seconds and without having to click into other programs and find your way to the file. I use this a lot, thus it’s position at the far left.
• Print Preview. This takes you to the print preview, where you can see how the spreadsheet will print out, and it also allows quick printer selection.
• Set Print Area. Speaking of printing, this one lets you set the print area with a simple “highlight and click”. 
• Trace Precedents, Trace Dependents, and Remove all Arrows. This one is important for any spreadsheet that has a significant number or complexity of  formulas. Tracing precedents lets you see what that cell’s data is coming from, while tracing dependents shows you where it is going – all via these handy, clickable arrows that Excel uses. To get rid of the arrows you simply hit “Remove All Arrows”.
• The next four have to do with grouping and hiding/exposing rows and columns. Again, very handy with large, complex spreadsheets.
• The next two are Normal View and PageBreak View, to let you see or hide the printable area of your spreadsheet (I know, printing far less these days, but still sometimes…)
• Freeze Panes is one of the most useful view options out there, and if you don’t use it frequently, you should. It very helpfully freezes, for example, the top row of headers on your spreadsheet, and the leftmost column or two with columnar headings and details. This one is so useful I should probably move it to the left!
• Select Visible Cells is great for when you are filtering or manually hiding rows, and doing copy/paste with only the data that is showing. Thus, it does not pick up all of the hidden row/column data and needlessly paste it at the destination.
• Finally, “Properties”. This tells you some handy facts about your workbook, the most useful of which is size in kb or mb. 

Okay, that was quick and fun. Can you use your mouse and click through file menus and ribbons to find this stuff? Yes you can. But why do that when your most used Excel tools can be front and center? I find that combining these QAT tools with the Home ribbon gives me a very large percentage of what I need when on Excel, all on one page/view. Combine that with the common keyboard shortcuts and you’ve got minimal mouse movement and maximal productivity.

Of course this wouldn’t be complete without showing you how to add these things, so…

…you simply click on that little down arrow highlighted above, which is on the right-hand-end of the QAT, and it will bring you to some quick adds for the toolbar, and to the all powerful “More Commands” you see highlighted here. From More Commands you can surf for QAT tools by sub-menu or alphabetically. There are a lot of these items, so you may need to spend some time perusing. But it’s worth it.

Another easy way to add things to your QAT is to right mouse click on almost anything in a ribbon, and choose, “Add to Quick Access Toolbar”:

Okay, that’s it. Give it a try and I bet you’ll find yourself adding your most used Excel tools and discovering a very real and measurable productivity bump. Enjoy!