I consider myself pretty handy with a spreadsheet, but I’ll bet at least 10% of the spreadsheets I’ve used in the last year have an error in them. Here are a few things I do to help prevent errors:

• Layout the spreadsheet so the inputs are readily identifiable and well labeled.
• Use cell comments to explain the more cryptic formulas and not using Excel speak, but plain language.
• When testing the formulas, use extreme examples. For example, what if the gross margin was negative, zero, or greater than 100%.
Posted in Uncategorized

## 4 thoughts on “Spreadsheet Errors”

1. Charlie III says:

The setup of a good spreadsheet is so important. I always believed that 85% of all errors are related to the setup of the spreadsheet. The other 15% is from fat fingers. A good spreadsheet can also control much of the fat fingers. I just saw this article at Microsoft and had to send to you. It relates to what are best ways to set up a spreadsheet. But, not even this article sets up a good spreadsheet.

So what is a good spreadsheet and how do you reduce errors from it?

I have my own opinion but its too long for a comment.

http://office.microsoft.com/en-us/assistance/HA011082671033.aspx

2. Jamie Collins says:

“Use cell comments to explain the more cryptic formulas”

A good tip.

Another good tip might be, avoid cryptic formulas, preferably in favor of self-documenting formulas e.g. using defined Names.

I’ve learned that the most important aspect of development is maintenance. I used to favor a long formula in a single cell because I didn’t like using multiple columns when one would do (also I used to think they made me look ‘clever’). Trouble with this approach is you end up with formulas like this one:

=IF(ROW(A1)=1,IF(ISERR(FIND(” “,\$A\$7)),\$A\$7,MID(\$A\$7,1,FIND(” “,\$A\$7,1)-1)),
IF(ROW(A1)