Find errors in a range

Learn how to find errors in a range using conditional formatting. We will apply a simple formula to identificate missing or incorrect data.

Manual data entry in Excel, not the best decision if you are using sensitive data. Today’s post explains how to highlight missing or incorrect data, errors, or incorrect data types.

Before we take a deep dive, check our ultimate guide about conditional formatting in Excel.

Let us assume that we are working with raw data. Using this method, we can quickly find and identify those errors and then fix them. It is worthy to note that color formatting is a splendid addition to a data cleaning.

In this example, we’ll show you how to quickly clean up your table.

Find errors using conditional formatting

Okay, here we go! We’ll use a basic workflow.

  1. Test any row in a range.
  2. Find incorrect and inappropriate records.
  3. Highlight the given rows.

Steps to highlighting errors in a range

To find the cells which contain errors, we’ll use the ISERROR function. If we want to find and highlight the blank cells in a range, use the ISBLANK function.

Conditional formatting evaluates the formula. If the result is TRUE, the given cell will be highlighted.

Because we have a table with four columns, apply the formula below to find errors in a range.

1. Select the range which contains data

select the range which contains data

2. Go to the Data tab on the ribbon and click conditional formatting

click cf icon

3. Click new rule

4. Choose ‘use a formula to determine which cells to format.’

select new rule for formula

5. A new window will appear

6. Locate the ‘Format values where this formula is true’ section

apply or iferror iserror formula

7. Enter the formula below

=OR(ISBLANK($A2),ISBLANK($B2),ISBLANK($C2),ISBLANK($A2),ISBLANK($B2),ISBLANK($C2),ISBLANK($D2))

8. Choose a formatting style and click OK

find errors in a range highlight values

Find errors using a helper column

If you want to use formulas to find errors in a range, enter the formula in cell E2 and copy the formula down.

find errors in a range using regular formulas
=OR(ISBLANK($A2),ISBLANK($B2),ISBLANK($C2),ISBLANK($A2),ISBLANK($B2),ISBLANK($C2),ISBLANK($D2))

In the example, The OR function has two outputs:

  • TRUE – the cell contains errors and / or blanks
  • FALSE – everything is OK

Additional resources and examples