8 Excel error messages that make you uncomfortable

Did you notice the small green arrow in the upper left corner of a cell? You know, this annoying little sign is used by Excel to indicate there's something wrong with the values ​​in the cell.

In many cases, clicking on the arrow will bring up information, helping to fix the problem.

But in other cases, you still can't seem to find the error and resolve it. The following article will point out 8 Excel error messages that make you uncomfortable and how to fix them.

1. #VALUE!

Excel displays the #VALUE! Error. when it finds spaces, characters or text in a formula that should be a number.

Excel requires the formulas to contain only numbers, so it will show the error, if you have included anything else.

How to fix

An easy solution to this error is to check the formula again to make sure you only use numbers. If you still see errors, check for empty cells, missing formulas that link to cells or any special characters you might be using.

In the example above, the "Sum" column is referring to blank cells. Excel cannot calculate the total of blank columns, thus causing an error.

2. #NAME?

This is a slightly more complex Excel error. Below is a summary of why this error may appear in a cell you are working on:

Reason

Error message #NAME? appears if Excel cannot understand the name of the formula you are trying to run or Excel cannot calculate one or more values ​​entered in the formula itself. To fix this error, check the spelling of the formula or use Formula Builder to have Excel build the function for you.

For example, if you are trying to execute the formula = VLOOKUP and you spell the word "VLOOKUP" incorrectly, Excel will return the #NAME? Error. After you enter the values ​​and press Enter . Check for this error below - as you can see, the "U" is missing in "= VLOOKUP".

You may also have written the formula correctly, but there is not enough information entered in the individual sections of the formula. As you can see in the example below, the array entry in the table is incomplete. Excel needs an actual worksheet name to know where to look for the desired value.

How to fix

To fix the #NAME? Error? In Excel, check the spelling of the formula you are trying to run.

If the formula is spelled correctly and the worksheet still returns an error, Excel may be confused with one of the items inside the formula. An easy way to fix this is to ask Excel to insert the formula for you. Highlight the cell where you want to run the formula, then click the "Formulas" tab in the top navigation bar. Select "Insert Function" (If you are using Microsoft Excel 2017, this option will be on the left side of the Formulas navigation bar).

When you select "Insert Function" , Formula Builder will appear on the right side of the worksheet where you can select the desired formula. Excel will then guide you through each step of the formula in separate fields, as shown below, to make sure that no errors occur and the program can read your cell correctly.

3. #####

When you see ##### displayed in the box, you may find it a bit scary. The good news is that this error simply means that the column is not wide enough to display the value you have entered. And so the fix is ​​easy.

How to fix

Click the right border of the column header and increase the column width.

Tip : You can double click the right border of the header to automatically match the widest cell in that column.

4. # DIV / 0!

When you see # DIV / 0 !, you're asking Excel to divide the formula by zeros or a blank cell. Just like when you do a division manually or on a computer, this calculation will not work in Excel.

How to fix

This error is quite easy to solve. Just change the value of the cell to a value other than zero or add a value if the cell is empty. Here is an example:

5. #REF!

This can sometimes be a bit difficult to find the cause, but Excel often displays #REF! when a formula refers to an invalid cell.

What is the #REF error in Excel?

Error #REF! In Excel appears if you are running a formula that refers to a non-existing cell. If you delete a cell, column or row in your worksheet, and formulate a formula that includes the deleted cell name, Excel returns the #REF! Error. in the cell containing the formula.

So what does this error really mean? That means you may have accidentally deleted or pasted on a cell used in the formula. For example, in the "Outcome" column below, reference the formula: = SUM (A2, B2, C2).

If you accidentally delete the "Number 2" column, you will see this error:

How to fix

Before you paste over a set of cells, make sure no formulas refer to the cells you are deleting. Also, when deleting cells, it is important to check which formulas are referenced in those cells.

Tip : If you accidentally delete a few cells, you can click the Undo button on the Quick Access Toolbar (or press CTRL + Z for PC / Command + Z for Mac) to restore them.

6. #NULL!

Error #NULL! occurs when you specify the intersection point of two areas that do not actually intersect or use the range operator incorrectly.

How to fix

First, check to make sure you are using the correct syntax in your formula.

You should use colons to separate the first cell from the last cell, when referring to a continuous range of cells in the formula.

On the other hand, you should use commas when you refer to two non-intersecting ranges.

7. # N / A

When you see # N / A, this usually means that the numbers you are referring to in the formula cannot be found.

You may have accidentally deleted some or rows that are being used in formulas or refer to a deleted or not saved sheet.

For advanced users, one of the most common causes of # N / A errors is when the cell from the formula referenced in VLOOKUP cannot be found.

How to fix

Carefully examine all formulas and be sure to carefully review spreadsheets or rows that may have been deleted or reference incorrectly. If you have several formulas linked to each other, check that everything in each formula is valid.

For advanced users using VLOOKUP functions, please refer to the instructions: How to fix VLOOKUP errors in Excel.

8. #NUM!

If the formula contains invalid numeric values, you will see the #NUM! Error. appear in Excel. Typically, this happens when you enter a numeric value that is different from the other arguments used in the formula.

For example, when you enter an Excel formula, make sure you do not include values ​​like $ 1,000 . Instead, enter 1000 and then format the cell with currency symbols and commas after the formula is calculated.

How to fix

Check that you have entered any currencies, formats or special symbols. Then make sure to remove those characters from the formula, keeping only the numbers.

Here's how you can format numbers after removing commas and currencies from your formula:

(To adjust the currency unit, select the small triangle to the right of the icon to select an option from the drop-down menu.)