This article aims at describing the useful drop-down lists in Excel.
How to create a drop-down list ?
You can create drop-down lists from the Data validation menu, located in the Data tab :
When choosing the source of your drop-down list, you can also use a named range :
You must have noticed just above that I am not using the mouse. Remember our first tip of the month about Excel shortcuts !
Why using drop-down lists ?
1. It is safer
One of the trickiest things in excel is the unpredictable behavior of the end user. With drop-down lists, you restrict the possible values of the cell and thus reduce the risk of spelling mistakes. In other words, your data entry is easier and safer.
2. It is more interactive
Drop-down lists are very easy to use. They increase the interaction between the user and the spreadsheet. Take a look at the example below :
You can download here the sample worbook.
Good to know
- Drop-down lists only represent one part of the Excel data validation menu. This feature also allows you to restrict the possible values of a cell to whole numbers or dates for example.
- The sample workbook is using Index/Match functions to make the graph above dynamic. If you don’t feel comfortable with this combination, you can have a look at this article.
- You can create dynamic drop-down lists (e.g with a dynamic source). We will see how in the next article.