How To Create a Drop-Down List in Excel (With Step-by-Step Instructions)

By Indeed Editorial Team

Published September 29, 2021

The Indeed Editorial Team comprises a diverse and talented team of writers, researchers and subject matter experts equipped with Indeed's data and insights to deliver useful tips to help guide your career journey.

If you want users to fill out a spreadsheet, creating a drop-down list may help improve the process. This feature allows users to select items from a list instead of typing in their individual values. Adding drop-down lists can make it easier for users to fill out your spreadsheets and simpler for you to organize your data. In this article, we share two different methods for creating a drop-down list in Excel and explore useful customization options, like how to prevent or allow invalid entries.

What is a drop-down list in Excel?

A drop-down list in Excel lets users choose an option from a list of choices. It can prevent users from entering their own values and make it easier to quickly fill out spreadsheets by providing set options.

Drop-down lists are within a single cell and accessible by clicking the drop-down arrow icon to the right of the cell. When they click on this icon, users can view all the items in the list. Longer lists may require users to scroll by clicking the up and down arrows on the scroll bar. They may also view long lists by hovering their mouse over the scroll bar and scrolling their mouse wheel.

Related: How To Include Excel Skills on Your Resume

How to create a drop-down list in Excel manually

Here's how to create a drop-down list in Excel manually:

  1. Select the cell that you want to contain in the drop-down list. If you want multiple cells to contain the same list, you can select multiple cells by clicking and dragging.

  2. Click on the Data tab in the ribbon. Go to the Data Tools group and click Data Validation.

  3. In the Data Validation dialog box under Settings, click the drop-down menu under Allow and click List.

  4. Click on the Source box and type in the values you want to appear in your list. Separate each value with a comma, ensuring you don't include a space after the commas before clicking OK.

Related: 25 Best Excel Tips To Optimize Your Use

How to create a drop-down list in Excel using a table

Here's how to create a drop-down list in Excel by using a table:

  1. Open your Excel workbook and create two sheets. Set aside the first sheet for your users to complete.

  2. In the second sheet, create a table and enter the items you want in your drop-down list. If you've already typed a list of items, you can convert it to a table by pressing Ctrl + T.

  3. Highlight the cell or cells in the first sheet you want to contain the drop-down list.

  4. In the first sheet, go to the Data Tools group under the Data tab. Click Data Validation.

  5. Go to the Settings tab in the Data Validation dialog box. Click on the drop-down menu under Allow and select List.

  6. Select the Source button to minimize the Data Validation dialog box. Then, open the second sheet and select the items in the table that you want to appear in the drop-down list.

  7. Finalize the drop-down list by pressing enter and selecting OK.

  8. If you don't want users to see the second sheet, right-click on the sheet name. Select Hide.

Related: 50 Questions To Help You Ace an Interview Test on Excel

Editing a drop-down list

To edit a drop-down list in Excel that you created manually, go to Data Validation. Then you can edit the list items in the Source box. If you created a drop-down list via a table, you can add or remove items by editing the table in the second sheet. The changes automatically update in the drop-down list on the first sheet.

How to allow or prevent other entries

It's important to decide whether to allow or prevent values other than the options in your drop-down list. Customize what entries your drop-down list in Excel accepts by following these steps:

  1. Click on the Data Validation button as you did when you created your drop-down list. Click on the Error Alert tab.

  2. If you want to allow invalid entries, uncheck the box next to "Show error alert after invalid data is entered." Click OK.

  3. If you want to prevent invalid entries, check the box next to "Show error alert after invalid data is entered." Click OK.

Related: 12 Excel Interview Questions and Answers To Help You Prepare

Error alert styles to choose from

When you prevent invalid entries in your drop-down list, Excel allows you to choose an alert style. Pick your preferred style by going to the Error Alert tab in the Data Validation dialog box. Under the Style drop-down menu, you can choose from Stop, Warning and Information. Here's more information on each of the three error alert styles:

Stop

Note that Stop is Excel's default option. With this error alert style, Excel prevents users from entering an invalid value. An error message appears to prompt the user to retry.

Warning

The Warning style displays an error message when users enter invalid data. However, unlike with the Stop style, users have the option of overriding the data validation settings. If you select Yes, Excel stores your invalid entry. They can also select No if they prefer to choose a valid entry. If the user hits enter, Excel automatically selects No.

Information

Information is similar to the Warning style in that users see a message when they enter invalid data. They can choose to bypass the message and complete their entry. The difference is that the Information style automatically stores a user's invalid value if they hit enter.

How to create an error alert message

After choosing the error alert style, you can customize the message that appears when a user enters invalid data. Here are instructions for creating an error alert message for a drop-down list in Excel:

  1. Go to the Error Alert tab in the Data Validation dialog box.

  2. Click on the Title field and enter a title.

  3. Enter an error message into the Error message field.

The default error alert title is "Microsoft Excel." However, you can customize it to something like "Invalid Entry!" to ensure users understand that their entry was invalid. Similarly, the default error message is "The value you entered is not valid." You may choose to change it to something more friendly, like "Please choose an option from the drop-down list."

Please note that none of the companies mentioned in this article are affiliated with Indeed.

Explore more articles