Start for free

All Excel functions

#NUM! in Excel: what it means and how to fix it

#NUM! means a calculation doesn't produce a valid number, such as the square root of a negative number or a result that's too large.

Start practising hereIn the lesson Order of operations and brackets, free

What does #NUM! mean?

#NUM! means that a calculation doesn't produce a valid number. Excel can read the formula, but the result doesn't exist or is too large.

The best-known cause is the square root of a negative number: =SQRT(-16) has no result. You also get the error if you ask for something that isn't there, such as the fifth-largest value in a list of four numbers, or if the start date in DATEDIF is after the end date.

When do you get #NUM!?

  • You take the square root of a negative number: =SQRT(A2) with -16 in A2.
  • With LARGE, you ask for a place that doesn't exist: =LARGE(A2:A5,5) with four numbers.
  • DATEDIF gets a start date that's after the end date.
  • A calculation gets larger than Excel can store: about a 1 with 308 zeros.

How to fix #NUM!, step by step

This is how to fix the error:

  1. Click the cell with #NUM! and look in the formula bar to see which function and which cells the formula uses.
  2. Check the numbers: is there a negative number where that's not possible, such as in SQRT? Remove the minus sign with ABS, if that's what you intend.
  3. If you're asking LARGE or SMALL for a place, check that the list actually contains that many numbers.
  4. With DATEDIF, the earlier date has to come first. Swap the two dates.

Examples

The square root of a negative number

C1 contains =SQRT(A2). A negative number has no square root, so Excel gives #NUM!.

C1
A
B
C
D
E
F
G
H
1
Number
#NUM!
2
-16
3
4
5
6
7
8
9
10
Ready

Fixed with ABS

C1 contains =SQRT(ABS(A2)). ABS first removes the minus sign: the square root of 16 is 4. Only do this if the minus sign really doesn't matter.

C1
A
B
C
D
E
F
G
H
1
Number
4
2
-16
3
4
5
6
7
8
9
10
Ready

A place that doesn't exist

C1 contains =LARGE(A2:A5,5). There are only four scores in the list, so a fifth-largest doesn't exist. With 1 to 4, it works.

C1
A
B
C
D
E
F
G
H
1
Score
#NUM!
2
14
3
17
4
11
5
15
6
7
8
9
10
Ready

Where it often goes wrong

  • LARGE and SMALL give #NUM! if you ask for a position higher than the number of values in the list.
  • DATEDIF gives #NUM! if the start date comes after the end date.
Want to try it yourself?
Practise this in the lesson Order of operations and brackets. It's free.

Start practising here

Frequently asked questions

Can I hide #NUM! with IFERROR?

You can, but first find out why the calculation fails. Often one of the cells holds the wrong number.

Read next

#VALUE!#N/A#DIV/0!#NAME?#REF!