Make your data entry easier with Drop down list

Drop down list in Excel allows you to restrict the data entry in any cell and at the same time, it also makes the data entry easier. In this tutorial, we will learn how to create drop down list, add items, remove items and where it can be used for productivity.

Drop down lists are widely used in excel to define a set of values for any cell, thereby letting the user select a value from a set of pre-defined values. These pre-defined values can be taken from anywhere else in the worksheet or workbook. You can easily limit the number of entries and prevent invalid data. Once you create a drop down list in any cell, an arrow is displayed. Any entry can be selected from a set which appears after clicking on this arrow.

Inserting a drop down list

Open a new excel workbook and select cell B2. We want a list to appear here from which one can select one fruit from a set of four. For excel 2003, go to data menu and select data validation. For excel 2007 and 2010, go to data tab and click on data validation from data tools group.

data-tools-group

The data validation dialog box is displayed. In the settings tab, you will find ‘allow’ drop down list; click on list in this menu.

Once you select the list, source text box appears. Write “Apple, Mango, Banana, Grapes”. Let the “ignore blank” and “in- cell dropdown” check boxes remain selected. Click on OK and you are done!

source-selection

fruit-list

Now you will see a little arrow appearing just right side of the cell B2. Click on this arrow and you are presented with the fruit list which you just entered. Try to enter some value other than list items. You get an error message and the entry you made is not accepted.

Source from a list in the worksheet

The above method to create a drop down list is the simplest and good if you have a small number of items. But if you have a huge list to select from, it will not be an efficient one to type all the data manually in the source box.

Let’s say you have a list of 20 numbers in the worksheet (from cell G1 to G20). You want to make a list of these twenty numbers in cell E1. Here, we will repeat the same steps as in above example till we get the source text box. Once the source text box appears, click inside it and then select the range which you want to include in the drop down menu. Optionally, you can type the reference of this range. Click OK.

list-same-sheet

The maximum number of entries that is possible in the drop down list is 32767. Well, that’s quite a huge number!

Source in another worksheet

The drawback using the previous method is that the data is in the same worksheet and is visible unnecessarily. It would be great if the source data is kept in another worksheet. It will also give us the flexibility to hide or protect this worksheet from unauthorized changes.

There are few extra steps in this case. You cannot just select the data source in another sheet. First we need to define the ‘name’ for this data list. Let’s create a drop down list in sheet2 from the same data in sheet1. Follow these steps:

  1. Select the range for list in the worksheet (in our case it is G1:G20)
  2. For excel 2003, go to insert – name – define. For excel 2007 and above, click inside the Name box which is located just at the left end of formula bar.

Name-box

Put a name (say validnumbers) and click enter.

Great! We have just defined the name of the range. Now go to another sheet (sheet2) and select cell E1. Repeat the steps for creating a drop down list till you get the source text box. In the source text box, write the name of the range followed by an “=” (equal sign). If you missed the equal sign (=), it will not work. For our example, type “=validnumbers”. Click on OK and you are done!

Advanced options

There are a few options which you can use depending on your requirements.

Input Message

You can optionally display a message when the cell is clicked. This can be handy when you wish to give some instruction or hint at the time of selection. For displaying the input message, follow these steps.

  1. Select the cell with the drop down list.
  2. Go to Data – Data Validation in Excel 2003 or click on Data Validation from Data tab in Excel 2007 or above.
  3. In the Data Validation dialog box, go to “input message” tab.
  4. The “show input message when the cell is selected” checkbox is already selected. Keep it as it is.
  5. Now type the input message title and message.

input-message-setting

 

Click on OK.

input-message-display

Allowing other entries

Sometimes, you want to allow the entries other than which are already available in the list. For example, you want the user to select his city. You have listed down the important cities for the convenience of entry but the list is not exhaustive. In that case, you would allow the entry of items which are not already in the list. That can be easily achieved with the following steps:

  1. Select the cell with the drop down list.
  2. Open the data validation dialog box and go to “Error Alert” tab.
  3. By default “Show error alert after invalid data is entered” checkbox is selected.

Clear this checkbox and click OK.

allow-other-entries

Now, apart from the existing list, you can also make other entries. In those cases, it is recommended that you also display an input message for clear understanding.

You can also display a warning message when someone tries to make entry which is not in the list. In this case, excel will allow the entry after showing the warning message. We will learn this in the next paragraph.

Customizing the error message

You have the option to customize the error message displayed at the time of invalid entry. By default, excel will display a message “The value you entered is not valid. A user has restricted values that can be entered into this cell” and will prevent the data entry. You have the option of both customization of the message and change the default behavior.

  1. Open the data validation dialog box and go to “Error Alert” tab.
  2. In the “style” dropdown list, you have three options – Stop, Warning and Information. If you select “Stop” (by default), excel will prevent the invalid entry. But if you select “Warning” or “Information”, a message will be displayed but the entry will be allowed.

You can write a custom title and message and click OK.

warning-message

warning-message-display

Using in combination of other functions

The drop down list can be combined with other functions of excel to make it more powerful and do some calculation based on the input value. Typically, you may like to use a nested if function to do some calculation based on the selected value.

Let’s understand it with an example. Suppose in a theater, the cost of the ticket depends on the choice of food served. The user can select from “beverage only”, “vegetarian” and “non-vegetarian”. We can use nested if function to calculate the ticket cost. See the screenshot below:

dropdown-with-if

Here in cell C3, we have used the following nested if function.

=IF(C2<>”Beverage only”, IF(C2=”Vegetarian”, 120, 125), 100)

Tips

  1. While, you can use a nested if function where the selection list is small. For a big list, it will be wise to use VLOOKUP function instead.
  2. You should adjust the width of the cell with drop down list. If it is less, the entry with larger text will be shown truncated.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>