Mastering Excel Drop-down Lists

Drop-down lists, also known as validation lists, are one of the most useful - yet underutilized - Excel features that can help you create clean and intuitive spreadsheets.

These lists provide a user-friendly interface for data entry, prevent errors, and streamline the process of inputting information into your Excel workbooks.

What is a Drop-down List in Excel?

When you click a cell that is linked to a drop-down list, you can see a small arrow button on the right. Click it and the list expands, showing the available choices listed, as shown below:

If you click any item in the drop-down list, the cell will take the value of the item. In the example above, clicking “Western Europe” in the drop-down list will fill the currently selected cell (B5) with the value “Western Europe”.

Why You Should Create Drop-down Lists in Microsoft 365 Excel

Using drop-down lists in Excel offers numerous benefits, enhancing both the cleanliness of your workbooks and user efficiency. Here are the three main reasons to incorporate drop-down lists in your Excel spreadsheets:

1. Providing Clear User Instructions

Drop-down lists offer users an intuitive interface, making it clear what input is expected from them. This is especially helpful for users unfamiliar with your file, as they won’t need to guess what to enter. For instance, you can allow users to test different scenarios by simply selecting from predefined options in your drop-down list.

2. Preventing Spelling Mistakes

By limiting user input to a pre-defined list of possible values, you ensure that their input matches the desired spelling or format. This eliminates typos and keeps your spreadsheet clean with homogeneous data and correct spelling. This is particularly crucial if you have formulas that depend on the values in the cell.

Using drop-down lists ensures correct spelling for formula-dependent text

3. Increasing Input Speed

Selecting from a pre-defined list is significantly faster than typing text manually. This time-saving feature becomes especially valuable when dealing with frequent user inputs or multiple data entry points in your spreadsheets.

How to Create a Drop-down Validation List in Excel

Summary

To create a drop-down list in Excel, follow these steps:

1. Select the cell or range where you want to add the drop-down menu.
2. Go to the Data tab on the Excel ribbon, and under the Data Validation menu, click Data Validation.
3. In the dialog box that opens, select List in the Allow menu.
4. Under Source, either enter values manually (with separators) or select a range containing the allowed values for the drop-down menu.

Adding a Drop-down List to Multiple Cells

You can add drop-down menus to multiple cells simultaneously by selecting:

• A single cell or range
• A combination of non-contiguous cells or ranges (hold the CTRL key while selecting)
• Entire rows or columns

Creating a Simple Drop-down List with Restricted Values

For a basic drop-down list with hard-coded choices:

1. Select your range and go to the Data tab, then Data Validation.
2. Choose List in the Allow menu.
3. Under Source, manually type each item for your list, separated by the Excel separator (comma or semicolon, depending on your local settings).
4. Click OK to confirm.

Your drop-down list is now ready for use. Users can click the small arrow on the right and select an item from the list instead of typing the full text.

If a user tries to enter a forbidden value, such as “Maybe” in a Yes/No list, an error message will appear:

While this method is simple and keeps your spreadsheet clean, it can be time-consuming for lists with many options. For more flexibility, you can create a drop-down list linked to an Excel range.

Creating a Drop-down List Linked to an Excel Range

To create a drop-down list based on a range of cells:

1. Follow the same steps as above, but instead of typing values in the Source field, select a range in your spreadsheet (or any open workbook).

This method offers several advantages:

• Saves time by eliminating manual typing in the Data Validation dialog box
• Allows easy modification of available choices by editing the source range
• Enables dynamic lists using formulas in the source range
• Facilitates nested validation menus based on previous user inputs

For organization, consider keeping all list ranges in a separate tab as a reference. This makes it easier to edit your lists while preventing accidental modifications by users.

Creating a Dynamic Drop-down List with Variable-size Named Range

To create a drop-down list that automatically updates when items are added or removed from the source range, use a variable-size named range:

1. Select the range with your current list of items.
2. Go to the Insert tab and click Table (or use Ctrl+T).

Alternatively, you can use a named range:

1. Create the drop-down list as usual.
2. In the data source field, press F3 to access the list of named ranges in your spreadsheet.
3. Select the desired named range as the source for your drop-down list.

For a more advanced solution, you can use array functions or the OFFSET function to define a variable-sized range:

This formula returns a range that depends on the number of rows in column B, automatically updating when new values are added.

Customizing Options for Your Drop-down List

Adding an Informational Message

To provide additional guidance to users:

1. Go to the Data tab and click Data Validation.
2. In the Input Message tab, ensure the “Show input message when cell is selected” checkbox is ticked.
3. Enter a Title and Message, then click OK.

Allowing Custom Values

To permit users to enter values not in the pre-defined list:

1. In the Error Alert tab, uncheck the “Show error alert after invalid data is entered” box.
2. Optionally, define a custom message for users to double-check their entry.

Controlling Data Type

To restrict input to specific data types (e.g., dates, numbers, or text):

1. In the Allow list, choose the desired data type instead of List.

Removing an Excel Drop-down List

To remove a drop-down list and allow any value to be entered:

1. Go to the Data tab.
2. Click Data Validation under the Data Validation menu.
3. In the Allow menu, change the value from List to Any value.
4. Click OK to confirm.

Creating Dynamic Charts with Drop-down Lists

You can create impressive, interactive Excel spreadsheets by allowing users to select data series for display in a chart using a drop-down list. This technique enables the chart to automatically update based on the user’s selection.

In this example, the chart adjusts to show the values for the product selected by the user in the drop-down list.

To achieve this:

1. Use a range that changes based on the drop-down list selection as the data source for your chart.
2. Implement a formula like the following in the data source column:

excel =OFFSET([@Date];;MATCH($J$2;Table2[[# Headers];[Product A]:[Product C]];0))

This formula dynamically selects the appropriate column based on the product chosen in cell J2.

By mastering these techniques for creating and customizing drop-down lists in Excel, you can significantly enhance the functionality and user-friendliness of your spreadsheets. Whether you’re designing simple data entry forms or complex, interactive dashboards, drop-down lists offer a powerful tool for streamlining your Excel workflows.