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.
From this there are some key advantages:
- You can keep all your favourite commands visible – no matter what ribbon page you’re on,
- You can access your favourite commands as keyboard shortcuts,
- 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.
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.
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.
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
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)