Reading error values

An error such as #DIV/0! is not a crash. It is a value with a name, and its name tells you what went wrong.

Errors are values

When a formula cannot produce an answer, it produces an error value instead. The error sits in the cell like any other value, and any formula that uses that cell gets the same error. That is why one mistake can fill a whole column with #VALUE!: the error flows down every formula that depends on it. The good news is that there are only a handful of error names, and each one points at a different cause. Google's ERROR.TYPE help lists the same set that Excel uses.

#DIV/0! appears when something is divided by zero. An empty cell counts as zero in arithmetic, so a sales-per-day formula shows #DIV/0! until someone fills in the number of days. #VALUE! means a calculation met text it could not turn into a number, as in the previous unit's chain of + signs. #NAME? is usually a typing problem. Microsoft's IF page says it usually means a misspelled formula, and its VLOOKUP page says it usually means text without quote marks: =SUMM(A1:A3) and =IF(A1>5,Yes,"No") both show it.

#REF! means a formula points at a cell that is gone. Deleting a row that a formula used is the classic cause; copying a relative reference off the top or left edge is another, as the previous unit showed. #N/A comes from lookup functions such as VLOOKUP when the value is not found, and the next course covers it. Finally, a cell full of #### is not an error at all. Microsoft's operator page notes that # is also used to show there is not enough space to display a value: widen the column and the number appears.

Where Excel and Google Sheets behave differently

Two situations are handled differently by the two programs. If you type a formula that cannot be read, such as a missing bracket in =SUM(A1:A3, Excel refuses to accept it and shows a message explaining the problem. Google Sheets accepts the entry and shows #ERROR! in the cell, with the note Formula parse error. The second case is a circular reference, a formula that depends on its own cell, such as =A1+1 typed into A1. Microsoft says Excel shows a warning the first time and then 0 or the last calculated value. Google Sheets shows #REF! with the message Circular dependency detected.

This lab follows Google Sheets in both cases so that you can see the problem in the cell, and the panel explains what Excel would do. When you meet an error, select the cell, read the formula in the formula bar, and follow the references back until you find the first cell where the error starts. Fix that one cell and the errors downstream usually disappear together.