In this tutorial, we'll review how to use data validation in Excel to organize lists more efficiently and control inputs.
Data Validation
What is Data Validation?
Data validation provides you a way to validate the information that's being inputted into Excel using either a dropdown or just regular cell input by making sure the information that's added to the cell meets certain criteria.
Exercise 1
Now in this exercise, we're going to focus on the dropdown menu. That is one of the options you have with data validation. I'm going to start with exercise one and I'm going to pretend that I need to send a form out to supervisors to rank their employees on a scale of one to three occasionally when I get the form back. I see responses that look like the following.
Now, there may be a misunderstanding. And what I'm asking for. I find that people are entering text values when I want them to enter numerical values from 1 to 3. So to prevent that from happening again, I'm going to go over to the data tab and I'm going to choose data validation. Now, since we're focusing on a dropdown list, that's the option I'm going to choose from the dropdown. But before we go there, notice that the kind of value that can go into this cell currently is any value. That's what I want to prevent.
So I'll click the dropdown and limit the values to values that will be found in a dropdown list. The values that I want a user to pick from when it comes to the dropdown or values that are either one, comma two, comma three. And it's as simple as that. I just type in the value separated by a comma.
I will now click OK the next time someone goes to fill out that form, they will see the following. This will let them know that the value doesn't match the data validation restrictions defined for that cell. So they can retry. And what they might notice is there's a dropdown here.
Well, let's cancel and start over. What they'll notice is there's a dropdown here and they can choose the values from either one, two or three. And then I'll simply choose three there.
Validation Lists
Another way that you can give people the option to choose values from the dropdown is referencing information somewhere else on your spreadsheet. Especially if you have a long list of values that you don't necessarily want to type all the values for inside the data validation input box.
So for this next dropdown, I'm going to go to data validation. Then I'm going to click the dropdown and choose “list”, the source of the values for the dropdown are going to be the cells underneath the region header.
I'll simply select them and then click, OK. Now, anyone looking to fill out that form can simply click the dropdown and choose any of the options below. A couple of options that are available to you if I go back to data validation.
The user can add in their own error alert. If someone does in each of the wrong values, you can create a custom error message. Also, you can add an input message. This is going to help someone when it comes to figuring out what they can do.
When it comes to entering values into that cell. So here I will simply say pick a value from the dropdown and then when I click, OK, it's available there, anytime someone clicks in the cell and reminds them there's a dropdown there that they can choose from for the values they want for that particular cell.
Exercise 2
The exercise that we work on in class relates to creating dropdowns where employees can choose whether or not they work full time or part time. So I go to data validation. Go to settings and then I'll choose “list” here. I can simply type FT, PT. I'll click.
OK, now I can't double click on the autofill handle to fill the values down because you need to have a value in the cell. Let's say I choose a value for the first employee. Now I should be able to double click, but unfortunately I've entered into value for every single employee. So I don't want that. So I'll just press delete.
And then re-chose the first value. Now all the other cells have the dropdown without having the values inside of them. Supervisor I'm going to select all this the entire superviser column. I'll head over to data validation. Choose list.
Error Alerts
I go to. Error alert. Let's take a look at another option that we have for error alert. Actually, let me first choose my supervisors. I'll do that right now.
If I go to the error alert, I can choose a different type of data validation. I can choose a type that allows people to into their own values. So I may not be the be all end all of all information.
Maybe someone is aware of a new supervisor before I ever get wind of it. So I'm going to allow them to choose their own value. I can choose to choose either warning or information and allow them to do that. I'll click, OK. For instance, here I'll click the dropdown Mary Brown, John Burke, Eli White.
Someone is aware of a new supervisor that just got hired, that supervisor's name is Garfield. We'll go in and type it. Now, this is a little different than the last dialog box we looked at. If I click, yes, it will allow me to enter that value. I'll just fill in a couple of more values and then I will say Garfield again shows up, I'll say yes and then choose some more values. Now here's the benefit.
The Benefit of Data Validation
The benefit of organizing your list. That you have when you choose data validation. And from the dropdown choose circle invalid data, if you choose that option. It will circle any values that are not already part of the dropdown.
This allows you to easily pick out what the new values are and update your dropdown list. When you're done with the circles, you can simply click the dropdown and choose clear validation circles.
Recap
So that's how to quickly and efficiently provide options for people when they're filling out your spreadsheet. You can have them choose values from a dropdown that are set up either by typing them into the data validation dialog box or choosing values that are listed somewhere else on the spreadsheet that are organized in a list.