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 predefined options. This will make data entry easier, faster, and reduce inaccuracies and typos.
For example, users can easily choose the status of the job as DONE from a drop-down menu. But if you give them the option to enter it manually, they can type the full word DONE or PENDING or IN PROGRESS or FAILED. It will take a long time to write the status of each job, but if it were a dropdown list, it will speed up the data entry process.
In this post, we will show you a quick and easy way to create a dropdown list using cell data, manually entering data, or using formulas in Excel.
For example, you can create a dropdown menu to track the status of each of the trips you plan to take (as shown in the image below).
First, type the items you want to appear in the dropdown into a cell array. You can do this on the sheet that will contain the dropdowns or on a different sheet.
In this example, we write the list of items for the dropdown list on Sheet 2.
Go back to Sheet 1 and then select cell B2 (this is the cell where you’ll set up your dropdown menu).
Next, go to the ‘Data’ tab and click on the ‘Data Validation’ icon and select ‘Data Validation’ from the dropdown menu.
In the “Data Validation” dialog, select “List” from the “Allow:” dropdown menu and click “OK”.
Click the ‘Source’ box and you can select the list of items you want to add as the options to appear in the list.
Now select the list of items (A1:A5) on Sheet 2.
And the location where the values for the dropdown will be automatically added 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.
You have now created a dropdown list in cell B2 of the Status column.
To copy the drop down list across all 5 rows, simply click on the small green square at the bottom left of the drop down box and drag it down onto cell B6.
Now the dropdown list is copied from cell B2 to cell B6.
Create a dropdown list by entering data manually
Alternatively, you can add items directly to the dropdown menu by manually entering them into the ‘Source’ field of the Data Validation dialog.
In this example, you are adding a dropdown list for the season in which you visit the cities. So, select cell C2 to create a dropdown list.
Open the ‘Data Validation’ dialog from the ‘Data’ tab.
Select ‘List’ from the Validation criteria and type your list in the ‘Source’ box. All items must be entered without a space, separated by a comma between each item.
Here, enter ‘Spring, Summer, Fall, Winter’ in the source field and click ‘OK’.
Now all the elements (options) entered in the Source field will appear on different lines in the dropdown list. You can then drag and copy the list to the rest of the rows as you did in the previous method.
Create a dropdown list using formulas
Another way to create a dropdown list is by using the OFFSET formula on the source field.
In this example, we are creating a dropdown 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 the cell reference or manually entered items:
=OFFSET(reference, rows, cols, [height], [width])
Now enter the list of items for the dropdown 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 prevent the reference from scrolling, and specify Height as 5 for the list items.
=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 years (B1:B5).
This will create a drop down list showing all the years in cell range B1:B5 of Sheet 2.
Deleting a dropdown list
You can also remove a drop down list in Excel. To remove a dropdown list, select the cell with the dropdown list. Then go to Data -> Data Tools -> Data Validation.
Click the ‘Clear All’ button in the lower left corner of the ‘Data Validation’ dialog and click ‘OK’. This will remove the drop down list in the selected cell.
If you want to remove all drop-down lists from the worksheet, please check ‘Apply these changes to all other cells with the same settings’ before clicking the ‘Clear All’ button. Then click ‘OK’ to apply.
Now, you can create and delete drop down lists by following this step by step guide.
