Create a drop-down list

You can help people work more efficiently in worksheets by using drop-down lists in cells where they can pick an item from a list you create. Watch this quick video to see how it's done.

1. In a new worksheet, type the entries you want to appear in your drop-down list. Ideally, you'll have your list items in an Excel table. If you don't, then you can quickly convert your list to a table by selecting any cell in the range, and pressing Ctrl+T.

   

   Notes: 

   • Why should you put your data in a table? When your data is in a table, then as you add or remove items from the list, any drop-downs you based on that table will automatically update. You don't need to do anything else.

   • Now is a good time to Sort your data in the order you want it to appear in your drop-down list.

2. Select the cell in the worksheet where you want the drop-down list.

3. Go to the Data tab on the Ribbon, then Data Validation.

   Note: If you can't click Data Validation, the worksheet might be protected or shared. Remove the protection or stop sharing the worksheet, and then try step 3 again.

4. On the Settings tab, in the Allow box, click List.

5. Click in the Source box, then select your list range. We put ours on a sheet called Cities, in range A2:A9. Note that we left out the header row, because we don't want that to be a selection option:

   

6. If it's OK for people to leave the cell empty, check the Ignore blank box.

7. Check the In-cell dropdown box.

8. Click the Input Message tab.

   • If you want a message to pop up when the cell is clicked, check the Show input message when cell is selected box, and type a title and message in the boxes (up to 225 characters). If you don't want a message to show up, clear the check box.

     

9. Click the Error Alert tab.

   • If you want a message to pop up when someone enters something that's not in your list, check the Show error alert after invalid data is entered box, pick an option from the Style box, and type a title and message. If you don't want a message to show up, clear the check box.

     

10. Not sure which option to pick in the Style box?

    • To show a message that doesn't stop people from entering data that isn't in the drop-down list, click Information or Warning. Information will show a message with this icon and Warning will show a message with this icon .

    • To stop people from entering data that isn't in the drop-down list, click Stop.

      Note: If you don't add a title or text, the title defaults to "Microsoft Excel" and the message to: "The value you entered is not valid. A user has restricted values that can be entered into this cell."

Learn more about drop-down lists

Build your knowledge of drop-down lists with LinkedIn Learning. Learn how to control how users enter data into workbooks, ensuring that data is consistent and accurate. You can control dates, times, even the length of the text they enter, or simply provide a list of acceptable choices to eliminate any possible mistakes.

Watch free preview >

Working with your drop-down list

After you create your drop-down list, make sure it works the way you want. For example, you might want to check to see if the cell is wide enough to show all your entries.

If the list of entries for your drop-down list is on another worksheet and you want to prevent users from seeing it or making changes, consider hiding and protecting that worksheet. For more information about how to protect a worksheet, see Lock cells to protect them.

If you decide you want to change the options in your drop-down list, see Add or remove items from a drop-down list.

To delete a drop-down list, see Remove a drop-down list.

To see a video about how to work with drop-down lists, see Create and manage drop-down lists.

Download our examples

You can download an example workbook with multiple data validation examples like the one in this article. You can follow along, or create your own data validation scenarios. Download Excel data validation examples

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

See also

Apply Data Validation to cells

Video: Create and manage drop-down lists

All Excel functions (by category)

Creating drop-down lists (free preview)