/ How to

How to Make a Drop Down List With Data Validation in Excel Spreadsheets

Introduction

Drop down lists in Excel allow you to restrict the values entered in a cell to a predefined list. This makes data entry easier, more consistent, and helps prevent errors.

In this comprehensive guide, I’ll show you step-by-step how to create different types of drop down lists using Excel’s Data Validation feature.

Why Use Drop Down Lists

Here are some of the benefits of using drop down lists in Excel:

  • Ensure data consistency – The user can only select values you predefined rather than typing anything
  • Prevent errors from invalid entries
  • Make data entry faster and more efficient
  • Improve usability for other users of the spreadsheet

Types of Drop Down Lists

There are a few different ways you can set up a drop down list in Excel:

1. Static List

A static list contains a predefined set of values that don’t change. This is good for small lists that don’t require frequent updates.

2. Dynamic List

A dynamic list refers to a range of cells that can change over time, allowing the drop down to update automatically. This is best for large lists that are frequently changed.

3. Dependent List

A dependent drop down list changes based on the value selected in another cell. For example, selecting a country filters the list of states/provinces.

Steps to Create a Basic Drop Down List

Follow these steps to create a simple static drop down list in Excel:

  1. Enter the list items into a single row or column on the worksheet. For this example, I entered categories in cells A1:A5.
  2. Select the cell where you want the drop down list (B1).
  3. Go to the Data tab > Data Tools group and click Data Validation.
  4. In the Allow box, choose List.
  5. In the Source box, enter the cell reference for the list items =$A$1:$A$5
  6. Make sure “In-cell dropdown” is checked below the Source box.
  7. Click OK. The cell now shows a drop down arrow to select the list items.

This creates a basic static list. Now let’s look at more advanced options…

Creating a Dynamic Drop Down List

To create a dynamic drop down list that updates automatically:

  1. Set up your list items in an Excel Table (Ctrl + T) instead of a normal range. Using a Table allows the list to expand dynamically when new items are added.
  2. Give the table a simple name without spaces (e.g. CategoryTable)
  3. Follow the same steps above, but change the Source to reference the table name, for example: =CategoryTable

Now when you add or remove items from the table, the drop down will update dynamically.

Making a Dependent Drop Down List

To create dependent drop downs that filter based on the selection of another cell:

  1. The first drop down should contain the main categories, for example: East, West, North, South.
  2. The second drop down will depend on the first. We will use Excel’s Data Validation and the INDIRECT function.
  3. Set up named ranges for each category’s sub-list, e.g. EastLocations, WestLocations, etc.
  4. Apply this formula in the Source for the second drop down: =INDIRECT(A3&”Locations”) Where A3 contains the main category selection from the first drop down.

Now when you change the first drop down, the second will filter to show the matching sub-list.

Additional Tips

Here are some additional useful tips when working with Excel drop down lists:

  • Sort the items alphabetically so they are easy to find
  • Use whole column references like $A:$A instead of fixed ranges whenever possible to allow unlimited list growth
  • Show input messages and error alerts to guide the user
  • Limit list lengths to 10-15 items where possible to avoid overly long drop downs
  • Give drop down cells a different color to indicate they have data validation applied
  • Test thoroughly and ensure your validation doesn’t break if you delete cells

Conclusion

By taking advantage of Excel’s Data Validation feature, you can create customized drop downs that improve data entry, prevent errors, and make it easier to manage information in large spreadsheets.

The key is to choose the right type of list, use structured data tables, and leverage tools like dependent lists. With a bit of planning, drop downs can go a long way in making professional Excel spreadsheets user-friendly and robust.

Related Posts