1 - Introduction ~5 mins
Welcome
Spreadsheets shout when something is wrong. Instead of a quiet wrong answer, they show error values such as {{code:#NAME!}}, {{code:#DIV/0!}}, {{code:#REF!}}, and {{code:#VALUE!}}. Today you learn what each one means, how it usually appears in real project sheets (budgets, VAT, hours trackers), and how to fix it before a reader, employer, or examiner sees it.
By the end of this lesson, you will be able to:
- Identify the causes of {{code:#NAME!}}, {{code:#DIV/0!}}, {{code:#REF!}}, and {{code:#VALUE!}}
- Fix each error type in a live sheet
- Document four errors with examples you caused and fixed in your own work
Open and check
Before we start, open your cloud storage and go to {{code:Project_Portfolio}}, then {{code:09_specialism}}, then {{code:sm3}}. Confirm you can open at least one Module 3 spreadsheet (for example a VAT sheet, hours tracker, or multi-sheet workbook). If that folder or file is missing, put your hand up: your teacher will give you the class practice workbook to copy into {{code:sm3}}.
Warm-up
Think of the last time a spreadsheet cell turned red or showed a strange code starting with {{code:#}}. What were you trying to calculate, and did you know how to fix it, or did you delete the formula and start again?
2 - Key Concepts ~7 mins
Error values are the spreadsheet's way of saying "this formula cannot give a number yet". They are more useful than a silent wrong total, because each code points to a different kind of mistake. Learn the four common ones below, then you will sort real scenarios into the right bucket as a class.
| Concept | Why it matters | Example |
|---|---|---|
| #NAME! — the spreadsheet does not recognise a function or named item in the formula | Usually a spelling typo; the sheet cannot run a function that does not exist | You type {{code:=AVARAGE(B2:B10)}} in a Work Experience hours sheet instead of {{code:=AVERAGE(B2:B10)}} |
| #DIV/0! — a formula is dividing by zero or by a blank cell treated as zero | Breaks unit costs, rates, and "cost per person" maths the moment the bottom of the fraction is empty | {{code:=B2/C2}} where C2 is units sold and the row is still blank before stock arrives |
| #REF! — a formula points at a cell, row, or column that no longer exists | Common after deleting a column you thought was unused; every dependent formula breaks at once | You delete column C (VAT rate) while {{code:sm3_vat_calc}} formulas still reference {{code:C2}} |
| #VALUE! — the formula expects a number (or another type) but finds the wrong type of data | Stops totals when a label, "N/A", or leftover text sits in a number column | {{code:=A2*B2}} multiplies price by quantity, but A2 still says {{code:pending}} from a draft note |
Sample Error Reference (all four rows)
Your portfolio page will look like this. Each row shows cause, fix, and what the cell should show after the fix:
| Error | What it means | How I caused it (in my sheet) | How I fixed it |
|---|---|---|---|
| {{code:#NAME!}} | Function name not recognised | In my hours tracker I typed {{code:=AVARAGE(B2:B10)}} | Edited the cell to {{code:=AVERAGE(B2:B10)}}, pressed {{key:Enter}}; cell showed a normal average (for example 6.5) |
| {{code:#DIV/0!}} | Division by zero or a blank divisor | I put {{code:=D2/E2}} before E2 (days worked) had a number | Entered a real day count in E2; rate recalculated to a normal number |
| {{code:#REF!}} | Broken cell reference | On a practice copy I deleted column C while a formula still used {{code:C2}} | Used Undo, or rewrote the formula to the surviving cells (for example {{code:B2}}); cell showed a normal value again |
| {{code:#VALUE!}} | Wrong data type in the maths | {{code:=A2*B2}} while A2 still said {{code:pending}} | Replaced {{code:pending}} with a real price number; product cell showed a normal total |
3 - Which Error Is This? ~12 mins
As a class you will sort eight real spreadsheet mishaps into the error value each one produces. First talk the placements through out loud. Only then drag the cards and check. Be ready to say why a card belongs where you put it.
Which error is this?
4 - Common Issues ~3 mins
Common Issues
| Issue | Solution |
|---|---|
| I fixed the formula but the error is still showing | Click the cell, press {{key:Enter}} to commit the edit, then check neighbouring cells that might still hold the old formula |
| I see {{code:#REF!}} after I deleted a column I thought was empty | Use Undo if you still can. If not, rewrite the formula to point at the cells that remain, or restore the column from an earlier file version |
| I cannot tell {{code:#NAME!}} from {{code:#VALUE!}} | {{code:#NAME!}} is almost always a misspelled function ({{code:SUMM}}, {{code:AVARAGE}}). {{code:#VALUE!}} means the function name is fine but a cell holds text, blanks-as-text, or the wrong type for the maths |
| My sheet shows {{code:#DIV/0!}} on rows I have not filled yet | That is expected until the divisor cell has a real number. Enter the missing value, or leave those rows blank until the data exists so a reader is not alarmed |
5 - Guided Practice: Cause and Fix Together ~8 mins
As a class you will safely break and repair one formula on a practice copy of a sample workbook. Watch the board first, then match the clicks on your own machine. You will cause {{code:#NAME!}}, see the error, fix the spelling, and watch the cell return to a normal value. Your teacher will also fill one sample Error Reference row live so you know what "good" documentation looks like.
How to cause each error on a copy (quick reference)
- {{code:#NAME!}} — type {{code:=AVARAGE(B2:B10)}}, press {{key:Enter}}, then correct the spelling to {{code:=AVERAGE(B2:B10)}} and press {{key:Enter}} again.
- {{code:#DIV/0!}} — type {{code:=B2/C2}} with C2 blank (or 0), then enter a real number in C2.
- {{code:#REF!}} — on the copy only, note a formula that uses column C, delete that column, then Undo or rewrite the formula to a surviving cell.
- {{code:#VALUE!}} — put {{code:pending}} in a price cell, multiply it by a quantity, then replace {{code:pending}} with a number.
Unlock the full learning experience
You're previewing this lesson. Get full access to this lesson and hundreds more — each one ready to teach, with interactive activities, printable resources and pupil progress tracking built in.
This lesson is copyright of Coding Ireland 2017 - 2025. Unauthorised use, copying or distribution is not allowed.