Work like a Pro Series. Use the Drop down list function in excel.

The Drop-down list feature is a great way to ensure the data entered in a cell is "clean". In this "like a pro" article, we will show you a number of ways to create a drop-down list in an Excel cell. 

When to use Drop-Down list

You should use a drop-down list if you only want data from a pre-defined items list. Common usages include:

  • State
  • Month
  • Gender
  • Yes/No
  • List of staff names
  • List of products

Using "State" as an example, if the data field is a normal text field, a user may enter the state of Victoria as "Victoria", "VICTORIA", "VIC", "Vic" or many other possible variations.

Having a drop-down list will ensure the data entered is "clean", this is particularly important if you have a spreadsheet to be used by a lot of people.

Creating a Drop-Down using Comma-Separated Values

A drop-down list is created via the "Data Validation" function. You can locate this function under the "Data" menu:

Data image

We will use the following examples to show you different methods in creating the drop-down.

Do you own a car

Let's say, for question 1, the pre-defined response is either "Yes" or "No". To create a drop-down list in cell "B2":

Data Validation

1. Click on the "Data Validation" button
2. In the "Allow" validation criteria field, select "List"
3. In the "Source" field, enter "Yes,No"
4. Click Ok

The result in cell "B2" will be as follows:

Do you own a care yes or no

Creating a Drop-Down using a Range of Cells

The comma-separated values method is convenient if you have a small number of selections. If you are going to a number of drop-down lists, it is recommended to have a worksheet to store all the drop-down values. We will use the second question as an example to create a drop-down list using values from a range of cells.

For demonstration purposes, we will create the list of Australia's states in column D as follows:

Do you own a car state list

In this example, we would like to create a drop-down list in cell B4 using the values in range D3:D9.

The steps are:
1. click on the "Data Validation" button
2. In the "Allow" validation criteria field, select "List"
3. In the "Source" field, click on the Buttonbutton on the right hand side.
4. The Data Validation form will become like this:

Data Validation bar

5. Now, click on cell D3, hold the mouse button and drag the cursor to cell D9. This will select the range of cells to be displayed in the drop-down list.

State list

6. Press "Enter" and then OK in the Data Validation form.


Cell B4 will look like this:

Do you own a car yes drop down

Creating a Drop-Down using a Named Range

When you are going to have a few drop-down lists, you should consider using "Named" range to help you manage the lists.

Let's use question 3 as an example and type in a number of fruits in column E:

Fruits

The drop-down list that we want to create in cell B6 for question 3 is in range E3:E9.

Instead of referring to the list of fruits as E3:E9, we can associate that range with a name.

The steps to do that are:

1. Highlight the cells E3 to E9
2. Click on the "Formulas" menu, then "Name Manager"
3. Click on "New ..."
4. In the pop up windows, type in the name "Fruits" (you can choose any name that is easy for you to remember).

Fruits New name

5. The "Refers to:" will be pre-popular with the range of the selected cells. If you have forgotten to do step 1, you can use the Buttonbutton on the right hand side to select the range again.
6. Click "OK "and then "Close" to close the "Name Manager".
7. You should notice the "Name Box" (in the top left corner underneath the menu bar) will now display the name "Fruits" when the cells in that range are selected.

Fruits dropdown

8. Now, you can use the name "Fruits" in creating the drop-down list for cell B6

Data validation Fruits

9. This is how the drop-down will look like in cell B6:

Favourite fruit

And that's how it is done! We hope that you found this article useful and that you can apply this technique next time you create your Excel spreadsheet.

As usual, if you have any questions, please don't hesitate to ask via our comments section on this blog post.

Stay tuned for our regular updates.