No Ticket Needed

Appearance

4 min fix 3 min read Reviewed 6 months agoBeginner

By , IT support technician

Quick Answer

Click the cell with the error, check the formula bar to see which cells it references, and ensure you haven't deleted a required column or tried to calculate text as a number.

How to Fix Common Excel Formula Errors ​

Applies to: Microsoft Excel 365, Excel 2019/2021
Article Type: Troubleshooting
Last Updated: 2026-01-14

Summary ​

Formula errors like #REF!, #VALUE!, and #DIV/0! stop your calculations. This guide explains what they mean and how to fix them.

Prerequisites ​

  • None. These steps apply to all versions of Excel.

Instructions ​

1. #REF! (Reference Invalid) ​

This happens when you delete a cell that was part of a formula.

  1. Click the cell with the error.
  2. Look at the formula bar.
  3. If you see #REF!, press Ctrl + Z (Undo) if you recently deleted something.
  4. Otherwise, re-type the formula to point to valid cells.

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.

2. #VALUE! (Wrong Data Type) ​

You are trying to do math on text (e.g., 10 + "Apple").

  1. Check the cells referenced in the formula.
  2. Look for numbers stored as text — they sit against the left edge of the cell and often carry a small green triangle in the corner.
  3. Re-type the data as a number, or select the column and run Data > Text to Columns > Finish to convert the whole lot at once. 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.

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

You cannot divide a number by 0 or an empty cell.

  1. Check the cell you are dividing by.
  2. If it is empty, fill it with a number.
  3. To hide the error, wrap your formula in IFERROR: =IFERROR(A1/B1, 0) – This will show "0" instead of the error.

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.

Troubleshooting ​

TIP

Use the "Trace Precedents" tool under the Formulas tab to see arrows pointing to the cells causing the error.

ErrorMeaningQuick Fix
#NAME?TyposCheck spelling of function names (e.g., SUM vs SOM). A range name you defined can also go missing — see How to Use Named Ranges in Excel.
#N/ANot FoundVLOOKUP couldn't find the value. Check your range.
A total shows 0, no error codeCircular referenceThe SUM range includes the total cell itself. See How to Fix Circular Reference Warnings in Excel.