No Ticket Needed

Appearance

How to Fix Common Excel Formula Errors

Applies toMicrosoft Excel for Microsoft 365, Excel 2024, Excel 2021, Excel on the web
6 min fix Updated 9 Aug 2026

By , 10+ years in IT support and helpdesk How these guides are checked

Quick Answer

Read which error Excel returned before changing anything, because each one names a different cause. #REF! means a referenced cell was deleted, #VALUE! means text where a number belongs, and #DIV/0! means dividing by an empty or zero cell.

Checked against Microsoft 365 Roadmap, Office release notes - Current Channel and between 5 Aug 2026 and 12 Sep 2026. Nothing published in that period has been linked to this guide.

Summary ​

#REF!, #VALUE!, #DIV/0! and the rest are not failures of Excel — they are Excel's answer to a question it could not finish. Each one names a different cause, and reading which one you have is what turns a red cell into a two-minute fix. This guide decodes the errors you will actually meet and fixes each at its source.

In this guide

Before You Start ​

  • No preparation needed. Every step below uses the standard Excel ribbon, and none of them changes a setting outside the workbook you are in.

Instructions ​

1. Read the Error Before You Change Anything ​

An Excel error value is a result rather than a crash — the formula ran, and Excel is telling you in six or seven characters what it could not do.

Nothing is broken about the file and nothing has been lost. The cell holds a real formula that produced a real answer, and the answer happens to be "I could not finish this". Excel chooses the wording deliberately, so two errors that look equally red can have nothing in common: #REF! is about a cell that vanished, #VALUE! is about the kind of data in a cell that is still there. Rewriting the formula before reading the message is how people spend twenty minutes fixing something that was never wrong.

Read the cell, match it to this table, then go to the section that deals with it.

ErrorWhat Excel is telling youGo to
#REF!A reference in the formula is no longer valid. What it pointed at is gone.Step 2
#VALUE!The formula was given the wrong kind of data, usually text where a number belongs.Step 3
#DIV/0!Something is being divided by zero, or by a cell that is empty.Step 4
#NAME?Excel does not recognize some text in the formula — a misspelled function, a missing quote mark, or a range name that is gone.Step 5
#N/AA lookup ran and found no match for the value you asked for.Step 5
#SPILL!The formula returned more than one result and the cells it needs are occupied.Step 6
#NUM!The formula asks arithmetic for something it cannot produce, or the result is too large to hold.Troubleshooting
#NULL!Two ranges were given as overlapping and they do not overlap.Troubleshooting
#####Not an error at all. The column is too narrow to display the number already in the cell.Troubleshooting

Note: If the cell shows no error but the number is wrong, the fault is in the formula's logic rather than in Excel's reading of it. Start at How to Write Your First Excel Formula if the formula is new to you, and at Understanding Absolute and Relative Cell References in Excel if the wrong number only appeared after you dragged the formula down a column.

2. Fix #REF! (Invalid Reference) ​

Excel returns #REF! when a formula points at a cell, row, column or sheet that no longer exists.

  1. Click the cell showing the error and read the formula bar. The dead reference is visible in the formula itself, as the literal text #REF! where a cell address used to be — for example =SUM(B2:#REF!).
  2. Press Ctrl + Z if deleting that row, column or worksheet was the last thing you did. Undo brings the deleted cells back, and every formula that referenced them recovers at the same time.
  3. Otherwise, click into the formula bar, replace the #REF! text with the address the formula should now point at, and press Enter. The cell shows a number again.
  4. Drag the corrected cell's fill handle — the small square at its bottom-right corner — across the neighboring cells if a whole column went the same way.

Note: A formula that returns zeros or the same answer in every row instead of an error has a different cause — a reference that moved when you dragged it. See Understanding Absolute and Relative Cell References in Excel.

3. Fix #VALUE! (Wrong Data Type) ​

#VALUE! means the formula was handed the wrong kind of data, most often text sitting where a number belongs.

  1. Click the cell and read the formula bar to see which cells the formula references.
  2. Look at those cells. Numbers stored as text sit against the left edge of the cell instead of the right, and often carry a small green triangle in the top-left corner.
  3. Select the whole column, then click Data > Text to Columns > Finish. Excel re-reads every value in the column as a number and the calculation below starts working. Clicking Finish on the first screen converts the values without splitting them, because there is nothing in the cells for the wizard to split on — and Ctrl + Z puts the column back exactly as it was if the result is not what you expected.
  4. For the stubborn cases — trailing spaces, currency symbols, non-breaking spaces pasted from a web page — see How to Fix Numbers Stored as Text in Excel.

4. Fix #DIV/0! (Divide by Zero) ​

#DIV/0! appears when a formula divides by zero, or by a cell that is empty.

  1. Click the cell and read the formula bar to find the divisor — whatever sits after the /.

  2. Look at that cell. An empty cell counts as zero here, so a formula waiting on a number you have not entered yet shows this error until the number arrives.

  3. Enter the missing value. The error is replaced by the result the moment you press Enter.

  4. Where the blank is expected — a percentage column in which some rows genuinely have no denominator — wrap the formula so it returns something readable instead:

    =IFERROR(A2/B2, "")

    Two double quotes with nothing between them leave the cell looking empty. Put 0 or "n/a" there instead if a visible marker suits the sheet better.

Warning: IFERROR hides every error type, not only this one. Build the plain division first and confirm it returns the right number, then wrap it. Wrap it too early and a genuine #REF! in the same formula disappears behind the same tidy blank.

Note: A total showing 0 with no error code at all is a different problem again. Check the bottom-left of the status bar for the words Circular References — that means the formula is adding up a range that includes its own cell. See How to Fix Circular Reference Warnings in Excel.

5. Fix #NAME? and #N/A (Not Recognized, Not Found) ​

Both errors mean Excel went looking for something and came back without it, but they look in different places.

#NAME? — Excel does not recognize text in the formula.

  1. Check the spelling of the function name. =SOM(A1:A10) returns #NAME?; =SUM(A1:A10) does not. If the formula is new to you, How to Write Your First Excel Formula walks through the parts from the equals sign up.
  2. Check for missing quote marks around text. =IF(A1=Yes,1,0) returns #NAME? because Excel reads Yes as the name of something; =IF(A1="Yes",1,0) works.
  3. Check whether the formula uses a named range that has been deleted or renamed — see How to Use Named Ranges in Excel.
  4. Check whether the formula uses a function your copy of Excel does not have. Functions that are not available in an earlier version return #NAME? when the workbook recalculates there, and the formula bar may show the prefix _xlfn. in front of the function name.

#N/A — a lookup found no match.

  1. Confirm the value you are searching for really exists in the column being searched, spelled identically. A single trailing space is enough to break the match.
  2. Confirm both sides are the same data type. A product code stored as text on one sheet and as a number on the other never matches, however identical the two look on screen.
  3. Wrap the lookup once it works: =IFNA(VLOOKUP(G2,$A$2:$D$500,4,FALSE), "Not found") shows readable text in place of the error. IFNA catches only #N/A and leaves every other error visible, which is what you want while you are still building the sheet.

6. Fix #SPILL! (The Answer Has Nowhere to Go) ​

#SPILL! means the formula returned more than one result and the cells those results need are not empty.

#SPILL! only exists in Excel for Microsoft 365, Excel 2024, Excel 2021 and Excel on the web. Excel 2019 and Excel 2016 have no dynamic arrays, so they never return it — a formula from a newer Excel returns #NAME? there instead (Step 5).

Functions such as XLOOKUP, FILTER, UNIQUE and SORT can return a whole block of values from a single formula, and Excel writes them into the cells below and to the right of the one you typed in. That block is the spill range. If anything already sits in it — a value, a stray space, a merged cell — Excel returns #SPILL! rather than overwriting what is there.

  1. Click the cell showing the error. A dashed border appears around the range the formula was trying to fill.
  2. Click the warning icon that appears beside the cell and choose Select Obstructing Cells. Excel jumps straight to whatever is in the way.
  3. Read what you find before removing it — Excel is protecting real content here, and a cell that looks empty may hold a space someone typed by hand. Move it somewhere else rather than deleting it if you are unsure; the formula only needs the range clear, not the data gone.
  4. The results appear across the range the moment the obstruction is cleared.

Note: Merged cells are the version of this that catches people out most, because a merged block cannot hold a spilled result. Select the merged cell, click Home > Merge & Center to unmerge it, and the formula spills. Unmerging keeps the text — it moves into the top-left cell of the block.

7. Trace an Error Back to the Cell That Caused It ​

When the formula in front of you looks correct, the fault is in a cell it depends on.

  1. Click the cell showing the error.
  2. Click the Formulas tab, then Trace Precedents in the Formula Auditing group. Arrows appear pointing from every cell that feeds the one you selected.
  3. Read the arrow colors. Blue arrows come from cells with no errors; red arrows come from the cells causing the error. Follow the red one.
  4. Click Trace Precedents again to step one level further back, and repeat until an arrow lands on the cell where the problem actually started.
  5. Click Remove Arrows in the same group when you are finished. This clears the arrows only — no formula, value or format changes.

Tip: For a long formula where the error appears somewhere in the middle, click Formulas > Evaluate Formula and press Evaluate repeatedly. Excel works through the formula one piece at a time and shows you the exact step at which the error first appears. This button is on Windows only.

Troubleshooting ​

TIP

A cell full of ##### is not an error. The column is too narrow to display the number already inside it — double-click the right-hand edge of the column header and the value appears.

Symptom / ErrorPotential CauseSolution
#NUM!Invalid numeric valueThe formula asks arithmetic for something it cannot produce — the square root of a negative number, for instance — or the result is too large for Excel to hold. Check the numbers going in rather than the formula.
#NULL!Ranges do not intersectA space between two ranges tells Excel to use only the cells they share. =SUM(A1:A5 C1:C5) returns this because those two ranges never touch. Replace the space with a comma.
#####Column too narrowDouble-click the right-hand boundary of the column header to fit the column to its contents.
A total shows 0, no error codeCircular referenceThe SUM range includes the total cell itself. See How to Fix Circular Reference Warnings in Excel.
The cell shows the formula text, not a resultCell was formatted as Text before typingSet the cell to General in the Number group of the Home tab, then double-click the cell and press Enter to re-enter the formula.
Every formula on the sheet shows its textShow Formulas is switched onClick Formulas > Show Formulas to switch it back off. This is a display toggle — no formula or value is affected either way.
#NAME? only after a colleague opened the fileFunction missing from their Excel versionTheir version never shipped that function. Rebuild the formula with one both versions have. To find out which version they are on, ask them to read File > Account > Product Information — that is more reliable here than the Compatibility Checker, which Microsoft documents as checking against Excel 2013, 2010 and 2007 rather than 2016 or 2019.

Last updated:

Frequently asked questions

What does #REF! mean in Excel?
It stands for invalid reference. The formula is pointing at a cell, row, column or sheet that was deleted and no longer exists, and Excel writes the text #REF! into the formula itself where the address used to be. Undo brings the deleted cells back if the deletion was your most recent action.
How do I fix a #VALUE! error?
A #VALUE! error means you are doing math on a cell that holds text. Numbers stored as text sit against the left edge of the cell instead of the right and often carry a small green triangle. Select the column and click Data, then Text to Columns, then Finish to convert the whole column back to numbers.
What does a #SPILL! error mean?
The formula returned more than one result and the cells those results need are not empty. Click the cell to see a dashed border around the range Excel was trying to fill, then use Select Obstructing Cells in the warning menu to jump straight to whatever is in the way and clear it.
Why does my formula show #NAME? after someone else opened the file?
The workbook uses a function that the version of Excel opening it does not have. Functions that are not available in an earlier version return a #NAME? error when the sheet recalculates, and the formula bar may show the prefix _xlfn in front of the function name. Rebuild that formula with a function both versions have.