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).
![](https://allthings.how/content/images/wordpress/2021/02/allthings.how-how-to-create-a-drop-down-list-in-excel-image-2.png)
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.
![](https://allthings.how/content/images/wordpress/2021/02/allthings.how-how-to-create-a-drop-down-list-in-excel-image-3.png)
Go back to Sheet 1 and then select cell B2 (this is the cell where you will set up your drop-down).
![](https://allthings.how/content/images/wordpress/2021/02/allthings.how-how-to-create-a-drop-down-list-in-excel-image-4.png)
Next, go to the ‘Data’ tab and click the ‘Data Validation’ icon, and select ‘Data Validation’ from the drop-down menu.
![](https://allthings.how/content/images/wordpress/2021/02/allthings.how-how-to-create-a-drop-down-list-in-excel-image-5-759x310.png)
In the ‘Data Validation’ dialog box, select ‘List’ from the ‘Allow:’ drop-down menu and click ‘OK”.
![](https://allthings.how/content/images/wordpress/2021/02/allthings.how-how-to-create-a-drop-down-list-in-excel-image-6.png)
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.
![](https://allthings.how/content/images/wordpress/2021/02/allthings.how-how-to-create-a-drop-down-list-in-excel-image-7.png)
Now, select list of items (A1:A5) from Sheet 2.
![](https://allthings.how/content/images/wordpress/2021/02/allthings.how-how-to-create-a-drop-down-list-in-excel-image-8.png)
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.
![](https://allthings.how/content/images/wordpress/2021/02/allthings.how-how-to-create-a-drop-down-list-in-excel-image-9.png)
Now you have made a drop-down list in cell B2 of the Status column.
![](https://allthings.how/content/images/wordpress/2021/02/allthings.how-how-to-create-a-drop-down-list-in-excel-image-10.png)
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.
![](https://allthings.how/content/images/wordpress/2021/02/allthings.how-how-to-create-a-drop-down-list-in-excel-image-11.png)
Now, the drop-down list is copied from cell B2 to B6.
![](https://allthings.how/content/images/wordpress/2021/02/allthings.how-how-to-create-a-drop-down-list-in-excel-image-13.png)
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.
![](https://allthings.how/content/images/wordpress/2021/02/allthings.how-how-to-create-a-drop-down-list-in-excel-image-14.png)
Open ‘Data Validation’ dialog box from the ‘Data’ tab.
![](https://allthings.how/content/images/wordpress/2021/02/allthings.how-how-to-create-a-drop-down-list-in-excel-image-15.png)
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’.
![](https://allthings.how/content/images/wordpress/2021/02/allthings.how-how-to-create-a-drop-down-list-in-excel-image-16.png)
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.
![](https://allthings.how/content/images/wordpress/2021/02/allthings.how-how-to-create-a-drop-down-list-in-excel-image-17.png)
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.
![](https://allthings.how/content/images/wordpress/2021/02/allthings.how-how-to-create-a-drop-down-list-in-excel-image-18.png)
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.
![](https://allthings.how/content/images/wordpress/2021/02/allthings.how-how-to-create-a-drop-down-list-in-excel-image-23.png)
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).
![](https://allthings.how/content/images/wordpress/2021/02/allthings.how-how-to-create-a-drop-down-list-in-excel-image-22.png)
This will create a drop-down list that shows all the years in the cell range B1:B5 of Sheet 2.
![](https://allthings.how/content/images/wordpress/2021/02/allthings.how-how-to-create-a-drop-down-list-in-excel-image-21.png)
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.
![](https://allthings.how/content/images/wordpress/2021/02/allthings.how-how-to-create-a-drop-down-list-in-excel-image-24.png)
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.
![](https://allthings.how/content/images/wordpress/2021/02/allthings.how-how-to-create-a-drop-down-list-in-excel-image-25.png)
Now, you can create and remove drop-down lists by following this step-by-step guide.
Member discussion