This article explains about creating and using drop down list in excel using 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. If not you need put in a lot of efforts and manual work to make the data suit to your analysis requirements.
Let us see an example to understand it better.
The Challenge
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. The food choice of the employee whether a vegetarian meal or non-vegetarian meal. Other details like pick up and drop points etc. Now, you have to identify from the results (in the below image) how many employees are attending /not attending / tentative to the party and the choice of meal.
Now you need to manually check and count whether the person is attending/ not. You can see 3 different options (Y/ Yes/ Attending) for attendance. Similar case is with the rest options. This consumes a lot of time if the employee count is big. How can we change the data into an organized format so that we can reduce the time for formatting the data.
Solution
Analysis will be easy, If we have the options restricted to yes/ no/ tentative for attendance (in Column B). Similarly a Vegetarian or Non-Vegetarian in Meal choice (Column C).
However, the question is Do we have an option to limit the choices/ options to a particular set in Excel. The answer is YES.
Data Validation option to Create a Drop Down List in Excel
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).
Let us see how to add in cell drop down list using data validation feature.
Different Ways to Create a Drop Down List in Excel
We can create a Drop down list in excel in 3 different ways.
- With Comma Separated values.
- Referring to a group of cells/ range from the same sheet or a different sheet.
- Using Named Ranges.
Create a Dropdown with Comma Separated Values
- Select the cell or range of cells where you need a drop down.
- Go to the Data Tab
- Select Data Validation option from the Data Tools group. You can see a dialog box open on the excel sheet.
- In the Dialog box, there will be 3 tabs.
- Settings – To customize the settings needed from the validation criteria drop down.
- Input Message – To show the desired message upon selecting a cell.
- Error Alert – To display an error message upon invalid data entry.
- Go to the settings tab and select list option from dropdown below allow option in the validation criteria.
- Upon selecting List option, you can see two options Ignore blank and In-Cell dropdown 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. Enter the values or range in the text box.
- Click on OK
- Now you can see the cell/ range having the button with arrow pointing downwards. You will be able to see the options upon expanding the dropdown.
- The cell with dropdown accepts only the values available in the drop down and gives an error if you try to enter any other value.
See the below image to understand the procedure of creating dropdown lists in excel
Creating a drop down referring to a group of cells or a range
The process to create the drop down remains the same as seen above. However, the only change is instead of entering options manually, select the range where the set of options are available.
Creating a drop down list in excel with Named Ranges
In order to create drop down list using named ranges you need to define or create a named range. Instead of typing the options manually or using a range you can directly give the name in the text bar in data validation window.
See the below image to understand the procedure of creating dropdown lists in excel referring to a group of cells/ named ranges. A Named Range with name “Options“ is created and the same is used to create the drop down list in column D.
Remove Drop Down List in Excel
In case if you want to remove the drop downs to be just select the cells/ range with data validation. Click on the clear all button in the data validation window. You can see the cells without drop down list now.
Do You use the Drop Down Lists in excel frequently. Share in the comments.
Do Not Forget to Share this article with your Friends !!!