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.
- Test any row in a range.
- Find incorrect and inappropriate records.
- 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
2. Go to the Data tab on the ribbon and click conditional formatting
3. Click new rule
4. Choose ‘use a formula to determine which cells to format.’
5. A new window will appear
6. Locate the ‘Format values where this formula is true’ section
7. Enter the formula below
8. Choose a formatting style and click OK
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.
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