How to Create a Drop Down List in Excel (With Examples)

At some point, you’ll be asked how to create a drop down list in Excel. Whether you’re an Excel novice or a whiz, you might not know this skill. After all, it’s not something you need every day. But trust me, when someone wants a drop down menu, they won’t settle for anything else. To be honest, I’m not a huge fan of the drop down, but it has its fervent supporters. And I’ll give this to them: a drop-down does allow for less variation in data entry, which can definitely be a problem.

So whether you love it or loathe it, the drop down list in Excel is a great tool to keep in your pocket for a rainy day. (If you don’t want to keep it in your pocket, just bookmark this page.)

Let’s Make That Drop Down List in Excel

While you might need the article so you can follow along for your learning, make sure you are actually doing each of these steps in Excel. You’ll learn much better that way! Here’s what my Excel sheet looks like:

how to create a drop down list in excel

First, make a table in Excel. You might be saying, “But that data is already in a table!” Well, unless you have officially named it — it’s not yet in a table. Naming tables in Excel is incredibly useful and best practice for a variety of reasons. Here, you want to do because your data validation will automatically update if you change anything in your table.

Along the way, let’s practice some shortcuts. To make your table, click anywhere on the data with the header “Fruit Table”, then press Ctrl+A, and then Ctrl+T. (Ctrl+A means select all data that’s next to each other, and Ctrl+T means make a table. If you hate shortcuts — and you shouldn’t — you can also click and drag with your mouse to select and the go to the top menu and choose Insert > Table.)

Whoa! You should now have a dialogue box that looks like this:

how to create a drop down list in excel

Check that little box next to where it says “My data has headers” — because, indeed, your data does have headers — then press OK. You can see that your data is a table now because Excel makes it look all fancy and stuff.

Just underneath the Home tab on the Ribbon, you have the option to enter a Table Name, which is again Excel best practice, so let’s do it. Call your table whatever you like!

how to create a drop down list in excel

Now that you have laid the groundwork, you can actually build your drop down list. Select all of the cells that you want the drop-down to exist in. If you want to choose all of the cells except the header in Column A, then click on Cell A2 and press Ctrl+Shift+Down Arrow to go the bottom of the sheet.

Next, click on Data in the Ribbon, then Data Validation. You should see a screen like this:

how to create a drop down list in excel

In the drop-down under Allow, choose List. Then, in the Source selector, click and drag across cell D2:D5 (you don’t want you folks to be able to choose the data header). Then, press OK! (You’re so close!)

Check it out — you created a drop-down list! Now your data won’t get messy ever again. Just kidding, but you’ve made great strides today.

how to create a drop down list in excel

Want the full final spreadsheet? Download it here:

Drop Down List Sample Spreadsheet – Final

Pro Tip: Enhance Your Drop Down List

Here’s a few key tips to make your drop down list even more functional. These are just a few ideas to get you started, so don’t feel limited by them!

  • Hide the list items. You might have noticed that the list of fruit is just hanging out on the front tab, which is kind of silly. Either hide the column of data by right clicking the column header and pressing “Hide” or (better yet) keep your data on a separate tab.
  • Set up Input Messages and/or Error Alerts. If you select Column A and return to Data > Data Validation, you’ll find two additional tabs called “Input Messages” and “Error Alerts”. These tabs allow for total customization of your drop-down menu. Input Message is what you get when your user clicks on the drop-down so you can tell them more about what you what the user to do. Error Alert yells at (excuse me, gently corrects) your user if they enter the wrong thing.
  • Combine data validation with VLOOKUP. Imagine if you, the fruit vendor from my example above, were able to just click a fruit from the drop-down menu, enter the quantity, and your spreadsheet would automatically calculate the total cost of the order. Imagine no more… instead read Magoosh’s VLOOKUP tutorial and combine your skills.

Any questions about drop downs? Any other tips and tricks that you’re looking to learn? Post ‘em in the comments below!

Are you quickly becoming the Excel whiz around the office thanks to Magoosh? Get better even faster: Magoosh offers Master Excel: Beginner to Advanced, a great series of video lessons that can help you improve your Excel skills.

Comments are closed.


Magoosh blog comment policy: To create the best experience for our readers, we will only approve comments that are relevant to the article, general enough to be helpful to other students, concise, and well-written! 😄 Due to the high volume of comments across all of our blogs, we cannot promise that all comments will receive responses from our instructors.

We highly encourage students to help each other out and respond to other students' comments if you can!

If you are a Premium Magoosh student and would like more personalized service from our instructors, you can use the Help tab on the Magoosh dashboard. Thanks!