Monday, June 29, 2020

Home Excel tips Excel Formula Errors

Excel Formula Errors

June 29, 2020 Excel tips,

Excel Formula Errors

The more formulas you write, the more errors you'll run into.

Although frustrating, formula errors are useful, because they tell you clearly that something is wrong. This is much better than not knowing. The most disastrous Excel mistakes usually come from normal-looking formulas that quietly return incorrect results.

When you run into a formula error, don't panic. Stay calm and methodically investigate until you find the cause. Ask yourself, "What is this error telling me?" Experiment with trial and error. As you gain more experience, you'll be able to avoid many errors, and more quickly correct errors that do arise.

#DIV/0!, #NAME?, #N/A, #NUM, #VALUE!, #REF!, #NULL, ####, #SPILL!, #CALC!

Fixing Errors

Here is a basic process for fixing errors below. Remember that formula errors often "cascade" through a worksheet, when one error triggers another. As you find and fix the core issue, things often come together quickly.

1. Find errors. You can use Go to Special > Formula as described below.

2. Trace the error back to its source. If this is difficult, try the trace error feature.

3. Figure out what's causing the error. If needed, break the formula into parts.

4. Fix the error at the source.

Video: Excel formula error examples

Video: Use F9 to debug a formula

Finding all errors

You can find all errors at once with Go To Special. Use the keyboard shortcut Control + G, then click the "Special" button. Excel will display the dialog with many options seen below. To select only errors, choose Formulas + Errors, then click "OK":

Trapping Errors

Trapping errors is a way of "catching" errors to stop them appearing in the first place. This makes sense when you know certain errors are likely and you want to stop error messages from appearing. There are two basic approaches:

2. Trap the error with IFERROR or ISERROR. With this approach you are watching for an error, and providing an alternative when an error is detected. This page shows a VLOOKUP example.

3. Prevent calculation until required values are available. In this case, instead of watching for an error, you try to prevent the error from occurring by checking values first. This page shows several examples.

Excel's error codes

There are 9 error codes that you're likely to run into at some point as you work with Excel's formulas. This section shows examples of each formula error, with information and links on how to correct the error.

#DIV/0! error

As the name suggests, the #DIV/0! error appears when a formula tries to divide by zero, or by a value equivalent to zero. You may see a #DIV/0! error when data is not yet complete. For example, a cell in the worksheet is blank because data has not been entered, or is not yet available. You also may see the divide by zero error with the AVERAGEIF and AVERAGEIFS functions, when the criteria does not match any cells in the range.

For example, in the worksheet below, the DIV error displayed in cell D4 because C4 is empty. Empty cells are evaluated as zero by Excel, and B4 can't be divided by zero:

In many cases, empty cells or missing values are unavoidable. You can use the IFERRROR function to trap the #DIV/0! and display a more friendly message if you like.

More: How to fix the #DIV/0! error

#NAME? error

The #NAME? error indicates that Excel does not recognize something. This could be a function name misspelled, a named range that doesn't exist, or a cell reference entered incorrectly. For example, in the screen below, the VLOOKUP function in F3 is misspelled "VLOKUP". VLOKUP is not a valid name, so the formula returns #NAME?.

To fix a #NAME? error, you must find the problem, then correct spelling or a syntax. For more details and examples, see this page.

Video: How to use F9 to debug a formula error

#N/A error

The #N/A error appears when something can't be found. It tells you something is missing or misspelled. This could be a product code not yet available, an employee name misspelled, a color that doesn't exist, etc. Often, #N/A errors are caused by extra space characters, misspellings, or an incomplete lookup table. The functions mostly commonly affected by the #N/A error are classic lookup functions, including VLOOKUP, HLOOKUP, LOOKUP, and MATCH.

For example, in the screen below, the formula in F3 returns #N/A because "Bacon" is not in the lookup table:

If the value in E3 is changed to "coffee", "eggs", etc. VLOOKUP will work normally and retrieve the item cost.

The best way to prevent #N/A errors is to make sure lookup values and lookup tables are correct and complete. If necessary, you can trap the #N/A error with IFERROR and display a more friendly message, or display nothing at all. '

More information: How to fix the #N/A error.

#NUM! error

The #NUM! error occurs when a number is too large or small, or when a calculation is impossible. For example, if you try to calculate the square root of a negative number, you'll see the a #NUM error:

In the screen above the SQRT function used to calculate the square root numbers in column B. The formula in C5 returns the #NUM! error because the value in B5 is negative, and it is not possible to compute the square root of a negative number.

You might also run into the #NUM error if you reverse start and end dates inside the DATEDIF function.

In general, fixing the #NUM! error is a matter of adjusting inputs as required to make a calculation possible again.

More information: How to fix the #NUM! error.

Video: Examples of Excel formula errors

#VALUE! error

The #VALUE! error appears when a value is not an expected or valid type (i.e. date, time, number, text, etc.) This can happen when a cell is left blank, when a text value is given to a function that expects a numeric value, or when dates are evaluated as text by Excel.

For example, in the screen below, cell C3 contains the text "NA", and the formula in F2 returns the #VALUE! error.

Below, the MONTH function can't extract a month value from "apple", since "apple" is not a date:

Excel #VALUE! error example with MONTH function

Note: you may also see a #VALUE! error if you create an array formula and forget to enter the formula with Control + Shift + Enter.

To fix a #VALUE! error, you need to track down the problematic value, and supply the right type of value. For more details and examples, see this page.

#REF! error

The #REF! error is one of the most common errors you'll see in Excel formulas. It occurs when a reference becomes invalid. In many cases, this is because sheets, rows, or columns have been removed, or because a formula with relative references has been copied to a new location where references are invalid.

For example, in the screen below, the formula in C8 was copied to E4. At this new location, since the range C3:C7 is relative, it becomes invalid and the formula returns #REF!:

#REF! errors can be somewhat tricky to fix because the original cell reference is gone forever. If you you delete a row or column and see #REF! errors, you should undo the action immediately and adjust formulas first.

More details on #REF! errors.

#NULL! error

The #NULL! error is quite rare in Excel, and is usually the result of a typo where a space character is used instead of a comma (,) or colon (:) between two cell references. For example, in the screen below the formula in F3 returns the #NULL error:

Technically, this because the space character is the "range intersect" operator and the the #NULL! error is reporting that the two ranges (C3 and C7) do not intersect. In most cases, you can correct a NULL error by replacing a space with a comma or colon as needed.

More: How to fix the #NULL! error

#### error

Although technically not an error, you may also see a formula that displays a string of hash characters (###) instead of a normal result. For example, in the screen below, the formula in C3 is adding 5 days to the date in column B:

In this case the hash or pound characters (###) appear because the dates in column C are formatted with a long format and do not fit into the column. To fix this error, just make the column wider.

Note: Excel won't display negative dates. If a formula returns a negative date value, Excel will display #####.

More: How to fix ##### errors

#SPILL! error

The #SPILL error occurs when a formula outputs a spill range that runs into a cell that already contains data. For example, in the screen below, the UNIQUE function is configured to extract a list of unique names into a spill range starting in D3. Because D5 contains "apple", the operation is stopped and the formula returns #SPILL!.

When "apple" is deleted from D5, the formula will work normally, and return "Joe", "Sam", and "Mary".

Details: How to fix #SPILL errors

Video: Spilling and the spill range

#CALC! error

The #CALC error occurs when a formula runs into an calculation error with an array. For example, in the screen below, the FILTER function is set up to filter the source data in B5:D11. However, the formula is asking for all data in the group "apple", which doesn't exist: