Microsoft Excel is a powerful tool that allows you to organize and manipulate data efficiently. When used effectively, it is a very beneficial tool to use as a business owner, a professional, or when you need to manage your household finances or schedule. This article will highlight the steps you should take to create a drop down list in Excel.
One interesting feature in Excel is the ability to create a drop down list, which provides a set of options for data entry. Drop down lists are very helpful in Excel, they help users streamline data, simplify data input within Excel, and improve the accuracy of data in Excel.
How to create a drop down list in Excel
Data entry is quicker and even very accurate when there is a drop-down list to limit the entries made in a cell. When you select a cell, the drop-down lists’ down-arrow appears, and you can click on it and make your selections. Follow these steps to create a drop down list successfully:
First, before proceeding, setup your data. That is, set up the list of options you want to include into the drop down list. Also decide which row and column will have the drop-down list, enter the options in the adjacent cell. For instance, if you want to create a drop down list for a column, enter the options in the header of that column.
- Select the cells that you want to contain the lists, that is the row or column that carries the drop down list.
- After selecting the cell that houses the drop-down list, navigate to the “Data’’ tab in the Excel ribbon and click the “Data Validation’’ button. Or right-click on the selected cells and select the “Data Validation’’ option from the context menu.
- In the Data Validation dialog box, you will see several tabs; select the Settings tab.
- Go to the Allow field, and select List from the menu.
- After selecting “List’’ as the data validation criteria, find a field labelled “Source”. Here, you specify the range of cells that contains the options for your drop down list. Also you can type the text or numbers (separated by commas, for a comma-delimited list) you want in your drop-down list.
- Click OK to apply the drop down list to the selected cells.
Excel has an additional option in the Data Validation box to enable users to customize the drop down list. For instance, you can access the “Input Message” and “Error Alert” tabs settings in the data validation dialog box.
To ensure that the dropdown list is functional, you can test it by:
- Clicking on the cells with the drop down list you created.
- You will see a small arrow appear next to the cell, click this arrow to open the drop down menu displaying the options you specified. You can even select an option from the list.
How to add or remove items in an Excel drop-down list
After creating a drop-down list, you might need to add more items or delete some items from the drop-down list.
- To add an item, simply navigate to the end of the list and type the new item.
- To remove an item, press Delete.
Tip: If the item you want to delete is located in the middle of your list, right-click the cell containing the item, click Delete. Then click OK to shift the cells up.
How to edit a drop-down list based on name range
- Select the worksheet that has a named range based drop-down list.
- Then do any of the following:
- Go to the end of the list and type the new item to add an item .
- Press Delete to remove an item you no longer want.
- Then Go to Formulas
- Click on Name Manager.
- In the Name Manager box, click the named range you will like to update.
- Click the “Refers to” box.
On your worksheet, select all the cells that contain the entries for your drop-down list. - Click Close, and then select Yes to save changes.
How to create a yes/no drop-down list in Excel
Follow these steps to create a yes/no list in Excel:
- Select the cells that you want to contain the lists, that is the row or column that carries the drop down list.
- Click on Data Validation from the “Data’’ tab in the Excel ribbon
- Then insert Yes or No in the Source field on the pop-up, separated by a comma.
- Click OK to save the yes/no drop-down list.
Is there a formula to create drop-down lists in Excel?
There is no formula needed in creating a drop-down list in Excel. Just change how the data is added to the cell from typing in text to selecting from your list by going to Data Validation within the Excel toolbar.
We have highlighted a step-by-step way to create your own drop down list in Excel, and keep your data more organized and accurate.
