A lifeboat for Excel's stormy seas....

"Its not the boat, its the skipper". This harsh advice was often directed at me by my father when I came off the water. I'd be blaming my poor performance on the sail, mast, rudder, wind, anything but me. And if I'm honest, it was all me. Sailing was truly not my forte. I was born to be a landlubber.

I read a blog about Excel today that reminded me of those happy times. It was posing the very valid question of why Excel always gets the blame when reported results turn out to be incorrect. I read this and thought of my dear father. I would willingly bet my house that almost all of the errors that are blamed on Excel, are due to the Excel user.

It is way too easy to make an error in Excel, due to being rushed (who hasn't been under deadline pressure?), not sense checking the results, or - the scariest - not fully understanding what Excel does, and therefore not knowing that the formula you think is perfect, is actually picking up the wrong data.

In an attempt to throw out a lifebelt to all those in difficulty, I have listed below a couple of the most common (and easy) errors to make, and how to prevent them. (And yes, I know these well, because I have fallen into their evil little traps before...)

1) Circular references

You may be thinking, hang on....Excel tells you when you have a circular reference. You're right, it does.When you create a formula that contains a circular reference, an error message pops up that helpfully lets you press ok and allow the circular reference. You then save and close the spreadsheet. When you next open it, it doesn't bother telling you again that you have a circular reference.

Therefore, you open a spreadsheet that maybe someone has sent you, you assume all is ok, and you happily use the results given. You would be wrong. And you may just end up blaming Excel. Before you do, check the bottom left hand corner of your spreadsheet. If there is a circular reference, it will tell you exactly what cell contains the wrong formula. If the error is in a different sheet within your workbook, it simply states 'Circular References'. Go through each sheet until you find the pesky blighter. Ignore it at your peril.

2) Is Excel set to Manual Calculation?

Again, you may be thinking 'Doh'. But really, it happens. You're working with huge sets of data, way too many formulas hanging off them to be practical, and everytime you press enter, your spreadsheet churns through all those figures and formulas to recalculate. So ten minutes and a cup of coffee later, you decide to make your life easier and you switch on 'Manual Calculation'.

(In case you're wondering how, go to Formulas - Calculation Options, and the box below will pop up...Make your choice wisely....)

You enter all your data with ease, you print your dashboard that picks up totals from each of your spreadsheets, and wa'hey....your results are all wrong. Why? Because Manual Calculation is still on.

To prevent this catastrophe while using Manual Calculation, do both of these:

• Press F9 on a regular basis. This recalculates all your formulas immediately.
• Check that handy little bottom left hand corner on your spreadsheet before you print or send anything. If it says, "Calculate", heed the warning and press F9.

And remember to switch back to Automatic when you're done.

3) Are your sums right?

Not such a stupid question. Here are 3 scenarios where your autosums might not be adding up:

a) You have a filter on. This can sometimes throw out your sum function, depending on how your spreadsheet is set up and what you are trying to sum. How do you know whether you have a filter on? Simple - if the row numbers on the left are blue, you have a filter applied. To quickly remove a filter that is applied, and show all your data, press ALT D,F,S.

b) You think you have dragged down to include new rows in your sum, but instead of dragging, you've just moved the range. A picture will explain this better:

If you wanted to include row 7 in your sum, you can point your mouse at the bottom right hand corner of cell B6 and a little double headed arrow will show up. Drag down to row 7 and voila, your sum now includes everything.

The error comes in when you drag down when the four headed arrow is showing, not the double headed arrow. This results in you just dragging down the range. In this case, your sum would be SUM (B4:B7). Easy mistake to make, not so easy to spot when you have thousands of rows to sum.

So how to spot it? Excel tries to help again (see, it is NOT the enemy!). A little green triangle appears in the top right hand corner of the cell with your formula. Click on it, and it will tell you that your "Formula Omits Adjacent Cells". Go find those cell numbers and include them.

c) You simply have left a cell out. This is again, easy enough to do, especially when you are trying to add only certain cells, so you add each one individually. The easiest way to check that you have included every cell you should have, is to click on the cell with your formula, and then click once in the formula bar. All the cells you have chosen will be highlighted by different colour borders, which makes it easy to identify what cells, if any, you have forgotten. See below for an example.

If we were just trying to add sales for the dinghies only, by using this check, we would immediately see that we have left out the sales for Dinghy B (cell B6).

As an aside, this trick is also very useful when trying to make sense of long, complicated formulas. The colours help track what you have done, and Excel even provides different coloured brackets for embedded formulas to make your life even easier.

So these are all fairly basic, but oh so easy, errors to make. And while I recognise that Excel is not perfect, when there are errors in reports or results, it is normally the fault of the skipper!

For some more handy hints on errors, why not sign up for our weekly newsletter at http://www.excelangels.ie/faqs_and_weekly_tips_8.html

Or check out our training program to avoid your own rocky seas at www.excelangels.ie

Hope your spreadsheet seas are calm ones this week!