Excel #NUM! Error: Causes, Meaning, and How to Fix It

Excel’s #NUM! error means a formula has an invalid numeric value or cannot complete a calculation with the numbers or settings it was given. Check the formula’s inputs and limits first; for example, =SQRT(-4) returns #NUM! because a real-number square root cannot be taken from a negative value.

What the #NUM! error means

#NUM! is one of Excel’s formula error values. It indicates a numeric problem, but it does not identify one single cause. The formula may be asking Excel to calculate something outside the supported numeric range, passing a number that is not valid for a particular function, or relying on a numerical search that does not converge.

The error is different from #VALUE!, which commonly signals an inappropriate type of input, such as text where a number is required, and #DIV/0!, which identifies division by zero or an empty divisor. These errors can overlap in a larger formula, so inspect the exact cell showing the error and the values feeding into it rather than assuming every problem is a bad number typed directly into that cell.

In Microsoft 365 for Windows, selecting the cell displays its formula in the formula bar. A formula can also return a valid result for most rows but show #NUM! for one row whose input is negative, unusually large, or otherwise outside the function’s valid range.

Common causes and examples

Invalid mathematical input: Functions such as SQRT have a restricted domain. With the number -4 in A2, =SQRT(A2) returns #NUM!. A real square root of a negative number is not available in ordinary Excel arithmetic.
Numbers outside Excel’s numeric range: With 1000 in A2, =POWER(10,A2) attempts to calculate 10 to the 1000th power, which is beyond Excel’s supported range for a number, so it returns #NUM!.
Invalid arguments for a function: With -1 in A2, =FACT(A2) returns #NUM!, because factorial is not defined for a negative integer in this function. Other functions have their own permitted ranges and conditions.
Failed numerical iteration: Functions such as IRR search for a result by trying successive estimates. If Excel cannot find a solution within its iteration limit, the function can return #NUM!.
Date or time values beyond supported limits: Excel stores dates as serial numbers, but date functions cannot represent every imaginable year. A date formula using a year outside Excel’s supported date range can return #NUM!.

These examples illustrate different causes, not a rule that every unusual input will produce the same error. The function’s documentation and the actual values in the workbook determine the result.

How to trace the formula that fails

Start with the formula in the error cell. Follow each referenced cell and check whether the values are what the formula expects. A value that looks numeric may actually be text, and a formula may use a value from another sheet or an intermediate calculation that already contains an error.

Select the cell showing #NUM! and read the formula bar. Note its function names, cell references, and any constants.
Click referenced cells or use the formula bar to inspect the inputs. Check for negative values, exceptionally large values, unexpected blanks, and formulas that return errors.
On Windows, select Formulas > Evaluate Formula to step through parts of a formula and see where the calculation fails. The same command is available on the Formulas tab in Excel for Mac, though ribbon layout can vary by version.
For a complicated worksheet, use Formulas > Trace Precedents to display cells feeding the selected formula. Remove the arrows with Formulas > Remove Arrows when finished.
Check whether the failing row has different data from rows that calculate successfully. One bad input can explain an error in an otherwise consistent column.

Evaluate Formula is useful for nested formulas, but it does not automatically tell you which input is logically wrong. Compare the step where the error first appears with the function’s accepted inputs and the purpose of the calculation.

Fix invalid inputs and protect formulas

The most reliable repair is to correct the source value or add a condition that prevents an invalid calculation. Do not replace every error with zero without considering what zero means in the worksheet: doing so can make a report look complete while hiding missing or unusable data.

With a value in A2 that should be nonnegative, =IF(A2>=0,SQRT(A2),"Enter a nonnegative number") calculates the square root only when the input is valid. If A2 is 9, the result is 3; if A2 is -4, the formula displays the instruction instead of attempting the invalid square root. This approach makes the input condition visible to the person using the sheet.

If the calculation is valid but you prefer a friendly message instead of an error, use IFERROR carefully. With a calculation in A2, =IFERROR(A2,"Check the calculation") shows the original result when it is valid and the message when it returns any error. Because IFERROR catches all error types, it can also conceal a broken reference or an unrelated mistake. During troubleshooting, it is often better to expose the original error until the underlying cause is understood.

For inputs entered by people, data validation can reduce mistakes before formulas run. Select the input cells and choose Data > Data Validation; set an appropriate rule, such as allowing a whole number greater than or equal to zero when negative inputs are not permitted. Validation is a guardrail, not a guarantee: pasted values and external data can still require checking.

When IRR or another iterative function returns #NUM!

IRR estimates the periodic rate of return for a series of cash flows. It needs a meaningful series containing both an initial outflow and later inflows or outflows, and the pattern can affect whether Excel finds a solution. A #NUM! result may mean the attempted numerical search did not converge, rather than that one cell contains an obviously malformed number.

For an illustrative example, put -1000, 400, 400, 400, and 400 in A2:A6, one cash flow per period. With those cash flows in A2:A6, =IRR(A2:A6) returns an estimated periodic rate of about 21.9%. The estimate is periodic because the function treats successive cells as successive equal-length periods; it does not know whether those periods are months, quarters, or years.

If an IRR calculation does not converge, review the signs, order, and timing of the cash flows first. You can also supply a starting estimate. With the same cash flows in A2:A6, =IRR(A2:A6,0.1) starts its search from 10%. A guess can help the search find a solution, but it cannot make an invalid or unsuitable cash-flow series meaningful. For irregularly dated cash flows, XIRR may be more appropriate, provided its dates and values are valid.

Date limits and numeric overflow

Excel dates are numbers formatted to look like calendar dates. That makes ordinary date arithmetic convenient, but a date function still has to return a serial date within Excel’s supported range. For example, with 10000 in A2, =DATE(A2,1,1) asks for a year beyond the supported range and returns #NUM!. Check year, month, and day inputs, especially if they come from imported data or calculations rather than direct entry.

Numeric overflow is a separate limit. Excel handles a very wide range of magnitudes, but not every arbitrarily large result. With 1000 in A2, =POWER(10,A2) exceeds that range. If the calculation represents a real-world quantity, consider whether its units or scale can be changed—for example, working in thousands rather than individual units—without changing the meaning of the result. Do not simply cap or truncate values unless that is justified by the task.

How #NUM! differs from other Excel errors

Identifying the error type helps narrow the investigation, but an error label is a clue rather than a complete diagnosis.

#NUM!: A numeric argument is invalid, a result is outside the supported range, or a numerical method cannot find a result.
#VALUE!: A formula received a value of an unsuitable type or form, such as text in a calculation that expects a number.
#DIV/0!: A formula divides by zero or by a blank cell treated as zero.
#N/A: A value is not available, often because a lookup cannot find a match or a formula deliberately returns this marker.
#REF!: A formula refers to a cell reference that is no longer valid, often after a referenced row or column was deleted.

A formula can contain several operations, and an error in an earlier step can flow into later calculations. Fix the first meaningful error in the chain where possible rather than masking the final displayed result.

Frequently Asked Questions

What does #NUM! mean in Excel?
It means Excel cannot calculate a formula because a numeric input or result is invalid for the operation, exceeds supported limits, or cannot be resolved by a numerical search. Inspect the formula and its inputs to identify which condition applies.

How do I fix #NUM! in Excel?
Check the formula’s arguments and the cells it references. Correct invalid values, bring calculations within supported limits, or revise the formula so it handles known edge cases. For iterative functions, verify the data and consider a suitable starting guess.

Why does SQRT show #NUM!?
SQRT returns #NUM! when its input is negative. Check the referenced value and whether the calculation should use a nonnegative input. If negatives are possible, add a condition that reports them rather than silently changing them.

Can IFERROR remove a #NUM! error?
Yes. IFERROR can display a chosen value or message when a formula returns #NUM!. It also catches other error types, so it can hide problems that still need attention. Use it only when the fallback is appropriate and understandable.

Why does Excel IRR return #NUM!?
IRR can return #NUM! when Excel cannot find a rate after trying numerical estimates. Check that the cash-flow values and signs are suitable, and, if needed, provide a reasonable guess as the function’s second argument. A guess is not a substitute for valid data.

Does #NUM! mean the workbook is corrupted?
No. It usually points to a formula or input issue, not workbook corruption. Trace the calculation and check its inputs before considering broader file problems.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.