How to Create a Dropdown List in an Excel Spreadsheet

I love a dropdown box, especially in a spreadsheet. When creating a form to be used by a variety of people, it’s most helpful to simplify things as much as possible. Even the easy task of entering a month can be complicated by the variety of abbreviations different users might input. So what could be easier than choosing from a dropdown box? Adding your own dropdown box … here’s how!

Instructions

Difficulty: Easy

Things You’ll Need:

• MS Excel
• A list!

1. Let’s pretend that we want to offer a list of months for a user to choose from. Before going through the mechanics of adding the dropdown box, type the months’ names in an inconspicuous place. You may enter the list on the same worksheet in column AX or somewhere equally unobtrusive, or you may type the list on a different tab.
2. Now select the cell where you want the dropdown box to appear. Select Data from the menu bar across the top of the screen, and click on Validation.
3. The Data Validation window appears. While on the Settings tab, you will specify the validation criteria. From the Allow dropdown box, choose List.
4. Next, you will have to enter the source of the list. Click in the Source field, then find the list you’ve typed in the spreadsheet. You may type the cell references (ex. =$G$1:$G$12) or highlight the cells on the spreadsheet to populate the Source field.
5. Select the Input Message tab. Here, you may choose to show a message to users when the cell has been selected. Give your message a title, and type the specific message you want to display.
6. Select the Error Alert tab. Now, you may think that by supplying a list to choose from, that users won’t try to enter their own unique value. I’m telling you now that you’re sadly mistaken. On this tab, you may choose to display an alert telling the user that they’ve entered invalid data. Choose from the dropdown the style of alert. Give the error message a title, and type a little message to users who can’t follow directions.
7. Click the OK button at the bottom of the Data Validation window to complete setup of the dropdown box and test it out. You can always go back in and edit messages, change the location of the source list, or clear the dropdown box completely.