Excel errors / #NULL!
#NULL! error in Excel
Two ranges that were expected to cross do not.
#NULL! is the rarest of Excel's errors, and almost always a typing slip. In a formula, a space between two ranges is an operator: it means "the cells these two ranges have in common". =SUM(A1:C3 B2:D4) adds the cells that are in both.
When the two ranges share no cells, the answer is #NULL!. Hardly anyone uses the space on purpose, so in practice the error means a space was typed where a comma or a colon belongs.
Have a formula that shows #NULL!? Paste it into the formula checker with a few rows of your data. It points at the part that fails and says why.
3 causes of #NULL!, and how to fix each
A space where a comma belongs
=SUM(A2:A5 C2:C5)asks for the cells that A2:A5 and C2:C5 share. They are different columns and share none.How to fix it. Separate the ranges with a comma:
=SUM(A2:A5, C2:C5).A space where a colon belongs
=SUM(A2 A5)asks for the cell that A2 and A5 have in common, and two different cells have none.How to fix it. Join the two corners of the range with a colon:
=SUM(A2:A5).An intersection that is empty
Used on purpose, the space picks the cell where a row and a column meet, often with named ranges:
=March Sales. If the two do not cross, there is no such cell.How to fix it. Check that both ranges cover the cell you expect them to share.
Common questions
How do I fix #NULL! in Excel?
Look for a space between two cell references in the formula. Replace it with a comma if they are separate ranges to be used together, or with a colon if they are the two ends of one range.
Practise it
Functions involved
- SUM: Adds up numbers, cells and ranges.
- INDEX: The value at a given row and column of a range.
- XLOOKUP: Searches a range and returns the matching item from another range. The modern replacement for VLOOKUP.
Other errors
- #N/A: A lookup could not find the value it was given.
- #VALUE!: The formula was given the wrong kind of value, such as text where it needs a number.
- #REF!: The formula refers to a cell that no longer exists.
- #DIV/0!: The formula divides by zero or by an empty cell.
- #NAME?: Excel does not recognise a word in the formula.
- #NUM!: The calculation has no valid number as its answer.
- #SPILL!: A formula that returns several values has no room to put them.
- #CALC!: A formula that returns a list ended up with nothing to return.
- #####: The column is too narrow to show the number, or a date is negative.
- Circular reference: A formula depends on its own result.