← VCE Data Analytics U3O1

Formula error values and how to fix them

A message starting with # (such as #DIV/0!, #N/A, #VALUE! or #REF!) that a cell shows when a formula fails. It hints at what went wrong so you can fix the formula or data.

Part of: VCE Data Analytics - U3O1 - Data Analytics — Module 80, ACARA Digital Technologies Years 9–10 — Acquiring and Validating Data — Module 105

Understanding Formula error values and how to fix them in VCE Data Analytics U3O1

What it does: IFERROR returns the second argument when the first argument gives an error, so the cell shows Check data instead of #DIV/0!. Deleting cells that a formula depends on leaves an invalid reference, so the formula shows #REF! until the reference is fixed. #DIV/0! means the formula tried to divide by zero or by an empty cell. Fix the divisor or check it before dividing. #N/A means the value could not be found. Check the spelling of the lookup value and the range being searched. #VALUE! means the formula has the wrong type of data, such as text where a number is needed. Fix or clean the data in C2.

Common mistakes: The lookup range A2:C9 has only 3 columns, so column index 4 is outside it and the formula returns #REF!. The prices are in column 3 of the range, so the index should be 3. IFERROR takes the formula to test first and the fallback value second. With the arguments swapped it always returns 0 and never shows the MATCH result. It should be =IFERROR(MATCH(A2,G:G,0),0).

Class: _____________________  ·  Name: _____________________  ·  Date: _________

Free printable worksheet with full answer key — or add this module to a class and your students practise it interactively.

🖨 Print worksheet + answer key Add Formula error values and how to fix them to a class →

This module's key terms are also in a printable crossword (Modules 50–91) and word search (Modules 17–91).

Take this worksheet further — free with Cloodit

Each concept is reinforced from every angle — learned, then applied, then tested.

Learning
Duo
A formula divides by an empty cell. Which error value appears?
#REF!#DIV/0!
Matches
=B2+C2 where C2 contains the text "n/a"
↓
Adding a text value with + gives #VALUE! because the formula expects a number. Replace the text with a number or clean the data.
Applying
Meanings
Term
error value
Meaning
A message starting with # (such as #DIV/0!, #N/A, #VALUE! or #REF!) that a cell shows when a formula fails. It hints at what went wrong so you can fix the formula or data.
Practice
=IFERROR(B2/D2,"No rain")
Testing
Gaps
=(B2/C2,"Check C2")
Check Answer
Spots
3F2 holds the item to find
4G2: =VLOOKUP(F2,A2:C9,4,FALSE)

Cloodit turns curriculum-aligned coding content into journeys students work through one biome at a time.

The 'Void Whale' badge — Definition Drifter, one of Cloodit's 106 collectible badges
Students earn collectable badges

Over 100 of them, across seven natural biomes.

Study Buddy
Students build a real study resource

Turns their work into revision material ready for Flash Cards and Exam study.

Diamond Dash — one of Cloodit's live, competitive Sprint games
Students lock in with Sprints

Live, competitive practice for the start of the lesson or a teacher-led lesson finisher.

A Cloodit certificate of achievement, ready to personalise and print
Custom printable certificates

Honour your top performers, or encourage a student who's trying hard — ready to print in seconds.

Add to class →

Quick Questions

1. A formula divides by an empty cell. Which error value appears?
A. #REF!
B. #DIV/0! ✓
#DIV/0! means the formula tried to divide by zero or by an empty cell. Fix the divisor or check it before dividing.
2. =XLOOKUP("Zara",A2:A9,B2:B9) and Zara is not in the list. What does the cell show?
A. #N/A ✓
B. #VALUE!
#N/A means the value could not be found. Check the spelling of the lookup value and the range being searched.
3. =B2+C2 where C2 contains the text "absent". Which error value is most likely?
A. #DIV/0!
B. #VALUE! ✓
#VALUE! means the formula has the wrong type of data, such as text where a number is needed. Fix or clean the data in C2.
4. You delete a column that a formula refers to. Which error value appears?
A. #REF! ✓
B. #N/A
#REF! means the formula refers to a cell that no longer exists. Re-enter the correct cell reference or undo the delete.
5. Which statement about error values is true?
A. An error value is a clue that helps you find what went wrong. ✓
B. An error value means the spreadsheet file is damaged and must be deleted.
Error values are messages starting with #. They tell you the type of problem so you can fix the formula or the data.

Match the Pairs

#DIV/0! → The formula tried to divide by zero or by an empty cell
#N/A → A lookup could not find the value it was searching for
IFERROR shows a friendly result instead of an error value → =IFERROR(B2/C2,"Check data")
#REF! appears when a formula refers to a cell that has been deleted → =SUM(B2:B5) after deleting column B
=B2+C2 where C2 contains the text "n/a" → Adding a text value with + gives #VALUE! because the formula expects a number. Replace the text with a number or clean the data.
=AVERAGE(B2:B9)/COUNTIF(B2:B9,">100") when no value is over 100 → COUNTIF returns 0, so the formula divides by zero and shows #DIV/0!. Wrap it in IFERROR or check the divisor first.

Key Term

error value
A message starting with # (such as #DIV/0!, #N/A, #VALUE! or #REF!) that a cell shows when a formula fails. It hints at what went wrong so you can fix the formula or data.

Fill in the Blanks

1. =IFERROR(B2/C2,"Check C2")
Hint: Type the function name that catches any error value and shows a replacement instead.
2. =XLOOKUP("Zed",A2:A9,B2:B9) Zed is not in the list, so it shows #N/A
Hint: Type the error value shown when a lookup cannot find a match.
3. =B3*2 Row 3 was deleted, so it shows #REF!
Hint: Type the error value shown when a formula points to a cell that no longer exists.
4. =B2/C2 C2 holds 0, so the cell shows #DIV/0!
Hint: Type the error value shown when a formula divides by zero.
5. =A2+B2 A2 holds text, so the cell shows #VALUE!
Hint: Type the error value shown when a formula uses the wrong type of data, such as text in a sum.

Spot the Error

Question 1
A Find a price by item name
B Items A2:A9, prices C2:C9
C F2 holds the item to find
D G2: =VLOOKUP(F2,A2:C9,4,FALSE)
Line D — The lookup range A2:C9 has only 3 columns, so column index 4 is outside it and the formula returns #REF!. The prices are in column 3 of the range, so the index should be 3.
Question 2
A =IFERROR(0,MATCH(A2,G:G,0))
B Shows 0 if A2 is not in G:G
C No #N/A will be displayed
Line A — IFERROR takes the formula to test first and the fallback value second. With the arguments swapped it always returns 0 and never shows the MATCH result. It should be =IFERROR(MATCH(A2,G:G,0),0).
Question 3
A B2: 40 C2: 0
B D2: =IFERROR(B2/C2,none)
C Cell shows "none", not #DIV/0!
Line B — Text used as the fallback value in IFERROR must be in double quotes. Without quotes, Excel reads none as a name it does not know and gives #NAME?. It should be "none".
Question 4
A B2: 88
B C2: n/a
C D2: =IF(ISTEXT(C2),B2+C2,0)
D Shows 0 instead of #VALUE!
Line C — C2 holds text, so ISTEXT(C2) is TRUE and B2+C2 is calculated, which gives #VALUE!. The check should be ISNUMBER(C2), so that only numbers are added.
Question 5
A E2: =C2*D2
B Delete column D
C E2 becomes =C2*#REF!
D Fix: add a check for zero
Line D — #REF! means a referenced cell was deleted. Checking for zero fixes #DIV/0!, not #REF!. The fix is to undo the delete or re-enter the correct cell reference.

Student with a class code? Join your class →