Drop-down lists in Excel

The drop-down list in Excel is a powerful tool that simplifies the process of choosing values ​​for the user. It is a technique widely used in which we can create lists that have the source data located on another sheet in Excel, which are hidden from the main sheet.

In this post we will look at two ways to create a drop-down list, first using data validation and then using the form control option.

1. Drop-down list with data validation

To create a drop down list using data validation, the first thing that we need to do is choose the cell where we want to place the drop-down list.

We want for example to create a drop down list containing the last 15 years, and the user can only choose one.

To do this we need to have a list of the years that we want to include in the drop-down list, in my case I have it in the range A2:A16.

We now go to: Data> Data Validation

Once we're inside the data validation dialog box, we have to do the following:


On the Settings tab, in the Allow box, click List. In the Source box, type an equal sign (=) followed by the range where we have the data to go to the drop-down list, in my case = $A$2:$A$16. Once done, click OK.

It is important to see that in this case the source data is located in the same sheet from the drop-down list, but this need not be the case as nobody likes to see that list of years in a dashboard or presentation. So let's see how we can place the source data on different sheet.

See below:

There we can see how the source data can be located on another sheet, in my case Book 1, and my drop-down list is on Book 2 in cell B1. We could even hide the sheet where the source data is located without affecting the drop-down list. This is a very common technique in advanced dashboards in order to simplify and show only what is truly important.

2. Drop-down list with form control

We have just seen how to create a drop-down list using data validation, but we can also create a drop-down list in Excel using the form control named combo box.

To do this we have to go to:

Developer> Insert> Form Controls - Combo Box (Form Control)

--------------------------------------------------------------------------------------------------------------------------

If the Developer tab is not activated (not showing basically), we need to activate it.

This is how:

1. Click the File tab.

2. Click Options.

3. Click Customize Ribbon.

4. Under Customize the Ribbon and under Main Tabs, select the Developer check box.

--------------------------------------------------------------------------------------------------------------------------

We now add it to our sheet in the desired location with the desired size. Now just right click on it and go to Format Control> Control tab where we will select the appropriate input range which is the data that will be displayed in the drop-down list (in my case the list of years). The selection will be linked to a cell that we choose which will show the relative position of the chosen value in the drop-down list with the rest of the input range. This is the cell that will be the feeder for the dashboard or any other table/report/formulae that we work with.

For example:

The input range is still in my Book 1 in the range A2:A16. The cell that links the form control is the cell B2. In the example, we see that 2005 is in the sixth position of the input range.


Well there it is, we now know how to add drop-down list to our files using 2 different techniques having the source data on the same sheet or another.


To download the example workbook click on this link:

 Drop-down lists