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:

dc1

And here I will set it for just Information only:

dc2

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

dc3

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:

dc4

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

dc5

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

dc6

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:

dc7

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

dc8

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!

Related Post

Free Download!

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

, , ,

No comments yet.

Leave a Reply