Spreadsheet Formula Shows an Error? Fix It
A formula error is a message, not a mystery: each error type points to a specific problem, such as a missing value or a divided-by-zero. Read the error first, then check the cells the formula points to.
TL;DR / Quick Fix
Quick fix: read the error code, check that every cell the formula references holds the expected type of value, and fix one part at a time. Then copy the corrected formula down the column and check the results. Last, copy the fixed formula and verify results to confirm it.
Follow these steps
- Read the exact error code. Click the cell and read the full error, such as a value error or a reference error. Each code narrows the cause, so do not guess before reading it.
- Check the cells the formula references. Trace the formula's references and open each one. A blank cell, a text value where a number belongs, or a deleted row explains most errors.
- Fix text stored as numbers. If numbers are stored as text, convert them to numbers. A formula that adds text instead of numbers fails, even when the digits look right.
- Check for division by zero or empty cells. Look for a divisor that is zero or blank. A division by an empty cell fails, so guard it or fill the missing value first.
- Fix one part at a time. Break a long formula into parts and test each part in a helper cell. Fixing one part at a time beats rewriting a long formula from scratch.
- Copy the fixed formula and verify results. Copy the corrected formula across the range, then spot-check several results by hand. A formula that runs without errors can still be wrong.
What NOT to do
- Do not hide an error by deleting the formula. A blank cell hides the problem and breaks totals that depend on it.
- Do not wrap every formula in an error-hiding function. Hiding errors makes bad data look clean.
- Do not paste values over a broken formula to make it look fixed. Pasted values will not update when inputs change.
- Do not guess at a formula you do not understand. Break it into parts, or check a simple example first.
Frequently asked questions
What does a value error mean?
Usually the formula received the wrong type of value, such as text where a number was expected.
What does a reference error mean?
Usually a referenced cell or row was deleted, so the formula points to a place that no longer exists.
Should I use error-hiding functions?
Only for expected gaps, such as a missing lookup. Hiding genuine errors makes bad data invisible.