Categories
Excel

Master Excel Drop-Down Lists in 3 Steps

Excel drop-down lists are similar to ordering food in a restaurant. When you go to a restaurant, you can’t just order anything you want and assume that the restaurant will have all of the ingredients to prepare your meal. Most of the time you order from a menu.

An Excel drop-down list presents spreadsheet users with a list of options to choose from. This makes data entry faster and more accurate. Spelling mistakes are virtually eliminated, and choices can be limited to available options in the list.

This article covers the 3 steps involved in using Excel drop-down lists.

Excel Courses: We offer Microsoft Excel training delivered online by a live instructor. Participants can connect from anywhere in Canada.

Step 1: Create a List of Items

The first thing you’ll need to do is create a list of items that will be presented to spreadsheet users in the drop-down list. You can create the list in the same worksheet where the drop-down list will be used, or you can create the list on a separate worksheet.

In this example, I created a list of items on the Sheet1 tab. Travel destinations are listed in cells F1:F5.

List of cities
List of Options

Step 2: Create a Drop-Down List

The second thing you’ll need to do is create a drop-down list that displays items from the list you created in step 1.

Normally, you can type whatever you want into any cell in your worksheet. With drop-down lists, you leverage a data validation technique that only permits items from a list.

Step #2a – Select the cell(s) where your drop-down list will be displayed. In the screenshot, I highlighted cells A1:A5.

Cells A1:A5 selected
Selected Cells

Step #2b – Click the Data Validation button in the Data > Data Tools group on the Ribbon.

Data Validation button
Data Validation Button

Step #2c – Select List from the Allow: box on the settings tab, in the Data Validation dialog box.

Data Validation dialog box
Data Validation Dialog Box

Step #2d – Click in the Source: box, drag to select cells F1:F5, then click OK.

Source box highlighted in the Data Validation dialog box
Drop-Down List Source

More: Relative and Absolute Cell References

Step #2e – Items in the drop-down list are displayed when you click the down-arrow in any of the cells in A1:A5.

Cell with a drop-down list
Drop-Down List Example

Copy Drop-Down Lists

Are you ready for some good news? You can copy drop-down lists!

In the preceding steps, I created a drop-down list in cells A1:A5. If I want to utilize the drop-down list in additional cells, I can simply copy one of these cells to any number of additional cells.

In this screenshot, I copied cell A5 by selecting it and pressing CTRL + C, then I highlighted cells A6:A10 and pasted the contents of cell A5 (including the drop-down list) by pressing CTRL + V.

Drop-down list copied
Drop-Down Lists Copied

All of the cells in A1:A10 now include the drop-down list.

Allow Other Entries in Drop-Down Lists

By default, users are restricted to entries in the drop-down list. If I type Toronto in cell A1 in the spreadsheet I created, I’ll get an error message.

Excel error message

To allow other entries in cells where a drop-down list has been defined, display the Data Validation dialog box, select the Error Alert tab, and click to clear the Show error alert after invalid data is entered checkbox.

The Error Alert tab in the Data Validation dialog box

Users will still be able to select items from the drop-down list, which will ensure a high level of accuracy for those options, but they can also enter destinations that weren’t specified in the source data.

Add/Remove Items from a Drop-Down List

You can add and remove items from your drop-down list as needed. Let’s look at two examples.

To add Toronto to the list, right-click Tahiti in cell F4 and select Insert… from the shortcut menu. In the Insert dialog box, verify that Shift cells down is selected, then click OK. Type Toronto in cell F4.

Shift cells down selected in the Insert dialog box

To remove London from the list, right-click London in cell F6 and select Delete… from the shortcut menu. In the Delete dialog box, verify that Shift cells up is selected, then click OK.

Shift cells up selected in the Delete dialog box

Enter Options Directly

If all you need is a simple drop-down list – one that displays Yes/No, True/False, or something similar – you can enter those options into the Data Validation dialog box directly.

The Source box highlighted in the Data Validation dialog box

Hide/Unhide Lists

The source data for a drop-down list can be hidden. Hiding the list of items can reduce visual distractions in your spreadsheet.

To hide the list of cities for our drop-down list, right-click the heading for column F and select Hide from the shortcut menu. Drop-down lists work whether the source data is hidden or not.

The Hide command highlighted in a shortcut menu
Hide Column

To unhide the list of cities, drag to select headings for columns E:G, right-click anywhere in the selected area, and select Unhide.

Step 3: Remove a Drop-Down List

To remove a drop-down list, turn Data Validation off.

Step #3a – Drag to select cells containing the drop-down lists you’d like to remove.

Cells A1:A5 highlighted
Remove Drop-Down

Step #3b – Click the Data Validation button in the Data > Data Tools group on the Ribbon.

Data Validation button
Data Validation Button

Step #3c – Select Any value from the Allow: box on the settings tab, in the Data Validation dialog box then click OK.

The Allow box in the Data Validation dialog box

Microsoft Excel is a registered trademark of Microsoft Corporation. XL Basics contains articles and educational content related to Microsoft Excel but is not affiliated with Microsoft in any way.

By Michael Belfry

Working as a full-time training consultant, Michael provides Microsoft Office courses to government and private sector clients across Canada.