How to Create a Drop Down List in Excel

An Excel drop-down list or drop-down menu is a graphical control element that allows users to choose an option from a list of pre-defined options. This will make data entry easier, faster and reduce inaccuracy and typos.

For instance, users can choose easily the status of the work as FINISHED from a drop-down menu. But if you provide them the choice to enter it manually, they may type the whole word FINISHED or PENDING or ONGOING, or FAILED. It will take a lot of time to type the status for each work, but if it were a drop-down list, it will speed up the data entry process.

In this post, we’ll show you a quick and easy way to create a drop-down list using data from cells, or by entering data manually, or by using formulas in Excel.

Creating a Drop-Down List using Data from Cells

For instance, you can make a drop-down menu to track the status of each of the trips you are planning to take (as shown in the image below).

First, type the items you want to appear in the drop-down in an array of cells. You can do this on the sheet that will contain the drop-down lists, or on a different sheet.

In this example, we typed the list of items for the drop-down list in Sheet 2.

Go back to Sheet 1 and then select cell B2 (this is the cell where you will set up your drop-down).

Next, go to the ‘Data’ tab and click the ‘Data Validation’ icon, and select ‘Data Validation’ from the drop-down menu.

In the ‘Data Validation’ dialog box, select ‘List’ from the ‘Allow:’ drop-down menu and click ‘OK”.

Click in the ‘Source’ box and you can select the list of items you want to add as the options that will appear in the list.

Now, select list of items (A1:A5) from Sheet 2.

And the location where the values for the drop-down menu will be added automatically to the Source box. Now, click ‘OK’. If you uncheck the ‘Ignore black’ option, Excel will force users to select a value from the list.

Now you have made a drop-down list in cell B2 of the Status column.

To copy the drop-down list to all 5 rows, simply click on the little green square at the lower left of the drop-down box and drag it down to over cell B6.

Now, the drop-down list is copied from cell B2 to B6.

Creating a Drop-down List by Entering Data Manually

Alternatively, you can add items directly to the drop-down by entering them manually in the ‘Source’ field of the Data Validation dialog window.

In this example, you are adding a drop-down list for what season you’re visiting the cities. So, select cell C2 to create a drop-down list.

Open ‘Data Validation’ dialog box from the ‘Data’ tab.

Select ‘List’ from the Validation criteria and type your list in the ‘Source’ box. All the items should be entered without space, separated by a comma between each item.

Here, enter ‘Spring, Summer, Fall, Winter’ in the source field and click ‘OK’.

Now, all the items (options) entered in the Source field will appear in different lines in the drop-down list. Then, you can drag and copy the list to the rest of the rows as you did in the previous method.

Creating a Drop-down List Using Formulas

Another way you can create a drop-down list is by using the OFFSET formula in the source field.

In this example, we are creating a drop-down list in the Year column. Select cell D1 and go to Data –> Data Tools –> Data Validation.

In the Data Validation windows, enter this formula source field instead of cell reference or manually entered items:

 =OFFSET(reference, rows, cols, [height], [width])

Now enter the list of items for the drop-down list (Year) in Sheet 2.

In the formula, specify the cell reference as B1 (the starting point of the list), specify rows and columns as 0 to avoid offsetting the reference, and specify as Height as 5 for items in the list.

=OFFSET(Sheet2!$B$1,0,0,5)

Now, if you enter this formula in the source field, it returns an array that has the list of the years (B1:B5).

This will create a drop-down list that shows all the years in the cell range B1:B5 of Sheet 2.

Removing a Drop-down List

You can also remove a drop-down list in Excel. To remove a drop-down list, select the cell with the drop-down list. Then go to Data –> Data Tools –> Data Validation.

Click the ‘Clear All’ button at the bottom left corner of the ‘Data Validation’ dialog box and click ‘OK’. This will remove the drop-down list on the selected cell.

If you want to remove all the drop-down lists in the worksheet, check ‘Apply these changes to all other cells with the same settings’ before you click on the ‘Clear All’ button. Then, click ‘OK’ to apply.