Excel #NUM! Error: What It Means and How to Fix it

The Excel #NUM! error occurs when an Excel formula can’t perform a calculation. It may not be immediately clear, however, why Excel can’t perform a calculation. In this tutorial, you’ll learn how to identify and resolve the main causes of the Excel #NUM! error. There are three main causes of the Excel #NUM! error, including calculating numbers that are too large or too small.

By the end of this tutorial, you’ll have learned:

• Why the Excel #NUM! error happens and how to fix it
• What the three main causes of the Excel #NUM! error are
• How to find and highlight all cells containing the Excel #NUM! error

Understanding and Fixing the Excel #NUM! Error

In the following three sections, you’ll learn how to resolve each of these causes.

Fixing the Excel #NUM! Error Due to Numbers Being Too Large or Small

This is a complicated instance of the Excel #NUM! error because it’s one that can’t be solved. Excel places limits on how large (or small) a number can be. The largest number that Excel can handle is 2.2251E-308, while the smallest number Excel can handle is -2.2251E-308.

Let’s take a look at an example. We’ll raise a few numbers to a given exponent, using the POWER() function. The POWER() function is useful for raising values to a given power or for squaring values in Excel.

.

We can see that when numbers get to be too large, Excel will raise the #NUM! error. Like I mentioned earlier, there is unfortunately no fixing this error.

Now, let’s take a look at another cause of the #NUM! error: impossible calculations.

Fixing the Excel #NUM! Error Due Impossible Calculations

Another cause of the Excel #NUM! error is when a formula attempts to make an impossible calculation. For example, if we try to calculate the square root of a negative number, Excel will raise a #NUM! error. Because negative numbers cannot have a square root, Excel correctly display an error.

So, you may be thinking, “If we’re calculating something impossible, how do we fix it?” The answer to that is that we adjust what we’re calculating. For example, we can calculate the square root of the absolute value. This means that we calculate the square root of the positive version of the number.

Let’s modify our example to calculate the square root without raising the error:

We can see in the example above that the #NUM! error has been resolved! Now, let’s take a look at another cause of the error: incorrect function arguments.

Fixing the Excel #NUM! Error Due Incorrect Function Arguments

Another common cause for the #NUM! error in Excel is the use of incorrect function arguments. For example, Excel will raise a #NUM! error when using the DATEDIF() function if the second argument is later than the first argument.

Let’s take a look at what this looks like:

In the example above, we can see that a #NUM! error is raised for values where the first argument is later than the second. In order to resolve this error, we can modify the data to have the second argument be the later date.

Finding All Cells Containing the Excel #NUM! Error

In this final section, you’ll learn how to find and highlight all cells in Excel that contain the #NUM! error. Let’s take a look at how you can use Excel to do this:

1. We enter the Find and Replace menu using the shortcuts CTRL + F (or CMD + F on macOS).
2. We then search for the string #NUM! in the Find what menu.
3. We adjust our parameters to search for all values in the Workbook, specifying to search in the Values (rather the default Formulas).
4. We then select Find All to find all errors.

5. From here, we can select all the errors identified in the section below the menu.
6. Select the background color to highlight all versions of the error.

Doing this won’t resolve the error, but it’ll make finding issues in your workbook easier. From here, you can more easily trace back why the issues are happening.

Conclusion

In this tutorial, you learned how to understand and resolve the Excel #NUM! error. The error occurs when there is an issue with the formula used in a cell. We explored three different causes of the error and how to resolve them, including:

1. Numbers that are too large or too small,
2. Formulas that are impossible, and
3. Formulas with incorrect arguments.

From there, you also learned how to find and highlight all cells in Excel that contain the #NUM! error. While doing this doesn’t resolve the error, it can be helpful to be able to find issues in your workbook.

Additional Resources

To learn more about related topics, check out the tutorials below:

• Excel #REF! Error: What It Means and How to Fix It
• Excel #DIV/0 Error: What It Means and How to Fix it