Why Create a Dropdown in Excel?
A dropdown list is a great feature you can use if you want your users to choose from a list of predetermined values. With a dropdown list, you can ensure that the users will not type in any value that’s not on the list or type in misspelled values.
How To Create a Dropdown in Excel
Creating a dropdown list is easy. But before you can do that, you should already have a list of values you want to include in the dropdown list.
1. Select or highlight the cells where you want the dropdown to appear.
2. On the Ribbon toolbar above, click Data > Data Validation.
3. On the Settings tab and under Allow field, select List.
4. Under Source, list down the values in your dropdown menu. Separate them with a comma.
Click OK.
Once done, you should be able to see the dropdown arrow on the cells you highlighted earlier. When you click the arrow, you can now see the values you listed in Source.
Alternative to Creating a Dropdown in Excel
Combo Box (Form Control)
In Excel, Form Controls are objects designed to make it easier for the user to select from a list of values.
If you are using an Excel version earlier than 2010, you should be able to access the Combo Box (Form Control) by clicking Insert and selecting it from the Form Controls. However, if you are using Excel 2010 and later versions, you should first enable the Developer tab on your Excel sheet. To do this, follow these steps:
- In the search tab, type in “Customize” and from the results, you should see “Options: Customize Ribbon.” Click it.
From the Excel Options, tick the box beside Developer. Click OK.
- After enabling the Developer tab, start creating your dropdown list values.
- Click the cell where you want the dropdown to appear then click the Developer tab. From the Form Controls, select Combo Box (Form Control). In should be the second icon from the left, on the very top of the options.
You should see this right after clicking the Combo Box (Form Control) icon.
- Right-click anywhere on the dropdown box and from the pop-out window, select Format Control.
- On the Input range, indicate the cell where you listed the values for the dropdown. You can simply do it by putting your cursor on the Input range field and then drag your mouse in the dropdown values range.
The Cell link field is optional. However, if in our example, you want to indicate the number associated with the Contact Driver Dropdown values (for example, 1 for Technical issues and 2 for error messages) in a specific cell, simply put your cursor on the Cell link field then click the particular cell where you want the number to be indicated.
For the Drop down lines, it indicates the number of values in the dropdown, in the example, there are 4.
Click OK.
Once done, you should see the four values when you click the dropdown arrow.
Summary
Creating a dropdown list is easy and it can be done in a few seconds. However, one limitation of creating a dropdown list from the Data Validation menu is that you cannot customize the properties of the dropdown. If you want to do some customization in the properties, try using the Combo Box (Form Control) option.