1. Formula Showing as Text Instead of Calculating
If your formula appears as =VLOOKUP(A2,Sheet2!A:B,2,FALSE) in the cell instead of the result, the cell was formatted as Text before you entered the formula.
Fix: Select the cell → Home tab → Number Format dropdown → change from Text to General → press F2 to edit the cell → press Enter. The formula will now calculate.
Also check there is no space before the = sign. A formula starting with a space is treated as plain text.
2. #VALUE! Error — Wrong Data Type
Formula is trying to do math on a text cell, or you passed the wrong type to a function.
Numbers stored as text (imported from CSV), mixing text and numbers in a SUM range, using text in a date calculation, VLOOKUP looking up a number but the table stores it as text.
Use =VALUE(A2) to convert text to number. Use =TRIM(A2) to remove hidden spaces. Check the cell format — if it shows a green triangle, Excel knows it is a text-number.
3. #N/A Error in VLOOKUP or XLOOKUP
The lookup value cannot be found in the table. This is the most common Excel error and has a few distinct causes:
- Trailing spaces: "Apple " (with space) ≠ "Apple". Fix: wrap lookup value with
TRIM(A2) - Type mismatch: Looking up the number 123 but the table stores "123" as text. Fix: use
VALUE(A2)on the lookup or table - Value genuinely absent: The item does not exist in the table. Use
IFERROR(VLOOKUP(...),"Not found")to handle gracefully - Extra characters: Em-dashes, non-breaking spaces, or invisible characters from copy-paste. Fix:
CLEAN(TRIM(A2))
4. #REF! Error — Deleted or Moved Reference
A cell or range the formula pointed to has been deleted, or a VLOOKUP col_index_num is larger than the table width.
- If you deleted a row or column: undo (Ctrl+Z) and rewrite the formula without referencing that specific cell
- If col_index_num is too large: reduce it to match your table width (e.g. if table is A:C, maximum index is 3)
- Use entire columns (A:B) instead of fixed ranges (A1:B100) to avoid references becoming invalid
5. Formula Works on Row 1, Breaks When Copied Down
Cell references shift when you copy a formula. Fix: add dollar signs ($) to lock the references that should not move.
Press F4 after selecting a cell reference to toggle through locking options: A2 → $A$2 → A$2 → $A2 → A2.
6. #DIV/0! — Division by Zero
7. #NAME? — Unrecognised Function
Causes: misspelled function (VLOKUP → VLOOKUP), text not in quotes (Paid instead of "Paid"), or using a function unavailable in your Excel version (XLOOKUP in Excel 2019).
Fix: check spelling carefully, add double quotes around text values, or check your Excel version supports the function you are using.
8. Formula Not Updating Automatically
Excel calculation is set to Manual. Fix: go to Formulas tab → Calculation Options → Automatic. Or press F9 to force recalculate immediately. This is a common issue when opening files from other users.
9. Formula Returns a Wrong Number (Not an Error)
The formula runs without error but the result is wrong. Common causes:
- Operator precedence:
=2+3*4= 14 (not 20). Add brackets:=(2+3)*4= 20 - AVERAGE including blank cells in the denominator — use AVERAGEIF to exclude them
- SUM accidentally includes hidden rows — use SUBTOTAL(9, range) for visible-only sums
- Date stored as text — arithmetic on a text date returns wrong results