Excel: Common Formula Errors and How to Fix Them

Excel formula errors are confusing but each one means something specific. Here's what they mean and how to fix them. #DIV/0! — Division by zero The formula is trying to divide by a cell that is empty or contains zero. Fix: Wrap the formula with IFERROR. Example: =IFERROR(A1/B1, "N/A") will show "N/A" instead of the error. #VALUE! — Wrong type of value The formula is expecting a number but the cell contains text, or vice versa. Fix: Check that all cells referenced in the formula contain the correct data type. Select each referenced cell and look at the format in the Home tab. #REF! — Invalid cell reference The formula is pointing to a cell that no longer exists — usually because a row or column it referenced was deleted. Fix: Click the cell with #REF! and look at the formula bar. Find where it says #REF! and update the reference to the correct cell. #NAME? — Unrecognized formula name Excel doesn't recognize the function name — usually a typo or a missing quote mark in text. Fix: Check the spelling of your function. Make sure text strings are wrapped in double quotes. For example: =IF(A1="Yes", 1, 0) — the word Yes must be in quotes. #N/A — Value not available Most common with VLOOKUP and XLOOKUP — the value being searched for wasn't found. Fix: Verify the lookup value exists in the lookup table. Check for extra spaces (use TRIM() to clean). Use IFERROR to handle it gracefully: =IFERROR(VLOOKUP(...),"Not Found") #NUM! — Invalid number The formula produces a number too large for Excel, or an invalid numeric operation (like square root of a negative number). Fix: Check the values going into the formula. #NULL! — Invalid range Two ranges don't actually intersect as intended. Fix: Check that range references are separated by a colon (A1:A10) not just a space. General tips: - Press F2 on a formula cell to see the cells it references highlighted in color - Use Formulas > Show Formulas (or Ctrl + `) to see all formulas at once - Use the Evaluate Formula tool (Formulas tab) to step through a formula's calculation