How to create a drop down list in Excel

It's easier than it sounds

Microsoft Excel Online
Image Credit: Microsoft

Creating a simple drop down list in Microsoft Excel might sound like an intimidating and difficult process best left for pros, but the simple truth is that anyone – even a high school student – can do it in a few quick steps. 

That’s the beauty of spreadsheet applications like Excel; they make what would be otherwise complicated tasks so much easier to do, which make them all the more accessible to every user, whether you’re utilizing them every day in the office or you’ve never used Excel in your life.

Below are the simplest ways to create a drop down list for both macOS and Windows 10 users.

How to create a drop down list in Excel

Creating a drop down list is as easy as pie (though, unfortunately, not as delicious), regardless of your Excel skill level. There’s a more technical process, but we won’t discuss that here; we’re only covering the easy methods, of which there are two: first, by creating a drop down list utilizing existing data in cells, and second, by creating a drop down list by manually entering the items. 

Using existing data

In this first method of creating a drop down list in Excel, we’re working under the presumption that you already have a working list of items you want to use in your drop down list, already entered in your spreadsheet.

How to create a drop down list in Excel

(Image credit: Michelle Rae Uy)

Step 1. Select the cell for your drop down list.

Make sure that this is the cell you want to use for the drop down list, so you won’t have to adjust later.

How to create a drop down list in Excel

(Image credit: Michelle Rae Uy)

Step 2. Go to the Data tab then click on Data Validation.

This opens the Data Validation dialogue box.

How to create a drop down list in Excel

(Image credit: Michelle Rae Uy)

Step 3. In the Settings tab, select List under Allow. Be sure to tick off the In-cell dropdown, if it isn’t checked already.

How to create a drop down list in Excel

(Image credit: Michelle Rae Uy)

Step 4. Click on the Source field. Then click and drag your mouse pointer over all the cells that contain the items you want to use for your drop down list. 

This selects those cells all at once and automatically fills in the Source field with the data you need for your drop down list. 

Step 5. Click OK. 

Manually entering items

Using the second method, you don’t have an existing list in your spreadsheet to work with so you will be entering the items manually. To do that, you just need to follow the steps above until Step 4.

Step 1. Select the cell for your drop down list.

Step 2. Go to the Data tab then click on Data Validation to open the Data Validation dialogue box.

Step 3. In the Settings tab, select List under Allow. Be sure to tick off the In-cell dropdown, if it isn’t checked already.

How to create a drop down list in Excel

(Image credit: Michelle Rae Uy)

Step 4. Click on the Source field. Then enter every single item for your drop down list, separating each item by a comma.

Step 5. Click OK.

How to create a drop down list in Excel

(Image credit: Michelle Rae Uy)

How to create several drop down lists at once

If you want to create several of the same drop down list all at once, you’ll be happy to know that the process is just as easy as creating a single drop down list. The only difference is that you’re selecting several cells – the cells you want your drop down lists in.

How to create a drop down list in Excel

(Image credit: Michelle Rae Uy)

Step 1. Select the exact cells you want to use for the drop down lists.

Step 2. Go to the Data tab then click on Data Validation to open the Data Validation dialogue box.

Step 3. In the Settings tab, select List under Allow. Be sure to tick off the In-cell dropdown, if it isn’t checked already.

How to create a drop down list in Excel

(Image credit: Michelle Rae Uy)

Step 4. Click on the Source field. Then click and drag your mouse pointer over all the cells that contain the items you want to use for your drop down list. If you’re entering the items manually, simply enter every single item, separating each item by a comma.

Step 5. Click OK. 

How to create a drop down list in Excel

(Image credit: Michelle Rae Uy)

Isn’t that painless? It’s worth noting that there are other, perhaps trickier ways, to do this. You can use Excel formulas, for example, to create a simple drop down list. You can even create a dynamic drop down list that can easily be extended when necessary. But, that’s a tutorial for another day.

Images credit: Michelle Rae Uy