Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
The data validation capability in Excel is designed to work for new data, but sometimes you want to apply rules to an existing worksheet. There's a lot of data there already, but in effect you're saying, from now on I have this restriction. Let's imagine that in Column I here, I'm going to put in a restriction that says, any salary that we put in here must be over 20,000. And you can see already that there are a few entries there that don't meet that criterion. We're not going to change what's there, data validation can't do that.
But data validation can show us where there are exceptions. And surely, an alternative way of doing that here would be simply to sort the data. Let's say we don't want to sort the data, we can't do that right now. Let's apply a data validation rule here in Column I that says in effect from now on, these must be Whole numbers that are greater than 20000. Okay, so that's the new rule.
From now on, if I change this salary here, maybe I'll say that's going to be 19,000, doesn't work. I can't sort the data right now for a couple of different reasons, and I want to know which records have violated my new rule. What can we do here? Go to Data Validation>Circle Invalid Data. There are the records that violate our rule. That's a good visual, however, there's not much you can do with it, other than to see it.
I think it does point out the idea, that yes, those are the ones we need to change or at least consider, but you can find records that violate rules. And possibly you could have some scenarios where you really don't want to use the validation tool for controlling anything; you just want certain data to stand out. So apply a rule and then find out which cells violate the rule by using this feature. And if we no longer need this here, we simply go, if it's too late to do an Undo, go back to Data Validation, click the drop arrow and choose Clear Validation Circles. There we go! A great tool for locating cells that violate data validation rules.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 69864 Viewers
80 Video lessons · 127307 Viewers
52 Video lessons · 62306 Viewers
59 Video lessons · 47990 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.