How to Fix Excel Formulas That Aren’t Working

You have written the formula. Excel has looked at it. Excel has apparently decided not to participate.

Before rebuilding the workbook or accusing Microsoft personally, work through the common causes below.

Check the formula starts with =

An Excel formula normally begins with an equals sign. If the cell contains the formula text rather than the result, check that it has not been entered as plain text.

Check the cell format

If a cell was formatted as Text before the formula was entered, Excel may display the formula instead of calculating it. Change the format appropriately and re-enter the formula.

Check calculation mode

If values change but formula results do not, the workbook may be using manual calculation. Check Excel’s calculation settings and recalculate the workbook.

Look for numbers stored as text

Imported data often contains values that look numeric but are stored as text. A formula relying on those values can give unexpected results or errors.

CSV imports, copied web data and PDF conversions are common sources of this problem.

Read the error message

Errors such as #DIV/0!, #VALUE!, #REF! and #N/A are clues rather than random abuse.

A division-by-zero error means the formula is attempting to divide by zero or an empty value. #REF! usually indicates an invalid reference. #N/A commonly appears when a lookup cannot find the requested value.

Check cell references

When formulas are copied, relative references move. Sometimes that is exactly what you want. Sometimes the formula wanders away from the lookup table it was supposed to use.

Check whether references should be relative, absolute or mixed.

Check ranges after inserting or deleting data

If rows, columns or sheets have been added, removed or moved, make sure formulas still cover the intended range. Totals that quietly omit the newest rows are particularly easy to miss.

Test a smaller piece

For a complicated formula, test individual parts in separate cells. Finding the broken component is usually easier than staring at a 200-character formula until one of you apologises.

Can’t be faffed?

If the workbook contains broken formulas, lookups that refuse to behave or calculations nobody understands anymore, Faff It Off.

Send us the spreadsheet, tell us what it should be doing, and we’ll quote for finding and fixing the problem.