← Google Sheets from Scratch: Formulas, QUERY, and Collaboration
Lesson
Lesson 3: Error values and final practice: a mini dashboard
Recognize the causes of errors (#N/A, #REF!, #DIV/0!, #NAME?, #VALUE!) and build a small working report from what you’ve learned.
Errors in Google Sheets: what they mean and how to fix them
The five main Google Sheets errors
An error in a spreadsheet isn’t a disaster; it’s a hint. Each code points to a specific problem.
#N/A means a value was not found. It usually comes from VLOOKUP and MATCH when the value you’re looking for isn’t in the range. A common hidden cause is extra spaces: “Moscow” and “Moscow ” do not match. Fix it with TRIM on the source data or a wrapper: =IFERROR(VLOOKUP(...), "Not found") or =IFNA(VLOOKUP(...), ""). #REF! is an invalid reference: a deleted row or column the formula used, or a VLOOKUP column number outside the range (col_index=5 in a three-column range). Fix the reference.
#DIV/0! is division by zero: the formula divides by zero or an empty cell. Check the denominator: =IFERROR(A1/B1, 0) or =IF(B1=0, "", A1/B1). #NAME? is an unknown function name: a typo (=VLOKUP instead of =VLOOKUP), a function name in another language (such as the German SUMME) where English names are expected, or text arguments without quotes. #VALUE! is a mismatched data type: text where a number was expected, as in =A1+B1 when B1 holds a word. Check the types; convert to numbers if needed.
Lesson notes
The five main Google Sheets errors
An error in a spreadsheet isn’t a disaster; it’s a hint. Each code points to a specific problem.
#N/A means a value was not found. It usually comes from VLOOKUP and MATCH when the value you’re looking for isn’t in the range. A common hidden cause is extra spaces: “Moscow” and “Moscow ” do not match. Fix it with TRIM on the source data or a wrapper: =IFERROR(VLOOKUP(...), "Not found") or =IFNA(VLOOKUP(...), ""). #REF! is an invalid reference: a deleted row or column the formula used, or a VLOOKUP column number outside the range (col_index=5 in a three-column range). Fix the reference.
#DIV/0! is division by zero: the formula divides by zero or an empty cell. Check the denominator: =IFERROR(A1/B1, 0) or =IF(B1=0, "", A1/B1). #NAME? is an unknown function name: a typo (=VLOKUP instead of =VLOOKUP), a function name in another language (such as the German SUMME) where English names are expected, or text arguments without quotes. #VALUE! is a mismatched data type: text where a number was expected, as in =A1+B1 when B1 holds a word. Check the types; convert to numbers if needed.