A Guide To Spreadsheet Errors And Fixes

Understanding errors and how to fix them is a big part of keeping your data clean, so it’s important to know how to deal with issues as they come up, and more importantly, not to get discouraged.

These strategies will help you avoid spreadsheet errors to begin with, making your life in analytics a whole lot less stressful:

• Filter data to make your spreadsheet less complex and busy.

• Use and freeze headers so you know what is in each column, even when scrolling.

• When multiplying numbers, use an asterisk (*) not an X.

• Start every formula and function with an equal sign (=).

• Whenever you use an open parenthesis, make sure there is a closed parenthesis on the other end to match.

• Change the font to something easy to read.

• Set the border colors to white so that you are working in a blank sheet. 

• Create a tab with just the raw data, and a separate tab with just the data you need.

The following table is a reference you can use to look up common spreadsheet errors and examples of each. Knowing what the errors mean takes some of the fear out of getting them.

If you are working with Microsoft Excel, an interactive page, How to correct a #VALUE! error, can help you narrow down the cause of this error. You can select a specific function from a drop-down list to display a link to tips to fix the error when using that function.

Pro tip: Spotting errors in spreadsheets with conditional formatting

Conditional formatting can be used to highlight cells a different color based on their contents. This feature can be extremely helpful when you want to locate all errors in a large spreadsheet. For example, using conditional formatting, you can highlight in yellow all cells that contain an error, and then work to fix them.

Conditional formatting in Microsoft Excel

To set up conditional formatting in Microsoft Excel to highlight all cells in a spreadsheet that contain errors, do the following:

• Click the gray triangle above row number 1 and to the left of Column A to select all cells in the spreadsheet.

• From the main menu, click Home, and then click Conditional Formatting to select Highlight Cell Rules > More Rules.

• For Select a Rule Type, choose Use a formula to determine which cells to format.

• For Format values where this formula is true, enter =ISERROR(A1).

• Click the Format button, select the Fill tab, select yellow (or any other color), and then click OK. 

• Click OK to close the format rule window.

To remove conditional formatting, click Home and select Conditional Formatting, and then click Manage Rules. Locate the format rule in the list, click Delete Rule, and then click OK.

Conditional formatting in Google Sheets

To set up conditional formatting in Google Sheets to highlight all cells in a spreadsheet that contain errors, do the following:

• Click the empty rectangle above row number 1 and to the left of Column A to select all cells in the spreadsheet. In the Step-by-step in spreadsheets video, this was called the Select All button. 

• From the main menu, click Format and select Conditional Formatting to open the Conditional format rules pane on the right.

• While in the Single Color tab, under Format rules, use the drop-down to select Custom formula is, enter =ISERROR(A1), select yellow (or any other color) for the formatting style, and then click Done.

To remove conditional formatting, click Format and select Conditional Formatting, and then click the Trash icon for the format rule.