You can run into a circumstance in Excel when you need to repeatedly enter the same value. It can be irritating to repeatedly type those values. To enter recurring values into your Excel sheet, there is, however, a shortcut. A drop-down list can be created to accomplish this. You can learn how to utilize a drop-down list in Excel, which enables users to choose input values from a predetermined list of possibilities.
Excel: How to Insert Drop-Down List (2023)
The following steps will show you how to insert the drop-down list on your Excel sheet using either Data Validation or Form Controls:
1. Data Validation
You can limit the types of data that can be entered into a cell using the data validation tool. The steps below can be used to generate a drop-down menu from a group of cells or a list of values using data validation:
- Select the cell range where you want to insert the drop-down list.
- Click the Data tab and select the Data Validation icon.
- In the Data Validation dialog box, go to the Settings tab and choose List from the Allow drop-down.
- In the Source box, either enter the values for your list separated by commas.
- Alternatively, you can type the values in the new sheet beforehand and select a range of cells that you want to include on the drop-down. This trick is useful when you are working with large datasets.
- If you want to allow users to enter values that are not in the list, check the Ignore blank and In-cell drop-down boxes. Otherwise, leave them unchecked.
- Finally, click the OK button to insert the drop-down list. Now you should see a drop-down arrow in the selected cell range. You can click on it to choose an option from your list.
2. Form Controls
You can add form controls to your spreadsheet to make interactive components like buttons, checkboxes, and drop-down menus. Consequently, use the following procedures to create a drop-down list using form controls:
- Go to the Developer tab and click on Insert in the Controls group. If you don t see the Developer tab, go to File > Options > Customize Ribbon and check the Developer box under the Main Tabs section.
- Then click the Insert icon to expand it.
- Here, select the Combo Box (Form Control) icon from the Form Controls section which is shown with a sheet icon with an up and down arrow on the right side.
- Adjust the size of the combo box and drag it to the cell where you want to insert the drop-down list.
- Then right-click the combo box and select the Format Control option from the menu.
- In the Format Control dialog box, go to the Control tab and enter the list on the Input range field by separating each value by a comma.
- Now click the Cell link field, and enter or select a range of cells that display the selected option from your list.
- In the Drop down lines box, enter the number of items that you want to show in your list at a time. You can adjust this number according to your preference.
- Then click the OK button to add the drop-down list.
- Finally, you should see a drop-down arrow on your combo box. Click on it to open the drop-down list.
Everything about inserting a drop-down list in Excel has been addressed. Check out our other guides as well, such as How to spell check in Excel or How to erase duplicates in Excel.