Excel 2010 is a great spreadsheet program – and here are my ten tips
1. Hide the ribbon
I hate the ribbon. If you need to do anything in Excel that requires clicking through the ribbon, you’ll end up wasting loads of time clicking with your mouse (see #3 for why, and #2 for what to do instead).
2. Fill the Quick Access Toolbar with all your favourite commands
The quick access toolbar is the answer to the ribbon! All your favourite commands in one place – no matter which ribbon tab you’re on!
Follow this link for a detailed blog on how to set this up.
3. Learn the Alt Keystrokes for your Quick Access Toolbar
Replace lengthy mouse sweeps and clicks with speedy key commands! All your items on the quick access toolbar are available as key commands; just press and release the alt key and the numbers appear next to the command.
Also don’t forget the classics: control S = save, F12 = save as – and so on. If there is a key command available – then you should use it where possible.
4. Remove Duplicates
My favourite function. If you have a set of data where one or more rows repeats itself, and you want only unique rows, then this function quickly does this for you.
5. Clear all filters
Don’t you hate going through your filtered table, trying to find the columns which are filtered? There is now a function that will clear all filters for you instead!
6. Conditional formatting
Conditional formatting got good in 2010. A quick trick, try hovering over the various options with your mouse and your data previews the changes live.
SUMIFS is like SUMIF on acid. You can choose more than one criteria to sum on. Did you know that you can also use logical operators such as * and <> (“all” and “not equal to” respectively).
Mini graphs that fit inside a cell. This a really quick way to add instant graphical context to your data.
9. Intellisense (formula autocomplete)
When you start writing out a formula, have you noticed that auto complete options come up? If you press Alt then this option gets selected. Try it now – type “=VL” and then press the alt key. Voila! VLOOKUP formula!
10. Filter by colour
Certainly one of the most popular amongst my finance colleagues. You can filter data by cell colour AND text colour!
I hope you enjoyed my top ten tips for Excel 2010. Most of the above tips will help your speed and effectiveness in using this spreadsheet tool.