Common mistakes in Excel and how to fix them

You are new to Excel and have many surprises. You encounter some common errors but don't know how to fix them. The following article, TipsMake will guide you how to fix some common Excel errors when you work!

1. Error not seeing the name of the row or column.

When you open the Excel spreadsheet and you don't see the names of the columns (A, B, C, D .) and the names of the rows (1, 2, 3, 4 .), what are the solutions?

You go to File, select Excel Options.Go to Advanced, then check Show Row and Column headers, then click Ok is finished!

Common mistakes in Excel and how to fix them Picture 1

2. Error with missing lines - common mistake in Excel

When Excel spreadsheet does not display vertical and horizontal lines surrounding cells, go to View and check Gridlines.

Common mistakes in Excel and how to fix them Picture 2

If you want to select the color of the border, go to File, select Excel Options.Go to the Advanced section.You can then choose the color of the grid lines you like and click OK.

Common mistakes in Excel and how to fix them Picture 3

3. Error losing sheet tab

There are a lot of sheets in the worksheet, but the sheet tabs are invisible, making it impossible to open other sheets.

You go to File, select excel Options.Go to the Advanced section.Check Show sheet tabs.

Common mistakes in Excel and how to fix them Picture 4

4. Column showing numbers 1,2,3 . instead of A, B, C .

When you open the spreadsheet and do not see the names of the columns displayed by the letters A, B, C . but instead are digits 1, 2, 3 . then you go to File, select Excel Options. Go to Formulars. Then uncheck the R1C1 Reference style and OK.

Common mistakes in Excel and how to fix them Picture 5

5. Press Enter. The cursor jumps up.

When pressing the Enter key to finish typing, the cursor jumps to the upper cell, the right cell, or the left cell without jumping to the lower cell.

You go to File, select excel Options, select Advanced.In the Direction section select Down and click OK.

Common mistakes in Excel and how to fix them Picture 6

6. Faulty arrow keys

When you press the arrow keys on the keyboard, then scroll the screen instead of jumping to the box above, below, left, right.To fix, see if the light of the Scroll Lock key is lit, if it is, then press the Scroll Lock key to turn it off.

7. #NULL.Data error is empty

Use an inappropriate math sequence

Use an array without separators.

(This error occurs when using operators like = SUM (A1: A5 B1: B5), there is no intersection between these two functions, to fix you, you must determine the exact intersection point between the two functions. .

8. ### Width error

When you enter the date or time value as a negative number.

When the column is missing width.Use the mouse to drag the width of the column accordingly

9. #VALUE!Error value

+ You enter a string formula while the function requires a number or a logical value.

+ Enter one or edit the components of the formula that press Enter.

+ Enter too many parameters for an operator or a function while they only use one parameter.Example = LEFT (A2: A5)

+ Execute a macro (automatic command) related to a function that returns #VALUE!

(Occurs when there are calculations that do not have the same data type. For example, the formula = A1 + A2, where A1 has a value of 1, A2 is Hanoi. Because 1 is an arithmetic character, Hanoi is a character string, so it cannot perform calculations. To fix this, you have to edit the values ​​of the calculations with the same data type as arithmetic characters or character sequences.)

10. # DIV / 0!Error divided by 0

Enter the divisor formula as 0. For example = MOD (10.0).

The divisor in the formula is a reference to a blank cell.

11. #NAME!Wrong name.

You use non-permanent functions in Excel such as EDATE, EOMONTH, NETWORKDAYS, WORKDAYS, . Then you need to go to the Tools - Add-in menu.Tick ​​the Analysis ToolPak utility.

Common mistakes in Excel and how to fix them Picture 7

+ Enter the wrong name for a function.This case happens when you use Vietnamese keyboard in Telex mode to accidentally make a function name like IF become Ì, VLOOKUP to VLÔKUP.

+ Use characters not allowed in the formula.

+ Enter a string in the formula without closing and opening quotation marks. ''

+ No colon: in the cell address range in the formula.

(Excel does not recognize the name in the formula. For example, the function = SM (BBB), in this function, both the formula name and the array name refer to this function. If you want to fix it, you must correct the name of the function or enter it correctly. Array needs reference.)

12. # N / A Data error

+ Return value is incompatible with [color =] [/ color] detection functions such as VLOOKUP, HLOOKUP, LOOKUP or MATCH.

+ Use HLOOKUP, VLOOKUP, MATCH to return a value in an unsorted table.

+ Inconsistent data when you use array addresses in Excel.

+ Forget one or more arguments in the custom function.

+ Using an improperly created function.

(Occurs when the formula refers to a cell in an array but the cell has no value. This error often occurs with search functions such as VLOOKUP, HLOOKUP, etc. You just need to carefully review the formula and edit the cell. The function refers to.)

13. #REF!Wrong reference region

+ Delete cells that are referenced by the formula.Or paste the values ​​generated from the formula into the formula reference area itself.

(This is a cell reference error, for example, if you delete a row or column that is used in a formula, the #REF! Error will be displayed.)

+ Link or reference to an application that cannot be run.

14. #NUM!Number data error

+ Using an inappropriate argument in the formula using an argument that is numeric data.For example, you enter a negative number formula while it only calculates positive numbers.

+ Using the function repeatedly leads to a function that cannot find the returned results.

+ Using a function that returns a number too large or too small for the calculation capability of Excel.

(The numeric value in the formula is not valid, the number is too large or too small. For example, the calculation = 99 ^ 999 will get the #NUM! Error, you just need to reduce the recalculated value).

The above is a summary of common errors in Excel and how to fix them.Hope the article helps you.

TipsMake wish you a successful reading!