Inserting and Using Drop Down Lists in Excel

A helpful tool when working in Excel spreadsheets is the drop down codes. These can be used a variety of ways. For this example we’ll use a spreadsheet with names and address that we are tracking for how a customer was contacted.

In the Status column I want drop down choices for the following:

[list style=”bullet”][li]Direct Mail[/li][li]E-Mail[/li][li]Phone Call[/li][li]Face To Face[/li][li]No Contact[/li][/list]

Here’s how we’ll insert these choices.

1. First, you’ll add a new spreadsheet to your Excel workbook to hold your drop down choices. I named mine KEY.

2. Input your data for your drop down choices.

3. Go back to your main list spreadsheet and highlight all cells within the column you want the drop downs to be active in. For example, I’m using column A and titled it Status.

4. Go to the Data tab on the Ribbon and click Data Validation.

5. The Data Validation window will appear. In the first tab Settings, under Validation criteria, choose “List” under the Allow drop-down option.

6. A new box will appear called Source. This is where you will put the location of your Key.

7. Put your cursor in the Source field. Click on the KEY spreadsheet tab and then select your drop down codes. Click OK in the Data Validation window.

8. Now in your first spreadsheet in your Status column, you’ll see a drop down arrow next to the cell your cursor is in. Click on the drop down to select your choice. That’s your result!

Drop downs in Excel spreadsheets are immensely helpful if you need to reuse the same options many times and want to ensure consistency in data entry. We hope this tip helps!