Being more productive with the Excel Quick Access Toolbar

I have been meaning to share more tips and tricks for working with Microsoft Excel on my blog, since this constitutes a large portion of my daily activity, but there always seems to be something more pressing to write about. One of my favorite productivity features in more recent versions of Excel – if you can still call the Office Ribbon, introduced in Excel 2007, ‘recent’ – is the Quick Access Toolbar. Usually merged with the Title bar, the Quick Access Toolbar comes prepopulated with a small set of tools like Save, Undo and Redo, which you can customize to your needs.

Icons you should have in your Excel Quick Access Toolbar

I saw a YouTube video a couple of days ago, which also prompted the current article, that made a compelling case for the feature and laid out a set of practical rules to select which commands to include in the QAT. It all comes down to how often do you use the feature, and how easy it is to access via Excel’s interface or keyboard shortcuts. Some commands are so hidden they have their own section in the Quick Access Toolbar customization called ‘Commands Not in the Ribbon’. In my case, here are the features I regularly add to the Quick Access Toolbar:

• Email – basically the easiest way to share a spreadsheet as an email attachment.
• Paste Values – working with data, this is a must-have tool to avoid pasting formulas and formatting from the source, which may break in the destination file or require extensive cleanup afterwards. As an alternative, I sometimes use ‘Paste Values & Number Formatting’.
• Clear Formats – I use it extensively with spreadsheets exported from SAP, which are formatted with extra backgrounds and borders. Having this button on the QAT saves me a couple of clicks each time I need to remove unwanted formatting.
• Filter & Clear – although they are relatively easy to access, I have grown accustomed to using filters from the toolbar, because they are always visible, as opposed to having to switch to the ‘Data’ or ‘Home’ tab.
• Freeze Panes – namely the version of this feature available under ‘Commands Not in the Ribbon’ that works with a single click, as opposed to three if you have to navigate the ribbon.
• If you are working with macros in Excel, the Quick Access Toolbar is the ideal place to add buttons for commonly used macros – just keep in mind that the code will only run if the file hosting the macro is opened, or the macros were saved in your Personal Macro Workbook.
• As an honorary mention, Screen Clipping, which works much as the Windows 10 screen clipping to capture portions of the screen to insert in Excel, even from other applications. This can be useful if you are working in an older version of Windows without the modern screen clipping, or in a remote environment like Citrix, which interferes with normal screenshots.

As a bonus tip, the buttons on the QAT can be activated with their own set of keyboard shortcuts, something I wasn’t aware of before watching this video. The first nine are the easiest to access, by pressing Alt + the number corresponding to their position on the toolbar. From my experience, the Autosave button in Office 365 is counted as part of the QAT, so it responds to the shortcut Alt + 1, and the rest follow. This made me rethink the order of the buttons, to make it easier to trigger the commands I use more often.

Another feature discovered via a different video is the ‘Camera’ function, which allows you to create a live view of another part of the worksheet – amazing that something like this exists and I haven’t heard of it until now!