Download Article
Download Article
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
Download Article
-
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.
-
Click the cell in which you'd like the drop-down to appear. This selects the cell.Advertisement
-
Click the Data tab. You'll see it in the toolbar running along the top of Excel.
-
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.
-
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
-
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.
-
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.
-
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.
-
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.
-
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.
Advertisement
Community Q&A
Search
-
QuestionHow can I select multiple options with the drop down?Community AnswerProgram 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.
-
QuestionHow do add an entry if the drop down already exists?Community AnswerRight-click on the area where you want to add a new entry, then click on "Insert", then "New Row" in between two existing rows.
-
QuestionHow do I delete a range that has already been created?Community AnswerSimple: 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
Advertisement
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
- 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
Submit a Tip
All tip submissions are carefully reviewed before being published
Thanks for submitting a tip for review!
Advertisement
References
About This Article
Thanks to all authors for creating a page that has been read 496,397 times.
Reader Success Stories
- "The wikiHow article helped me create an audit tool for monitoring staff supervision and compliance. Thank you."
Advertisement
"The wikiHow article helped me create an audit tool for monitoring staff supervision and compliance. Thank you."
"Amazing! Very detailed, with many pictures to follow step by step. Loved it. Thanks."
"Great visuals and step-by-step instructions for how to add simple drop-down lists!"
"I wanted to know how to create a drop down list. This article is well explained."
"It answered my question, helped me to finish my business report successfully."
