XLOOKUP returns #N/A even though the value exists
Hidden spaces, numbers stored as text, invisible characters and wrong ranges, with a formula fix for each.
Includes a live exampleFix common Excel errors and problems
Pick the error or problem you're seeing. Each guide starts with the most likely fix, then walks through the other causes in order of how often they happen, with formulas you can copy. Several include a live sheet where you can break the formula and fix it yourself.
Error values
What the error code in the cell means and how to get rid of it.
How to fix the #SPILL! error in Excel
Something blocks the cells a dynamic array formula needs: data, a table, merged cells or a whole-column reference.
Includes a live exampleHow to fix #VALUE! in Excel
Text in a calculation, spaces that look blank, ranges of different sizes and dates stored as text.
Includes a live exampleHow to fix or hide #DIV/0! in Excel
Dividing by zero or a blank cell, averages with nothing to average, and percent change from zero.
Includes a live exampleHow to fix #REF! after deleting rows or columns
Deleted cells, VLOOKUP column numbers past the table, formulas copied off the sheet and INDEX out of range.
Problems and tasks
Wrong totals, formulas that won't work, and jobs like matching two lists.
Why Excel SUM shows 0 (or doesn't add up)
Numbers stored as text, manual calculation, circular references, hidden decimals and filtered rows.
Includes a live exampleExcel formula shows as text or doesn't update
Show Formulas, Text-formatted cells, an apostrophe or space before =, and manual calculation.
VLOOKUP not working: #N/A, wrong value or #REF!
Approximate match by default, a wrong column number, ranges that slide and left lookups.
Includes a live exampleHow to reconcile two bank statements in Excel
Match a bank export to your records with COUNTIFS, flag unmatched items both ways and handle duplicates.
Includes a live exampleHow to highlight duplicate rows across two sheets
Conditional formatting with COUNTIF across sheets, whole-row matches with a helper key, and values in one list only.
Includes a live exampleHow to read an Excel error
Click the cell with the error, then click the small warning icon next to it. The first line of the menu names the problem, and Show Calculation Steps lets you watch Excel work through the formula one part at a time. You can also use Formulas > Evaluate Formula for the same view. If an error appears in a total, the real problem is usually in one of the cells the total adds up, so use Formulas > Error Checking > Trace Error to follow it back.