Common Excel Error tips and problem solving methods

Excel often shows some error value information, such as #n/a!, #VALUE!, #DIV/o!, and so on. There are many reasons for these errors, most notably because formulas do not calculate the correct results. For example, use text in a formula that requires a number, delete a cell that is referenced by a formula, or use a cell that is not wide enough to display results. Here are some common errors in Excel and how to fix them.

1.#####!

Reason: If the cell contains a number, date, or time that is wider than the cell, or if the date-time formula for the cell produces a negative value, a #####! error is generated.

Workaround: If the cell contains a number, date, or time that is wider than the cell, you can modify the column width by dragging the width between the lists. If you are using the 1900 date system, the date and time in Excel must be positive, and the earlier date or time value minus the later date or time value can cause a #####! error. If the formula is correct, you can also change the format of the cell to a non date and time type to display the value.

2. #VALUE!

An error value #value! occurs when you use an incorrect parameter or an operation object type, or when the Formula AutoCorrect feature cannot correct a formula.

Reason one: If you enter text when you need a number or logical value, Excel cannot convert the text to the correct data type.

Workaround: Confirm that the formula or function requires the correct operator or parameter, and that the formula refers to a cell that contains a valid number. For example, if cell A1 contains a number, cell A2 contains the text "status", the formula "=A1+A2" returns the error value #value!. You can add these two values with the SUM worksheet function (the SUM function ignores text): =sum (A1:A2).

Reason two: Enter a cell reference, formula, or function as an array constant.

Workaround: Verify that the array constants are not cell references, formulas, or functions.

Reason three: give a numeric range of operators or functions that require a single numeric value.

Workaround: Change the range of values to a single value. Modify the range of values so that it contains the data row or column in which the formula is located.

3. #DIV/o!

When the formula is removed by 0, an error value #div/o! is generated.

Reason one: In a formula, the divisor uses a reference to a cell that points to an empty cell or to an 0-value cell (in Excel, if the Operation object is a blank cell, Excel treats this null value as a 0 value).

Workaround: Modify the cell reference, or enter a value that is not zero in the cell used as a divisor.

Reason two: The formula entered contains an obvious divisor of 0, for example: =5/0.

Workaround: Change 0 to a value other than 0.

4. #NAME?

Error values are generated when you use text that is not recognized by Excel in a formula #name?

Reason one: Delete the name used in the formula, or use a name that does not exist.

Workaround: Verify that the name you are using does exist. Select Menu "Insert &line;" Name "&line;" Definition command, if the name you want is not listed, use the Define command to add the appropriate name.

Reason two: the spelling of the name is wrong.

Workaround: Modify the name of the spelling error.

Reason three: Use flags in formulas.

Workaround: Select "Tools" &line; "in Menu" Options command, open the Options dialog box, click the Recalculate tab, and under Workbook options, select the Accept Formula flag check box.

Reason four: You don't use double quotes when you enter text in a formula.

Workaround: Excel interprets it as a name, ignoring the idea that the user is ready to use it as text, enclosing the text in the formula in double quotes. For example, the following formula merges a paragraph of text "total:" with the values in cell B50: = "Grand total:" &b50

Reason five: Missing colon in reference to zone.

Workaround: Confirm that all the range references used in the formula use colons. For example: SUM (a2:b34).

5. #N/A

Reason: error value #n/a is generated when no numeric value is available in a function or formula.

Workaround: If some cells in the worksheet do not have a value for the time being, enter "#N/a" in those cells, and the formula will not count when the cells are referenced, but instead return to #n/a.

6. #REF!

An error value #ref! occurs when a cell reference is not valid.

Reason: Deletes a cell that is referenced by another formula, or pastes the moved cell into a cell that is referenced by another formula.

WORKAROUND: Change the formula or click the Undo button immediately after you delete or paste the cells to restore the cells in the worksheet.

7. #NUM!

An error value #num! occurs when a number in a formula or function has a problem.

Reason one: An unacceptable parameter is used in a function that requires a numeric parameter.

Workaround: Verify that the parameter types used in the function are correct.

Reason two: A worksheet function that uses an iterative calculation, such as IRR or rate, and the function does not produce a valid result.

Workaround: Use a different initial value for the worksheet function.

Reason three: The number produced by the formula is too large or too small to be represented by Excel.

Workaround: Modify the formula so that the result is between the range of valid digits.

8. #NULL!

An error value #null! is generated when an attempt is made to specify an intersection point for two disjoint regions.

Reason: An incorrect area operator or incorrect cell reference was used.

Workaround: If you want to refer to two disjoint areas, use the union operator comma (,). Formula to sum two areas, make sure that you use commas when referencing the two regions. such as: SUM (A1:A13,D12:D23). If you do not use commas, Excel attempts to sum cells that belong to two regions at once, but because A1:a13 and d12:d23 do not intersect, they do not have a common cell.

Tip: To toggle between displaying cell values or cell formulas, simply press CTRL + ' (above the TAB key).