Drop-down lists (★)

Drop-down lists (★)

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 :

Drop6

 

When choosing the source of your drop-down list, you can also use a named range :

Drop8

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 :

Drop9

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.
Advertisements

3 thoughts on “Drop-down lists (★)

  1. Awesome post! You are right, interactive features make a great spreadsheet. This makes your report more dynamic and flexible to the users’ needs. It also makes it more fun 🙂 I love the visual effects of drop-downs on a graph.

    Liked by 1 person

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s