Excel: Make Dropdowns Like a Pro

Ever get a spreadsheet from someone where cells are limited to only certain options, and you can click on it and all the options are available on a list. Then you think, this is amazing and I wish I knew how to do this. Or had a spreadsheet you needed people to fill out but wanted to limit someone’s selection options. You can have this power. The power of the dropdown!

restrict options

We all like choices, but sometimes that just is not feasible. No judgement here.

We will be using the Data Validation tool to make this possible. We will look at two different ways to set this tool up. I have a simple survey that I will be using a spreadsheet with two questions about painting the outside of my house. On one sheet are my survey questions and on the second sheet I have listed the options for the house color.

sheets set up

Having the controls on a second sheet allows you to lock and hide that sheet so people can’t create their own options. Also, it makes it seem like you have a control panel – and who doesn’t like that kind of power.

The data validation tool restricts the data that can be entered based on the set-up. For a drop down menu, we will be using the ‘List’ setup. Select the Data Validation tool. In the ‘Allow:’ criteria, pick ‘List’. You will then enter the source. Here is where we have two options.

Your source can be a range of cells or just a written out list. If you choose a range of cells, the options will be whatever is in those cells. Otherwise, if you list the options, separate them by a comma for each individual item. Click OK and your cells will be set up.

Data Validation

It really is this easy. You are about to own the Excel game.

You have dropdown menus. You are now massively impressive.

*Please enjoy this new found power of control over options responsibly.

Casey Love

Like what you've learned?

Help us out by telling your friends!