How to Create a Drop Down List in Excel – The Main Steps

Knowing how to create a drop down list in Excel could prove to be very useful for you IT interview. In this article, we will tell you exactly how to do that, step by step so that you can explain the process to the interviewer.

What Is a Drop Down List in Excel?

drop down list in ExcelA drop down list in Excel is a list of items that appears in the sheet when users attempt to fill a certain cell. The users will only be allowed to choose one of the values included in the list. They will not be able to input their own values.

In fact, this is exactly what makes them different from list boxes or combo boxes. While these two types of boxes allow users to either choose from the provided options or enter their own values, the drop down list confines them to the values included in the list.

As you can imagine, creating a drop down list in Excel could prove to be useful in a wide range of situations where users need to be limited to a certain amount of options.

How to Create a Drop Down List in Excel

Creating a drop down list in Excel is quite easy to do. We will walk you through each step of the process so that you understand it properly. Take a look!

Step1.

Create a sheet with the introduction to the drop down list in Excel. In our example, this will be “Favorite TV shows”. Our aim is to provide users with a list of options, but prevent them from entering their own values.

Printscreen of drop down list in Excel

Step 2.

Create a separate sheet where you will type in the options you want to include in your drop down list in Excel. You want to place them in the exact order you want the users to see because you will not be able to modify this later on.

Printscreen of drop down list in Excel - create a separate sheet with the options

Step 3.

Go back to the first sheet and select the cell where you want your drop down list in Excel to be. Then go to the Data menu and click on the Data Validation button. A window will pop up with your options.

Printscreen of drop down list in Excel - click on the Data Validation button

Step 4.

To create a drop down list in Excel, you need to go to the Allow section and select the List option.

Printscreen of drop down list in Excel - choose the list option

Step 5.

Then you will have to select the options for your drop down menu in Excel. To do this go to the Source section and click on the icon located next to the empty field. This will allow you to select the exact options you want to include.

Printscreen of drop down list in Excel - choose the variables for your list

Step 6.

Go to the second sheet and select the first cell where you have included the options (in our example, this is “Game of Thrones”, A1). When you do this, “=Sheet2!$A$1” will appear in the Source box. Then press the Shift key on your keyboard and hold it. Then select the last item on your list (in our example, this is “The Walking Dead”, A5). In your Source box, “=Sheet2!$A$1:$A$5” will appear. This is the formula that defines the contents of your drop down box in Excel. Then click the Ok button to complete your list.

Printscreen of drop down list in Excel - choose the variables for your list from sheet teo

Step 7.

As you can see, your Excel dropdown list is now complete. The users will be able to choose from the options you have provided.

Printscreen of drop down list in Excel - the compelted list

Step 8.

To make sure that the users can only select from the options you have included in your Excel dropdown list, let’s try to type in a value that is not included in the list.

Printscreen of drop down list in Excel - trying to type in new value

Step 9.

When we do that, a window will appear to inform us that this is not permitted. Therefore, the users will only be able to select from the options we have provided.

Printscreen of drop down list in Excel - excel does not allow users to type in new values

We hope that the information we have provided was useful to you. Now you are ready to answer any questions about the drop down list in Excel for your interview! Good luck on your job meeting from the IT interview Guide team!

Image Source: Wikimedia

Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.