Dropdown Lists Made Simple | Excel Tutorial

Dropdown Lists Made Simple in Excel

In this tutorial, Microsoft Certified Trainer Allison Gonzalez demonstrates how to create dropdown lists in Excel to streamline data entry, improve accuracy, and maintain clean, consistent datasets. This guide is ideal for professionals managing forms, order sheets, or any repetitive data entry tasks.

Why Use Dropdown Lists?

 
• Speed: Quickly select from predefined options instead of typing repeatedly.
• Accuracy: Prevents typos, inconsistent capitalization, and formatting errors.
• Data Consistency: Ensures uniform entries, which is essential for reporting and analysis in tools like Power BI.

Creating a Basic Dropdown List

1. Select the Target Cells: Highlight the cells where you want the dropdown to appear.
2. Open Data Validation: Go to the Data tab, then click Data Validation in the Data Tools group.
3. Choose List: In the settings tab, change the validation criteria to List.
4. Enter Source Values: Manually type values separated by commas (e.g., Florida, Georgia).
5. Apply and Test: Click OK and test the dropdown in the selected cells.

Using a Cell Range as a Source

  For longer lists, such as all 50 U.S. states, Alison recommends using a cell range instead of manual entry:

• Place the list of values in a separate sheet or off to the side of your current sheet.
• Convert the list into a table using Ctrl + T or the Insert > Table option.
• Return to Data Validation and set the source to the table range.
• This method allows for easier updates and ensures alphabetical order.

Creating a Product Dropdown

  Allison applies the same method to a product list, which includes various coffee-related items. She converts the product list into a table and uses it as the source for the dropdown in the product column.

Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on Excel and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.