How to Create a Drop-down List in Excel

Drop-down lists are a powerful tool in Excel, typically used for designing questionnaires. A drop-down list allows the end-user to choose a specific answer or response from a provided list.

This guide shows you how to easily create drop-down lists in Excel. I will be using the Office 365 version of Excel (build 2203). However, the instructions will be similar for other versions.

Creating a Drop-Down List

First, we’ll make a sample table for this example. We have a table asking two questions. The first question will have 12 possible answers and the second question has a simple yes or no answer.

A table in Excel
Our sample table

To make the drop-down list for the first question, you will need to write down the answers in a column. We recommend doing this in a separate sheet to keep things simple.

  1. Open a new sheet by clicking on the + icon at the bottom of your screen.
    A table in Excel
    Open a new sheet

    Once the new sheet is open, write down the answers for the first drop-down list in a column.

    Excel Spreadsheet with data
    Enter data in the new sheet
  2. Back in our first sheet, select the cell where the first answer will go. Then, click on the Data tab from the top menu.

    A table in Excel
    Select the Data tab from the top menu
  3. From the Data Tools section, select Data Validation.
    A table in Excel
    Select Data Validation from the Data tools section

    You will get a Data Validation pop-up window.

    A table in Excel
    Open Settings in the pop-up window
  4. In the window, open the Settings tab. Under Validation criteria, select List.
    Excel Data Validation
    Select List for the data validation

    Next, you will need to add the data to the drop-down list. Click on the icon to the right of the Source tab.

    Excel Data Validation
    Select the icon to add data
  5.  Navigate to the second sheet by selecting Sheet2 at the bottom of your screen.
    A table in Excel
    Navigate to Sheet2

    Highlight the cells that contain the data for the drop-down list and press Enter.

    Data in excel sheet
    Highlight the data from Sheet2

    Your drop-down list is now automatically created.

A table in excel
The first drop-down list is created

How to make a drop-down list with a yes/no answer

The method we just looked at is ideal for making a drop-down list that contains multiple answers. However, follow these steps if you want to make a drop-down list with a simple answer, such as yes or no.

  1. Select the cell for the second drop-down list and open Data Validation from the Data tab.

    A table in Excel
    Select data validation for the second drop-down list
  2. Under Validation Criteria, select List and enter the possible answers in the Source box separated by a comma as shown.
    Excel Data validation
    Enter the data for the drop-down list in the Source box

    Once you have written the options for your drop-down list, select OK to close the pop-up menu.

    Excel Data Validation
    Select Ok to apply

    The second drop-down list will be created.

    A table in Excel
    The second drop-down list is created.

Was this article helpful?

Leave a Comment