Errors In Excel

What are Errors In Excel?

MS Excel is a powerful tool that enables users to record massive data sets in cells defined by row and column coordinates. However, when applying a formula to the worksheet, we may get an unexpected error code. Such errors in Excel indicate the probable mistakes in our functions and what we can do to resolve them.

But first, to ensure we can view all the errors in our worksheet,

• Navigate through Home > Find & Select > Go To Special or use the shortcut keys Ctrl + G and click on Special.
• The Go To Special window appears. Then, under Formulas, check the box against Errors and click OK.

Now that we can find errors in Excel, let us learn how to fix errors in excel.

For example, we have a table showing the yearly incomes in column A. Now, we need to determine the monthly earnings in column B by dividing each annual income by 12. However, while applying the formula in cell B3, we divide the numerator by 0 instead of 12. As a result, we will get an error as depicted below:

• The cell with the error code will have a small green triangle in the top-left corner.
• We will get a yellow diamond icon with an exclamation mark when we click on this cell.
• Also, if we hover our mouse cursor over the icon, a hover text will appear explaining the reason for the error.
• In addition, when we click on the icon, we will get a menu with options to handle the error according to our requirements.

Thus, we can find errors in Excel and learn to fix them.

List Of Top 10 Errors In Excel

Here are the top 10 types of errors in Excel which we will typically encounter while working on MS Excel.

Consider the table below:

Let us understand each error code we might get while applying different formulas to the above table data.

1) #DIV/0

Description

We will see the error code #DIV/0 when the formula we use in a cell tries to divide an empty cell or if the value of the cell is 0.

On the other hand, if we attempt to find the average of empty cells or non-numerical values, then again, we will get the #DIV/0 error.

How To Fix?

Firstly,

• Ensure the cells used and referred to in the formula is correct and contains valid data.
• Check whether the cell references in excel we use to divide other data values are not blank or contains 0.
• See the cells referred to in the formula does not display the #DIV/0 error code.

However, the best way to fix the #DIV/0 error is by using the IF() function and IFERROR() function. These evaluate the denominator for 0 or blank cells.

And, when we use these functions, we can ensure that we do not see the error code. Instead, the output will be 0 or a statement that we can display in the cell as the outcome of the function, letting the user know what to do to get the correct answer.

Example

Consider the below table listing the grocery items and units sold. Now, let us add a column that shows the Rate Per Unit in $, obtained by dividing the total price by the total units. Clearly, we can see that the total units of tomatoes are missing from the table.

Step 1: Choose cell E2 and enter the formula for dividing the total prices by the total units.

Step 2: Once we drag the fill handle downwards and copy the formula in cells C3:C14, the expression in cell E13 will become =D13/C13.

So, the output will be:

The #DIV/0 error occurs for the item, Tomato as the formula divides the total price (0) by 0. So, here is how we can use the IFERROR() in column E and avoid #DIV/0 error. The formula in cell E13 is =IFERROR(D13/C13,””).

The formula now shows the rate per unit for every item. And in this case, it checks the denominator for 0 or spaces to return a blank cell, as in cell E13.

Alternatively, the IF() formula in cell E13 (used to avoid the #DIV/0 error) will be

=IF(C13=0,””,D13/C13)

The function checks for 0 or blank cells in column C, as the cells in column C are the denominators in the formula. So, the cells in column E will be blank if the respective cell values in column C are 0 or empty.

2) #N/A

Description

The #N/A error code implies that a value is not available. It typically occurs when we use lookup functions, such as VLOOKUP, HLOOKUP, and MATCH excel function.

Ideally, when we try looking up a value in a column to display its data, we will get the #N/A error when the lookup value is not present in the specific column. The error also occurs due to extra spaces or misspelled data in the formula.

How To Fix?

As the first step, perform a sanity check of our lookup table.

• Check for extra spaces and incorrect spellings in the lookup value and the table.
• Ensure the lookup table is complete
• Next, the lookup value and the table should have the same data types.
• Also, the exact match configuration should be correct.

The IFERROR() and IFNA() can help us fix errors in excel. We can nest our LOOKUP and MATCH excel functions in them and ensure we do not get the error code in our results. Then, instead of seeing the #N/A error, our output will show space or a meaningful comment, thus improving it.

Example

Suppose we want to display the total price for certain items on another table, say Corn and Cheese. We get the below output as shown in the image below:

We look up the total prices for Cheese and Corn in the second table, from the first one, using the formulas shown in cells I3 and I4.

However, the function returns the error #N/A for both items. On checking closely, we find an extra space before the lookup value, Cheese, in the second table. And Corn is not available on the first table’s grocery list.

Step 1: Remove the additional space before the lookup value, Cheese, in cell G3. The output will be:

Step 2: Use IFERROR() with the VLOOKUP() to remove the #N/A error and provide a more meaningful output for the item not available. The formula in cell I4 will be:

=IFERROR(VLOOKUP(G4,A:D,4,0),”Item Not Available”)

The above formula will return the output Item Not Available even if we incorrectly type the function name VLOOKUP. On the other hand, the IFNA() is a function that exclusively fixes the #N/A error. The formula in cell I4 will be:

=IFNA(VLOOKUP(G4,A:D,4,0),”Item Not Available”)

3) #NAME?

Description

If Excel does not recognize any part of the formula such as the function name, syntax, cell references, or value in a cell, we will see the error #NAME?.

Typically, we will get this error when we misspell the function name, miss a colon, refer to a name not defined, or provide a text value without double quotations. The error can also occur when we use a function requiring an add-in, but we do not enable the add-in.

How To Fix?

Using the Formula Wizard is a straightforward way to overcome function name typo errors in Excel. Once we start entering the function name in a cell or the Formula Bar, Excel will list the functions starting with the entered word. And when we click on the required function or enter the open parenthesis, the Formula Wizard will show the syntax as hover text.

Alternatively, we can select the cell with the formula and choose Formulas > Insert Function. It will open the Function Arguments window, where we can check and correct the syntax errors.

Example

Let us assume that the data for the vegetables are not available in the Grocery Items table. So, let us define a name for the cell range A1:C14 as Grocery_Item_Total_Units, using the below steps:

Step 1: Select the cell range A1:C14 and choose Formulas > Define Name > Define Name.

Step 2: Enter the name as Grocery_Item_Total_Units in the New Name window. And then, click OK.

For instance, if we need to check the stock availability for the vegetables in the grocery item list, we will apply the IF() and VLOOKUP() to determine the output, as depicted below:

The output in column H should be ‘Item Not Available’ for the first three items in column G and ‘Item Available’ for Lettuce. However, when applying the formulas (refer to the column I), we will see the error #NAME? for all four vegetables. Let us understand how to fix errors in excel.

Step 1: The formula in cell H2 has a typo error. Correct the IF() name from IIF to IF to get the below output.

Step 2: The VLOOKUP() is missing a colon in the cell range. Insert a colon between A and C column references to get the correct output.

Step 3: The text outputs in the IF(), Item Not Available and Item Available, are not in double quotations. Thus, put them in double quotes to remove the error #NAME?.

Step 4: The VLOOKUP() in cell H5 uses the defined name for the A:C cell range. But the spelling is incorrect. Once we update the defined name correctly, the error #NAME? will get fixed.

4) #NULL!

Description

The #NULL! error appears when we use a space in place of the specific intersection operator, such as a colon or comma, in cell ranges and references. The error also occurs if we enter a space instead of the required mathematical operator between cell references.

How To Fix?

Here are a few checks we can perform to fix errors in excel.

• Ensure we enter the required mathematical operators between the specific cell references.
• A cell range should have a colon in between to denote the intersection between the start and end points in the cell range.
• Ensure the formula has comma-separated individual cell references.

Example

Suppose we want to determine the overall total units of all items and those of fruits from the Grocery Items table. We can create two tables and using the SUM() (shown in cells H2 and K2), we can calculate the required values in each table, as depicted below:

However, we get the #NULL! error in both the tables. The steps used to fix errors in excel are as follows:

Step 1: The cell range in the cell G2 SUM() has a space in between and is missing a colon. Remove the space, insert a colon, and press Enter to achieve the required output.

Step 2: The SUM() in cell J2 is missing a comma in between the cell references C9 and C10. Instead of the comma, there is a space. Thus, remove the space, insert a comma between cell references C9 and C10 and press the Enter key to fix the error.

5) #NUM!

Description

We get the #NUM! error when we enter a numeric value as an argument in a function or in a format that it does not support.

The following reasons show when we get #NUM! error.

• The function output is too large.
• Argument in the function is incorrect.
• The mathematical calculation is not possible to perform.
• The iteration functions, such as RATE(), cannot find the output after multiple iterations.

How To Fix?

We can fix the #NUM! error by:

• Identifying the error cells and adjusting the argument values as required in the formula.
• Ensuring the entered arguments have the proper formats, as required in the formula.

We can also use the IFERROR() to remove the #NUM! error and show a meaningful output.

Example

Assume the rate per unit of White Rice will grow exponentially by 600.

Step 1: Enter the below formula in cell H2.

=E4^600

But since the output is too large, we will see the below error.

Step 2: Instead, we can use the IFERROR() to display a more meaningful comment, as depicted below:

=IFERROR(E4^600,”Too large a number”)

However, a more common scenario for getting the #NUM! error is when we attempt to find the square root of a negative number. In such a case, we need to use the ABS() nested in the SQRT().

The syntax will be:

=SQRT(ABS(value))

Likewise, to ensure that the iteration functions do not return the #NUM! error, first choose File > Options > Formulas. Then, under Calculation options, check the box against Enable iterative calculation and adjust the Maximum Iterations and Maximum Change values.

While the first one should have a higher value, the second parameter needs to be small. Such settings will ensure more iterations and more accurate output without the #NUM! error.

6) #REF!

Description

We will get the #REF! error if a cell reference we use in our formula is invalid. Such errors in Excel occur when the referenced cell gets deleted. Another scenario is we copy a formula with relative cell references in another location where the references are not valid.

The error also occurs when we enter an incorrect table_array in a VLOOKUP() or index lookup range in the INDEX().

How To Fix?

Here are a few solutions we can check.

• Using Excel’s undo option, recover the deleted row or column cells.
• Re-enter the deleted data and update the cell references in the formula.

Also, as in the case of other errors in Excel, we can remove #REF! error using the IFERROR(), especially when developing cell references dynamically with the INDIRECT().

Example

Suppose the Grocery Items table has another column, Item ID, appended before the Grocery Item column. We use the INDEX Excel function to determine the total price of the item with ID R085.

Step 1: Enter the INDEX() in cell J2.

=INDEX(A1:E14,9,5)

Now, assume we accidentally deleted the Item ID column. The output will become:

The index lookup range changes automatically from A1:E14 to A1:D14, and we get the #REF! error in cell I2. We can immediately use the shortcut keys Ctrl + z. It will undo the delete action and fix the error.

Similarly, if we enter the row or column range incorrectly, say, we entered 15 as the row range instead of 9 in the INDEX(), the output becomes:

We can manually correct the cell references in the formula to get the correct output.

Otherwise, we will see a blank cell instead of the error when using the IFERROR() with the INDEX() containing the incorrect lookup range.

7) #VALUE!

Description

When we enter an argument in a formula, which does not have the required data type, the function will return #VALUE! error. The error indicates we have typed our expression incorrectly, or there is an issue with the cell references we used in our formula.

It typically happens when we enter a text when the function requires a numeric value as input or one that should have a Date type.

Here are a few facts that we can check:

How To Fix?

• Check our formula by selecting the cell with the function and choosing Formulas > Evaluate Formula > Evaluate. Then, we can check the step-by-step evaluation and determine the error source.
• Check and remove the extra spaces in cells whose values might cause the error.
• Check for special characters and text in cells causing the error.
• Instead of typing in the formulas with mathematical formulas, use the Excel built-in functions.
• Use the IFERROR() to replace the error with a more meaningful function return value.

Example

Consider we have three new columns in the Grocery Items table. The first two columns show each item’s stock order and receive dates. And the third column determines the waiting period during the stock arrival.

Assume we used the DATEDIF() to determine the waiting period.

Step 1: Enter the DATEDIF() formula in cell H2 and drag the fill handle downwards to copy the formula in cells C3:C14. So, the formula in cell H10 will be:

=DATEDIF(F10,G10,”D”)

In the table above, cell H10 shows the #VALUE! error as the Stock Receive Date for Oranges is NA, a text instead of a valid date.

Step 2: Either we correct the entry in cell G10 to get the correct result, or we can use the IFERROR() to replace the error with an apt comment. The outputs in the two scenarios will be:

8) #####

Description

If we see a row of # (hash symbol) in a cell, it indicates a hashtag error. However, it is not an error; it suggests that the column width is not adequately wide to show the cell content.

We will get the hashtag error when:

• We enter a numeric value or text data exceeding a limit of 253 characters in a cell that stores numbers, dates, or time.
• We enter a negative value in a cell that stores numbers, dates, or time.
• We enter a formula whose return value exceeds the cell column width.

How To Fix?

When we see the ##### error:

• Increase the cell column width.
• Reduce the cell data length or change the cell format type to General.
• Correct the cell value or the function that returns an incorrect value.

Example

For example, the Stock Order Date format is Day, Month Date, Year in the Grocery Items table.

Step 1: Choose column F and select Home > Date > More Number Formats.

Step 2: In the Format Cells window, choose Date and the required date format as depicted below.

When we click OK, the table will appear as shown in the image below:

As the column F width is shorter, we will get the ##### error.

Step 3: Increase the column F width to make the table error-free.

9) Circular Reference

Description

A circular reference error appears when a function refers to its own cell. The cell reference can be direct or indirect, building endless loops of evaluations.

In the case of the direct circular reference, we will use a reference to the cell containing the formula directly within the function. On the other hand, when a function refers to a cell containing a circular reference, it is a scenario of indirect circular reference.

How To Fix?

We can trace the circular reference in excel to the source to correct the initial formula or remove the reference stage by stage.

Excel offers two ways to trace the relationship between functions and cells to remove the circular reference, which are:

• Trace Precedents – It helps in tracking cells that affect the active cell.
• Trace Dependents – It helps track cells that depend on the current cell.

We can find the above options in the Formulas tab in the Excel ribbon.

Example

Let us consider the data for Milk, White Bread, Eggs, and Cheese from the Grocery Items table. And suppose we need to get the product of Total Units and Total Price for these four items to calculate the Overall Total Price in cell G10.

Step 1: Enter the SUM() in cell G7. Suppose we provide an incorrect cell range, G2:G7 instead of G2:G5. The formula in cell G7 will be:

=SUM(G2:G7)

As we enter the SUM() in cell G7 and use a reference to G7 in the formula, we will see the below warning message about circular reference when we press the Enter key.

When we click OK, the function returns 0 as the output.

Step 2: Using the SUM() in cell H7, calculate the total price for the four items with the formula =SUM(H2:H5).

Step 3: Get the product of values in cells G7 and H7 in cell G10. It will be 0.

We need to correct the circular reference error to get the correct output.

Step 4: Choose Formulas > Error Checking > Circular Reference.

It shows which source cell has a circular reference. So now, we can correct the error by providing the correct cell range in the SUM() in cell G7.

Step 5: Change the cell range in the SUM() in cell G7 as G2:G5.

So, the formula is =SUM(G2:G5)

Once we correct the cell range used in the SUM() in cell G7, we will get the correct output in cell G10.

10) #SPILL!

Description

We will see the #SPILL! error when the formula we entered in a cell returns multiple results, but Excel does not return the output to the respective grid.

In other words, the function returns a spill range, evaluating through a cell that already contains data.

How To Fix?

• First, identify the cells creating the hindrance and blocking the function from execution.
• Next, clear the values from the obstructing cells.

We will observe the formula output shows up in the required cells.

Example

Suppose we wish to create another column Grocery Items_New with the first seven items from the Grocery Items table followed by a list of new essentials.

Step 1: In column H with the heading Grocery Item_New, enter the new items from cell H8:H14.

Also, the UNIQUE() in cell G2 is:

=UNIQUE(A2:A8)

We will get the #SPILL! error as the cell range we chose, A2:A8, overlaps with the G8 value, Cherries. It means that the spill range is not blank.

Step 2: Delete the cell G8 value, and immediately the array will spill, filling the cells from G2:G8, as depicted below:

Conclusion

To summarize, the Errors in Excel help identify where we might have gone wrong while performing calculations in our worksheets. We will find them useful, particularly when working with massive data sets.

So, understand them thoroughly to make the necessary corrections in your calculations and achieve your desired results.

Download Template

This article must be helpful to understand Errors in Excel, with examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to Errors in Excel. Here we discuss the list of top 10 errors in excel & how to fix them with examples and downloadable excel template. You can learn more from the following articles – 

• Mixed References In Excel
• Alternatives to VLOOKUP In Excel
• Track changes in Excel