How to Create a Drop Down List in Excel (Step-by-Step)

Learn how to create a drop down list in Excel in this step-by-step tutorial. We’ll firstly teach how to create a drop down list with a selection from the days of the week.

Then, we’ll also explain how to create a smaller drop down list with only two options: yes and no.

By the end of this tutorial you’ll know two simple ways to create drop down lists in Excel.

1. Create a drop down list with the days of the week
2. Create a simple yes/no drop down list

Prefer the Drop Down List Video Instructions?

Introduction to Data Validation

Data validation in Microsoft Excel is a powerful tool that helps you control the type of data or values that users can enter into a cell or range of cells. By setting up data validation, you can ensure that only specific entries are allowed, which greatly improves data consistency and accuracy across your spreadsheets.

One of the most popular uses of data validation is to create a drop down list. With a drop down list, users can simply select an option from a predefined menu, rather than typing their own values. This not only speeds up data entry but also reduces the risk of errors and invalid data. To access this feature, go to the Data tab on the Excel ribbon and look for the Data Tools group. Here, you can click Data Validation to open the settings.

When you set up a drop down list, you can either type your list items directly into the Source box, separated by commas, or reference a range of cells that contains your list values. Excel shows the first item in your drop down menu by default, but users can click the arrow to see and select other entries. If you want to allow users to enter their own values in addition to the drop down options, you can adjust the settings on the Error Alert tab by unchecking the “Show error alert after invalid data is entered” box. However, this may lead to invalid data being entered, so use this option with caution.

For more advanced needs, you can create a dynamic drop down list that automatically updates as you add or remove items from your source list. This is often achieved using the OFFSET function to create a range reference that expands or contracts based on the number of entries. For example, if your list is in column B on a second sheet, you can use a formula like =OFFSET(Sheet2!$B$1,0,0,COUNTA(Sheet2!$B:$B),1) in the Source field. This ensures your drop down list always reflects the current data in your range.

By leveraging data validation and drop down lists in Excel, you can streamline data entry, minimize mistakes, and maintain clean, reliable data throughout your workbooks. Whether you’re working with a simple list or a dynamic drop down list, these tools help you achieve greater accuracy and efficiency in Microsoft Excel.