Skip to content
Tutorials Center
My learning

Topics

Lesson 10 of 29 ยท Formulas and Functions

Understanding Formula Errors

Learn what #DIV/0!, #N/A, #REF!, #VALUE!, #NAME? and circular reference errors mean in Google Sheets, and how to trace and fix each one.

Everyone sees errors in a spreadsheet. A cell turns into #DIV/0! or #N/A, a red corner appears, and it feels like the sheet is broken. In fact, an error is the sheet telling you something specific: this formula cannot produce a sensible answer, and here is the category of reason.

Once you can read the message, most errors take under a minute to fix. This lesson goes through the common errors, shows what causes each one, and gives you a repeatable method for tracing a problem back to its source. It assumes you can already write formulas (Your First Formulas) and use functions such as IF and IFERROR.

The six errors you will meet

#DIV/0! Divided by zero or blank.
#N/A A lookup found nothing.
#REF! A referenced cell is gone.
#VALUE! Wrong kind of data.
1 / 2

#DIV/0!: division by zero

Dividing by zero is undefined, and a blank cell counts as zero. =B2/C2 fails when C2 is 0 or empty. This often appears on a new row that has a formula but no data yet, or in an average of an empty range (AVERAGE of nothing divides by zero items).

Fix it by deciding what a missing divisor should mean:

TEXT
=IF(C2=0, "", B2/C2)
=IFERROR(B2/C2, "n/a")

The IF version is more precise because it only reacts to the zero case, so a typo elsewhere in the formula still shows up.

#N/A: not available

#N/A means "a value could not be found". The classic source is a lookup that has no match: =VLOOKUP("Pear", A2:B10, 2, FALSE) when "Pear" is not in column A. See VLOOKUP, HLOOKUP and Their Limits. Other causes are functions that need ranges of equal size, and IFS or SWITCH formulas with no matching branch.

Before wrapping the formula, check the data. Typical culprits are trailing spaces ("Pear " does not equal "Pear", so try TRIM), text versus number mismatches (101 against "101"), and a lookup range that does not include the row you expect. Only after that, use IFNA(formula, "not found") for the lookups that may legitimately fail.

#REF!: broken reference

#REF! appears when a formula points at a cell that no longer exists. You deleted a column that a formula used, or you pasted over a cell something depended on. The formula text itself shows the damage: =SUM(A2:#REF!).

Two ways out. Immediately after the mistake, press Ctrl+Z (Cmd+Z on Mac) to undo. Later, open the cell, replace the #REF! token with the right reference and press Enter. Prevention: insert rows and columns rather than deleting those that other formulas read, and use a range like A2:A or a named range that you re-point. A #REF! can also come from a reference to another sheet that is not allowed yet, such as an IMPORTRANGE that needs permission. Hover over the cell and click Allow access when Sheets offers it.

#VALUE!: wrong type of data

A formula got something it cannot work with, such as adding a number to text: =A2+B2 when B2 contains "n/a". A range passed where a single value is required also causes it, as does a function whose argument is out of its allowed range.

Look at each input cell. Is it text that looks like a number, left-aligned? Does it hold a space? Use VALUE(A2) to convert numeric text, TRIM to clear spaces, or N() to turn text into zero. The aggregate functions covered in SUM, AVERAGE, MIN, MAX and COUNT skip text in ranges, but the + operator does not, which is why =A2+B2 fails when =SUM(A2,B2) may not.

#NAME?: unknown name

Sheets does not recognize something you typed. The usual reasons are a misspelled function (=SUMM(A1:A5)), text without quotes (=IF(A1>5, High, Low) treats High as a name), or a named range that was renamed or deleted. Check spelling, add the missing quotes, and use the autocomplete popup that appears as you type a function name.

Circular references

If a formula refers to its own cell, directly or through other cells, Sheets cannot calculate it: =A1+1 entered in A1, or A1 reading B1 while B1 reads A1. The cell shows #REF! with a message about a circular dependency.

Find the loop by clicking the cell and reading its formula, then each cell it mentions. The fix is to break the cycle: keep inputs and results in different cells, or move the running total into the next row (=B2+C3 in row 3 instead of a cell that adds to itself). Iterative calculation can be switched on under File > Settings > Calculation, but it is meant for special models, and it hides genuine mistakes in everyday sheets.

A method for tracing errors

When a formula returns an error and the cause is not obvious:

  1. Read the message. Match it to the list above.
  2. Click the cell and look at the formula bar. Sheets colors each reference and outlines the matching cells on the grid, so you can see what it reads.
  3. Check every input cell. Is it empty, text, or itself an error? Errors propagate: if A2 is #N/A and B2 is =A2*2, then B2 is #N/A too. Fix the cell at the start of the chain.
  4. Break the formula apart. Test each nested function in a scratch cell.
  5. Use Ctrl+` (grave accent) to show all formulas in place, which makes patterns of mistakes easy to see.

The Sheets screens described here are read-only, since a spreadsheet app cannot run on this page. The TryIt blocks below are JavaScript stand-ins that show the same ideas: errors as values, propagation, and loop detection.

JS Stand-in: errors spread through formulas

Press Run (or Ctrl + Enter) to see the console output here.

No output.

JS Stand-in: detecting a circular reference

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Preventing errors

Good habits cut errors dramatically. Put inputs (rates, thresholds) in labeled cells instead of typing numbers into formulas. Use data validation so people cannot type text into number columns. Guard divisions and lookups with IF, IFNA or IFERROR only where failure is expected, and leave real problems visible. Finally, test a new formula on a row with blanks, zeros and text before copying it down 1,000 rows.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which error does =B2/C2 produce when C2 is empty?

    An empty cell is treated as zero in arithmetic, and dividing by zero gives #DIV/0!.
  2. 2. You delete a column that a SUM formula was reading. What do you most likely see?

    Deleting cells a formula points to leaves a broken reference, shown as #REF! inside the formula.
  3. 3. A VLOOKUP returns #N/A even though the item looks like it is in the list. What is a likely cause?

    Lookups need exact matches. An invisible space or a number stored as text makes the values different.
  4. 4. B2 is #N/A and C2 is =B2*2. What does C2 show?

    Errors propagate. Fix the first cell in the chain and everything that depends on it recovers.

Summary

  • #DIV/0! is division by zero or blank, #N/A a missing lookup match, #REF! a broken reference, #VALUE! a wrong data type, #NAME? an unknown name.
  • A circular reference means a formula depends on its own result; break the cycle by separating inputs and results.
  • Errors propagate through dependent formulas, so trace back to the first bad cell.
  • Check the data (spaces, text numbers, deleted cells) before hiding an error with IFERROR or IFNA.
  • Guard only the failures you expect and keep real mistakes visible.

Next: VLOOKUP, HLOOKUP and Their Limits