How to make an Excel drop-down list

Drop-down lists is a feature of Excel that enables you to have users select an option from a predetermined list instead of them typing their own values.

It’s like a lot of forms you see around the web where you can choose between different options from a list.

Creating a dropdown list in Excel

1. Open an Excel workbook.

2.Create a second sheet by clicking on the + sign near the bottom left-corner in Excel.

Add a new sheet by clicking +
Add a new sheet by clicking +

3. Type the options you want to have in the Drop-down list on the second sheet.

Type the options you want to have in your Drop-down list
Type the options you want to have in your Drop-down list

4. Go back to the first sheet by clicking Sheet1 near the bottom-left corner in Excel

Go back to the first sheet by clicking Sheet1
Go back to the first sheet by clicking Sheet1

5. Select cell B1 on the first sheet. Note that this can be any cell.

6. Click on the Data tab.

Click Data on top of the Excel ribbon
Click Data on top of the Excel ribbon

7. Click Data Validation, which is located in the Data Tools section of the ribbon.

Click Data Validation in the Data Tools section of the ribbon
Click Data Validation in the Data Tools section of the ribbon

The Data Validation dialog box will open.

8. In the Allow box select the option List.

In the Allow box select List
In the Allow box select List

9. In the same Data Validation dialogue box click the button located on the far-right of the Source text box.

Click the button located on the right of the Source text box
Click the button located on the right of the Source text box

10. Manually select the range of data you want to include in your Drop-down list and afterwards click OK.

Manually select the range of data you want in your Drop-down list
Manually select the range of data you want in your Drop-down list
After having selected your data click OK
After having selected your data click OK

And there you go, you created your own dropdown list in Excel! This is what you’ll see:

Example of a Drop-down list
Example of a Drop-down list

Note that if you do not want users to be able to access the data on Sheet2, you can hide the sheet. In order to do this, right click the Sheet2 tab and click Hide.

Right click Sheet2 and select Hide so other users cannot view the data
Right click Sheet2 and select Hide so other users cannot view the data

Dropdown list directly into the data validation pane

Instead of typing the options into Sheet2, it is also possible to insert the items directly into the Source text box.

Directly write the options into the Source text box
Directly write the options into the Source text box

As always: any questions, let me know in the comments below: