How to Create Drop Down Lists in Microsoft Excel

Sometimes, you just need to know how to do one thing in Excel and tutorial such as this is just what you need. However, if you find yourself always looking up how to do things and spending longer looking for solutions than actually using them then you should consider a Microsoft Excel Training course. Our courses are tailored for all ability levels; we run public Beginner Excel training and Advanced Excel training but can also tailor a course specifically to your requirements. Call us on 01273 622272 to discuss the level of training suitable for you.

Using a drop down list in Excel can make data input a lot easier – after all, you don’t want to waste time typing in the same thing again and again!

Preparing Your Excel Drop Down List Data

To start with, let’s make a list. Writing it out in alphabetical order can help, as the cell order is the order in which they will appear in the drop down list (the drop down list won’t automatically reorder itself). In this example, we’ll be using a list of flowers.

First, we write out our flower names in cells A1-A7:

excel-drop-down-list-tutorial-preparation

Creating the drop down list

Once your source has been created, you can start creating the drop down list. Highlight all the cells you want your drop down list to appear in (here, we are using cells E2-E20):

creating-an-excel-drop-down-list-

Next, click the data menu (at the top of the screen) and select validation. Scroll down on the “allow” list, and select “list”, like so:

excel-drop-down-list-data-validation

Next, click the source button:

excel-drop-down-list-source

and select our source list:

excel-drop-down-list-source-list

Finally, click OK!

You should now find that when you click any of the cells E2-E20 (or wherever you chose to place your drop down lists) that an arrow appears to the side of it:

excel-drop-down-list-drop-down

Clicking on the arrow will make your drop down box appear!

excel-drop-down-list-tutorial-final

Now you are ready to use your drop down boxes, and won’t have to type anything too repetitive ever again!

Read more of our recent Microsoft Excel Tutorials:

Tags

, , , , ,
Andy Trainer


Connect with Andy on Google.

View all posts by Andy Trainer

One Comment

Wilson says:

Nice tip. I also find using “alt + down” handy when filling in long lists in Excel. It shows you all previous entries.

Leave a Comment