Hidden Gems of MS Excel - Data Validation - Circle Invalid Data

Q) You apply Data Validation to a range of Cells.

e.g. You apply Data Validation Drop-down List of 5 Indian Cities: Indore, Lucknow, Mumbai, New Delhi, Pune, to a range of Cells

What if invalid data is already entered in few of those cells?

How could those cells be easily identified (without having to manually search for those cells)?

A) Step 1: That particular worksheet should be active (on which You want to identify the cells with invalid data)

Step 2: Click on Data tab
Step 3: Data Tools group > click on Data Validation drop-down

Step 4: Click on Circle Invalid Data

Result: the Cells with invalid data will be Circled as below.

Once You have identified the invalid data, You can Hide the Circles:

Step 1: That particular worksheet should be active (on which You want to identify the cells with invalid data)

Step 2: Click on Data tab
Step 3: Data Tools group > click on Data Validation drop-down

Step 4: Click on Clear Validation Circles

*Source: Microsoft Official Website