Table of Contents
- Best Data Recovery Software
- Video Recovery
- SD Card Recovery
- Recycle Bin Recovery
- Recover Data from USB
- Recover Deleted Emails
- Hard Drive Not Showing Up in Windows
- Recover Unsaved Word Documents
- Recover Deleted Files in Windows 10
- Recover Files from Virus Infected Hard Drive
- Best Free Photo Recovery Software
- Recover Files from Formatted Hard Drive
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.
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.
How to Fix Runtime Error 1004 in Excel? There are four effective methods for you to choose from.
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".
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.
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".
Was This Page Helpful?
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