How to Create a Drop-Down List in Excel:
Introducing the “1 Minute Drop–Down“
Written by co-founder Kasper Langmann, Microsoft Office Specialist.
The drop-down list is a great way to seem like a superuser and impress your co-workers and boss 🙂
At the same time, it’s a very user-friendly asset in almost all custom-made Excel sheets.
In this tutorial, I’m going to show you:
The 5 steps to create a drop-down in 1 minute or less.
I call it the “1 Minute Drop-Down”.
In the animation right below you can see the drop-down menu in action. Behold its mighty glory!
A drop down list is actually a tiny window from where you can peek into some other data you have constructed.
This also gives you the possibility of controlling exactly what can be entered into a cell. This makes the drop-down menu a very common and powerful data validation tool.
Luckily, creating a drop-down menu is super easy if you know the exact steps.
In a few seconds, I’m going to show you the exact steps to create your very own drop-down.
*This tutorial is for Excel 2019 for Windows. Got a different version? No problem, you can still follow the exact same steps.
Follow along by downloading our sample file right below!
BONUS: Download the Drop Down Excel Workbook Sample File (with 2 pre-loaded exercises) to go along with this post.
Before we start the clock, let’s take a look at the data in the sample file.
First we have a sheet with all the names of our employees.
This is the data we would like to see when opening our drop-down menu.
Then we have the “gateway” where we want to be able to scroll through all the names on the other sheet.
This “gateway” is placed in the “Dropdown” sheet of the Excel sample file.
We’ll put our drop-down menu in cell B1.
Step 1: Go to the “Data” tab
This step is easy!
Simply move your cursor to the “Data” tab and click it.
Step 2: Click “Data Validation”
Now click the button “Data validation” in the middle of the ribbon (do not click the little arrow – simply click the top part of the button).
Step 3: Choose the “List”
In the pop-up box, you need to click the drop-down menu to pick what kind of data validation method you want to use (how meta is that?!).
Click the arrow next to the field and click “List” from the… well… list!
Then click “OK” to move on to the next step.
Step 4: Select the source
Remember the data sheet?
That’s where I’ve put all the employees for our drop-down list.
Now it’s time to tell Excel that our drop-down should be connected to that data.
Click once in the “Source:” field.
Then click the “Data” sheet and select all the employees.
When you’re done click “OK”.
Step 5: Here’s the end result!
And that’s the 1 Minute Drop-Down! Beautiful isn’t it?
You have now created a drop-down list in Excel in 60 seconds (or hopefully even less!).
With some practice, you can almost create these drop-downs instantly. I’ve managed to create one in a mere 15 seconds.