How to Circle All the Invalid Data in Excel at Once

It’s not surprising that there’s some invalid data in an large Excel spreadsheet. If you want to screen out all of them at once, the best idea is to set Data Validation in Excel, and then choose to Circle Invalid Data.

Here’re the detailed steps:

1.Select the cells.

2.Go to Data tab.

3.Click Data Validation – Data Validation…

4.Click the down arrow beside Any value, there are some pre-set criteria in the list you can choose directly sumo suit.

5.In my case, I want to analyze the data between 70-90 only, and circle all the data smaller than 70 & larger than 89. So the setting should be as the picture:

6.Now the validation is set. Next step is to click Data Validation again and choose Circle Invalid Data.

7.You can see all the data smaller than 70 and larger than 89 have been circled.

8. If you don’t want these red circles any more, just click Data Validation and hit Clear Validation Data.