Fixes for Excel and Google Sheets formulas that do not work

Your formula, working again.

XLOOKUP returning #N/A, a #SPILL! error, VLOOKUP picking the wrong row: find the error, see why it happens and the exact change that fixes it, from Microsoft and Google documentation.

Every fix checked against Microsoft and Google documentation
Sources listed under every page
No ads, no cookies, no account

Browse by topic

Pick the area your error comes from.

LookupsXLOOKUP, VLOOKUP, INDEX and MATCH
2 fixesExcel errors#SPILL!, #N/A, #REF!, #VALUE!
3 fixesFormulasSUMIF, totals and formulas that do not calculate
2 fixesGoogle SheetsIMPORTRANGE and Sheets-only functions
1 fix

Latest fixes

Each one explains why the error happens and the exact steps to clear it.

Formulas

Excel formula not calculating or showing as text: the fixes

An Excel formula shows as text when Show Formulas is on, the cell is Text or it lacks a leading =. Stale results mean Manual calculation: set Automatic.

7 min read6 official sources

Read the fix Google Sheets

IMPORTRANGE #REF!: "You need to connect these sheets"

IMPORTRANGE shows #REF! until the source file is connected: click Allow Access once, or get access from the owner, then check the URL and range string.

6 min read4 official sources

Read the fix Excel errors

#REF! error in Excel: find the broken reference and fix it

#REF! means a formula points at a cell that is no longer valid: deleted, pasted over or out of range. Undo, rebuild the reference or open the source file.

6 min read8 official sources

Read the fix Excel errors

#SPILL! error in Excel: what each cause means and the fix

A #SPILL! error means Excel cannot place a formula's multiple results in the grid. Clear the blocked spill range, unmerge cells or leave the table.

6 min read4 official sources

Read the fix Formulas

SUMIF returns 0 or the wrong total in Excel: the fixes

SUMIF gives 0 or a wrong total? Quote text criteria, write ">"&A1 for operators, convert numbers stored as text and make both ranges the same size.

7 min read8 official sources

Read the fix Excel errors

#VALUE! error in Excel: find the bad cell and fix it

#VALUE! means a formula hit text, a hidden space or a text date where it needed a number, or mismatched ranges. Find the cell, clean it, rerun.

7 min read7 official sources

Read the fix Lookups

VLOOKUP not working: #N/A, #REF!, #VALUE! or wrong value

VLOOKUP fails when range_lookup is TRUE on unsorted data, the key is not in the first column, or types differ. Use FALSE and lock the range.

6 min read6 official sources

Read the fix Lookups

XLOOKUP returns #N/A when the value exists: the fixes

XLOOKUP returns #N/A when two values only look equal: hidden spaces, text versus numbers, or a shifted range. Clean the data, match types, lock ranges.

5 min read9 official sources

Read the fix

No fix matches that search yet.