What is a drop-down list?
Drop-down lists are one of the most useful - yet little used - Excel features that can help you make clean and intuitive spreadsheets with Microsoft Excel.
When you click a cell that is linked to a drop-down list, you can see a small arrow button on the right. Click it and the list expands, showing the available choices listed as shown below:
If you click any item in the drop-down list, the cell will take the value of the item. In the example above, click "Western Europe" in the drop-down list and the currently selected cell ("B5") will be filled with a "Western Europe" value.
Why you should create drop-down lists in Microsoft Excel
Using drop-down lists in Excel has many benefits. Such lists help keep your workbooks clean and make users more efficient. There are typically 3 main reasons to use drop-down lists in Excel.
1) Drop-down lists tell users what input is expected from them
While it may seem obivous for you when you create an Excel spreadsheet, it is certainly not obvious for other users what you expect them to type as input. With a drop-down list, they will see a limited number of choices and won't need to guess what is expected from them.
2) Drop-down lists prevent spelling mistakes
By limitating the user's input to a pre-defined list of possible values, you make sure that their input will match the desired spelling or format. Your spreadsheet will be clean, with homogeneous data and correct spelling.
This is all the more important if you have formulas that depend on the values in the cell! In that case you can't afford to have a small spelling error (like an extra blank) that makes all your formulas return errors.
If you have formula depending on text, use drop-down lists to make sure the spelling is correct
3) Drop-down lists allow users to type inputs faster
You may have experienced this when filling forms on the Internet for instance. Typing text is way longer that if you just have to select within a pre-defined list of choices. By using drop-down lists in your Excel workbooks, you will make it easier and faster for users to interact with your spreadsheets and type data. That's not very significant if the file is not used often and if you need a limited number of user inputs. But if you need several user inputs, it can very well save minutes for each user entry.
How to create a simple or dynamic drop-down lists
Drop-down list with very few items
First, select the range in which you would like to add drop-down lists. You can select:
A single cell or range
A combination of non-contiguous cells or range: keep the "Ctrl" key pressed and click each cell or range you want to add to the selection
Entire rows or columns: in that case each cell of the row/column will be linked to the drop-down list
Second, in the Excel ribbon, go to the "Data" tab, and under "Data Validation", click "Data Validation".
A dialog box opens. Select "List" in the... drop-down list. And in the "Source" field, type each item that will populate your list, separated by the Excel separator (comma or semicolumn depending on your local settings).
Click "OK" to validate.
That's it! You now have your drop-down lists created in the selected cells. Users can now click the small arrow on the right and click on a list's item instead of typing the full text.
This simple drop-down list has the benefit of being very easy to create. It also doesn't require to "pollute" your spreadsheets with other ranges containing the list items.
However it requires you to type each value manually, which can be cumbersome and time-consuming. To remedy to this, you need to create a drop-down list that is linked to an Excel range.
Drop-down list linked to an Excel range
If you don't want to type manually each item in your list, you need to link it to an Excel range.
Proceed as above, but instead of typing values in the "Source" field, select a range in your spreadsheet (or in any workbook, for that matter).
There are multiple advantages to this:
It saves you the time of typing each item.
It also allows you to make your list more dynamic, using formulas in the source range to change the values. For instance, you can callect a Yes/no input in a drop-down list, and then another drop-down list will display only the items that make sense given the previous user input.
However there is still one limit that can bother you on some occasions. Adding new items to the list requires you to change the source range. This can be quite annoying if you have to do it often. What you should want is the list to dynamically update when new items are added under your source range, right? Hell yeah, let's do it using a variable-size named range!
Drop-down list linked to a variable-size named range
Making a list dynamic will save you the pain of updating the data source. I'm assuming you already know about named ranges. If this is not the case, you MUST have a look at this post that will tell you all about it.
When selecting the data source for your drop-down list, you can press F3 and select a named range as data source. So if your named range has a variable size, so will your data source. And there are basically 2 ways to make a variable-sized range name.
The easiest and most efficient way is to create a table. First select the range with your current list of items. Then go to the "Insert" tab and click "Table" (you can also use Ctrl+T).
Then you can simply create the drop-down list, and in the data source, press "F3". The list of named ranges in your spreadsheet will appear and you can select the named range as source for your drop-down list.
You now have a dynamic data source that will update automatically when new values are added to or removed from the table.
An alternative - but less elegant solution - to using an Excel Table, is to leverage the OFFSET function. You can define the variable-sized range like this:
Basically, this formula will return a range that depends on the number of rows in column B. Just like with the Table, use it as data source for your drop-down list, and it will update automatically when new values are added in column B.
Customizing options for your drop-down list
Showing a message with indications when the user clicks a cell with a drop-down list
You can create a short information notice that will appear when a user clicks a cell that has a drop-down list. This is useful when you want to give some additional information to the user about how he should provide inputs for instance.
Go again to the "Data" tab, click "Data validation" and go to the "Input Message" tab. Make sure the checkbox "Show input message when cell is selected" is ticked. Then type a Title and Message, click "OK" and the input message will appear when the dropd-down cell is selected, like in the example below:
Allowing the user to add values that are not listed
Sometimes you want the drop-down list to enable rapid typing, but you don't want to limit the user to a pre-defined list of items.
In that case, go in the "Error alert" tab and un-tick the "Show error alert after invalid data is entered" checkbox. This will make the list non-exclusive, allowing users to type values that are not in your list.
Controling the type of data, not the value itself
Sometimes what you want is not to limit the number of items for the user to choose, but simply to control the type of data. Typically, allowing only dates, numbers or text as input.
To do so, instead of "List", choose the data type of your choice in the "Allow" list as shown below:
Removing a drop-down list
You can remove a drop-down list to allow any value to be entered by the user. To do so:
Select the cell
Go to the "Data" tab, and click "Data validation"
In the "Allow" list, select "Any value"
Adding a drop-down list to make a chart dynamic? No problem!
It makes quite an impression when you deliver an Excel spreadsheet that allows the user to select in a drop-down list the series that should be displayed in a chart. For instance, the user can choose Product A, Product B or Product C in the list and the chart will automatically update to show the sales trend for that specific product.
To do so, all you need to do is use, as a data source for the chart, a range that will change when the cell with the drop-down list takes a different value.
In this example, the chart adjusts to show the values for the product selected by the user in the drop-down list.
This result can be achieved by using, as data source for the chart, the values in column F. And column F contains the following formula:
=OFFSET([@Date];;MATCH($J$2;Table2[[#Headers];[Product A]:[Product C]];0))
What it does: it starts from the "Date" column in the Table, and go 2 columns to the right to take the values for Product B. The MATCH function returns 2 because "Product B" (in cell "J2") is the value of the 2nd cell in the range [Product A]:[Product C].
>>> You can download the file with the example above here.
For more detailed explanations on dynamic charts, check this dedicated post here.
You should by now be a master of Excel drop-down lists. If you have questions or want to share some ideas, feel free to reach out in the comments section.