#NUM! Error in Excel: Causes, Fixes & Prevention

Your formula was working fine yesterday, and now it's showing #NUM! — so what changed? The #NUM! error in Excel appears when a formula produces a numeric result that Excel either can't calculate or can't represent. Not because your data is wrong, necessarily. Because the math itself is impossible, or the number is outside the range Excel can handle. This error almost always traces back to one of three things: an impossible mathematical operation, an iterative formula that can't find a solution, or a number that's too large for Excel to process.

Before touching anything, there's one version-specific wrinkle worth knowing. In Excel 365, numbers that fall below Excel's minimum threshold don't always throw a visible #NUM! error: they silently return 0 or display as 0.00E+00. If you're on 365 and something looks off but you're not seeing an error, that's worth investigating. The rest of this guide covers how to diagnose and fix the error you can see, but keep that quiet version in mind.

What Triggers a #NUM! Error vs. a #VALUE! Error

This trips people up. A #VALUE! error means Excel got the wrong type of input — text where it expected a number, for instance. A #NUM! error means the input type is fine, but the resulting number is mathematically invalid. =SQRT(-4) doesn't error because "-4" is the wrong type. It errors because the square root of a negative number isn't a real number. (Excel isn't being dramatic. It genuinely cannot return an imaginary number in a cell.) If you're unsure which error you have, that distinction alone will point you to the right fix. For a broader look at how Excel errors differ, the guide to common Excel errors and troubleshooting examples is a good reference.

---

Step 1: Diagnose Which Cause Is Behind Your #NUM! Error Before You Fix Anything

The next move is figuring out which of the three causes you're actually dealing with, because the fix is different each time. Jumping straight to a solution without diagnosing first is how you end up wrapping broken formulas in IFERROR and calling it done.

Ask yourself three quick questions. Is the formula using SQRT or a power function on a value that could be negative? Is it a financial function like IRR, XIRR, or RATE that needs to iterate toward a solution? Or is a number somewhere in the chain genuinely enormous (exponentiation results that push past roughly 9.99 × 10^307, which is Excel's ceiling)?

If you already know the cell, open the formula with Evaluate Formula (Formulas tab → Evaluate Formula) and step through it. Building formulas from the inside out helps here: when something breaks, you can isolate exactly which nested function received an impossible input. It takes two extra minutes and saves twenty.

Use Go To Special to Find #NUM! Errors Fast in a Large Spreadsheet

In a large worksheet, hunting for errors cell by cell isn't realistic. Use Go To Special instead:

1. Press Ctrl + G to open the Go To dialog, then click Special.
2. Select Formulas, then check only the Errors box.
3. Click OK. Excel selects every cell returning an error, including any #NUM! cells.

From there you can tab through them and evaluate each one. This is the diagnostic step most tutorials skip, and it's genuinely useful once your spreadsheet grows past a few dozen formulas.

---

Step 2: Fix the #NUM! Error Based on What Actually Caused It

Once you've identified the cause in Step 1, the fix is usually one targeted adjustment: not a workaround, an actual correction to the formula logic.

Fix a SQRT or Power Formula Returning a #NUM! Error

The SQRT function returns a #NUM! error any time its input is negative. If your data can legitimately be negative and you want the square root of the magnitude, wrap the input in ABS: =SQRT(ABS(A1)). If a negative value genuinely indicates bad input, add an IF check before the SQRT so the formula flags it rather than hiding it. The same principle applies to LOG and other functions that can't accept certain value ranges: check what the function requires, then validate the input before it arrives.

The ABS fix is appropriate when a negative number is valid data (such as a loss figure) and you only need the magnitude. If a negative input means something went wrong upstream, use an IF check to surface it rather than silently converting it.

Fix an IRR or Iterative Formula That Can't Converge

IRR and XIRR use iterative calculation: they try different values repeatedly until they find one that works. If the cash flows don't produce a solvable internal rate of return, or if the formula starts its iterations too far from the actual answer, it gives up and returns #NUM!. Two fixes to try:

1. Add a guess argument to IRR that's closer to the expected rate. The default is 10% — if your actual IRR is likely to be very high or negative, that starting point may be too far off. Try =IRR(A1:A10, 0.5) or a negative guess like =IRR(A1:A10, -0.1).
2. Check your cash flow signs. IRR requires at least one sign change: some values must be negative and some positive. All-positive or all-negative cash flows will always return #NUM!.

If a file was built in an older Excel version and you're now running it in Excel 365 (or vice versa), version compatibility can affect iterative functions. This is a less common cause, but worth checking if the formula worked previously.

---

Common Mistakes When Handling a #NUM! Error — Including the IFERROR Trap That Hides the Real Problem

IFERROR is a finishing tool, not a debugging tool. Get your formula working correctly first. Entire IRR columns have gone out in reports silently showing 0% — or blank — because someone wrapped a broken formula in =IFERROR(IRR(...), "") and assumed the error was cosmetic. It wasn't. The underlying cash flow data was wrong, and IFERROR buried it.

There are legitimate cases for suppressing a #NUM! error. A SQRT formula applied to user input that might occasionally be negative, where you'd rather show a blank than an error message, is a reasonable use. But you should be able to explain exactly why the error appears and confirm that the suppressed result won't mislead anyone downstream.

Diagnose before you fix, and fix before you suppress. The #NUM! error is Excel telling you something real about the math. Silence it only after you understand what it's saying.

The other common mistake: assuming the error is a data-entry problem when it's actually a formula-logic issue. If you check the article on fixing the #VALUE! error in Excel and find your problem described there instead, that's a sign the root cause is different than you thought. Same troubleshooting instinct, different fix.

New to Excel errors in general? The Excel for beginners starter guide covers the foundational concepts that make troubleshooting easier across the board.

---