How To Create A Drop Down List Excel 2007

Download Article

Adding a drop-down box to your Excel 2007 spreadsheet can speed up data entry by offering users a list of items to select from instead of having to type information each time. When you put a drop-down box in a spreadsheet cell, the cell displays an arrow. You enter data by clicking the arrow and then selecting the desired entry. You can set up a drop down box in just a few minutes, and greatly improve your data entry speeds.

Steps Download Article

1

Open the spreadsheet in which you want to add the drop-down box(es).
2
Create a list of the items to appear in the drop-down list. Type the data in the order it will be displayed in the list. The entries should be typed in a single column or row and should not contain any blank cells.
- To create the list of the desired items on a separate worksheet, click the tab of the worksheet in which to enter the data. Type and then highlight the data to appear in the list. Right-click the highlighted range of cells and click "Name a Range" from the list that appears. Type the name for the range in the "Name" box and click "OK." You can then protect or hide the worksheet to prevent other users from making changes to the list.
Advertisement
3

Click the cell you want to place the drop-down box in.
4

Click the "Data" tab of the Microsoft Excel 2007 ribbon.
5

Click the "Data Validation" button from the "Data Tools" group. The "Data Validation" dialog box appears.
6

Click the "Settings" tab and then click "List" from the "Allow" drop-down list box.
7
Click the toggle button at the end of the "Source" box. Select the list of items you want to have in your drop-down box.
- If you created a range name, in the "Source" box, type an equal sign and then type the name of the range.
8

Select or clear the "Ignore blank" check box, depending on whether you allow the cell containing the drop-down box to be left blank. Make certain the "In-cell dropdown" check box is selected.
9

Click the "Input Message" tab to display a message box when the cell of the drop-down box is clicked. Make certain that the "Show input message when cell is selected" check box is selected and then type the "Title:" and "Input message:" that will appear in the message box.
10

Click the "Error Alert" tab to display an error message if invalid data is entered into the drop-down box cell. Make certain that the "Show error alert after invalid data is entered" check box is selected. To display a warning or information but not prevent invalid data from being entered, select either "Warning" or "Information" from the "Style" drop-down list box. To display a message and prevent invalid data from being entered, select "Stop" from the "Style" drop-down list box. Type the "Title:" and "Error message:" that you want to appear.
11

Click "OK" to save the validation criteria and create the drop-down box.

Advertisement

Add New Question

Question

How can I select multiple options with the drop down?

Program the box using VBA (Code: .AddItem "Item Name") to add some items. To do this, make sure you say to add the items when something happens.
Question

How do add an entry if the drop down already exists?

Right-click on the area where you want to add a new entry, then click on "Insert", then "New Row" in between two existing rows.
Question

How do I delete a range that has already been created?

Simple: delete the row or column that contains the range and the drop down list vanishes.

Ask a Question

200 characters left

Include your email address to get a message when this question is answered.

Submit

Video

If a drop-down list entry is longer than the cell containing the drop-down list box, change the cell width to reveal the entire text.
To delete a drop-down box, click the cell containing the box. To delete a drop-down list, select the cell with the list. Click the "Data" tab of the Microsoft Excel 2007 ribbon. Click the "Data Validation" button from the "Data Tools" group. Click the "Settings" tab, click the "Clear All" button, and then click "OK."

Thanks for submitting a tip for review!

About This Article

Thanks to all authors for creating a page that has been read 473,686 times.

Is this article up to date?