What’s a QAT?

QAT is short for Quick Access Toolbar and this bar is typically setup to store frequently used Excel functionalities; making them readily available anytime you need them.  I personally am an avid user of Excel’s QAT and grew curious as to how other Excel power-users use the toolbar.  So I began a mission to ask some of the top Excel bloggers & consultants how they use the QAT and if they would mind sharing their setup.  Below are all the setups I could get my hands on with a brief description of each button’s functionality.  Hopefully these setups will help inspire the make-up of your own QAT or maybe generate some ideas for writing a few new macros you had never thought of before.  

At the bottom of this post I will give my analysis of everyone’s QAT setup. Check it out and see if you agree!

Jon Acampora, ExcelCampus.com

Jon is here to help you learn Excel and save time with your everyday tasks.  His blog contains in-depth tutorials and videos that cover a wide range of topics for beginner to advanced Excel users.  Jon has also developed a variety of Excel add-ins and utilities to help automate common processes and make you more productive.  Most of these are available to download for free!

You Should Check Out His Tab Hound Add-in which allows you to quickly navigate through your workbook with a Google-like search.  It also helps automate common processes like unhiding multiple sheets, flipping between two sheets, copying and renaming sheets, and much more.  You can also add a Table of Contents sheet to the front of any workbook with the click of a button.  Available for Windows & Mac.

Jon’s QAT Setup

1. Tab Hound Window – opens the Tab Hound window to search and jump to sheets (Add-in)
2. Flip Back – flip back and forth between two sheets (Add-in)
3. Copy File Path – copies the current file path to the clip board for pasting to attach to emails (Macro)
4. Undo- usually use Ctrl+Z, but nice to see the undo history in the drop-down (Excel Command)
5. Redo (Excel Command)
6. Save – usually use Ctrl+S, but nice for visual confirmation during screensharing/meetings (Excel Command)
7. Open Recent File – view list of recent files (Excel Command)
8. Add-Ins – opens the Add-ins Window to quickly enable/disable add-ins (Excel Command)
9. COM Add-Ins – opens the COM Add-ins Window to quickly enable/disable COM add-ins (Excel Command)
10. Calculation Options – quickly select Automatic or Manual calculation mode (Excel Command)
11. Shapes – Insert shapes menu (Excel Command)
12. Select Objects – makes it easier to select and work with shapes (Excel Command)
13. Display the Selection Pane – Great when working with shapes, charts, dashboards, etc… (Excel Command)
14. Clear Filter – shows when a filter is applied to a sheet or table. Usually use Alt+A+C shortcut (Excel Command)
15. Personal Macro Assistant – makes it easy to search and run macros in Personal Macro file. (Macro)
16. PowerPivot – Open the PowerPivot window (Excel Command)
17. Nice Table Style – Peltier Tech Chart Utility Add-in button to create a simple table style. (Add-in)
18. Trace Precedents  – JKP RefTreeAnalyser feature to display the trace dependents form. (Add-in)
19. Table of Contents  – Add a table of contents sheet to the front of the workbook. (Macro)
20. PivotPal – open the PivotPal window to quickly modify Pivot Tables. (Add-in)

Examples Of Jon’s Userforms

Sumit Bansal, TrumpExcel.com

Sumit is an Excel Enthusiast who started his Excel-focused blog in 2013. He has been awarded the Excel MVP (2014) award for his work on his website and contributions to various Excel communities & forums.  Based out of India, he is a data analyst during the day and an Excel blogger at night.

You Should Check Out His latest Excel Course that will help you skyrocket your productivity. Delivered through 26 High Definition videos and example files (downloading is available), this course will make you a ROCKSTAR at work. 

Sumit’s QAT Setup

1. Save (Excel Command)
2. Undo (Excel Command)
3. Redo (Excel Command)
4. Align Left (Excel Command)
5. Align Center (Excel Command)
6. Align Right (Excel Command)
7. Align Top (Excel Command)
8. Align Middle (Excel Command)
9. Align Bottom (Excel Command)
10. Distribute Horizontally (Excel Command)
11. Distribute Vertically (Excel Command)
12. Camera Tool (Excel Command)
13. Insert Table (Excel Command)
14. Freeze Panes (Excel Command)
15. Split View (Excel Command)
16. Data Validation (Excel Command)
17. Sort A to Z (Excel Command)
18. Sort Z to A (Excel Command)

Jordan Goldmeier, OptionExplicitVBA.com

Jordan is an internationally recognized spreadsheet and data visualization expert, author, producer, and Excel MVP. He is the owner of Cambia Factor, a data consulting agency and his Excel blog, Option Explicit VBA. Jordan challenges companies to rethink how they use their data by focusing on returning value rather than acquiring/implementing new technology. He has consulted and provided training for the North American Treaty Alliance, International Security Forces, the United States Air Force and Navy, the Pentagon, the Center for Business Analytics at the University of Cincinnati, and many others. 

You Should Check Out His brand new book, Advanced Excel Essentials.  Advanced Excel Essentials is a book for experienced spreadsheet users who want to take their skills to the next level!

Jordan’s QAT Setup

1. Save (Excel Command)
2. Undo (Excel Command)
3. Redo (Excel Command)
4. Selection Pane (Excel Command)
5. Merge & Center (Excel Command)
6. Camera Tool (Excel Command)

Note From Jordan: I put my QAT below the Ribbon, where it should be!

Bill Jelen, MrExcel.com

Bill Jelen is the author of 44 books about Microsoft Excel and the host of MrExcel.com. When he isn’t kayaking the intercoastal waterways in Florida, he is likely entertaining a room full of Excellers with his Power Excel seminars.

You Should Check Out His Excel Mall – a collection of Excel books, courses, and utilities from many of his Excel friends.  You can learn more by visiting the MrExcel Excel Mall page.

Bill’s QAT Setup

1. Save (Excel Command)
2. Undo (Excel Command)
3. Redo (Excel Command)
4. Recent File Pane – Difficult to add in Excel 2013 (Excel Command)
5. AutoFilter – Better than Filter as it will Filter by Selection similar to Access (Excel Command)
6. Insert Screen Clipping – Hides the Excel window, drag a box around item to be clipped (Excel Command)
7. Go To Special (Excel Command)
8. Page Setup (Excel Command)
9. Quick Print (Excel Command)
10. Calc Range (Fast Excel add-in from Charles Williams)
11. Automatic & Manual Calculation – Better than hidden Ribbon dropdown (Excel Command)
12. Launch Power Pivot Window (Excel Command)
13. Open Power Map (Excel Command)
14. Excel Analyzer – A spreadsheet auditing tool that color codes all formulas by their R1C1 formula. You can quickly spot formulas within a range where someone has altered one formula. (Beta Add-in)
15. Better Pivot Table – Macro to override bad pivot table defaults such as… (Macro)

Chris Newman, TheSpreadsheetGuru.com

Chris is a finance professional who started TheSpreadsheetGuru blog in early 2014.  His mission is to provide practical tips and straightforward techniques/tutorials for data analysts covering the main Microsoft Office programs (Excel, PowerPoint, Word).  Chris follows the philosophy of striving to become the resident guru among your colleagues and has tailored an email newsletter with great tips to achieve that status in your own company.

You Should Check Out His online course which teaches you how to build your very own Excel add-in user interfaces on the Ribbon. You can learn more about this by visiting My First Add-in promo page.

Chris’ QAT Setup

1. Paste Values Only (Excel Command)
2. Paste Formats Only (Excel Command)
3. Paste Formulas Only (Excel Command)
4. Save Workbook (Excel Command)
5. Email Excel File as an attachment (Excel Command)
6. Undo (Excel Command)
7. Redo (Excel Command)
8. Filter (Excel Command)
9. Clear Filter (Excel Command)
10. Allows you to do simple math to your selected cells.  Fast way to convert numbers into thousands or millions (Userform-Based)
11. Add vertical line series (really an error bar) to the ActiveChart (Macro)
12. Close multiple workbooks at the same time (Userform-Based)
13. Copy a worksheet multiple times based on user’s need (Userform-Based)
14. Compare two open workbooks and highlight any differences in the data between them(Userform-Based)
15. Change all formula references at once. Mimics F4 shortcut, but for all cell references in the  selected cells’ formulas (Macro)
16. Create date headers (Jan – Dec, Q1-Q4, YTD).  Defaults to use current year for dates (Macro)
17. Converts numbers stored as text to numerical values very quickly (Macro)
18. Toggle between option for double clicking a cell.  Allows editing directly inside the cell (Macro)
19. Creates formulas for summing Quarters  and FY.  This needs to be added after a full 12 months of data (Macro)
20. Wrap IFERROR() formula around all cells containing a formula. Based on user’s selection(Macro)

Examples Of Chris’s Userforms

John Michaloudis, MyExcelOnline.com

John has an accounting background and has worked in Australia, Ireland, and Spain as a financial controller & finance analyst.  He has over 15 years of Excel experience and has helped his fellow colleagues along the way and is now sharing his Excel knowledge through his Excel blog.

You Should Check Out His Excel courses. John has one of the most comprehensive Pivot Table courses on this planet which includes over 200 short tutorials covering every Pivot Table tool imaginable. You can learn more by visiting his Course Page. 

John’s QAT Setup

1. Save (Excel Command)
2. Undo (Excel Command)
3. Redo (Excel Command)
4. Camera Tool (Excel Command)
5. Add Comma (Excel Command)
6. Increase Decimal (Excel Command)
7. Reduce Decimal (Excel Command)
8. Sum (Excel Command)
9. Format Painter (Excel Command)
10. Clear the Filter (Excel Command)
11. Fill Color (Excel Command)
12. Sort A to Z (Excel Command)
13. Sort Z to A (Excel Command)
14. Email Workbook as Attachment (Excel Command)
15. Insert Pivot Table (Excel Command)
16. Insert Table (Excel Command)

Mynda Treacy, MyOnlineTrainingHub.com

Mynda is a Microsoft Excel MVP, Accountant, and co-founder of My Online Training Hub, a site where she shares her knowledge in her Excel Blog and teaches her highly acclaimed online Excel Dashboard course.

You Should Check Out Her Excel Dashboard course.  If you do any type of reporting in Excel this is the course for you. It’s packed full of tips you can use in your day-to-day Excel work and it will teach you how to make interactive Dashboards that are quick to update and will wow your boss and colleagues.  You can learn more by visiting the courses Overview Page.

Mynda’s QAT Setup

1. Save (Excel Command)
2. Undo (Excel Command)
3. Redo (Excel Command)
4. Set Print Area (Excel Command)
5. Print Preview (Excel Command)
6. Open File (Excel Command)
7. Delete Cell (Excel Command)
8. Delete Column (Excel Command)
9. Delete Row (Excel Command)
10. Insert Column (Excel Command)
11. Insert Row (Excel Command)
12. Insert Cell (Excel Command)
13. PivotTable and PivotChart Wizard (Excel Command)
14. Camera Tool (Excel Command)
15. Change Shape (Excel Command)
16. AutoFilter (Excel Command)

Bob Ulmas, Author & Excel Legend

Bob is the longest-running Microsoft MVP, winning the award for over 20 consecutive years.  He does everything from development to consulting work. In his spare time, he likes to write awesome Excel books and is always willing to help those who want to learn more about Excel

You Should Check Out His Excel-based books. One of his more recent books Excel Outside The Box was designed with the Excel guru in mind. This handbook introduces advanced and creative solutions and hacks for the software’s most challenging problems.

Bob’s QAT Setup

1. New workbook (Excel Command)
2. Open (Excel Command)
3. Save (Excel Command)
4. Undo (Excel Command)
5. Redo (Excel Command)
6. Quick Print (Excel Command)
7. Print Preview (Excel Command)
8. Format Painter (Excel Command)
9. Switch Windows (Excel Command)
10. Reset EVERYTHING – resets  to original keystrokes (overriding macro-created keystrokes),  Right-mouse clicks, cursor direction, OnKey, OnEntry, OnDoubleClick, many many more (Macro)
11. Shows location of current workbook (QAT-“Document Location”)
12. Close All workbooks with prompt (Builtin QAT)
13. Camera Tool (Excel Command)
14. Toggle Read-Only (Excel Command)

Jan Karel Pieterse, JKP-ADS.com

I have been supporting Microsoft Excel users from over the world for many years, helping them with their day-to-day tasks using Microsoft Excel.  These efforts have been rewarded by Microsoft by granting me the MVP Award many times (each year starting from 2002).  Worldwide, there are only about 100 Excel MVPs, so with JKP Application Development Services, you are sure to get top-level expertise. Get a real Excel expert to help you!  As a process engineer with about 20 years of corporate experience, I have experience in business processes as well as knowledge of the systems used for those processes.

You Should Check Out His RefTreeAnalyser Excel add-in which is a formula auditing tool on steroids!  This utility allows you to trace errors, find circular references, determine worksheet calculation speed bottlenecks, and much more!

JKP’s QAT Setup

1. Open File (Excel Command)
2. Save (Excel Command)
3. Save-As (Excel Command)
4. Undo (Excel Command)
5. Redo (Excel Command)
6. Print preview (Excel Command)
7. Document Properties (Excel Command)
8. Pivot Table wizard (Excel Command)
9. Camera (Excel Command)
10. Recent files (Excel Command)
11. Show/hide formulas (Excel Command)
12. Export to PDF (Excel Command)
13. Select previous comment in sheet (no idea why that is there! ) (Excel Command)

Examples of JKP’s Userforms

Oz du Soleil, DataScopic.net

Oz is a recent transplant from Chicago to Portland and is co-host of ExcelTV. He co-authored the forthcoming book Guerrilla Data Analysis version 2 with Mr. Excel, Bill Jelen. Oz has been working with Excel for more than 10 years and is currently a consultant, trainer, speaker, and blogger. His passion is in the area of Data Literacy because “not all of us need Excel’s advanced features. But as more roles are de facto analyst positions, more of us do need to think more carefully about how we handle our data.”

You Should Check Out His Excel blog where he writes about his experience, Excel features, and what others are posting about in the Excel blogosphere.

Oz’s QAT Setup

1. Save (Excel Command)
2. Undo (Excel Command)
3. Re-do (Excel Command)
4. Print Preview (Excel Command)
5. Format painter (Excel Command)
6. Inking – Lets you free-hand draw on your Excel spreadsheet with various pen and highlighting tools (Excel Command)
7. Camera tool (Excel Command)
8. Decrease decimals (Excel Command)
9. Increase decimals (Excel Command)
10. Quick print (Excel Command)
11. Paste values (Excel Command)

What I Gleaned

First I want to thank everyone who took the time to share their QAT setups.  I know you all are extremely busy and I was amazed that so many members of the Excel blogging community were willing to participate.  After spending quite a few hours putting this post together I did start to notice some very interesting things about the various QAT setups.

I Don’t Use That Thing!

To my surprise, there were a handful of Excel bloggers I reached out to who don’t use the Quick Access Toolbar at all!  This includes the likes of Petros Chatzipantazis (Spreadsheet1.com), Andy Pope (AndyPope.info), Dick Kusleika (DailyDoseOfExcel.com), and Oscar Cronquist (GetDigitalHelp.com).  Jon Peltier (PeltierTech.com) even went as far as to state that he “hate, hate, hates the QAT (it ain’t worth squat!).”  I found this extremely intriguing and I hope these guys will share their philosophy on not making use of the QAT in the comments section below.

QAT Key Shortcut Combinations

My favorite thing about the QAT is that you can easily execute its commands via the keyboard by using the Alt key in combination with a number.  Because an Alt+5 or Alt+6 can be a tricky key combination to do with your left hand, typically the first three to four spots in the QAT are prime real estate, reserved only for the most highly used actions.  As I look at everyone’s setups I noticed that the first 3 spots in the QATs were mostly filled with Save, Undo, & Redo.  Theoretically, this could make sense as I’m sure everyone uses these commands quite frequently, however, there are already dedicated keyboard shortcuts for these commands.  I’m assuming that since Excel defaults these commands to the first 3 QAT positions, most people are just accustomed to those buttons being there.  I personally reserve my first three QAT positions for PasteSpecial commands, as I use those most often throughout the day and prefer the Alt+# shortcuts over Ctrl+Alt+V shortcuts.

Where Are The Macros?

One of the biggest surprises for me was that there were not too many people running macros out of their QAT.  I was especially surprised that some people who have dedicated blogs for VBA (cough, cough…Jordan Goldmeier….yeah I’m calling you out!) didn’t have one trace of VBA code hanging out in the QAT.  I did get feedback from some stating that most of their macro code used on a regular basis was executed via assigned keyboard shortcuts and that does make sense.  About 5 months ago I started to shy away from using shortcuts with my macros.  Here was my reasoning:

1. Macros can’t be undone! It is too easy to accidentally key in a shortcut or use a shortcut without thinking about the consequences.  Most macro automations can’t be easily undone and taking the extra second to execute from your mouse gives you time to think about your intentions.
2. Other people can use your keyboard shortcut combinations as well!  I’ve come across numerous situations where I was working in someone else’s workbook and couldn’t execute the macro I intended to because there were two available macros with the same assigned keyboard shortcut (this can occur when your personal macro and the workbook’s built-in macro share the same keyboard shortcut).  I can’t tell you how many hairs I pulled out of my head trying to figure out what was going on!

For those reasons, I vowed not to rely on macro keyboard shortcuts, with the hope of preventing similar situations like those mentioned above from happening in the future.

QAT Location

I also thought it was interesting that virtually everyone placed their QAT above the ribbon.  The one exception was Jordan Goldmeier who claimed the Quick Access Toolbar belongs below the ribbon.  I personally have tried moving the QAT below the ribbon on numerous occasions but always ended up moving it back to the top.  I couldn’t get rid of my muscle memory to go to the top of the screen to access my QAT and it just didn’t look “right” visually.  I’d be interested to hear if you have your QAT above or below the ribbon and why?

What Did You Learn Or Glean From This Post?

I learned so much from seeing how everyone uses the Quick Access Toolbar and I have already made tweaks to my own setup throughout the process of writing this post. I want to hear from you and discover if this post caused you to rethink your own setup, or even convince you to start using your QAT more often!  I look forward to reading your thoughts!

Hidden Hacks For VBA Macro Coding

After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!

Learn These Hacks Now For Free!

Keep Learning

Explore more →

Add Line Breaks In Screentips & SuperTips (Ribbon XML)

Parts Of A Ribbon Button’s Tool Tip Programatically, there are two parts to a button’s tool tip box that appears...

Top Microsoft Excel Add-ins You Should Consider Using

What Are Add-ins? Add-ins for Excel are similar to the apps you can download and purchase for your smartphone. They...

How To Prevent The Excel Error Message For A Missing Add-in

I’ve run into this issue numerous times and I just figured out how to prevent the dreaded error message from...

Chris Newman

Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.