How-to create a drop-down list in Excel

One extremely common request I hear as an Excel consultant is “how do I create a drop-down list to keep my data clean?” (Maybe not those words exactly, but you get the point).

With a little bit of working knowledge on named ranges, you too can have this professional looking feature in your worksheet.

First, make a table of the data you want to appear in the drop-down and apply a named range to it. In this case, we will name this range ‘employees’.

List of names to include in a drop-down list

Next, select the cell where you want the drop-down list to appear. For this tutorial’s purpose, the cell should be on the same worksheet as the range ‘employees’.

Navigate to the data validation section of the menu by following the cues below.

navigation to the data validation section

Within the dialog box that pops up, select Allow->List and for Source, write “=employees”.

dialog box for setting up a drop-down list

Ta-Da! You should now have a drop-down list for the cell.

drop-down list in Excel

drop-down list in Excel in action

Bonus! – You can copy and paste this cell and the drop-down list is copied right along with it.

Have questions about how to get the most out of Excel?

I bend Excel to my will every day. Now you can hire me to be your Excel Consultant.

Contact me today for a FREE QUOTE.