Lesson 1: Read the Error Value

Welcome, Error Detective

This course is independent of Microsoft and is not endorsed by it. It teaches Excel in Microsoft 365 on Windows and notes where Excel for the web, Excel for Mac and Excel 2021 or 2019 behave differently.

An error value is Excel's way of telling you what went wrong, much like a fault code on a car dashboard. Each code points to a different kind of failure. Once you can read the code, you know where to start looking. In this level you will:

1. Build a small practice table.
2. Cause each of the nine error values on purpose.
3. Learn which errors exist only in versions with dynamic arrays.

Step 1: Build the Practice Table

Open a blank workbook and type this into cells A1:C5:

Put the test formulas below in column E, starting at E2, one per row. Leave column F and the cells below each test empty unless a step tells you to type something there.

Step 2: Reproduce the Errors Found in Every Version

These seven errors appear in every current version of Excel. For each one, type the formula, read the result, then go on to the next.

1. #DIV/0!: a number was divided by zero or by an empty cell.
   • Try: =B3/C3 (800 divided by 0).
   • Common causes: a zero in the divisor, or a divisor cell that has not been filled in yet.
2. #N/A: a value that was looked up could not be found.
   • Try: =MATCH("Zed",A2:A5,0) or =VLOOKUP("Zed",A2:B5,2,FALSE).
   • Common causes: the key really is missing, or it is spelled or formatted differently from the key in the table.
3. #VALUE!: a value has the wrong type for the operation.
   • Try: =A2+B2 (text plus a number).
   • Common causes: text in a cell used for arithmetic, or an argument of the wrong kind passed to a function.
4. #REF!: a reference is not valid.
   • Try: =INDEX(B2:B5,10) (the range has no 10th row) or =VLOOKUP("Ann",A2:B5,3,FALSE) (the range has no 3rd column).
   • Common causes: rows, columns or sheets that a formula referred to have been deleted, or an index points outside its range.
5. #NAME?: Excel does not recognise something you typed.
   • Try: =SUMM(B2:B5) or =IF(A2=Ann,1,0).
   • Common causes: a misspelled function or defined name, or text typed without quotation marks.
6. #NUM!: a number is not valid.
   • Try: =SQRT(-1) or =10^400.
   • Common causes: an argument a function cannot accept, such as the square root of a negative number, or a result that is too large or too small for Excel to show. Functions that work by iteration, such as IRR, can also return #NUM! when they fail to find a result.
7. #NULL!: two ranges were joined by a space but do not overlap.
   • Try: =SUM(B2:B3 B5).
   • Common causes: a space typed where a colon (B2:B5) or a comma (B2:B3,B5) was meant. In Excel, a space is the intersection operator.

Step 3: Reproduce the Dynamic-Array Errors

Dynamic array formulas can return many values, which spill into the cells next to them. These two errors belong to that system.

1. #SPILL!: the result cannot spill onto the grid.
   • Try: type x in F3, then enter =SEQUENCE(3) in F2. The formula needs F2:F4, and F3 is in the way.
   • Common causes: a non-empty cell blocking the spill range, or a spill range whose size keeps changing and never settles, as with volatile functions.
   • Fix: select the error alert and choose Select Obstructing Cells, then clear or move the blocking entry.
2. #CALC!: the calculation engine hit a case it cannot handle.
   • Try: =FILTER(A2:B5,B2:B5>5000). No sales are above 5000, and Excel cannot return an empty set.
   • Common causes: an empty result (fix it with FILTER's third argument, if_empty, for example =FILTER(A2:B5,B2:B5>5000,"None")), an array nested inside an array, or a LAMBDA that is never called, such as =LAMBDA(x,x+1).

Step 4: Check Your Version

Whether you can see #SPILL! and #CALC! depends on your version of Excel.

• These versions have dynamic arrays and can show #SPILL! and #CALC!:
  • Excel for Microsoft 365 on Windows and Mac
  • Excel for the web
  • Excel 2021 and Excel 2024
• These versions have no dynamic arrays:
  • Excel 2019, 2016 and earlier
  • In these versions, =FILTER(...) or =SEQUENCE(3) returns #NAME?, because the function does not exist there.
• The other seven errors (#N/A, #VALUE!, #REF!, #DIV/0!, #NAME?, #NUM!, #NULL!) appear in all of these versions.

If a colleague using Excel 2019 opens your file and reports #NAME? where you see results, check whether the formula uses a dynamic-array function.

Quick Reference Checklist

When you see an error, ask:

• #DIV/0!: Is the divisor zero or blank?
• #N/A: Is the lookup value really in the table, spelled exactly the same way?
• #VALUE!: Is text being used as a number?
• #REF!: Was something deleted, or does an index go past the end of the range?
• #NAME?: Is something misspelled, missing quotes, or not available in this version?
• #NUM!: Is an argument impossible, or is the result too large?
• #NULL!: Is there a space where a colon or comma should be?
• #SPILL!: Is something blocking the spill range?
• #CALC!: Is the result empty, or is an array nested inside an array?

Key Takeaways

• Each error value names a category of problem. Read it before you start changing the formula.
• #DIV/0!, #N/A, #VALUE!, #REF!, #NAME?, #NUM! and #NULL! appear in every version.
• #SPILL! and #CALC! come with dynamic arrays: Microsoft 365, Excel for the web, and Excel 2021 and later.
• In Excel 2019 and earlier, dynamic-array functions return #NAME? instead.
• A space between ranges is the intersection operator, and ranges that do not overlap give #NULL!.
• FILTER's if_empty argument prevents the #CALC! error that an empty result would cause.