Why Type when you can Select?
OVERVIEW: Learn how to create a dropdown list that populates cells in Microsoft 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.
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.
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.
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.
Select the Data Validation dropdown list, then the Data Validation option on the list.
This will open the 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.
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.
Select your choice and it will populate the cell.
That’s it! Pretty simple, right?
Applying the Finishing Touches
Wanna See More Great Excel Tips and Tricks?
These articles have even more Excel Tutorials and quick tips:
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.