You might think that creating and using a drop down list in Excel is only something for the “experts” because it’s too complicated. Actually, creating and choosing items in a drop down list can be really easy, and there’s no programming knowledge needed. Today we’re going to show you how you can easily and simply create a drop down list in Excel and some practical ways you can use them.
Why would you want to create and pick items in a drop down list?
Drop down lists are really helpful when you often enter the same information in a column. Let’s say you keep track of your bills in Excel, and you have a column for whether or not it’s been paid. You could use the values of Not Paid, Payment Sent and Cleared. You can use drop downs when tracking elements of a project or work that you need to get done. Or, you can use it for the kids’ names on a chore list. Drop down lists can make using your spreadsheet faster, can keep you more organized and can reduce typing mistakes.
Drop down lists are actually one of the most simple tools to use in Excel, but they are more of a hidden feature since they can’t be accessed by a simply labelled single click. This is why so many people haven’t attempted to use them, instead typing the entries to each cell when they could make their lives so much easier with drop downs.
This feature is useful in business settings, for simple home management, financial planning and especially for entrepreneurs or freelancers who tend to have projects with many moving parts.
When you want a breakdown of your data in a visual format, you can use your drop down entries to create a pivot table. It is pretty easy too, and we’ll cover that in another article.
Creating your first drop down list
Be patient with yourself for the first few times you try this. The steps aren’t intuitive until you get used to it, but when you do, it will take you less than three minutes. We’ve tried several methods to create drop down lists and this one is the easiest since it involves the least amount of manual typing or different commands that you may be unfamiliar with.
We’ll make the drop down list with the Data Validation tool.
To begin, enter the values you want in your drop-down list in one column in a sheet. We are going to use the kids’ chores as an example here. By the end of this tutorial, you’ll be able to create your own drop down lists and duplicate them.
Next, highlight all items in the chores you have listed and name the list. Naming the list is done by simply clicking the box that says A13 in the image above and Chores on the image below and typing in the name of your list, in this case, Chores.
Next, select the cell where you want your drop-down list to be. After it’s selected we’re going to go to the Data Validation option in the Data tab. After you choose Data Validation, you will see a dialog box like the one below.
In this box, you will then be able to choose your “Validation criteria.” For this example, choose List from the selections.
The next step is to use the Chore list name that we created for the cells that had the chore names in them. Make sure to UN check the box that says Ignore Blank. The box with In cell Drop down should remain checked, since this is how you get to use this cool feature.
Simply type = Chore in the Source box, as in the example in the below image.
You can even add a message to prompt yourself or others to choose an option. This is done in the Input Message tab. It allows for a long message, but remember the small space that it will be displayed in when you type out what you want it to say. The display box will grow if more text is added, in the case of creating instructions, or more extensive notes. For this project, let’s keep it short.
You’ll also have to type Chore in the Title box to make sure it displays correctly.
Now click OK!
You can see in the below image that the drop down box has been created and when the cell that contains it is selected, the desired message is displayed in a small yellow box. Note that the small arrow to the right of the cell that allows you to choose options is only visible when the cell is selected.
What’s awesome is that you only need to go through that process once if you’re going to re-use the same list. You can simply select that cell that has the drop down list in it, copy it and paste it in another cell.
When you’re ready to assign the tasks to the kids, select the cell that the list is contained in. After it’s selected and you can see the little arrow to the right of the cell, just click the arrow. This will bring up all the selections in the list. You can choose any of the options that are available, but once you click the arrow, you can’t leave the cell blank unless you included a blank cell in your previous list of chores.
You can get creative in any way you’d like with your drop-down lists. This is an excellent way to have your response options already filled in and all you need to do is make one click.You can even have multiple lists on one spread sheet. For example, you can create another list of how well the kids did their chores, like Great, Done and Please Redo.
When you’re ready for a more advanced trick (it’s not that advanced!) you can make your lists on another tab in your workbook, so the original list is no longer visible on the same page as where you have your drop-down lists.