Tumblelog by Soup.io
Newer posts are loading.
You are at the newest post.
Click here to check if anything new just came in.

July 22 2010

How to Create a Dropdown List in Excel

Why Type when you can Select?

OVERVIEW: Learn how to create a dropdown list that populates cells in Microsoft Excel.

how to create a dropdown list in excel

You know how e-commerce websites have dropdown lists to let you select data that they want to be valid? This is a pretty handy way of making sure that they get the correct data. Well, you can do this in Microsoft Excel too. The best part is that it takes a few seconds and it’s really simple.

Let’s go!

Creating Your Destination Cell

The first thing you need is a cell where the dropdown list will be placed. Here is a spreadsheet that I created where you can log products that were ordered.

excel spreadsheet

Pretty basic stuff, but I want to be able to populate the Product cells from a dropdown list of valid products.

That’s the other half of the equation – the list.

Creating the List of Values to Populate the Dropdown

Now, go to some blank cells or preferably a blank tab in your workbook.

Type in your list of values going down a column.

list of excel values

These are the only selections that I want to appear in the Product cells.

Now, let’s create the dropdown list.

Creating the Dropdown List

You need to go to the cell that you want to populate from the list (B2). Then, click the Data tab on the ribbon.

excel data validation button

Select the Data Validation dropdown list, then the Data Validation option on the list.

This will open the Data Validation menu.

excel data validation menu

You need to determine what values that you will allow in cell B2 using the Allow list.

Select “List” from the Allow list.

excel data validation menu populated

This will produce the Source field. Click in this field, then select the range of cells where you typed your list.

Press the Enter key on your keyboard to finalize the dropdown list creation.

Using the Dropdown List

If you click in the cell with the list (B2), you will see the dropdown arrow to the right of the cell.  When you click on it, the list of valid values that you created will appear.

excel dropdown list

Select your choice and it will populate the cell.

That’s it! Pretty simple, right?

Applying the Finishing Touches

You just added the dropdown list to one cell (B2). Copy that cell and paste it into the rest of the cells in the Product column and the list will work for every row.

The other thing that I would recommend is hiding the cells with your values. You can either Hide the cells or just place them on another worksheet.

See the Video Tutorial for Creating a Dropdown List in Excel

The following video shows the entire process.

Did you Like this Article?

Why not get free updates from VitaminCM.com?

RSS IconSubscribe via RSS Reader

VitaminCM.com Email Feed

Enter your Email Address to Subscribe via Email

VitaminCM on YouTube Subscribe via YouTube

Or just VitaminCM on Twitter Follow me on Twitter, where I am constantly sharing great articles written by people WAY smarter than me. Or, see some of the other places where you can connect with me out there in the wilds of the internets.

Don't be the product, buy the product!