How to Create a Drop Down List in Excel
3,279In this video I'll show you how to create a drop down list in Excel.
See more videos by Max here: https://www.youtube.com/c/MaxDalton
Timestamps:
Steps to Create Drop Down List in Excel: 0:36
Deleting Options from Drop Down List in Excel: 3:04
Deleting Created Drop Down List in Excel: 3:25
Other Great Tutorials:
How to Password Protect Excel File: https://www.maxdalton.how/tutorial/how-to-password-protect-excel-file
How to Lock a Cell in Excel: https://www.maxdalton.how/tutorial/how-to-lock-a-cell-in-excel
How to Remove Duplicate Rows in Excel: https://www.maxdalton.how/tutorial/how-to-remove-duplicate-rows-in-excel
Video Transcript:
If you've ever used Microsoft Excel for gathering information through a form or a survey, you know that it can be difficult to control what people enter. One option for controlling the information people enter is to create drop down lists that limit people to choosing from a list of options you provide. Making a drop down list in Excel only takes a few minutes, and will save you a lot of time in cleaning up non-standardized information.
Now let's walk through the steps to create a drop down list in Excel.
Step 1. Launch Microsoft Excel on your computer. In this example you can see I've got a form where I've got a couple of questions that I want to add Excel drop down lists for so I can control the options people can input as answers.
Step 2. Click the "New Sheet" button along the bottom of the screen to add a new sheet to your Excel workbook. The New Sheet button looks like a plus sign. A new sheet will be created.
Step 3. Double-click the name of the sheet to make it editable, and then name the sheet "Drop Down Lists," or something else so you'll know what it is. It doesn't matter what you name this sheet, as long as you know what's contained inside of it.
Step 4. Create lists containing the options you'll want to include in your Excel drop down lists. In this example, I'll create three lists: one that contains the options I want to make available for someone to choose their favorite iPhone model, another list for someone to choose how long they've had their iPhone, and another list that contains options for a question around when someone plans on trading in their iPhone. At the top of each list you should include something that indicates what values are contained in this list. In this example, I've included Favorite iPhone Model, Phone Age, and Trading in iPhone.
Step 5. After you're done putting information into the sheet containing your list values, go back to the sheet where you want to actually create the drop down lists in Excel and then click inside of one of the cells where you want to add an Excel drop down list to select that cell.
Step 6. Click "Data" in the menu above the ribbon toolbar to display the Data options in the ribbon. Click "Data Validation" in the Data Tools section. The Data Validation window opens on the screen.
Step 7. Select the "Settings" tab, and then click to expand the drop down field beneath Allow. Choose "List" in the list of options.
Step 8. Click inside of the field beneath Source that appears in the Validation Criteria section, and then highlight the cells in the corresponding list on the data sheet you created. That cell range appears in the Source field. Click "OK."
When you click in the cell where you added the drop down list, a drop down arrow appears on the right side of that cell. Clicking that drop down arrow will display the items you choose to include in your Excel drop down list. Choosing any of those options populates that item in the drop drown list. In this example, I'll choose "iPhone XS." As a note, you can easily delete items from the drop down lists you create by deleting them from the source lists. However, if you add an item to any of the source lists that you want to include in a drop down list you've created, you'll need to select that cell, go back into the Data Validation editor and then modify that cell range to include any options you added.
Alternatively, you can remove your created Excel drop down list at any time by highlighting the cells containing the drop down lists you want to remove, navigating back to the Data Validation editor, clicking "Clear All," and then clicking "OK" in the Settings tab. The Data Validation editor will close and you'll be returned to the cell that previously contained a drop down list, where you'll now see that the Excel drop down list has been removed.