Excel shows a #NUM! error when a formula or function contains an invalid numeric value, such as a negative number where a positive one is required, or when a calculation produces a number too large for Excel to handle. This guide walks you through the most common causes and provides clear, step-by-step fixes to resolve the error and prevent it from recurring.
What You’ll Need
- A computer with Microsoft Excel (2016 or later, including Microsoft 365).
- The workbook containing the #NUM! error.
- Basic familiarity with selecting cells and entering formulas.
Step-by-Step
- Identify the cell with the error. Look for a cell displaying
#NUM!. Click on it to see the formula in the formula bar. Note the cell reference (e.g.,B5) so you can trace the issue. - Check for negative numbers in functions that require positive values. Functions like
SQRT(),LOG(),LOG10(), andFACT()will return#NUM!if given a negative number. For example,=SQRT(-4)produces the error. Replace the negative input with a positive one, or useABS()to take the absolute value if that makes sense for your calculation. - Inspect date and time functions for out-of-range arguments. Functions like
DATE(),TIME(), andDATEDIF()can return#NUM!when the date is before January 1, 1900, or when the start date is later than the end date. For example,=DATE(1899,12,31)gives#NUM!. Ensure all dates are within Excel’s supported range (1900–9999) and that start dates precede end dates. - Verify that iterative calculations converge. If you use circular references (a formula that refers to its own cell) or functions like
RATE(),IRR(), orNPV()that rely on iteration, the error may occur when the calculation does not converge to a result. Try changing the initial guess (theguessargument inRATE()orIRR()) to a value closer to the expected result. For example,=IRR(B2:B10, 0.1)instead of omitting the guess. - Check for numbers that exceed Excel’s limits. Excel can handle numbers between approximately
1E-308and1E+308. If a formula produces a value outside this range, you’ll see#NUM!. For instance,=10^309returns the error. Break the calculation into smaller parts or use logarithms to handle very large numbers. - Look for invalid arguments in statistical functions. Functions like
BINOM.DIST(),NORM.DIST(), andTDIST()require specific argument ranges. For example,BINOM.DIST()requires the number of successes to be between 0 and the number of trials. If any argument is outside the allowed domain, the function returns#NUM!. Review the function’s documentation and adjust the inputs accordingly. - Use Excel’s error checking tool. Click on the cell with the error, then click the yellow warning icon that appears. Choose “Trace Error” to see which cells are involved. This visual aid helps you spot the offending input quickly.
- Manually recalculate the workbook. Sometimes the error is due to stale cached values. Press
F9to recalculate all formulas, or go to Formulas tab → Calculation Options → Automatic to ensure calculations update automatically.
Common Mistakes
- Using a negative number in a square root or logarithm without realizing the function’s domain restriction.
- Confusing dates before 1900 – Excel does not recognize them, so any date calculation involving them fails.
- Omitting the
guessargument in iterative functions likeIRR()orRATE(), which can cause non-convergence. - Entering percentages as whole numbers when the function expects a decimal (e.g., using 10 instead of 0.10 for 10%).
- Copying formulas without adjusting cell references – relative references may shift and point to unintended cells, leading to invalid inputs.
Tips
- Use
IFERROR()to handle errors gracefully. Wrap your formula like=IFERROR(SQRT(A1), "Invalid input")to display a custom message instead of the raw error. - Test your inputs separately. If a formula fails, break it down into smaller parts in separate cells to isolate which argument is causing the issue.
- Use the
ABS()function when you need the absolute value of a number for functions that cannot accept negatives. - For large numbers, use scientific notation or logarithms to keep values within Excel’s range.
- Keep your data clean – use data validation to restrict input ranges, preventing invalid entries before they cause errors.
Troubleshooting
If the above steps don’t resolve the error, try these additional checks:
- Check for hidden characters or spaces. If you imported data, cells may contain non-breaking spaces or other invisible characters that make a number text. Use
=ISNUMBER(A1)to test if the cell is numeric. If it’s text, clean it with=TRIM(A1)or use Text to Columns (Data tab → Text to Columns → Finish) to convert. - Verify that all referenced cells contain valid numbers. If a cell contains text, the formula may return
#VALUE!rather than#NUM!, but it’s worth checking. - Update Excel. Sometimes bugs in older versions cause spurious errors. Go to File → Account → Update Options → Update Now to ensure you have the latest fixes.
- Open the file in a different spreadsheet program (like Google Sheets) to see if the error persists. If it doesn’t, the issue is specific to your Excel installation or file settings.
- Check the formula’s syntax. A misplaced comma or parentheses can cause a different error, but if the function name is misspelled, Excel may interpret it as a named range and return
#NAME?. Ensure the function name is correct.
Frequently Asked Questions
Why does Excel show #NUM! instead of a number?
#NUM! appears when a formula or function receives an invalid numeric argument, such as a negative number in a square root, or when the result is too large or too small for Excel to represent.
How do I fix #NUM! in a VLOOKUP?
VLOOKUP itself doesn’t typically return #NUM!. If it does, it’s usually because the lookup value is a number that exceeds Excel’s limits, or the table array contains a #NUM! error. Check the lookup value and the data in the table for invalid numbers.
Can I ignore #NUM! errors?
You can use =IFERROR(your_formula, "fallback") to display a custom message or a blank cell instead of the error. However, it’s better to fix the underlying cause to ensure accurate results.
Why does #NUM! appear when I use the DATE function?
The DATE function returns #NUM! if the year is less than 1900 or greater than 9999, or if the month/day combination is invalid (e.g., month 13). Ensure your date components are within the valid range.
How do I fix #NUM! in an IRR calculation?
IRR returns #NUM! if it cannot converge to a result after 20 iterations. Provide a guess argument, such as =IRR(cashflows, 0.1), or adjust the cash flow values to ensure there is at least one positive and one negative cash flow.
Does #NUM! affect other formulas?
Yes, if a cell containing #NUM! is referenced by another formula, that formula will also return #NUM! (or a related error). Fixing the source cell will resolve downstream errors.
Related
General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.
More in this bed
- How to Create App Shortcuts in Windows 10 Start Menu
- How to Rename a SQL Server Database
- Recover Deleted Admin Account in Windows 11
- Install Custom Recovery on Samsung Galaxy E5
- Fix Administrator Blocked You From Running This App Error
- Fix Google Calendar Not Syncing on iPhone