Find errors in a range

find errors and blanks in a range excel

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

Manual data entry in Excel is not the best decision if you use sensitive data. Today’s post highlights missing or incorrect data, errors, or data types.

Before we dive deep, 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 worth noting that color formatting is a splendid addition to data cleaning.

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

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

We’ll use the ISERROR function to find the cells containing errors. Alternatively, we can use the ISBLANK function to find and highlight the blank cells in a range.

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 that 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