Drop Down
By Richard Rost
12 months ago
Creating Drop-Down Lists in Microsoft Excel
In this Microsoft Excel tutorial, we will learn how to create dynamic drop-down lists that allow you to select from a list of options in your spreadsheets. I'll guide you through setting up a drop-down for states, selecting entire columns to accommodate future entries, and even show you how to move your list to a different sheet to keep your main sheet uncluttered. We will also explore how to manage dynamically changing lists, ensuring your drop-down updates with new options seamlessly. Whether you're building a simple listing or organizing large data sets, this tutorial will enhance your Excel skills.
Prerequisites
Links
Recommended Courses
Learn More!
Questions?
Please feel free to post your questions or comments below. Thanks.
Keywords
TechHelp Excel, drop-down lists, Excel drop-down, create drop-down list, Excel data validation, list of options, dynamic drop-down list, name range, absolute references, cascading drop-down list, sort drop-down list, Excel Expert Level 10, move list different sheet, Excel VBA lessons, Excel automation, custom macros, Excel programming, VBA programming, Excel trick, lookups in Excel, Excel sheet organization, relational database video, Excel tips, Excel TechHelp
Subscribe to Drop Down
Get notifications when this page is updated
Intro In this video, we'll see how to create drop-down lists in Microsoft Excel using Data Validation. I'll show you how to set up lists of options, select and deselect cells, specify your source range, and configure in-cell drop-downs. We'll also cover techniques for making your lists dynamic, such as naming ranges or using entire columns, and how to move lists to other sheets to keep your data organized. Other tips include adding new list items, sorting options, and restarting your data validation when needed.Transcript Welcome to another TechHelp video brought to you by ExcelLearningZone.com. I'm your instructor Richard Rost. Today we're going to see how to make drop-down lists in Microsoft Excel. That's where you can drop a box down and pick from a list of options.
Here I am in the spreadsheet. Let's say I have a real simple sheet. I have someone's name and I have their state. I have Rick, Joe, Bill, and Sue. Now over here, I want to be able to pick from a list of options. Maybe there are only five or six states you do business in. So come over here, wherever you want it to be, and put your list of states there: Florida, New York, PA, Texas, Vermont, etc. That's my list of states that are valid options.
Now, come over here. You can select just these cells, but we're going to assume you're going to probably add more customers to this list later on. So what I'm going to do is I'm going to select the entire column and then deselect the first row like that. I held down the control key on my keyboard and clicked on the first cell. So now I've got B2 to the very end of the sheet selected. We're excluding that header row there.
Now I'm going to go to Data, and then Data Validation is that little button right there. You want Data Validation. I know it's hard to see because I have a small video window but if you open this up a little bit more, you'll see it says Data Validation. So click on that Data Validation button and this dialog box comes up.
Under Settings, we're going to say Allow, and we're going to pick from a list of options. Ignore blank; check that off if you don't want to allow blank values. I leave it on though because I'd rather have no data than bad data. If you make it so they have to put something in there, they might enter anything. So you'd rather just leave it blank. Trust me.
And then In-cell drop-down: if you want to be able to see that little drop-down list, which we do. Now here's the important part, Source. This is the range of where your list of states is. Click this little button here. That'll collapse that window. Select your list: H3 to H7. Make sure you have absolute references and those dollar signs so that range doesn't shift up and down. Hit that button again. There's your range. Hit OK.
Now look at that. We have a little drop-down box there. Click in here. Click. You have a list of options you can pick from. See? That's nice and simple. That was really easy to do.
Now, what if you want to add to this list? Let's add Alaska, New Hampshire. Let's add Oregon. Over here, notice they're not in there. Because that list isn't dynamic. So, I like to either name the range and you can change the name, or we can just use the entire column. I'm going to slide these entries to the very top. Now let's delete the validation already. In fact, I'm going to just delete this whole column and start over: right-click, delete.
Let's start over: state. I'm going to select the column and again, I deselect that first row. Data Validation. Allow. List. Now, my source this time, move this out of the way. I'm going to pick the whole column. And it says G to G. That's the whole column. Hit OK.
Now notice we've got Pennsylvania, New Hampshire. We have the new ones in here. If I add more down here, say I add Kentucky, now Kentucky is a valid option. If I do stuff later like sort this column, now you'll see that your list should be sorted.
There are all kinds of tricks you can play. You can name this list. You could move this to a different sheet if you want to. Click to add Sheet2. I can just cut out this entire column: Control X. Come over here, and then Control V. Now if you go back to Sheet1, look at that. It's not cluttering up Sheet1, but you still have your list. There are all kinds of tricks you can play with this stuff.
If you'd like to learn more about drop-down lists in Excel, I cover them in detail in my Excel Expert Level 10 class. We talk about lots of things. And one of the cool tricks I show you is something called a cascading drop-down list where you have make and model. In this box, you can pick the make, and then this model box will only show you models from that make. So if you pick Ford, you'll only see Ford models in the next box. That's called a cascading drop-down list. That's really cool.
Now, if you're interested in going beyond Excel, I also have a new video on how to create drop-down lists in Microsoft Access. For those of you who aren't familiar with Access, it's most of what I do. It's a powerful relational database tool designed specifically for managing large sets of structured data. While Excel is great for quick lists and calculations, Access shines when you're working with related tables, complex forms, and ensuring data stays clean and organized. So if you ever struggled with lookup tables in Excel, or maintaining consistency across multiple sheets, multiple workbooks, Access can do all of that and more with built-in relationship tools.
If you're ready to take your data skills to the next level, check out my Access drop-down list video. I'll put a link down below. It'll show you how to do it the right way. I'll even throw in a cool trick using VBA for those who like to tinker under the hood with some programming.
That's going to do it for today, folks. That is your TechHelp video for today. Hope you learned something. Live long and prosper, my friends. I'll see you next time.
I'm excited to announce that I'm creating a brand-new series of lessons focused on programming in Microsoft Excel VBA. If you've been looking to take your Excel skills to the next level and learn how to automate tasks, write custom macros, and unleash the full power of Excel VBA, these lessons are for you. Sign up now on my website at the link shown. You'll find a copy in the description down below the video window, and I'll send you more information on this exciting new series.
If you'd like to see me make more Excel TechHelp videos, post a comment down below and say, 'I want more Excel.' The vast majority of my videos are from Microsoft Access, the database program, because that's been my forte for the past three decades or so. However, I love Excel, and I'm more than happy to make more videos for Excel, Word, PowerPoint, and all the other topics that I teach. As you know, the squeaky wheel gets the grease, so make your voice heard and let me know you want more Excel videos.
TOPICS:
Creating drop-down lists in Excel
Selecting and deselecting cells
Using Data Validation in Excel
Setting up a list of options
Configuring in-cell drop-downs
Specifying a source range for drop-downs
Making lists dynamic in Excel
Naming ranges for dynamic lists
Deleting and restarting data validation
Using whole column as source range
Adding and sorting data in lists
Moving lists to different sheets
COMMERCIAL:
In today's video, we're learning about creating drop-down lists in Microsoft Excel. We'll walk you through setting up dynamic lists of states, teaching you how to make these lists adaptable and easy to update. You'll learn how to move list entries between sheets to keep your main sheet uncluttered, and we'll even discuss the exciting world of cascading drop-down lists, allowing for more complex data entry options. If you're eager to explore drop-down lists in Microsoft Access, which offers even greater capabilities for managing large data sets, there's an extra video linked for your viewing. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.Quiz Q1. What is the primary purpose of a drop-down list in Excel?
A. To perform complex calculations
B. To allow users to select from a predefined list of options
C. To format cells with colors
D. To merge multiple cells together
Q2. How do you deselect a row when selecting an entire column in Excel?
A. Click and drag across the column header
B. Use the Page Down key
C. Hold down the Shift key and click the first row
D. Hold down the Control key and click the first cell
Q3. In the video, why is it recommended to allow blank values in the Data Validation settings?
A. To prevent any data entry errors
B. To ensure users always enter data in every cell
C. To avoid forcing users to enter incorrect or random data
D. Because it automatically fills in default values
Q4. What is the 'Source' field used for in the Data Validation settings?
A. To specify the data entry format
B. To set the font style for the drop-down list
C. To define the range from which the drop-down options are pulled
D. To indicate the error message text
Q5. Why should the range for the source of a drop-down list be set with absolute references?
A. To make sorting easier
B. To prevent the range from shifting when you sort or adjust the spreadsheet
C. To simplify formula calculations
D. To allow custom styling options
Q6. How can you make a drop-down list dynamic, allowing new entries to automatically appear?
A. By selecting the entire column as the source
B. By manually adding each new entry to the validation settings
C. By enabling an automatic update feature in Excel
D. By entering entries in a separate document
Q7. What is a cascading drop-down list as described in the video?
A. A drop-down list that applies to all cells in a row
B. A list where the choices in one drop-down depend on the selection made in another drop-down
C. A feature that automatically sorts data in alphabetical order
D. A list that automatically filters data based on keywords
Q8. Why might someone choose to use Microsoft Access instead of Excel for managing large sets of data?
A. Access is better for graphic design
B. Access allows for faster spreadsheet calculations
C. Access is designed for managing large sets of relational and structured data with built-in relationship tools
D. Access is more commonly used for web development
Answers: 1-B; 2-D; 3-C; 4-C; 5-B; 6-A; 7-B; 8-C
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's TechHelp tutorial from ExcelLearningZone.com is all about creating drop-down lists in Microsoft Excel. I'm your instructor, Richard Rost. We'll explore how to create these lists, which allow you to select from a pre-defined set of options within your spreadsheet.
Imagine you're working with a basic sheet containing names and their respective states, like Rick, Joe, Bill, and Sue. You may need to choose from a limited set of states where your business operates. To do this, you start by listing those states in your spreadsheet, such as Florida, New York, Pennsylvania, Texas, and Vermont.
You'll want to select the column where these states will be applied, but exclude the header row. To do this, select the entire column, then deselect the top row by holding the control key and clicking the first cell. This ensures your selection includes everything from B2 to the end, minus the header.
Next, go to 'Data' and then choose 'Data Validation.' Once there, under Settings, select 'Allow' and choose 'List' to create your drop-down from these options. Decide whether to check 'Ignore blank,' which I suggest you leave on to prevent incorrect data entry. Enabling 'In-cell drop-down' allows you to interact with the list.
The key part is the Source field where you'll define the range of your state list, using absolute references to maintain the selection stability. Once set, hit 'OK,' and you'll see your drop-down list ready for use.
If you want to expand the list, you might run into an issue where new entries aren't reflected automatically. To address this, naming the range or using the entire column as your source can offer a dynamic solution. Should you need to reset, you can easily delete the validation or the column itself and redo the steps using the full column to encompass possible future additions.
For those interested in more advanced features, I cover cascading drop-down lists in my Excel Expert Level 10 class. This allows for more complex setups, such as choosing a car make in one list and then having the model list dynamically adjust to show only models from that make.
If you're curious about working beyond Excel, there's also content on creating drop-down lists in Microsoft Access, a powerful tool for managing more complex, interconnected data.
These topics and much more are covered in detail on my website. Be sure to check the link below to find a complete video tutorial with step-by-step instructions on everything we've discussed today.
Lastly, if you have a particular interest in expanding your Excel knowledge through VBA programming, I'm launching a new series dedicated to automating tasks and customizing Excel through VBA. You can sign up for updates through the link provided.
If you want more videos on Excel, let me know in the comments. Although Access is my long-time expertise, I'm always eager to create more content for Excel, Word, and other applications. Remember, your feedback guides the content I produce.
Live long and prosper, my friends.Topic List Creating drop-down lists in Excel
Selecting and deselecting cells
Using Data Validation in Excel
Setting up a list of options
Configuring in-cell drop-downs
Specifying a source range for drop-downs
Making lists dynamic in Excel
Naming ranges for dynamic lists
Deleting and restarting data validation
Using whole column as source range
Adding and sorting data in lists
Moving lists to different sheets |
