Create a drop-down list of items in Excel to make data entry easier, faster and error free. With Excel’s Data Validation feature, you can easily create drop down lists to enter data on a worksheet or workbook.
An Excel drop-down list or drop-down menu is a data validation function that allows users to select an option from pre-defined options. This will make data entry easier, faster and reduce inaccuracy and typing mistakes.
For example, a user from a drop-down list can select easily the status of the work as the FINISHED. But if you give him the option to enter it manually, he may enter 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
You can create a drop-down list 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 list 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 the B2 cell (this is the cell where you’ll initially place your drop-down list).
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 data you want to include 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 list 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 created 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
Instead of referring to a list of items on the worksheet, you can add items directly by entering them manually in the Source field of Data Validation.
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 entered in the source field will be displayed 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 the example, you 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])
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.
Now, if you use this formula: =OFFSET(Sheet2!$B$1,0,0,5), it returns an array that has the list of the years (B1:B5).
This will create a drop-down list that lists all the years in the cell B1:B5.
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. 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.
To remove all other drop-down lists in the worksheet with the same settings, check ‘Apply these changes to all other cells with the same settings’ before you click on ‘Clear All’. Then, click ‘OK’ to apply.
Now, you can create and remove drop-down lists by following this step-by-step guide.