Excel Data Validation Drop-Downs

Data validation. Data validation provides you a way to validate the information that's being inputted into Excel using either a drop down or just regular cell input by making sure the information that's added to the cell meets certain criteria. Now in this exercise we're going to focus on the drop down 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 a numerical value from one to three. 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 drop down list that's the option I'm going to choose from the drop down 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 drop down and limit the values to values that will be found in a drop down list. The values that I want a user to pick from when it comes to the drop down are values that are either one comma two comma three and just it's as simple as that.

I just type in the values separated by a comma. I will now click okay. 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 drop down here. Well let's cancel and start over.

What they'll notice is there's a drop down here and they can choose the values from either one two or three and then I'll simply choose three there. Another way that you can give people the option to choose values from the drop down 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 drop down I'm going to go to data validation then I'm going to click the drop down and choose list. The source of the values for the drop down are going to be the cells underneath the region header. I'll simply select them and then click okay.

Now anyone looking to fill out that form can simply click the drop down 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 doesn't enter the wrong value 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 drop down and then when I click okay it's available there anytime someone clicks in the cell and reminds them there's a drop down there that they can choose from for the values they want for that particular cell. The exercise that we work on in class relates to creating drop downs where employees can choose whether or not they work full-time or part-time. So I'll go to data validation, go to settings and then I'll choose list.

Here I can simply type ft, pt. I'll click okay. Now I can't double click on the auto fill 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 in a value for every single employee so I don't want that. So I'll just press delete and then she re-choose the first value.

Now all the other cells have the drop down without having the values inside of them. Supervisor, I'm going to select all the entire supervisor column. I'll head over to data validation, choose list.

I'll 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 error alert I can choose a different type of data validation. I can choose a type that allows people to enter 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 either warning or information and allow them to do that. I'll click okay. For instance here I'll click the drop down 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 of organizing your list that you have when you choose data validation and from the drop down choose circle invalid data. If you choose that option it will circle any values that are not already part of the drop down.

This allows you to easily pick out what the new values are and update your drop down list. When you're done with the circles you can simply click the drop down and choose clear validation circles. 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 drop down that are set up either by typing them in to the data validation dialog box or choosing values that are listed somewhere else on the spreadsheet that are organized in a list.