The Quick Access Toolbar

Excel 2010 shipped with loads of great new features, however the one I use most is actually from their 2007 update: The Quick Access Toolbar.

In a nutshell this is a horizontal tab that runs above or below the ribbon (I prefer below) and allows the user to add common commands to the strip.

Oli's Quick Access Toolbar Setup

This is my setup for the Quick Access Toolbar. You can see that I’ve chosen to located the bar beneath the ribbon. This works for me – but you may prefer to have yours above the ribbon.

From this there are some key advantages:

  1. You can keep all your favourite commands visible – no matter what ribbon page you’re on,
  2. You can access your favourite commands as keyboard shortcuts,
  3. You can add macros to this list

This is a fantastic way to tailor Excel to your way of working. At the bottom of this post I have listed out my Quick Access Toolbar setup.

Beat the ribbon

Lots of people dislike the way Excel organises the commands using the ribbon. This means you need to click on each ribbon menu when you want to access the commands on that strip.

By ensuring all your commands are on the Quick Access Toolbar you are in control of what commands you see, and they’re present no matter what ribbon strip you’re on.

Keyboard Shortcuts

Anybody that has worked with me knows how much I love keyboard shortcuts. They allow you to minimise your mouse clicks and maximise your productivity.

The Quick Access Toolbar allocates a keyboard shortcut to each command on it. Just press and release the alt key and you’ll see a prompt which tells you what the keyboard shortcut is.

Oli's Quick Access Toolbar Keyboard Shortcuts

This is how my Quick Access Toolbar looks after I have pressed and released the Alt button. If I then press (for example) 1, I will get the command ‘Paste Values’. The macros show up as a small box surrounded by three boxes.

So this opens all excel commands to be set up as keyboard shortcuts! Neat huh?

Oli note: I wouldn’t use the Quick Access Toolbar to hold commands that already have a keyboard shortcut (e.g. Control-O is “Open”). I think that is a massive waste of space on the Quick Access Toolbar. The default commands on the Quick Access are things like ‘save’, ‘print’ and ‘undo’ – all of which are classic keyboard shortcuts (control – S, P and Z respectively). I’d lose these if I were you!

Macros and the Quick Access Toolbar

Excel unfortunately does not offer all the commands that I require as keyboard shortcuts (e.g. removing gridlines from the sheet) – so the Quick Access Toolbar can offer a solution. By adding macros to the Quick Access Toolbar you are able to fully flex Excel to meet your needs.

In order to achieve this, I have a ‘startup’ file, which opens whenever I start excel. Within this are some common macros that are accessible by all open workbooks, and are available to be stored on the Quick Access Toolbar.

Summary

For me, the Quick Access Toolbar has been a game changer. I hope you found this useful, and give the Quick Access Toolbar a try.

I’m a big fan of the ‘For Dummies’ series of books, and Excel 2010 for Dummies (available from Amazon) is no exception.

There are some other good links that are worth a look. The Microsoft Office site is always a good place for hints and tips. For an alternative view, check out Windows Secrets, which comes from a Microsoft Word perspective.

Oli’s Quick Access Toolbar Setup

Alt 1 – Paste Values
Alt 2 – Paste Formulae
Alt 3 – Macro – Format as Currency
Alt 4 – Wrap Text
Alt 5 – Fill Colour
Alt 6 – Font Colour
Alt 7 – Set Print Area
Alt 8 – Format Painter
Alt 9 – Increase Decimal
Alt 09 – Decrease Decimal
Alt 08 – Crop
Alt 07 – Autoshapes
Alt 06 – Remove Duplicates
Alt 05 – Data Validation
Alt 04 – Freeze Panes
Alt 03 – Clear Filters
Alt 02 – MacroColour Cell
Blue
Alt 01 to Alt 0H- Macros to colour cells specific colours that fit my corporate reporting needs
Alt 0I – Macro Hide Gridlines
Alt 0J – Macro Turn Off Auto Calculate
Alt 0K – Macro Turn On Auto Calculate
Alt 0L – Borders (external and internal)

Tagged on:

2 thoughts on “The Quick Access Toolbar

  1. Pingback: My Top Ten Tips for Excel 2010 | olivergearing.com

  2. cellulit na ramionach

    Hi! This is kind of off topic but I need some guidance from an established blog.
    Is it tough to set up your own blog? I’m not very techincal but
    I can figure things out pretty quick. I’m thinking about making my own but I’m
    not sure where to begin. Do you have any ideas or suggestions?

    With thanks

Leave a Reply

Your email address will not be published. Required fields are marked *