How to Add a Drop Down Box in Excel 2007

Last Updated: March 8, 2023 Tested

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 wikiHow 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

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.
2
Click the cell in which you'd like the drop-down to appear. This selects the cell.

Advertisement
3
Click the Data tab. 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.
5
Click the "Allow" menu and select List. This tells Excel which data format to use for the cell.^{[1]
X
Trustworthy Source
Microsoft Support
Technical 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.
10
Click OK. 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

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.
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.
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.