Skill Level Beginner
- [Instructor] When data is entered manually into our tracker, it's important for entries to remain consistent, and we can use drop-down lists to help us. Here, you and I are admins who receive emails with course details, and we've been asked to track the number of students and total revenue for each city. The cities where we have courses is our list that starts in cell H15, Amsterdam through Toronto. And we've started tracking our data in the table that starts in cell B3.
We have a pivot table giving us a summary, and that's in cell H3, and notice there's a problem, do you see it? We have an Ft. Lauderdale for Ft. Lauderdale, and there is a F-O-R-T Lauderdale. Those should be the same thing. Now, let's use dorp-down lists to prevent this from happening. Okay, let's take the list of our courses. I'm going to move it over here. We want to put Cities up here.
And I'm leaving a blank so that blank is an option when we have our drop-down list made. Put this data into a table, Home, Format As Table, let's go with this nice blue here, and it does have headers. Okay, now we have to make a named range to tie to our drop-down list. I like these entries, and let's call this Cities. And press Enter. Okay, let's see that it's there, there it is.
Cities, is one of our named ranges. We want drop-down lists in this City column, so let's highlight it, Data, Data Validation, Data Validation, List, we do want to In-Cell Dropdown, we want to uncheck Ignore Blank. Equals cities, and we can go to our Error Alert and even put please choose from the list.
And we're selecting Stop so that a person will not be able to put something goofy in our dataset. Okay, now, we can see our drop-down list entries. And notice that we have an error flag at this F-O-R-T Lauderdale, because that is not a legitimate entry. Let's choose Ft. Lauderdale. And now, we have another city, New York, and that was August.
New York, please choose from the list, Cancel. Here's what we have to do, we can go over here, New York, it is now available for us to choose, it is legitimate, and there were 40 students, and revenue of $41,000. And let's go and refresh our pivot table, Refresh All. Now we have New York, just one Ft. Lauderdale.
Everything is all squared away, and we were greatly helped by having a drop-down list control the entry of the legitimate options.