All You Need to Know About #NUM Error in Excel | How to Correct It

This article is a full guide about the #NUM error in MS Excel. We will tell you what's the meaning of this error and how to correct it. For further use, you will also learn how to avoid the #NUM error in this post.

Table of Contents

When you received an Excel document and want to check its content, but only to find that there are some #NUM errors, what you want to do? Of course, you are wondering why this error happens and want to correct it immediately. This is also what we will introduce in this article, telling you why you would see this #NUM error in Excel and how to correct it. Keep reading and check more details.

What Does #NUM Mean in Excel and Why it Happens?

The #NUM error can appear for many reasons, generally, there are three common reasons.

Reason 1. Faulty Numbers

One of the main reasons why you see the #NUM error is that the number is either too big or too small. Excel has limits on the smallest and largest numbers users can use. If the number you enter is out of this range, you will see it shows as #NUM. For your information, the smallest available number in Excel is -1*10^308, which means a -1 with 308 zeros after it. The largest available number is 1*10^308, which means a 1 with 308 zeros after it.

Reason 2. Impossible Calculation

Another possible reason is you might perform an impossible calculation, and Excel can only display a #NUM error. For example, when you perform a wrong formula, you will get a #NUM. The below image shows when users want to take the square root of a negative number in Excel, it shows the #NUM error.

Excel #NUM error

Reason 3. Iteration Formula Can't Find Valid Result

An iterative formula is a formula that performs the same calculation repeatedly. An example of this is the IRR function, which calculates the internal rate of return for a series of cash flows. IRR performs calculations cyclically until the result is accurate to within 0.00001%. If IRR cannot find a valid result after 20 attempts, Excel will display a #NUM error.

How to Correct #NUM Error in Excel

There are different solutions for #NUM errors caused by different causes. In this part, we will tell you how to do it one by one.

1.Solution for Faulty Numbers and Impossible Calculation

If you see the error because the number is out of the Excel limitation range or you perform an impossible calculation. Then the only solution is to check your number and formulas and change them to the right one.

2.Solution for Iteration Formula Can't Find Valid Result

If you perform a formula like IRR or RATE, and you see the #NUM error, you can apply the following steps to change the number of times Excel iterates formulas.

Step 1. Open MS Excel, go to File > Options.

Step 2. Click the Formulas tab, and check the Enable iterative calculation box under Calculation options.

Step 3. Type a large number in the Maximum Iteration box. The higher the number of iterations, the more time Excel needs to calculate a worksheet.

Step 4. Enter the amount of change you can accept between calculation results in the Maximum Change box. You should keep the number as small as possible.

Excel iteration calculation

related articles

How to Fix Runtime Error 1004 in Excel?

How to Fix Runtime Error 1004 in Excel? There are four effective methods for you to choose from.

Excel runtime error 1004

FAQ About Excel Number Sign (#) Error

1. What's the meaning of Excel # errors?

  • #DIV/0 error: division by zero error
  • #N/A error: No value available
  • #NAME?: Excel doesn't recognize a name
  • #NULL!: You specified an intersection of two cell ranges whose cells don't actually intersect
  • #NUM!: Problem with a number in the formula
  • #REF!: Invalid cell reference
  • #VALUE!: Wrong type of argument in a function or wrong type of operator

2. How to remove #NUM error in Excel?

First check if the number exceeds the Excel limitation, then check if you apply the correct formula. At last, go to the Excel file option to change the iterative calculation settings.

3. How to fix #REF! error in Excel?

If you have deleted the cells referenced by formulas, your excel will show #REF! error because the cell's data is invalid.

To fix the error: Press Ctrl + Z to undo the delete operation. If the Ctrl + Z works, this error will be corrected.

To delete the error: Press Ctrl +F to find #REF! error, and type blank to replace it.

Bonus Tip: How to Recover Lost Excel Files

Files can be lost or deleted in many situations. For example, when you working with an Excel document, a sudden computer shut down could let you lost your file. Besides, accidentally format a hard drive or virus attack will also lead to data loss.

This is why we want to recommend EaseUS Data Recovery Wizard here, no matter how you lost your files, this data recovery software will find and recover the files for you.

Here is the example of how EaseUS Data Recovery Wizard recovers the Excel document, you can check the whole process and download the tool to have a free trial.

Step 1. Select location and click "Scan"

Choose the drive X: or a specific folder on Desktop or somewhere else where you lost the excel files, and then click "Scan".

select a location and click scan

Step 2. Locate your excel files

To quickly find the Excel files from the scan results, click on the "Filter" on the top area, choose "Excel", and click tree view on the left side. Or, search .xls or .xlsx in the Search box. 

scan excel files

Step 3. Preview and recover

You can double click on the Excel file to preview its integrity. Finally, select the desired Excel files and click "Recover".

preview and recover excel files

Was This Page Helpful?

Updated by Gemma

Gemma is member of EaseUS team and has been committed to creating valuable content in fields about file recovery, partition management, and data backup etc for many years. She loves to help users solve various types of computer related issues.

Read full bio

Product Reviews

TrustScore 4.7 | 323 reviews
  • EaseUS Data Recovery Wizard is a powerful system recovery software, designed to enable you to recover files you’ve deleted accidentally, potentially lost to malware or an entire hard drive partition.

    Read More
  • EaseUS Data Recovery Wizard is the best we have seen. It's far from perfect, partly because today's advanced disk technology makes data-recovery more difficult than it was with the simpler technology of the past.

    Read More
  • EaseUS Data Recovery Wizard Pro has a reputation as one of the best data recovery software programs on the market. It comes with a selection of advanced features, including partition recovery, formatted drive restoration, and corrupted file repair.

    Read More