Creating Drop Down lists in Excel – Data Validation

Why in survey’s we will see options are limited (majority of the cases) where in the user can choose his/ her option from the available list of options. The answer is simple and well known. An organized/ properly formatted data makes analysis much easier when compared to unformatted/ unorganized data. Else donkey work is need to make the data suit to our analysis requirements.

Let us see an example to understand it better.

The Problem

Imagine, you have your company Annual party and a survey form is sent out to employees regarding it. This includes option whether the employee is attending/ not and the food choice of the employees whether a vegetarian meal or non-vegetarian meal and other details like pick up and drop points etc. Now, the results (as shown below) are given to you to identify how many employees are attending /not attending / tentative to the party and the choice of meal.

Datavalidation

Now you need to manually check and count whether the person is attending/ not. For attending 3 different options are used (Y/ Yes/ Attending). Similar case is with the rest options. This consumes a lot of time if the employee count is big. How to we change the data into an organized format so that we can reduce the time.

Solution

If we have the options restricted to yes/ no/ tentative in Column B it makes us easy to do the analysis.

Now, the question is Do we have an option to limit the choices/ options to a particular set in Excel. The answer is YES.

The Data Validation feature provides you the option of the restricting the data entry in the cell to a specific set of predefined values by having the values in a Drop-Down in the same cell (also known as in cell dropdowns).

In this post, Let us see how to add in cell drop downs using data validation feature. The basic ways to create a drop down list are

  • With comma separated values.
  • With referring to a group of cells (where options are available) from the same sheet or different sheet and
  • Using named ranges

Creating a Drop Down with Comma separated values

  1. Select the cell or range of cells where you need a drop down.
  2. Go to the Data Tab (learn more about tabs in excel).
  3. Select Data Validation option from the Data Tools group. You can see a dialog box open on the excel sheet.
  4. In the Dialog box, there will be 3 tabs.
    1. Settings – To customize the settings needed from the validation criteria drop down.
    2. Input Message – To show the desired message upon selecting a cell.
    3. Error Alert – To display an error message upon invalid data entry.
  5. Go to the settings tab and select list option from drop down below allow option in the validation criteria.


  6. Upon selecting List option, you can see two options Ignore blank and In-Cell drop down with check boxes against each of them. These are checked by default and leave them as is. There will be a Source option available with a text bar below. The text bar is used to enter the values, range etc.
  7. In the text bar enter the options separated by a comma.


  8. Click on OK.
  9. Now you can see the cell/ range selected have the button with arrow pointing downwards. Clicking on it will show the options which are entered by you in the validation box.
  10. The selected Cell accepts only the values available in the drop down and gives an error if any other value is entered in the cell.

See the below image to understand the procedure of creating drop down lists in excel

Datavalidation

Creating a Drop Down with referring to a group of cells

There is no much change in the above procedure.

Except in step 7 instead of entering the options manually, select the range where the set of options are available. Rest of the steps remain the same.

Creating a Drop Down with Named Ranges

The procedure for creating the drop down remains the same.

Before going to create the drop downs, we need to define a name for the range of options and the same name needs to be given in the text bar instead of range or giving options directly.

See the below image to understand the procedure of creating drop down lists in excel referring to a group of cells/ named ranges.

DataValidation

Remove Data Validation from the cells

In case if you want the drop downs to be removed just select the cells/ range with data validation and click on the clear all button in the data validation window.

Download the Practice Workbook used in Example

Do share this post with your friends if you find it useful.


3 Replies to “Creating Drop Down lists in Excel – Data Validation”

  1. Jitendra Sahu says: Reply

    Keep it up Vamsi. Really helpful…

    1. Thanks Jiten.

      Glad that the content is of some help for u

  2. […] cell C10, I have a dropdown to choose the product I want to look data for. (Read: How to Create Dropdowns in […]

Leave a Reply