Creating drop-down lists in Excel with just two steps

If you’re the average Excel user, you’re probably wasting tons of minutes [hours] every week to get your work done. That’s because many processes can be sped up and made safer — if you’re familiar with advanced Excel features.

One of those advanced features I’m gonna show you in this tutorial: Drop-down lists.

Why drop-down lists are so useful

Suppose you are organising a workshop and you want to poll attendance. You create a simple spreadsheet where people can enter their name, department and whether they want to join or not.

Without having some sort of mechanisms to ensure the correctness of data, your sheet will become messy soon. Then, using the data further for sorting or filtering will mean you have to fix typos like the following first:

typo in an Excel cell

Manual entry of general data like department is bad for two reasons: First people have to do a lot more typing (‘A-c-c-o-u-n-t-i-n-g’ has 10 characters). Why not have people select their department with two clicks? The other downside of manual entry is there’s a high probability of data entry mistakes.

A drop-down list can be very helpful in such cases.

Now it’s your turn.

Learn how to set up a drop-down list yourself.

Tutorial: Create a drop-down list in Excel

Here’s the template we are going to create. Both column C and D use drop-downs. Column D in addition uses conditional formatting to set the color based on the entry.

image of dropdown list in Excel

Read on to see how I did this.

Step 1: Create a new tab for selection values

The selection values have to be stored somewhere in your Excel.  You could store them in your main sheet, but I recommend to create a separate tab so nobody else can mess around with them.

Create new tab departments and enter possible values:

a value list must be created in a separate tab
Add as many entries to your option-list as you want

Step 2: Enable data validation for cells

Now we have to modify the cell such that it accepts list entries. For this, select the cell you want to format and choose Data Validation from the menu:

enable data validation for cell
Enable data validation to pull list values

In the next dialog window you specify how Excel should validate cell values. Because we want the cell to accept certain list values only, you must choose List. Excel of course needs to know what list to pull the values from. This is called data source.

To select the department list as data source, click on the right button:

select source for list

Now open the departments tab and select the entire column:

go to other tab and select column

Then press Enter to get back to the following screen. This is what you should see. If it looks different you’ve made a mistake.

data source now selected

Done! You have just created your first drop-down list in Excel. You can now easily pick an entry from the department list.

Make sure you copy the cell content to adjacent cells below in order to turn them into drop-downs.

dropdown list in Excel now active

By the way, you can also hide the options sheet so people can’t change it:

click to hide sheet

Finito – this is the end of the first tutorial.

Next I’m gonna show you how to use conditional formatting for list option entries.

Bonus: Using conditional formatting for option lists

You can make your spreadsheet even prettier by color-coding different entries.

For this example I’m first creating an options list with three statuses. This is where colleagues can enter their workshop attendance.

Step 1: New create a new list with statuses

Enter the options people can choose from:

Repeat the same steps as above to set a up a selection list with the above entries.

Step 2: Enable conditional formatting

For this step, you need to have the drop-down list ready and the cells modified accordingly (see image). To enable conditional formatting with different color schemes, select the first cell and choose New Rule … from the menu.

Choose the classic style and set up a rule of the following kind:

Format only cells that contain a cell value equal to ‘will join’

format a cell

To choose the actual color style, go to Custom Format…

custom cell formatting

We want the values for ‘will join’ to appear with green background and white font color:

Close the dialog and repeat the step for each of the possible values:

  • will join: green background + white font
  • maybe: amber background + black font
  • can’t join: red background + white font

You can always modify your rules by going to Manage Rules…

manage rules

Manage rules will show you the conditional formatting rules you’ve set up. To make changes to an existing rule, click on Edit Rule… at the bottom left.

conditional formatting rules view

Here’s the final result. How do you like it?

image of dropdown list in Excel

Was this helpful? Let me know

You’ve just learned how to create drop-down lists. Was this helpful? Then I’d love to hear from you. Just leave a comment below. Your feedback helps me understand what kind of material you like. I’ll definitely publish more articles about Excel on the blog. So stay tuned.

Leave a Comment