TABLE OF CONTENTS
What is a drop-down list?
Drop-down lists, also known as validation lists, are one of the most useful - yet little used - Excel features that can help you make clean and intuitive spreadsheets.
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 365 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 provides users with an easy interface to understand 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 not familiar with your file to understand what you expect them to enter 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.
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.
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. No more typo entering data manually. 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 drop-down validation list in Excel
Summary
You can create a drop-down list is Excel by following the steps below:
Select the cell or range where you would like to add the drop-down menu.
From the Excel ribbon, go to the Data tab, and under the Data Validation menu, click Data Validation.
A dialog box opens. Select List in the Allow menu.
Under Source, enter values manually (with separator) or select a range containing the values that you will allow in the drop-down menu.
You can look at the video below tutorial to see it in action:
Adding a drop-down list to one or multiple cells at once
You can actually add drop-down menus to not just one, but multiple cells at the same time. 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
How to create a simple drop-down list, containing its own restricted values
When creating a data validation list, it's sometime simpler and easier to hard-code the available choices.
Select your range and go to the Data tab, and then Data Validation.
Select List in the Allow menu.
Under Source, manually 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.
If you try to insert a forbidden value, such as "Maybe", the error message below will show up:
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. This is also a way to keep your file simple and to hide the available choices so that users don't modify them.
However this method requires you to type each value manually, which can be cumbersome and time-consuming, especially if you want to allow many different options in the validation list.
To remedy to this, you need to create a drop-down list that is linked to an Excel range.
How to create a drop-down list linked to an Excel range
If you don't want to type manually each item in your validation 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).
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.
There are multiple advantages to using a range as source instead of hard-coded values:
It saves you the time of typing each item in the Data Validation dialog box.
It also allows you to easily change the choices available in the menu, by simply editing the range.
It also allows you to make your list more dynamic, using formulas in the source range to change the choices available, or even nest validation menus. 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.
When you have multiple data validation menus using different tanges, a good practice could be to keep all the list ranges in a separate tab you use as referential. This will make it easier to edit your lists afterwards, while preventing users from modifying the menus by mistake.
However there is still one limit to these menus 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!
How to make a drop-down list source of variable-size with 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).
Alternativly, instead of creating a Table you can use a named range 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 either an array function or the OFFSET function to define a variable-sized range.
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 a 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.
You can then define a custom message for the user to double-check the data entry.
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:
How to remove an Excel drop-down list
You can remove a drop-down list to allow any value to be entered by the user. To remove a validation menu in Excel, follow these steps:
Go to the Data tab.
Under the Data validation menu, click Data Validation.
In the Allow menu, change the value from List to Any value.
Click OK to validate.
How to add a drop-down list to a chart to make it dynamic
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].
Want to have an example? Download the sample file from the link below:
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 to us.
If you are an heavy user of Microsoft Office 365, you will most certainly like the Power-user add-in. Get dozens of new advanced features that will make you the boss of Excel and PowerPoint!