From the course: Excel: Data Validation in Depth

Creating drop-down lists - Microsoft Excel Tutorial

From the course: Excel: Data Validation in Depth

Start my 1-month free trial Buy for my team

Creating drop-down lists

“

- [Narrator] Data validation isn't always about whole numbers or controlling the length of a field. It can also be about supplying the information you want people to fill out through dropdowns. Who doesn't love a good dropdown box? Why? It removes the guesswork. It also removes miskeying of information when there are pre-established lists. We all win. Let's look at the opportunities for using dropdowns on our training request form. Let's take a look at type. This could be virtual, in-person, training videos, or books. Instead of giving instructions on what goes there, we can just set up a dropdown box. Let's go ahead and create a sheet for our dropdown list and let's call it lists. Okay, I'll go ahead and do type, in person, virtual, video, book, and other. All right, I'll go ahead and make that type bold. Now I'll go to my training request and I'll highlight E12 through E22. I'll go to data, data validation, and from the allow dropdown, I'll choose list. Now the source, this is where the data lives so I'll go ahead and choose lists, and I'll highlight A2 through A6. Now it's going to always point to A2 through A6 to pull a list of values. All right, I'll go ahead and click OK. Now by type, I actually have a dropdown that gives me all of the predefined options. Okay, it's always a good idea to test. Okay, now the question becomes, do we want to allow people to add their own values? So let's go ahead and highlight the list again. Let's go to data validation. Let's go to the error alert. Right now if we leave it at stop, then it will only allow what is defined in the list. Let's go ahead and make it a warning and let's do this as the approved list. Please be sure you choose from the approved list. All right, we're going to go ahead and click OK and I'll show you what that looks like. So if someone says hybrid, when they press Enter, it's going to give them the message and it's a warning. It says, please be sure to choose from the approved list, but it does give us an option to continue. So if we choose yes here, it'll accept that hybrid response. That's a process and an approval decision that needs to be made whenever you create these lists. All right, let's go ahead and choose cancel here. We're going to go ahead and highlight it again. Based on our business rules, you can only choose from what's on the list so I'll go to data validation. I'll change this warning to stop, click OK. I'll go ahead and type hybrid again just so you can see. And now we get retry and cancel, meaning we can't add our own values. All right, I'll go ahead and choose cancel there and that takes the value away. Basic lists can be valuable to both the user who fills out the information and the receiver who works with it after the fact. It's a very simple way to control and get valid data every time.

Contents