7 most common Excel errors for office people

It is true that Excel is not simply setting up tables and entering numbers. In fact, you will encounter a lot of problems when using this software and unfortunately not everyone knows how to fix it.

If you are a marketer, chances are you will know all these troubles when using Excel. From accidentally deleting the numbers to entering the wrong numbers, just one click is enough to make the sheet you spent on setting up completely useless, let alone spend time or you will have to start over.

Not only you. Even users who are considered "holy" Exel with a lot of experience and tips when using this software at least, must also experience several unexpected errors. For this reason, capturing common errors as well as how to fix them is something that anyone who regularly uses Excel in both learning and work must understand.

Practicing regularly and properly will help you reduce the number of mistakes.

A few tricks you can learn from Excel experts of Hubspot.

1. Each formula in Excel starts with a "=".
2. Use an asterisk (*) to multiply numbers together, not "X" characters.
3. Use open and closed parentheses to group numbers / cell addresses together.
4. Use quotation marks if you want to enter character-type arguments into the formula. If two parentheses are used to create quotation marks, Excel will report a fatal error.

7 most common Excel errors and how to fix them

We all have at least one function entry and "get" Excel error with a blue triangle icon in the upper left corner of that Cell. This implies that you used the wrong formula or entered the wrong value.

Now, if you enter the symbol "i" as shown in the picture below, you will have enough information to fix the error. However, in some cases the problem is not easy if you want to resolve quickly.

1. #VALUE!

Excel displays the #VALUE error when it detects in a formula with spaces, characters, or text that should have been entered in the number.

Except for using quotation marks, Excel will only allow numbers. Therefore, if after entering that Excel Excel has no response or error, then the formula already contains the "strange" argument.

Edit:

The simplest way to fix this is to double-click on the recipe and make sure you enter only the numbers. If the error continues, check the other Cells, see if you have entered values ​​into those Cells or do they contain strange characters?

In the above example, the "Sum" column refers to empty cells (not yet entered values). Excel cannot calculate the sum of non-valued cells, so it will report an error.

2. #####

When you see ##### displayed in Cell, some beginners who use Excel will feel a bit scared. However, the good news is that this error simply means that the column is not large enough to contain the values ​​you entered and of course, the solution is extremely easy.

Edit:

Move the mouse pointer to the right edge of the column name, corresponding to the column or line you want to adjust the width of. When the mouse pointer changes to an intersection with the line as shown below, click and hold the mouse.

Alternatively, you can double-click this location to automatically adjust the width of the box.

3. # DIV / 0!

When error # DIV / 0 occurs! that is, you enter the wrong formula (eg = 4/0) or when the formula refers to a cell equal to 0 or left blank. In Excel, this expression cannot be performed.

Edit:

Make sure the divisor in the function or formula is not a cell with a value of 0 or an empty cell. In addition, Excel also offers a number of other fixes that you can access here for more details.

4. #REF!

#REF error! appears when the formula references an invalid cell, most often when the cell referenced by the formula has been deleted or pasted.

For example, in the image below, the Outcome column is referenced by the formula: = SUM (A2, B2, C2). If you delete column "Number 2", Excel will issue a #REF!

Edit:

If you accidentally delete rows or columns, immediately press the Undo button on the Quick Access Toolbar or press the Ctrol + Z key combination (for Windows) or Command + Z (for with Mac).

Adjust the formula so that it uses a range reference instead of an individual cell, such as = SUM (A1; B1). Now, when deleting any column in the total range, Excel will automatically adjust the corresponding formula.

5. #NULL!

Error #NULL! Occurs when you use the cell range operator incorrectly in the formula or when using the intersection operator (space character) between the cell range reference to determine the intersection of two areas without intersection.

To understand more, you need to remember the following two things:

1. A colon (:) to separate the first cell from the last cell when you refer to the continuous cell range in the formula, such as = SUM (A1: A5) referring to the range that includes cells A1 to A5.
2. A comma (,) is a binding operator when referring to two regions that do not intersect. For example, if the formula calculates the two ranges (= SUM (A1: A5, B11: B5)), make sure there is a comma separated between them.

Edit:

First, check that you used the correct syntax in the formula.

Second, if this error still occurs, then you have used spaces between non-intersecting ranges, now change the reference or use the correct mark.

6. # N / A

When the # N / A error occurs, the reference numbers in the formula cannot be found. In addition, another common cause is when using the VLOOKUP, HLOOKUP, LOOKUP or MATCH functions, and a formula cannot find a referenced value, such as a lookup value that does not exist in the source data. .

Edit:

Check all formulas and see if any sheets or rows have been deleted or referenced incorrectly. If an error is related to VLOOKUP, you can refer to how to fix it here .

7. #NUM!

If the formula contains invalid numeric values, #NUM! will appear. Typically, this situation occurs when you have entered a numeric value using an unsupported numeric or data type in the argument section of the formula, such as not being able to enter a value of $ 1,000 in currency format.

Edit:

Check if you have entered a currency, date or special icon into the recipe? Then remove them from the formula and keep only the numbers.

In addition, there is another fix that you can follow under the following image.

Good luck.