Long Formulae

My number one rule for spreadsheets is a simple one:

A long formula is a bad formula.

Long formulae are the bane of spreadsheets. On more than 1 occasion I have been tempted to write to Microsoft and ask them to impose a 100 character limit on formula length (or at least provide such an option). But what is the problem?

Let's look at this the other way round for a moment. How many short formulae do you see that are difficult to understand after a little thought? I'll bet not many. Maybe there's a new function you need to look up in Excel help but that's about all. A long formula can suggest many things:

• There are lots of parts to a single calculation.
• A simple calculation is being performed in an overly complicated way.
• The author is basically showing off (frankly the worst crime of all!)

For sake of illustration I'm going to calculate a person's Body Mass Index (BMI).  BMI is defined as:

BMI = (Weight in kilograms) / (Height in metres) ^ 2

My spreadsheet is reasonably flexible. It allows people to choose the units they use for entering height and weight. Height can be entered in metres or feet. Weight can be entered in kilograms, stones or pounds. (I'm assuming that users are capable of entering fractions, e.g. 10 stones 6 pounds would be entered using =10+6/14.)

B1 = height value
C1 = height units ("metres" or "feet")


B2 = weight value
C2 = weight units ("kilograms", "stones" or "pounds")

To calculate BMI in one go you could use this formula:

=B2*IF(C2="kilograms",1,IF(C2="stones",1/0.1575,1/2.205))/((B1*IF(C1="feet",1/3.281,1))^2)

This isn't too bad but it could definitely be easier to follow! Think about how you would check it. You would probably have to break it down as follows and check each part somehow (e.g. using 'Evaluate Formula' and stepping through a couple of example calcs):

1. Convert height into metres
2. Convert weight into kilograms
3. Calculate BMI

Surely it would be clearer if these steps were shown separately?

Let's say we put the height in metres in cell B4. To convert to metres we just pick out this part:
=B1*IF(C1="feet",1/3.281,1)

Now let's put weight in kilograms in cell B5. Now we just pick out this part:
=B2*IF(C2="kilograms",1,IF(C2="stones",1/0.1575,1/2.205))

Finally the BMI formula goes from that first formula above to something that resembles the definition much more closely:
=B5/B4^2

Notice how much easier this is to check too.  You can easily check that each conversion is working correctly. Then checking the BMI formula itself is simplicity itself!

IFERROR - a new(ish) way to handle errors

Oftentimes I see formulae that attempt to deal with errors, such as division by zero or failed VLOOKUPs.  Something like this for example:

=IF(ISERROR([something]),[something else],[something])

I never liked this because Excel actually has to do the [something] bit twice. Worse yet you may get one right and one wrong, e.g. due to updating one but not the other. Excel 2007 introduced a new function that addresses these points, called IFERROR. The following formula does exactly the same as the above but more robustly:

=IFERROR([something],[something else])

If [something] returns a valid answer then IFERROR returns it! If it's an error then IFERROR will return the result of the [something else] calculation instead. Notice how much shorter the formula can be, especially if [something] is quite long. Plus you only have to type each part once so there's less risk of accidents.

As a final point do beware of sending the spreadsheet to anyone using an earlier version of Excel. If you do then this function won't be recognised and you'll just get errors! In such cases you may need to rewrite these formulae using the original format shown above. Alternatively you can easily recreate the IFERROR function in VBA instead - just paste the following into a module:

Public Function IfError(Value, OtherValue)
'Do not use this if saving in Excel 2007 format or later!
    If IsError(Value) Then IfError = OtherValue Else IfError = Value
End Function

Use Print Preview!

Easy one, this! To put it succinctly, get into the habit of looking at Print Preview.

You've written a spreadsheet and next job is to pass it on to a colleague to have a look. Before you do anything else, have a quick look at Print Preview.  A lot of people will automatically print everything out that you send to them - documents, pictures, spreadsheets, you name it.  Now I'm not going to be an eco-warrior here - the rights and wrongs of this are not the point.  The point is that in the real world and that's what (some) people do.

Now I don't know about you but I'm certain that my boss wouldn't be very happy if I handed him a spreadsheet and he ended up sending 100 pages to the printer, 90 of which were junk.  It really is that simple.  A quick look a Print Preview can save you a lot of (easily avoided) headaches!

On top of that Excel provides something called "Page Break Preview", which appears on the "View" tab in Excel 2007.  This gives you a visual guide to where all the page breaks will appear when you print it.  The dashed lines are where Excel has put a page break for you, the solid ones are added by hand.  You can drag these around as needed.  When you've finished switch back to "Normal" view and you're done.  A small investment of time goes a long way!

Overwritten Cells

Very often where I work I come across spreadsheets of a certain type, characterised as follows:

• 1 row containing headings (of varying levels of helpfulness) ...
• followed by 1 row containing formulae ...
• all copied down for several hundred rows or more.

This is the kind of thing that spreadsheets are really good at. Write your calculations for 1 record and copy them as many times as you need. Brilliant.

That's all fine as long as people don't mess it up! All too often I see situations where the formula in cell AZ653 doesn't quite work due to a quirk in that record. Rather than think about the wider implications the user simply types the right value in instead and carries on regardless. There are numerous problems with this but I'll concentrate on the poor person whose job it is to check this spreadsheet. It is very possible that this change will be missed, or perhaps just become overwritten, e.g. if someone copies all the formulae right the way down again. We need an easy way to spot it.

Enter column differences. First select the range containing all the formulae but leave out the header row(s). In Excel 2007 the command is found here:

• Home tab
• Find & Select
• Go To Special
• Select the "Column differences" option
• Press OK.

(A slightly quicker route is to press the F5 key followed by the "Special" button.) Now Excel will only select those cells where the formulae don't match with those in the first row. You can cycle through these cells by pressing the TAB key. You can even colour the cells in using the normal fill control.

This works equally well when you have lots of formulae in a column copied across to the right. Only this time - you guessed it - you would select "Row differences" instead.

These range differences are powerful tools that can be a real life saver. If you ever come across spreadsheets like the ones I've described then I heartily recommend getting used to using these tools.

GETPIVOTDATA

GETPIVOTDATA is a fabulous little function! Try it out, get used to it, learn its quirks and then use it. Frankly it's about as exciting as worksheet functions get. Check it out for yourself!

You've built a good spreadsheet, maybe drawn a few graphs and then added a handy pivot table summarising all the stuff you need to know. Then you realise you want to pull some numbers out of that pivot table. So you select an empty cell, press the equals sign and then click on the cell in the pivot table that you want to use. What happens? You end up with a formula like this ..

=GETPIVOTDATA("Sales",$A$3,"Month","Jul")

It's strange but people seem scared of this. Even without seeing the spreadsheet you can probably guess that it's looking up sales for the month of July! However people seem to prefer something like this instead:

=B5

..where B5 is the current cell in the pivot table containing sales for July. So why is the long-winded version so much better? Well a few reasons really:

1. Pivot tables are very user-friendly. It's very easy for people to play with them. That means users will reorganise columns and rows, add things and take things away. In fact you can almost guarantee that whatever it was you were originally interested in is going to move. With GETPIVOTDATA that doesn't matter.
2. Pivot tables can be filtered. If I used filters to remove "Jul" from my pivot table in the above example then I would want to be warned that this data wasn't available any more. GETPIVOTDATA does that too with a #REF! error. If your formula is =B5 then whatever is in B5 will be used, whether it's right or not.
3. Then when you amend the filter to put "Jul" back in, how do you know your answer is going to appear in B5 again? What if the user has added some more data? Guess what - GETPIVOTDATA deals with it.
4. You can add multiple conditions in GETPIVOTDATA. I often see people creating all sorts of unique identifiers by tacking things like surname, initials, date of birth, etc together into one "word". Why bother when GETPIVOTDATA makes it so easy to express in a logical way?

Get to know GETPIVOTDATA. It can be a very good friend.

VBA Tip - Screen Updating

Say you've written some VBA code that does various things with a spreadsheet. It does exactly what you want so you're essentially happy! However it may seem a bit slow or it just jumps around a lot on screen while it's running. In short it does the job but it doesn't look very professional.

Let's face it - you really don't want to change your code. It works! You just wish you could hide what was going on so that the user doesn't get distracted (or worse confused) by things jumping around on screen. If the user could switch the screen off while the code ran then that would be great! That's not going to happen but you can do the next best thing. Insert this line at the top of your lovely bit of code:

Application.ScreenUpdating = False

When Excel does something with a spreadsheet it redraws what's shown on screen. This simple line of code prevents that happening. The rest of your code is still doing all the same things as before but Excel doesn't draw any of it. This gives a really nice clean feel to a lot of processes.

At the end of your lovely code just remember to switch it back on again! Excel should do this for you but it's a good habit to get into just in case something doesn't quite go according to plan. (You can really confuse users if you forget!) The command is:

Application.ScreenUpdating = True

There is one added bonus, which you may have already guessed. Because Excel has got less work to do (no redrawing what's on screen) you may actually find that the same bit of code runs faster. Which is always nice!

VBA Tip - DoEvents

Say you're running a macro that's hogging your PC and you're unable to do anything else while it's running. You can't even break into the code to check it's running OK. Throw in "DoEvents" every now and then and the code will briefly pause while Excel checks what else is going on, e.g. you pressing the Escape key! These pauses are barely noticeable in real time but can spare the user much aggravation.

E.g. change this:
For i = 1 To 1000
...do stuff
Next

to this:
For i = 1 To 1000
...do stuff
If i Mod 10 = 0 Then DoEvents
Next

In this example the code will briefly pause on every 10th iteration, while Excel checks for things like key presses. The "Mod" condition allows you to control how often it checks. If each iteration is slow then you may want to check more frequently, in which case you would replace 10 with a smaller number and vice versa.