How To Use The Circle Invalid Data Option With Data Validation in Excel

Here’s how to use the Circle Invalid Data option with Data Validation in Excel:

You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.


I have a data range to which I want to limit the entry of data. I will use Data Validation so that it will just provide the user an information box if they enter a whole number between 5 and 20. To do so I will highlight the range, select Data Validation from the Data group on the Ribbon, and enter the following:


And here I will set it for just Information only:


So, if I enter a number between 5 and 20, it will give me a warning, but still allow the number to be entered:


Now, I am going to use RANDBETWEEN to enter a lot of numbers in this data range, some of which will be invalid. Excel will allow me to do this, but the question is, how do I find the numbers that are invalid:


Go the the Data Validation option on the Data tab of the Ribbon and select “Circle Invalid Data”:


Excel will highlight all the values that are invalid based upon the Data Validation rule that was set up:


However, one word of caution. If you are using a volatile function like RANDBETWEEN (which recalculates each time a change is make to the worksheet), notice how the circles stay in the same place even though the values have changed:


Make sure that each time your sheet recalculates, you need to run the Circle Invalid Data option again to reset the circles:


If you need a little refresher on the use of Data Validation, please see some of the posts I’ve done in the past listed below:

Data Validation Part 1 – Lists

Data Validation Part 2 – Dates & Values

Data Validation w/Tables & INDIRECT

Hyperlinks Using Data Validation

Dynamic Data Validation Lists

Data Validation Limiting Choices

Happy Excelling!

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

No comments yet.

Leave a Reply