BeginnerLesson 9 of 305 min read

Common mistakes, error messages and good habits

Read Excel's error messages, avoid the quiet mistakes that break totals, and build habits that keep your sheets correct.

In this lesson you will

  • Read common error values
  • Spot hidden problems like extra spaces
  • Cross-check totals
  • Adopt safe working habits

About 14 minutes

The sheet that was almost right

Farah sends her club’s accounts to the treasurer. The treasurer finds the total is off by 700, and nobody can say why. After an hour of searching they discover a number typed over a formula, a value pasted as text and a hidden row. None of it was dramatic, and all of it was avoidable. This lesson lists the common problems, how to read Excel’s error messages and the habits that prevent mistakes before they happen.

Reading error messages

Excel shows an error value when a formula cannot give an answer. An error is useful: it tells you something needs attention. These were really produced in a test sheet:

Formula Result Meaning
=1/0 #DIV/0! Dividing by zero or an empty cell
=NOPE(1) #NAME? Excel does not know the name; check spelling
=1+”abc” #VALUE! Math on text that is not a number
=NA() #N/A Value not available (often a lookup finds nothing)

A fifth common one is #REF!, which appears when a formula points to a cell you deleted. Press Ctrl+Z, or rebuild the reference.

IFERROR, a helper to use carefully

IFERROR shows a message of your choice when a formula fails. The test =IFERROR(10/0,”check input”) returned check input. It is handy in reports for people who should not see raw errors. But it can hide real problems too, so fix the cause first, and do not wrap everything in IFERROR just to make red messages disappear.

Hidden troublemakers

  • Numbers stored as text (lesson 2). SUM skips them.
  • Extra spaces. In a test, LEN of ‘Delhi ‘ (with a trailing space) was 6, but after TRIM it was 5. Counting cells equal to ‘Delhi’ found 1 in the untrimmed pair and 2 after TRIM. Use TRIM to clean pasted text.
  • Hardcoded numbers inside formulas. A tax rate typed as 1.18 in twenty formulas is hard to change. Put it in one cell and point to it.
  • Typing over a formula. One cell with a typed number among formulas is a silent error. Use Ctrl+` to scan.
  • Hidden rows and columns that still count in totals.
  • Merged cells that stop sorting, filtering and copying from working.

Cross-check your totals

A cheap, powerful habit is to calculate the same thing a second way. In a test, the column E amounts 10, 20 and 30 total 60. Adding 18% to each and summing gave 70.8, and adding 18% to the total directly gave 70.8. The two routes agree, which is a good sign that the rows are right. When they disagree, you have found a problem before anyone else did.

Good habits

  1. Keep inputs apart from calculations, in clearly labelled cells at the top or on their own sheet.
  2. Label everything: headers, units and what each total means.
  3. Save versions: use names like budget-2026-03-v2.xlsx, and keep a backup copy elsewhere.
  4. Check the end of every copied formula (lesson 6).
  5. Estimate before trusting a result (lesson 3).
  6. Look at the whole sheet with Ctrl+` before you send it.
  7. Keep a notes cell with the date, source of the data and any assumptions.

Protecting your work

On the Review tab, Protect Sheet can lock formula cells so nobody types over them while leaving input cells open. Select input cells, open Format Cells, Protection, and untick Locked, then protect the sheet. Passwords can be forgotten, so keep a record of any you set, and do not use one for secrecy; it is a guard against accidents.

Practice

Open any sheet you have made. Press Ctrl+` and scan for typed numbers among formulas. Check for numbers stored as text, trailing spaces in a category column and merged cells. Fix what you find, then write a three-line notes cell stating where the data came from, when it was updated and what the totals mean.

Practice answer

A quick sweep: look for left-aligned numbers, use Ctrl+F to find spaces, and use Home, Find and Select, Go To Special, Constants to highlight typed numbers in a column of formulas. Anything highlighted that should be a formula needs fixing.

Common mistake

Wrapping every formula in IFERROR to hide errors. The error was telling you something was wrong. Find out what, fix it, and only then decide whether a friendly message is needed.

Keeping a change log

When several people use a workbook, or when you return to it after months, a small change log saves hours. Add a sheet called Log with three columns: the date, what changed and who changed it. Write one line each time you change a formula, a rate or the structure. It takes ten seconds and answers the question ‘why did this number change?’ that always comes eventually. It also reminds you that spreadsheets are living documents, not one-off scribbles.

When to stop and ask for help

If a result looks wrong and you cannot see why after checking the basics, do not keep patching. Copy the problem into a small test sheet with only a few rows, and see whether it still goes wrong. A small example is easier to understand, easier to share with a colleague or an online help forum, and often reveals the cause on its own.

Check what you learned

Five quick questions. Pick an answer and you will see straight away whether it is right and why.

What does #DIV/0! mean?

  1. A missing sheet
  2. A damaged file
  3. A date problem
  4. A division by zero or an empty cell

Check the divisor.

What does TRIM do?

  1. Deletes a column
  2. Removes extra spaces from text
  3. Rounds a number
  4. Hides a row

It cleans pasted text.

Why avoid typing 1.18 inside many formulas?

  1. It is slow
  2. Excel refuses
  3. It is hard to change later
  4. It turns red

Put the value in one cell.

What is the benefit of calculating a total two ways?

  1. It reveals mistakes
  2. It doubles the answer
  3. It makes the file smaller
  4. It sorts data

Disagreement means a problem.

When is IFERROR a bad idea?

  1. When writing reports
  2. When naming files
  3. When it hides a real problem
  4. When sorting

Fix the cause first.

Summary

  • An error value is a clue; read it before hiding it.
  • Quiet mistakes: text numbers, spaces, typed-over formulas, hidden rows.
  • Cross-check by calculating a total a second way.
  • Keep inputs apart, label everything and save versions.

Next lesson

Next: your beginner project, a monthly budget tracker.

Questions

What does the green triangle mean?

Excel suspects a problem such as a number stored as text or a formula that differs from its neighbours. Click it to see the explanation.

How can I find which cell causes an error?

Select the cell with the error and use Formulas, Error Checking or Trace Precedents to see which cells feed it.

Can I recover a file I forgot to save?

Excel may offer a recovered version when you reopen it, and OneDrive keeps version history. Save often so you do not depend on it.

Progress is saved in this browser.