How to Circle All the Invalid Data in Excel at Once

Date:2019-9-27 Author:Cordelia

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.

How to Circle All the Invalid Data in Excel at Once

2.Go to Data tab.

How to Circle All the Invalid Data in Excel at Once

3.Click Data ValidationData Validation…

How to Circle All the Invalid Data in Excel at Once

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

How to Circle All the Invalid Data in Excel at Once

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:

How to Circle All the Invalid Data in Excel at Once

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

How to Circle All the Invalid Data in Excel at Once

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

How to Circle All the Invalid Data in Excel at Once

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

How to Circle All the Invalid Data in Excel at Once

Copyright Statement: Regarding all of the posts by this website, any copy or use shall get the written permission or authorization from Myofficetricks.

Leave a Reply

Your email address will not be published. Required fields are marked *