Examples of flagged errors in formulas

In this video, we'll look at a few examples of the kind of errors that Excel will flag on a worksheet and the rules that control these errors.

First, to recap, the rules that govern the errors that Excel flags are located at options > formulas > error checking rules

Each rule can be disabled individually or, you can turn off background error checking completely. If you turn off background error checking, you're disabling all the rules that visually flag information on the worksheet in the background. 

With background error checking disabled, you can still ask Excel to check for errors by clicking the "Check for Errors" button on the Formulas tab of the ribbon. Excel will then allow you navigate to cells with errors, but they aren't visually flagged on the worksheet.

I'll re-enable background error checking so we can more easily see the errors flagged in this worksheet.

Now let's look at a few examples of common errors that are flagged when all the error rules are enabled.

First, remember that some formulas return an explicit error code, as we've already looked at. These are definite errors as opposed to potential errors.

For example, here we see a Divide by Zero error and a Value error. Note that both cells are flagged as errors in addition to displaying an error code.

These errors are being flagged by the rule that checks formulas that result in an error.

Next, we have an inconsistent formula flagged. The rule that's flagging this problem checks formulas that appear in table columns for inconsistencies

This formula should be the same as the formula above and below, but it's not.

In the next example, we see a formula for running totals that's being flagged as an error. The reason for this that the formula omits other adjacent cells in the region. This is the way running totals work and so it's actually not a problem.

The next cell with an error is a number stored as text. The rule that checks for numbers as text can also be disabled.

The final example is a SUM formula. The formula is flagged because the reference includes a cell that is blank.

So that's a few common examples of errors you might see flagged. As you can see, it's easy to disable a rule individually or even turn off background checking entirely. But if you're just getting started with formulas in Excel, I'd recommend that you leave the flags enabled and pay attention to the kinds of errors that are flagged.

As you gain more experience, you can experiment with ignoring or disabling rules that aren't helpful to you.