Investigating formula errors in Microsoft Excel 2019

A few examples of the types of formula errors that could occur inExcel are shown next. For a more comprehensive list, please consult theHelp facility within the Excelprogram. You can search for help by pressing the F1 key on thekeyboard, which will open the Helppane to the right of the workbook. In this pane, search for formulaerror, where you will find a huge range of categories with somevery useful troubleshooting tips, as illustrated in the followingscreenshot:

ms office 423

When entering a formula into a cell, various error messages displayif there is something wrong. The calculations are therefore not able towork. Error messages normally always begin with #. Here are a few examples:

  • Do not divide by 0. The #DIV/0 error will appear in thecell if you try to divide by 0.
  • Enter values without formatting. For instance, do not enter thecurrency sign before a value in a cell, as the cell will be identifiedas text and not as a value. Apply formatting after the value is enteredinto the cell. This is a cosmetic change to the value in the cell.
  • Enter the correct type of arguments. If you are working with a textfunction, then be sure to use text as the cell argument.
  • Enter all arguments required for the function. The formula will notbe complete if you do not do so.
  • Use a colon to indicate a range—for example, =sum(A4:E7).
  • Use a parenthesis at both ends of the formula and make sure you havethe correct number of brackets depending on the number of functions youuse in a formula.
  • Start every formula with an = sign.
  • #### displayed in the cell means thatthe cell is not wide enough to display the result of the formula.
  • #ref! indicates that an invalid cellreference is included in the formula.
  • #VALUE! means an incorrect operator ortext is included in the formula.
  • #NAME? means a non-existing name rangeis included in the formula.
  • #NULL! means a space is incorrectlyincluded in the formula to separate cell references.
  • #NUM! refers to a number incorrectlyused in the formula.
  • #n/a means that the value is notavailable.

When a formula refers back to its own cell, it createsa circular reference. You can tell if a workbookcontains circular references in manydifferent ways, illustrated here:

  • By consulting the status bar, you will see the Circular References tab, along with theproblem cell, as illustrated in the following screenshot:

ms office 696

  • This provides a notification to the user via a dialog box on openingthe workbook, as illustrated in the following screenshot:

ms office 464

  • When you press Enter to accept an incorrect formula, anerror will appear at the top-left corner of the cell, then the Error Checking icon will appear to guide youto fix the error, as illustrated in the following screenshot:

ms office 855

  • You can visit the Error Checkingicon in the Formula Auditing group,located on the Formulas tab. From thedrop-down list, select CircularReferences. Here, you will see the location of the problem cell,or use the Error Checking icon to getsome help. You can see the ErrorChecking and the CircularReferences icons in the following screenshot:

ms office 560

We will see a circular reference error next. We will use theSSGProductSales.xlsx workbook for thisexample.

A circular reference appears whenthe formula entered refers back to the source cell. The formula includesthe cell in which the answer should appear. To correct this error,change the formula to reflect the correct cell references, asfollows: 

  1. Double-click on cell E10 onthe SSGProducts worksheet.
  2. With the formula now visible, look at the cell references tounderstand that the cell range is incorrect and refers back (includes)to the answer cell E10, which is incorrect. Alter theformula to read =SUM(E3:E9), thenpress Enter.
  3. The formula errors are removed from the workbook.

Note that, unfortunately, Excel Help will not be able to fix the error foryou in this instance, and only human interaction will do the trick!

Leave a Comment