In this article, we will describe Excel errors regarding formulas. Hopefully, after going through the article, you will know the cause of the errors and the solutions.
Before we go over the errors and tips, we wanted to share a few helpful tricks we’ve learned from our experiences. These precautionary lessons are designed to help you avoid formula errors in Excel altogether, making your life a whole lot easier:
- Begin every formula with an equal sign.
- Use the * symbol to multiply numbers, not an X.
- Match all opening and closing parentheses so that they are in pairs.
- Use a colon to indicate a range.
- Use quotation marks around the text in formulas.
- Enter all required arguments for a function.
- Enter the correct type of arguments (i.e., type of data formats).
- Enclose other sheet names in single quotation marks.
- Place an exclamation point (!) after a worksheet name when you refer to it in a formula.
- Keep your data entry and formula cells separate.
- Names (i.e., Name Ranges) & Table Names shouldn’t conflict with cell references.
- Enforce data entry rules with Data Validation.
- Keep the formula short and simple.
- Enter numbers without formatting – i.e., Don’t type a comma or provide space while entering a number.
- Avoid Long numbers (> 11 digits).
Why Excel Error Messages Appear
When you use functions in Excel, they expect their inputs to have certain characteristics. For example, when you use SUM to add cells together, Excel assumes that the references are numbers. Whenever Excel doesn’t find what it expects, it will return an error message. Excel alerts those errors by putting different symbols in the cell.
List of Common Excel Error Values:
Excel errors use several error values, all of which begin with the number sign (#). Listed below are common error values displayed by Excel, along with some common causes and solutions to help correct the problem.
|#####||Cell has a negative date or time value, or the column width isn’t wide.||This error is indicated it simply means the column isn’t wide enough to display the value, which is inputted, or cell has a negative date or time value.|
|#DIV/0!||Division by 0 or blank cell. (eg:=9/0, or =A2/0)||The division operation in the formula refers to a cell that contains the value 0 or is blank.|
|#N/A||Not Available or No ValueAvailable||Technically, this is not an error value. Error #N/A occurs when a formula or a function inside a formula cannot find the referenced data.|
|#NAME?||Text in the formula is not recognized||This error value appears when you incorrectly type the range name, refer to a deleted range name, or forget to put quotation marks around a text string in a formula.|
|#NULL!||It means when the two or more cell references are separated incorrectly or unintentionally by a space in a formula.||Because a space indicates an intersection, this error will occur if you insert a space instead of a comma (the union operator) between ranges used in function arguments.|
|#NUM!||A Formula has invalid numeric data for the type of operation.||This error can be caused by an invalid argument in an Excel function or a formula that produces a number too large or too small to be represented in the worksheet.|
|#REF!||A reference is invalid||This error occurs when you delete a cell referred to in the formula or if you paste cells over the ones referred to in the formula.|
|#VALUE!||The Wrong type of argument in a function or operator is used.||This error is most often the result of specifying a mathematical operation with one or more cells that contain text.|
Below is an example of an #NA error
If you manually enter an error value directly in a cell, it is stored as that error value but is not marked as an error. However, if a formula in another cell contains a reference to that cell, the formula returns the error value from that cell.
Using the Error Alert Button
When a formula yields an error value (other than #N/A) in a cell, Excel displays a green triangular error indicator in the upper left corner of cells. The green triangle indicates that the cell contents violate one of Excel’s error checking rules.
When you select a cell that contains a green triangle, the Trace Error Button, appears next to the cell in which the formula error occurs. The Trace Error button is a yellow diamond is Excel’s error options button and it contains options for correcting the perceived error.
Hovering the mouse pointer over the error options button displays a text message, known as hover text, that explains the reason for the error value.
- Help on This Error: Opens an Excel Help window with information on the type of error value in the active cell and how to correct it.
- Show Calculation Steps: Opens the Evaluate Formula dialog box where you can walk through each step in the calculation to see the result of each computation.
- Ignore Error: Bypasses error checking for this cell and removes the error alert and Error options button from it.
- Edit in Formula Bar: Activates Edit mode and puts the insertion point at the end of the formula on the Formula bar.
- Error Checking Options: Opens the Formulas tab of the Excel Options dialog box, where you can modify the options used in checking the worksheet for formula errors.
If you found this article on Excel Errors helpful, then share it with your friends and colleagues to make their life a little easy. If you think that we have missed out on some important stuff, then mention it in the comments box below.