Drop-down lists: a useful addition to your workbooks
Adding drop-down lists to your Excel sheets offers several benefits:
It provides an easy interface for people not familiar with your file. For instance, you can allow users to test different scenarios by simply selecting a scenario from your drop-down list. This is intuitive and helps create the positive user experience that is needed for your workbooks to be used by others.
It allows you to control the exact spelling of a cell, to avoid formula errors.
Creating a simple drop-down list
To create a drop-down list in Excel, select the one or multiple cells where you would like the list to be created. Go to the "Data" tab and click "Data validation". Then in the dialog box that opens, choose "Allow: List". In "source", select the range for the data you want to allow. If you want to list months in the drop-down list, this is where you will refer to a list of months somewhere else in your workbook (see example below). A good practice could be to have all your lists in a "Referential" tab.
Now if you go back to the cells you had selected initially, you should see the drop-down as in the example below. You can use the little arrow on the right side to display the list, then clicking on a value in the list will replace the value of your cell by the value you clicked, without having to type it manually.
If you try to insert a forbidden value, such as "Januar" instead of "January", the message below will show up:
Removing a drop-down list
If you want to remove the drop-down list, you can simply go back to "Data validation" in the "Data" tab, and change the "Allow" field back from "List" to "Any value".
Can I create drop-down list referring to a variable range?
Yes! You can use formulas in the "Source" field to create drop-down lists with conditions, or referring to a variable size range.
Example 1: create a drop-down list based on the value chosen in another column. Suppose you have a column "Market" with 2 different markets, and a column "Product". You would like to create a drop-down list that would list in the "Product" column the different possible products for the chose market. In the "Source" field, use this formula:
=IF(A1="Market 1", Products_market1, Products_market2)
Where Products_market1 and Products_market2 are named range for the list of products available on each market.
Example 2: create a drop-down list referring to a table with a variable number of rows. From the example above, imagine you have listed your products in a table (called "Table1"). New products can be added so you would like the list to adjust to the number of rows in your table. You will need to use the INDIRECT function to refer to the table and the corresponding column, such as in the formula below:
Doing this, you can create drop-down lists with a variable size that will adjust automatically!