How to Add a Drop Down Box in Excel 2007

Do you want to create a drop-down menu in your spreadsheet using Microsoft Excel 2007? Even if you're not using the latest version of Excel, it's still easy to create a drop-down list from a range of cells on any worksheet. Adding a drop-down can speed up data entry by offering users a list of items to select from a menu instead of typing values manually. This How.com.vn article will walk you through adding a drop-down list to any cell in your Excel workbook.

Things You Should Know

You can use Excel's Data Validation tools to create a drop-down menu in any cell.
The list of items in your drop-down list can be on a different worksheet—just name the range so it's easy to reference.
If you want to display a warning message when a user selects a certain item, it's easy to create.

Steps

Download Article

1
Type the list of items to appear in the drop-down. In a single column, type each item you want to appear in the drop-down menu in the order you'd like it to appear.
- You can create the list of items for your drop-down on the same sheet as the menu itself.
- If you create the item list on a different worksheet, you can add them to the drop-down by creating a named range that you can reference. After typing the list items on another sheet, follow these steps to name the range:
  - Highlight the list items on your other sheet.
  - Click the Insert tab and choose Define Name…
  - Type a name and click OK. You can protect or hide the separate worksheet to prevent other users from making changes to the list.
This selects the cell.
Advertisement
You'll see it in the toolbar running along the top of Excel.
4
Click Data Validation on the toolbar. It's in the "Data Tools" group at the top of Excel. This opens the Data Validation window.
- The Data Validation window should automatically open to the Settings tab. If it doesn't, click the tab to activate it.
This tells Excel which data format to use for the cell.^{[1]XTrustworthy SourceMicrosoft SupportTechnical support and product information from Microsoft.Go to source}
6
Select your range. There are two ways you can do this:
- If you created a list on the same sheet as your drop-down menu, click the toggle button on the right side of the "Source" box, then click and drag to select all of the cells containing your list items.
- If your list is on another sheet and you named the range, type =RANGENAME, where RANGENAME is the name you gave your range.
7
Choose your preferences for the list. You'll see two drop-down boxes in the top-right area of the Settings tab.
- Make sure "In-cell dropdown" is selected, as this is what turns your list of cells into a drop-down menu.
- You can also check the box next to "Ignore blank" if you don't want to include blank cells in your list.
8
Click the Input Message tab to show a pop-up when the cell is clicked (optional). If you want to add any instructions or details for your menu that will appear when a user clicks the cell, you can do so on this tab.
- Check the box next to "Show input message when cell is selected."
- Enter a title and message for your information box. The title will appear in bold letters at the top of the message.
9
Click the Error Alert to create a custom error message (optional). If you want to display an error message when the user selects an invalid option, you can create the error message on this tab.
- Check the box next to "Show error alert after invalid data is entered."
- To display a warning or information while allowing the user to still select the invalid option, select either Warning or Information from the "Style" menu.
- If you don't want to allow the user to select an invalid option, select Stop from the "Style" menu instead.
- Type a title and error message for your error box into the fields.
This saves your drop-down list and closes the Data Validation window. You'll now see an arrow on the right side of your new cell. Clicking this arrow expands the items you placed in the drop-down list.

Community Q&A

Search

Add New Question

Question
How can I select multiple options with the drop down?
Community Answer
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.
Thanks! We're glad this was helpful.
Thank you for your feedback.
If How.com.vn has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission.Support How.com.vn
Yes No
Not Helpful 3 Helpful 10
Question
How do add an entry if the drop down already exists?
Community Answer
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.
Thanks! We're glad this was helpful.
Thank you for your feedback.
If How.com.vn has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission.Support How.com.vn
Yes No
Not Helpful 19 Helpful 11
Question
How do I delete a range that has already been created?
Community Answer
Simple: delete the row or column that contains the range and the drop down list vanishes.
Thanks! We're glad this was helpful.
Thank you for your feedback.
If How.com.vn has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission.Support How.com.vn
Yes No
Not Helpful 9 Helpful 3

Ask a Question

200 characters left

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

Submit

Video

Tips

To delete a drop-down list, select the cell with the list. On the Data tab, click Data Validation > Settings > Clear All, and then click OK.
Thanks
Helpful0 Not Helpful1
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.
Thanks
Helpful0 Not Helpful0