How To Create A Drop Down Excel

Download Article

This wikiHow teaches you how to create a drop-down list in a Microsoft Excel spreadsheet, using a computer. This feature allows you to create a list of items to choose from, and insert a drop-down selector into any empty cell on your spreadsheet. The drop-down feature is only available on desktop versions of Excel.

1

Open the Excel spreadsheet file you want to edit. You can find and double-click a saved Excel file on your computer, or open Microsoft Excel and create a new worksheet.
2
Enter the list of values for your drop-down in a column. Make sure to enter each drop-down entry in a separate, consecutive cell in the same column.
- For example, if you want your drop-down list to include "New York," "Boston," and "Los Angeles," you can type "New York" in cell A1, "Boston" in cell A2, and "Los Angeles" in cell A3.
3

Click the empty cell you want to insert your drop-down. You can insert a drop-down list in any empty cell on your spreadsheet.
4

Click the Data tab on the toolbar ribbon. You can find this button above the toolbar at the top of your spreadsheet. It will open your data tools.
5

Click the Data Validation button on the "Data" toolbar. This button looks like two separate cell icons with a green checkmark and a red stop sign. It will open a new pop-up.
6
Click the Allow drop-down in the "Data Validation" pop-up. You can find this menu in the pop-up's "Settings" tab.
- The Data Validation pop-up automatically opens up to the Settings tab.
7

Select List in the "Allow" drop-down. This option will allow you to create a list in the selected empty cell.
8

Check the In-cell dropdown option. When this option is checked, you will create a drop-down list in the selected cell on your spreadsheet.
9
Check the Ignore blank option (optional). When this option is checked, users will be able to leave the drop-down empty without an error message.
- If the drop-down you're creating is a mandatory field, make sure not to check this box here. Otherwise, you can leave it unchecked.
10

Click the text box under "Source" in the pop-up. You can select the list of values you want to insert into your drop-down here.
11
Select your drop-down's list values on the spreadsheet. Use your mouse to select the list of values you want to insert into your drop-down on the spreadsheet.
- For example, if you have "New York," "Boston," and "Los Angeles" in cells, A1, A2, and A3, make sure to select the cell range from A1 to A3.
- Alternatively, you can manually type your drop-down list values into the "Source" box here. In this case, make sure to separate each entry with a comma.

1

Click the Input Message tab in the "Data Validation" pop-up. It's at the top of the pop-up window. This tab will allow you to create a pop-up message to display next to your drop-down list.
2
Check the Show input message... option. This option allows you to display a tiny pop-up message when the drop-down is selected.
- If you don't want to show a pop-up message, just leave the box unchecked.
3
Enter a "Title" and an "Input Message. " You can use this area to explain, describe or provide more information about the drop-down list.
- The title and input message you enter here will show up on a tiny, yellow pop-up sticky next to the drop-down when the cell is selected.
4

Click the Error Alert tab at the top of the pop-up. This tab will allow you to display a pop-up error message whenever invalid data is entered into your drop-down cell.
5
Check the Show error alert... option. When this option is checked, an error message will pop up when a user types invalid data into the drop-down cell.
- If you don't want an error message to pop-up, leave the box unchecked.
6
Select an error style in the Style drop-down. You can select an option from Stop, Warning, and Information here.
- The Stop option will show a pop-up error window with your error message, and stop users from entering data that isn't in the drop-down list.
- The Warning and Information options will not stop users from entering invalid data, but show an error message with the yellow "!" or blue "i" icon.
7
Enter a custom "Title" and "Error message. " (optional) Your custom error title and message will pop up when invalid data is entered into the drop-down cell.
- You can leave these fields empty. In this case, the error title and message will default to Microsoft Excel's generic error template.
- The default error template is titled "Microsoft Excel," and the message reads "The value you entered is not valid. A user has restricted values that can be entered into this cell."^[1]
8

Click the OK button in the "Data Validation" pop-up. This will create and insert your drop-down list into the selected cell.

Add New Question

Question

How can I add a drop down list, but not show all of the items in each row?

Separate the items in a more organized manner. Break down the items and add multiple entries.
Question

How can I add a dropdown box to multiple cells at the same time?

Copy the cell with the drop down list, highlight all the cells you wish to paste this to, select the paste special option and select "Validation" and then "OK."
Question

I added a new field, but it is not appearing on the drop-down menu. How do I fix this?

Click on cell A1. Select the DATA MENU. Select DATA VALIDATION. In the VALUE field, select LIST, and in the SOURCE field, enter A,B,C.
Question

How do I create it to allow the user to add text?

Place a checkmark next to the field labeled "Ignore blank" if you want users to have the option of selecting zero items from the drop-down list when they use the spreadsheet.
Question

How do I add another item to my current drop-down list?

Simply add to the table that corresponds to your drop-down list and it's automatically inserted.
Question

How do I delete the tab where the list items are, since this will be a form used for work?

Simply right-click on the tab and name and select, "hide."
Question

How do I protect the column so the user can only select from the drop-down menu?

Make sure that in the 'Error Alert' tab of the 'Data Validation' screen you have selected Style: 'Stop'. This enables the user to only select from the drop-down list.

Ask a Question

200 characters left

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

Submit

Video

After you finish creating your drop-down list, open the drop-down list to make sure all the items you entered display properly. In some cases, you may need to widen the cell in order to display all your items fully.
When typing the list of items for your drop-down list, type them in the order in which you want them to appear in the drop-down menu. For example, you can type your entries in alphabetical order to make it easier for users to find certain items or values.

Thanks for submitting a tip for review!

You will not be able to access the "Data Validation" menu if your worksheet is protected or shared. In this case, make sure to remove the protection or unshare the document, and then try to access the Data Validation menu again.^[2]

About This Article

Article SummaryX

1. Open an Excel spreadsheet.
2. Click the Data tab.
3. Click the Data Validation tool.
4. Select List under "Allow."
5. Check the In-cell dropdown option.
6. Enter your drop-down values into the "Source" box.
7. Click OK.

Did this summary help you?

Thanks to all authors for creating a page that has been read 998,017 times.

Is this article up to date?