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 in what I'm asking for, and 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, two, or three, and it's as simple as that.

I just type in the values 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 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 OK.

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 OK, 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 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 in a value for every single employee, so I don't want that. So I'll just press Delete and then rechoose 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 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 OK. 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 from 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 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 into the Data Validation dialog box or choosing values that are listed somewhere else on the spreadsheet that are organized in a list.