Fix Excel Errors: #VALUE!, #REF!, #DIV/0! Explained

You're staring at a cell showing #VALUE! — or maybe it's #REF!, or a column full of #DIV/0! — and Excel isn't telling you why. Just a red triangle and an error code that explains roughly nothing. That's not your fault. Excel's error messages are, to put it charitably, not designed with beginners in mind.

These three error codes cover the majority of formula errors most people hit in real spreadsheet work, and each one has a different root cause requiring a genuinely different fix. If you've been searching "fix Excel errors" and getting generic lists that don't actually help you diagnose what went wrong, this is the guide that'll walk you through it properly. Have your broken spreadsheet open before you start — you'll want to work through these steps on real data, not a practice file.

Three of the most common Excel formula errors, each caused by something different and fixed a different way.

What You'll Be Able to Fix — and Why These Three Excel Error Codes Behave So Differently

#VALUE! means Excel received a data type it wasn't expecting, usually text where a number should be. #REF! means a cell reference is pointing somewhere that no longer exists. #DIV/0! means a formula is dividing by zero or an empty cell. Three problems, three diagnostic paths, three different fixes.

Before touching any formula, you need a map of what's broken. That distinction — diagnose first, fix second — will save you from accidentally creating new errors while repairing old ones. The prerequisite is simple: have the spreadsheet open and know which cells are showing errors. The next step builds that map for you.

New to Excel? The concepts in this guide build on Excel for beginners and cell references in Excel. Both are worth reading alongside this one.

Step 1: Find Every Error in Your Spreadsheet Before You Touch a Single Formula

Editing the first error you see without knowing where the others are is how fixes break other things. Map everything first.

Use Go To Special to Highlight All Error Cells at Once

Most guides skip this entirely. Go To Special is Excel's closest thing to a spell-checker for formula errors — it selects every error cell in one move so you're not hunting through a 400-row sheet by hand.

1. Press F5 (Windows) or Ctrl+G to open the Go To dialog.
2. Click Special.
3. Select Formulas, then check only Errors. Uncheck the rest.
4. Click OK. Every error cell in your sheet gets selected simultaneously.

From there, you can see the scope of the damage before committing to any changes. In Microsoft 365, the Error Checking tool under Formulas → Error Checking does something similar — it steps you through errors one by one with a short explanation for each. Run it before you start editing anything.

Excel Online handles some error flagging differently than the desktop version, particularly around date-based calculations. If your results look inconsistent, verify you're working in the desktop app.

Step 2: Fix Each Excel Error Code Using the Right Method for That Specific Cause

Now that you have a full picture of where the errors are, you can go after each type with the right approach. The fix that works for #REF! does nothing for #VALUE!. They have to be handled separately.

Fix #VALUE! When a Cell Contains Text Where a Number Should Be

The most common real-world trigger is data imported from another system — a status label like "pending" sitting in a column your formula treats as numeric. Excel can't do math on text, so it returns #VALUE!.

A rogue space character is often the entire problem. Before anything else, run =TRIM() on suspect cells to strip trailing spaces and =CLEAN() to remove non-printing characters. A single invisible space can cause failures that are nearly impossible to spot by eye.

If the data looks like a number but Excel is treating it as text, select the column, go to Data → Text to Columns, click through the wizard without changing anything, and hit Finish. That forces Excel to re-evaluate the data type. Understanding cell references in Excel will also help you trace where the wrong input is actually coming from.

Fix #REF! After a Row, Column, or Sheet Gets Deleted

#REF! almost always means a formula is pointing to a cell that no longer exists. Click into the error cell and look at the formula bar — you'll see #REF! sitting inside the formula where a valid cell reference used to be. Undo the deletion if you can (Ctrl+Z on Windows, Cmd+Z on Mac). If you can't, rebuild the reference manually.

Early in my career as a data analyst, I copied a formula without locking the references. When a column got deleted, one quarter's figures inflated by 12% before anyone caught it. I've triple-checked absolute references ever since.

To prevent it from happening again: lock your ranges with F4 (Windows) or Cmd+T (Mac) before copying formulas anywhere. That turns a relative reference like A1 into an absolute reference like $A$1, which won't shift or break when rows and columns move.

Fix #DIV/0! When a Formula Divides by a Zero or Empty Cell

This one is structural — your formula is dividing by something that's either zero or blank. It's common in summary tables where not every row has data yet.

The direct fix is to wrap the division in an IF statement that checks the denominator first:

=IF(B2=0,"",A2/B2)

If B2 is zero or empty, the formula returns a blank. If it has a value, it performs the division. You can substitute "" with "N/A" or 0 depending on what your report needs.

Step 3: Use IFERROR or IFNA to Handle Errors That Will Keep Coming Back

Once you've cleaned up existing errors, some will return — especially if your spreadsheet pulls from live data or lookup tables that occasionally have gaps. That's where IFERROR and IFNA come in. But there's a trade-off worth understanding before you wrap everything.

=IFERROR(your_formula, "value_if_error") catches any error Excel can return — #VALUE!, #REF!, #DIV/0!, all of them — and replaces it with whatever you specify. It's broad. For a fuller look at how Excel formulas and functions handle error logic, that's a good companion read.

When to Choose IFNA Over IFERROR (And Why It Matters)

IFERROR's breadth is also its risk. If your VLOOKUP has a genuine syntax error — a wrong column index, a broken reference — IFERROR will quietly swallow it and display your fallback value. You won't see the problem. You'll just get wrong data that looks fine.

=IFNA(VLOOKUP(A2,Table,2,0),"Not found")

IFNA only catches #N/A — the "lookup didn't find a match" error. Everything else still surfaces. That's the right call for most VLOOKUP and XLOOKUP scenarios, because a #N/A usually means no match exists, while any other error means something in the formula is actually broken.

IF(ISERROR()) is the older equivalent and still works in Google Sheets if cross-platform compatibility matters to you. For XLOOKUP wrapped in IFERROR or IFNA, confirm you're on Microsoft 365 — that function isn't available in older perpetual licenses.

Common Mistakes When Fixing Excel Formula Errors

Three habits that undo most fixes:

Open a spreadsheet where you've seen one of these errors before. Apply the fix. Real data, not a practice file — that's when it actually sticks.