Quick, clean, and to the point

Examples of flagged errors in formulas

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

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 globally or, you can turn off background error checking to completely disable the small triangle that appears in cells are flagged.

With background error checking disabled, you can still ask Excel to check for errors by clicking the "Check for errors" button on the formula tab of the ribbon. Excel will let 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 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 for formulas that result in an error.

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

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 is that the formula omits other adjacent cells in the region. This is of course the nature of a running total.

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 entirely, or even turn off background checking entirely, but if you're just getting started with formulas in Excel, I'd recommend you leave the flags enabled, with background error checking, 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.

Dave Bruns

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.