Easily Create a Drop-Down List in Microsoft Excel: Setup & Customization

Use data validation to create a drop-down list in Microsoft Excel

Microsoft Excel's Data Validation feature allows you to create a list of items and insert a drop-down menu into any cell on your spreadsheet. It’s a useful feature for creating consistent data entry with categorical data. This wikiHow guide will show you how to create and edit a drop down list in Excel for Windows and Mac.

Things You Should Know

  • Create a list of drop-down items in a column. Make sure the items are consecutive (no blank rows).
  • Click the cell where you want the drop-down.
  • Click the Data Validation button in the Data tab.
  • Select the list of drop-down items. Then, customize the list using the data validation options.
Part 1
Part 1 of 2:

Creating a Drop-Down

  1. Image titled Create a Drop Down List in Excel Step 1
    1
    Enter the list of drop-down values in a column. Make sure to enter each drop-down item 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.
    • You can place these items in an existing worksheet, or a new one. They can then be referenced in any worksheet in the workbook.
    • For formatting tips, check out our guide on formatting an Excel spreadsheet.
  2. Image titled Create a Drop Down List in Excel Step 2
    2
    Click the cell where you want to insert your drop-down. This will select the cell. You can insert a drop-down list in any empty cell on your spreadsheet.
    • Drop-downs are helpful for information you want to enter consistently and repeatedly. For example, if you’re making a bill tracker, you could have a drop-down with bill types.
    Advertisement
  3. Image titled Create a Drop Down List in Excel Step 3
    3
    Click the Data tab. You can find this at the top of your spreadsheet. It will open your data tools.
  4. Image titled Create a Drop Down List in Excel Step 4
    4
    Click the Data Validation. It’s the button on the "Data" toolbar that looks like two separate cells with a green checkmark and a red stop sign.
  5. Image titled Create a Drop Down List in Excel Step 5
    5
    Click the Allow: drop-down. This is in the Settings tab of the Data Validation window.
  6. Image titled Create a Drop Down List in Excel Step 6
    6
    Select List. This option will allow you to create a list in the selected cell.
  7. Image titled Create a Drop Down List in Excel Step 7
    7
    Check the
    Windows Unchecked
    In-cell dropdown option.     When this option is checked, you will create a drop-down list in the selected cell on your spreadsheet.
  8. Image titled Create a Drop Down List in Excel Step 8
    8
    Check the
    Windows Unchecked
    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.
  9. Image titled Create a Drop Down List in Excel Step 9
    9
    Click the text box under "Source" in the pop-up. You can select the list of values you want in your drop-down.
    • Click the upward arrow button to minimize the Data Validation window, showing only the cell range text box.
  10. Image titled Create a Drop Down List in Excel Step 10
    10
    Select your drop-down's list values on the spreadsheet. Click and drag the cursor to select the list of values you want in the drop-down.
    • 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.
    11. Advertisement
Part 2
Part 2 of 2:

Adding List Properties

  1. Image titled Create a Drop Down List in Excel Step 11
    1
    Click Input Message. It's the second tab at the top of the Data Validation window. This tab will allow you to create a pop-up message to display next to your drop-down list.
  2. Image titled Create a Drop Down List in Excel Step 12
    2
    Check
    Windows Unchecked
    Show input message when cell is selected.     This displays a pop-up message when the drop-down cell is selected.
    • If you don't want to show a pop-up message, just leave the box unchecked.
  3. Image titled Create a Drop Down List in Excel Step 13
    3
    Enter a Title and 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 small, yellow pop-up sticky note next to the drop-down when the cell is selected.
  4. Image titled Create a Drop Down List in Excel Step 14
    4
    Click Error Alert. This tab will let you display a pop-up error message whenever invalid data is entered into your drop-down cell.
  5. Image titled Create a Drop Down List in Excel Step 15
    5
    Check
    Windows Unchecked
    Show error alert after invalid data is entered.     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. Image titled Create a Drop Down List in Excel Step 16
    6
    Select an error icon in the Style drop-down. You can select Stop, Warning, or Information.
    • 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. Image titled Create a Drop Down List in Excel Step 17
    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. Image titled Create a Drop Down List in Excel Step 18
    8
    Click OK. This will create and insert your drop-down list into the selected cell.
    • Now users can click the drop-down button (downward triangle) next to the cell to select an item.
    • Make changes to the data validation drop-down list by selecting the cell with the drop-down and clicking the data validation button. The data validation window will reopen and you can edit any of the options.
    • You can copy (ctrl/cmd + c) the cell with the drop-down and paste (ctrl/cmd + v) in other cells to duplicate the drop-down.
    9. Advertisement

Community Q&A

  • Question
    How can I add a drop down list, but not show all of the items in each row?
    Community Answer
    Community Answer
    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?
    Community Answer
    Community Answer
    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?
    Community Answer
    Community Answer
    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.
Advertisement

Warnings

Advertisement

You Might Also Like

Add a Drop Down Box in Excel 2007How to Add a Drop Down Box in Excel 2007
Apply Conditional Formatting in Excel
How to
Apply Conditional Formatting in Excel
Create a Currency Converter With Microsoft Excel
How to
Create a Currency Converter With Microsoft Excel
Create a Mortgage Calculator With Microsoft Excel
How to
Create a Mortgage Calculator With Microsoft Excel
Make a Spreadsheet in Excel
How to
Make a Spreadsheet in Excel
Unprotect an Excel Sheet
How to
Unprotect an Excel Sheet
Create a Timeline in ExcelHow to Create a Timeline in Excel: SmartArt, Templates, and More
Create a Graph in Excel
How to
Create a Graph in Excel
Change from Lowercase to Uppercase in Excel
How to
Change from Lowercase to Uppercase in Excel
Merge Cells in ExcelHow to Merge Cells in Microsoft Excel: A Quick Guide
Insert Pictures in Excel That Automatically Size to Fit Cells
How to
Insert Pictures in Excel That Automatically Size to Fit Cells
How to
Unhide Rows in Excel
Convert Excel to Word3 Easy Ways to Convert Microsoft Excel Data to Word
Use ExcelNew to Excel? Here's Super Easy Tricks to Get You Started
Advertisement

References

  1. ↑ https://support.office.com/en-us/article/create-a-drop-down-list-7693307a-59ef-400a-b769-c5402dce407b#ID0EAADAAA=Windows
  2. ↑ https://support.office.com/en-us/article/lock-or-unlock-specific-areas-of-a-protected-worksheet-75481b72-db8a-4267-8c43-042a5f2cd93a

About This Article

Kyle Smith
Written by:
Kyle Smith
wikiHow Technology Writer
This article was co-authored by wikiHow staff writer, Kyle Smith. Kyle Smith is a wikiHow Technology Writer, learning and sharing information about the latest technology. He has presented his research at multiple engineering conferences and is the writer and editor of hundreds of online electronics repair guides. Kyle received a BS in Industrial Engineering from Cal Poly, San Luis Obispo. This article has been viewed 1,106,672 times.
How helpful is this?
Co-authors: 14
Updated: February 11, 2023
Views: 1,106,672
Categories: Featured Articles | Microsoft Excel
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?
Advertisement