Spreadsheet Mistakes 3 - Formatting, Functions & Formulas

Updated: Oct 7


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:




Next: Spreadsheet Mistakes 4 - Syntax


Previous: Spreadsheet Mistakes 2 - Human Error

23 views