Updated: Oct 7, 2020
COMMON MISTAKES IN FORMATTING
Using blank columns or blank rows for spacing prevents the use of pivot tables and can lead to formula or calculation errors. Use column width or row height instead.
Merging cells can cause cell reference errors. Instead use Center Across Selection
Excessive use of font types, sizes and colors makes the spreadsheet harder to read and, quite frankly, can just be annoying. Also, remember that many people have color blindness so they cannot distinguish between certain colors. Better to use just one font type, then vary the size to improve readability. Instead of colors, use bold and italic.
Here is an example of what not to do.
Too many variations of font and color.
So this is harder to read.
I recommend this instead.
Same font, use italics to distinguish.
Or you can use bold or underline.
COMMON MISTAKES IN FUNCTIONS
Be careful when typing the name of the function! A typo in the function will give you the wrong answer. Here are a few examples:
AVERAGE() - Ignores all text and false entries
AVERAGEA() - Converts all text and false entries to zero
COUNT() - Counts the number of cells with numbers
COUNTA() - Counts the number of cells that are not empty
IFERROR() - If there is an error, will return the value you specify
ISERROR() - If there is an error, will return TRUE
ISERR() - For any error except #N/A, this will return TRUE
COMMON MISTAKES IN FORMULAS
Avoid hard coding formulas. Hard coding is a term that means you have entered a number in your formula instead of a cell reference. If you copy and paste a Hard coded formula, you will have to edit every copied instance to avoid an error.
Be consistent with your formulas. In any given row or column you should have only one formula. It is the best way to avoid errors. If you must have a different formula in your calculations, insert a new row or column for the new formula.
Avoid having too many nested levels in your formulas. More nested levels means it will be harder for someone else to understand what your formula is doing - this is so even for experienced spreadsheet developers. If you have to nest more than three levels, then you should use a different formula or use helper columns.
Lack of checksum cells is a common error I see. For even the simplest spreadsheet any critical values should be calculated two different ways to ensure that your numbers are adding up correctly. If you do this, you can immediately spot an error when you notice that the two checksum values do not match.
Here are examples of common formula mistakes: