applied tips : april 2008
microsoft excel : drop-down lists in cells
Help your users choose from a list using a drop-down menu.
Drop-down lists make data entry easier and more accurate, and they can speed up your work by providing a list of predefined data items to choose from. Drop-down lists can also be helpful if you are having others enter data and you want to restrict their entries to the choices in a list. The best way to explain this concept is with an example.
Say you keep an Excel list of the projects your sales team is working on for the quarter. Each project is listed in its own row going down the worksheet. As work progresses on each project, there are three stages you’d like to identify: Started, Reviewed, and Complete. You don’t want to have to constantly type the status next to each project—you’d rather select the status from a drop-down list. And you really only want these three choices -- not whatever the user ends up typing.
To get started, add a column to the left of your project list. This is where the drop-down lists will be available. Next, you need to enter the data that will be used in the lists—in this case, the three stages of completion. Scroll down the worksheet until you get to rows that you won’t be using for your project list. Type Started, Reviewed, and Complete in separate cells in a single column or row.
Linking a menu to the list
Click the blank cell to the left of the first project item. This is where you want to insert the drop-down list. Select Data > Validation from the menu. In the Data Validation dialog box, click the Settings tab. Click the Allow list arrow and select List, if it isn’t already selected.
Click the Source button, which causes the Data Validation dialog box to shrink. Locate and select the three cells where you entered the drop-down list data, and click the button in the Data Validation dialog box to return it to its normal size. The data has been linked to the drop-down list!
Before moving on, make sure the “In-cell dropdown” option is checked, as well as the “Ignore blank” option, if you want to allow the cell to be left blank.
Using a shortcut for short lists
Instead of typing your list source data items into the worksheet, you can simply type them right in the Source text box.
To do this, type the data directly into the Source text box in the Data Validation dialog box on the Settings tab. In the project list example, you would type “Started, Reviewed, Complete”, making sure to include the commas between each item.
This method works best for drop-down lists with only a few data items, such as in the project list example. It wouldn’t be as practical if your drop-down list included a long list of data items. For example, if you were using all 50 U.S. states as the choices in the drop-down list, you would want to enter the states in worksheet cells, not directly into the Source text box.
Using Named Ranges for lists
You can also use a Named Range as the source of a list, for those situations where you want the list to be somewhere completely different (like on another worksheet).
Create your list with each entry on its own line in a column or row as you did earlier. But this time, select the entire list and give your range a name (type the new name into the Name Box in the upper-left corner of the screen). Make sure your name does not contain spaces or symbols.
When defining your list in the Data Validation dialog box, instead of identifying the source as something like "=A15:A17" just reference it by the name of your range as "=ListOfStates".
Once you’ve identified the data to use, you can add an instructional message to the drop-down cell. Click the Input Message tab in the Data Validation dialog box, make sure the “Show input message when cell is selected” option is checked, and then type a title and message: for example, “Status” and “Select a progress level.” Now each time you select the drop-down cell, you’ll see a message telling you to “Select a progress level.”
Setting up an error alert
Finally, you can set up an error alert to appear if invalid data is entered in the drop-down cell. Invalid data is anything you type in the cell that isn’t an item in the drop-down list. Click the Error Alert tab in the Data Validation dialog box and make sure the “Show error alert after invalid data” option is checked. Click the Style list arrow and select an error style. The choices are Stop, Warning, and Information. Warning and Information messages don’t stop you from entering the invalid data—they just display a message—but the Stop option does.
If you like, you can type your own title and error message in the Title and Error message text boxes. If you leave these boxes blank, a generic default title and message will appear when invalid data is entered.
Filling down and moving on
Once you’re done setting up the data in the Data Validation dialog box, click OK to return to the worksheet. You’ve created a drop-down list in the first cell, and now you can copy the cell formatting down to the rest of the project list items. While the cell is still blank, click the cell fill handle and drag downward until you reach the end of the list.
Voila! You’ve created a drop-down list. As work progresses on each project, click the drop-down list cell next to the item, click the list arrow, and select the status.
schedule a class on excel
Request an on-site Excel class from Applied Office. Sessions are inexpensive and your employees will be shown how to create spreadsheets, charts, and use formulas that will save them a lot of time. Learn more here
upcoming classes on excel
Classes on Microsoft Excel are scheduled at University of the Pacific, and you can attend! Review the Upcoming Classes for more information.
quick reference card
Get the Quick Reference Card on Microsoft Excel! Download it for free and print it on your own printer. You might even want to laminate it.