Formula Errors and How to Fix Them

Task 1 of 4

Lesson theory

#DIV/0! — division by zero or by an empty cell;
#NAME? — a misspelled function name, or text without quotation marks;
#VALUE! — an operation on the wrong type of data, such as number + text;
#REF! — a reference to a cell that no longer exists (a row or column was deleted);
#N/A — a value was not found (VLOOKUP, MATCH);
#NUM! — an impossible number (the square root of a negative);
#SPILL! — filled cells are blocking an array result.

To find the cause, select the cell and look at the formula in the formula bar. Then check each reference: what is in those cells?

Enter — commit · F2 — edit · F4 — $ in a reference · Ctrl+C/Ctrl+V — copy and paste · Ctrl+Z — undo

Esc or F6 — leave the sheet for the «Check» button · Shift+Tab in the first cell — back to the page

10. Formula Errors and How to Fix Them