In my blog post about spreadsheet syntax errors, I explained that a syntax error occurs when your function is incorrect.
If your error is not a syntax error, it is a logical error. There are many types of logical errors, all of which can be prevented. Here is a list of logical errors that can occur in your spreadsheet.
You reference the wrong range OR
You reference only a portion of the needed data
NOTE: It is a syntax error if you reference a range that does not exist. When this happens, you will get a #REF! error
Order of Precedence Not Followed
Spreadsheet programs interpret how to calculate a formula by following the order of precedence shown in the following table.
If your formula results in an incorrect answer, you have not followed the order of precedence.
Performing calculations on data which do not agree
You use a number instead of a cell reference in a formula
Disbursed Variables / Assumptions
You have your variables and assumptions scattered throughout the spreadsheet instead of consolidating them all on one worksheet tab.
You want the user to be able to quickly understand your spreadsheet and the point you are trying to make. If you don't the user will draw the wrong conclusion.
Simplify the content to be reviewed
People don’t have time to decipher and review spreadsheets so point people to the outcomes they have to analyze
Hidden Sheets / Columns
If you hide a sheet, column, or row as a security measure, to conceal sensitive information – DON’T!
It’s not secure
Hidden items can be changed by any user edits
It is better to hardcode the data and send the user a duplicate of the worksheet
Lack of Formula Checks
You should always calculate the answer for critical data in different ways in different places in your spreadsheet
Use a checksum to ensure your formula is correct
Avoid complex formulas - they are difficult to understand
Complex formulas increase the likelihood of logical - and syntax - errors
Instead of complex formulas, use multiple simple formulas with helper columns
Resist the urge to create heavily nested lengthy formulas
Use named ranges to ensure you use the correct data and so your formula is much easier for the user to understand
If you label everything, then nothing is important
Duplication Without Inspection
Review your worksheet before you make copies
If your workbook has an error, then you only create and worsen a problem when you distribute copies
Have someone else review your file
Other Practices Best Avoided to Ensure Simplicity and Understanding
Not Using Named Ranges or Built-In Functions
Using VBA When Formulas Will Suffice
Too Many Columns in One Worksheet
Using Copy / Paste Instead of Connecting the Spreadsheet to the Data Source
File Names / Worksheet Names Don’t Make Sense
File Not Documented in File Properties
Not Understanding the Business Issues