Fixing broken formulas
8 minutes · 4 steps · free, no sign-up
Every spreadsheet user sees error messages. They look alarming but each one is a clue, and once you know the code you can usually fix it in seconds. Here is a small order sheet with four things wrong with it. Find and fix each one.
- Recognise #VALUE!, #NAME?, #DIV/0! and circular reference errors
- Know what causes each one
- Trace an error back to its source
- Fix the cause rather than hiding the error
Click a cell and start typing. Enter edits, Tab moves right, arrows move, Delete clears.
Quick referenceError codes
#VALUE! wrong kind of value, e.g. text in a sum
#NAME? Sheets does not know a name, often a typo
#DIV/0! dividing by zero or an empty cell
#REF! a reference points at a cell that is gone
#N/A a lookup found nothing
Circular a cell refers to itselfThe key ideasSummary: Fixing broken formulas
An error is Sheets telling you what kind of problem it hit. #VALUE! usually means text where a number was needed. #NAME? is nearly always a mistyped function name. #DIV/0! means you divided by zero or by an empty cell. A circular reference means a formula refers to its own cell.
Errors spread: a total that adds a cell with an error shows the error too. So always fix the first one, working back to where it started, and the others often clear up by themselves. Hiding an error with IFERROR is sometimes right, but never before you understand why it appeared.
Watch out forCommon mistakes
- Fixing the symptom, not the cause. A total showing an error is usually fine. The mistake is further up the chain, in one of the cells it adds.
- Covering errors with IFERROR straight away. It hides real problems. Fix the cause first, then use
IFERRORonly where an error is genuinely expected. - Mistyping function names.
SUMMorAVRAGEgives#NAME?. Sheets suggests names as you type, so let it complete them. - Dividing by a cell that has not been filled in yet. An empty cell counts as zero, so the result is
#DIV/0!.
Next stepTry it in the real Google Sheets
In Google Sheets, click a cell showing an error and hover over the small triangle or the message for a description. Double-click a formula and the cells it uses are outlined in colour, which makes it easy to see which input is the culprit.