What are some great Excel power user hacks and tips?

Posted by:

These should make you both faster and more accurate in Excel. Doing things quickly is great, but learning how to avoid bugging your model – and to fix it when it happens – is equally important.

(Apologies for low-res screenshots here – I’m using some fairly shoddy technology! – but the images should still illustrate the points.)

1. Master shortcuts – download and install Keyrocket.
This application is free, and will inform you of what shortcuts you could be using as it observes you using the more cumbersome mouse actions. It’s the quickest way to learn keyboard shortcuts, and keyboard shortcuts are the most reliable way to save you time on Excel.

Link here: http://www.veodin.com/keyrocket/

2. Activate the Analysis Toolpak
This is a tool built into Excel, but not automatically activated.

If you want to do some statistical analysis – anything from a correlation coefficient to a regression to just displaying some simple descriptive stats on a data set – this add-on will help you do it fast.

Instructions on how to install it are on the office website: http://office.microsoft.com/en-u…

3. (Almost) Never, ever hard code!
Hardcoding data seems like it’ll make things easier, but can make your life very painful indeed.

Consider a fictional example here. You’ve created a formula to select a tax rate based on whether an entry refers to China or the UK.

Three problems can arise from this hard code:

i) If you discover the data changes – e.g. you’ve used incorrect tax figures – you’ll need to manually replace the above numbers which can be a painstaking (and error prone) process

ii) If someone decides to alter a text input – e.g. wants to use “PRC” instead of “China” – the formula will break instantly

iii) if someone asks you to pull up a fixed data point fast (“what was the effective tax rate we’re using for China again?”), you won’t necessarily be able to give them a quick answer. Yes, you can dig it up from the formula, but imagine having tens of formulas across the model doing different things – all hard coded.

Better to have clear, separated inputs. In this case a lookup table would be much more effective.

There are rare occasions you can hard code – e.g. very small back-of-the-envelope-type models where it has no effect – but generally it’s bad practice.

4. Break things out

At some level it’s elegant and impressive to have a spreadsheet with few cells and large, complex formulas spanning multiple lines, e.g.:

This is great, until you need to explain to someone else how your formula works. Or worse, you come back two weeks later and you can no longer understand how all the sub-arguments fit together.

Better to split things out. Create several columns, each doing one step of the calculation and leading to a final result. This is more logical to follow, easier to explain, far easier to tweak and modify.

By all means make your output tabs pretty and minimalist, but when it comes to your calculations – break things out.

5. Use F9 to test formulas.

Used correct, this is huge, and can save you hours in testing to resolve bugs and errors. It’s best illustrated through an example.

Let’s say you have a set of items, where one is giving you an error.

Typically you’d have to play around with the inputs to identify where the problem lies, figuring it out by process of elimination. F9 saves you the need to do that.

i) First you click onto the formula bar

ii) Next you select particular arguments in the formula (basically anything with a comma after it)

iii) Hit F9, and you’ll see not the argument but the result of the argument – i.e. the number or cell flowing through to it. If there’s an error in that particular selection, it will also show through (as below). Then you immediately know the source of the problem.

iv) Finally, you can hit ‘esc’ to undo and then test the specific sub-elements of the malfunctioning formula to find out precisely where the problem lies. Even with, say, a match function within an offset function within an if function, this will enable you to very quickly pinpoint the issue.

0

Add a Comment