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.
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.
- Open a new sheet by clicking on the + icon at the bottom of your screen.
Open a new sheet Once the new sheet is open, write down the answers for the first drop-down list in a column.
Enter data in the new sheet - Back in our first sheet, select the cell where the first answer will go. Then, click on the Data tab from the top menu.
Select the Data tab from the top menu - From the Data Tools section, select Data Validation.
Select Data Validation from the Data tools section You will get a Data Validation pop-up window.
Open Settings in the pop-up window - In the window, open the Settings tab. Under Validation criteria, select List.
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.
Select the icon to add data - Navigate to the second sheet by selecting Sheet2 at the bottom of your screen.
Navigate to Sheet2 Highlight the cells that contain the data for the drop-down list and press Enter.
Highlight the data from Sheet2 Your drop-down list is now automatically 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.
- Select the cell for the second drop-down list and open Data Validation from the Data tab.
Select data validation for the second drop-down list - Under Validation Criteria, select List and enter the possible answers in the Source box separated by a comma as shown.
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.
Select Ok to apply The second drop-down list will be created.
The second drop-down list is created.