This article shows you how to make a drop down list in excel.
It provides two ways for you to quickly and easily make drop down lists in an Excel spreadsheet.
You can follow the instructions to create a new drop down list that will help streamline efficiency when collecting information in your spreadsheet.
A dropdown list can be a useful thing in your spreadsheet. Especially if you want to make it easier for others to enter information on your sheet quickly.
With a dropdown list, you can give other users a quick set of options instead of having to type in each piece of information in every cell.
It is also helpful for the person responsible for the spreadsheet because it standardizes the data you want to collect, which may cut down on confusion or problems when it’s time to inspect your data or make calculations.
This article will go through a step-by-step process of how to make a dropdown list in Excel.
We will show you:
- where to find the dropdown feature
- how to enter a list of options that will appear in your dropdown list
- and even how to use a dropdown list that you have in a different spreadsheet
Let’s get down to business and learn how to make a drop down list in excel.
How To Make A Drop down List In Excel: Getting Started
Today we are going to use an example of a group of friend’s favorite types of fruit. There are two ways you can do this.
The first involves making one spreadsheet. The other requires you to make two.
For the method involving two separate sheets.
The first spreadsheet, which will be the one where you will eventually insert your dropdown list, will contain the list of all the friends.
The second sheet will be where you create the possible selections of favorite fruits that will make up the dropdown list.
Here is the list of people who will be taking the survey:
And here is the sheet containing the list of fruits that the participants can select:
Now that we have our two spreadsheets set up, we can go about making the list of fruits in Sheet 2 into a dropdown list that users with access to Sheet 1 can select.
Finding the Dropdown Feature
The dropdown feature is not the easiest feature to find in Excel. However, with our help, you’ll be an expert in no time.
In Excel, you must scroll to the “DATA” tab in the menu.
Be sure you are currently on the sheet that you want your dropdown list to appear. In the section named “Data Tools” you will see the feature for “Data Validation” which should look like this:
Once you have found the button, begin by selecting the cell, or in this case the range of cells, you want the list to appear in.
Then click the data validation button.
After that, a dialog box will appear with several options.
Under the “Allow” box, click the dropdown menu and select “List,” which will look like this:
After selecting “List,” you will then tell the spreadsheet what source you want to use, in this case, the list of fruit we made in Sheet 2.
Note that in the “source” line there is now a somewhat complicated looking formula. That’s just telling spreadsheet 1 to look to Sheet 2 to find the items that will be in your dropdown list.
Once you press enter, you will be taken back to spreadsheet 1 with the names of the people taking the survey.
In the column underneath the “FAVORITE FRUIT,” the cells now have a dropdown list. You’ve now made a simple dropdown list for your spreadsheet.
If you want to do all this in a single spreadsheet you will follow some of the same steps for setting up spreadsheet one, but don’t need to make a second sheet.
Instead of pointing your dropdown list to Sheet 2, when you fill out the “source” in the data validation box, you type in the possible options for the dropdown box.
The one-sheet method is useful when you have a limited number of responses, for example, if you are gauging attendance for an event and your only answers are attending/not attending.
But, as in our example, if you have a long list, it’s much easier to make everything on a separate sheet. Using two sheets will also make it easier to modify the list later.
More on Dropdown Lists
Now that you have made your dropdown list, there are a few things that you need to decide on. And you also have a few things to check.
First, if you only want the users to see one spreadsheet, you can hide the other spreadsheet by right-clicking on the tab. And then selecting “hide” from there.
However, if you do it all on one sheet other users could make changes to the dropdown list. Thus, if you are the only one who wants to be able to control what is on the lists. It’s better to follow the two sheet method.
You can also protect the spreadsheet or choose to lock it down so that no other user besides yourself can make changes.
One thing to check on is making sure that the cells are wide enough to display the whole entry.
In our example above, all of the words are relatively short and this isn’t a problem. But it is always a good thing to check before you send your spreadsheet to a bunch of people.
Modifying Your Dropdown List
Maybe you forgot to add your own favorite fruit, or just want more options. Don’t worry. It is quite simple to add items or remove them, from your dropdown list.
If you have made your dropdown list with two spreadsheets, which we feel is the better method, all you have to do is go to that sheet and add the items that you want to be included.
The dropdown list will then automatically be updated in the other spreadsheet.
Similarly, if you want to delete an item you just go to the sheet. From there, you delete whichever items you no longer want to include in your dropdown list. Easy as that!
If you created a dropdown list by manually entering the options, you just have to go into Data Validation again. And then enter the new items to the list from there.
Conclusion
You now know how to make a dropdown list in Excel!
Dropdown lists are useful any time you want users to enter specific data that you have already identified. It can keep things uniform and is more efficient for other users who are entering data.
