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%.