Excel user are familiar with drop down lists from the time Microsoft used it in their Office Program. The drop down lists were helpful as they are we able to a lot of options, were cascade-able. The value of these dropdowns was multiplied as the options were available through shortcut keys.
The Drop Downs, though gone from Excel and Office (Replaced by tabs now) are still available in the form of Lists. – they are here to perform data validation in excel.
In today’s post, we will examine how we can apply data validation, different types of data validation and their pros and cones.
A little bit about Data Validation
Data Validation is used so that the user inputs only required type of data. For example if a field requires you to enter your name, the input should be a text, if it requires you to put date of birth, the field should accept only a date.
This in turn makes it possible to make your data more accurate and reliable.
The Drop down List Through Data Validation Menu:
By far, the most useful option of data validation is a list. A list allows users to select from a given set of values and make an input. For example while answering multiple choice question in exam, a student select out of three choices produced by a list or while selecting gender, a user can select out of a boy or a girl.
Setting up List from Data Validation menu:
While setting up drop down we can either refer to the range of cell or use data within the options provided by the Data Validation dialogue box – let’s understand them one by one:
Lists produced from range of cells:
- As a first step, we need to enter the data in a range of cells, say we want a list in cell A1, and we have range C1:C3 to input the option.
- After entering the options, select the cell you want to have drop down list.
- Go to Data Tab > Data Validation > Lists and then select the range C1:C3.
- Press Ok to Exit.
…and you have your data validation list.
Select “Ignore Blanks” to have empty options in the drop down list.
Lists produced without range of cells:
We can directly enter our options in to the space provided in the dialogue box. For the last example we have seen, we could have entered them directly into the space like this: A, B, C.
This is only a practical option when you have small number of options.
Other Options for Data Validation by Drop Down Menu:
This is perhaps the most common and the most useful method of Data Validation. The Data Validation by accessing “Data” Tab and pointing to “Data Validation”, we can apply this feature.
The data validation menu provides three options:
- It has option to add data validation to a cell.
- We can circle the invalid data
- And lastly the circles around the data can be removed.
Drop Down Lists with Form Controls Menus:
The other option to have drop down list is to have a List from Form Control Menu. The options for Form control is visible only when the user has enabled the “Developers Tab”. In Order to enable the developer tab:
- Go to Files > Options
- Choose Customize Ribbon
- Select Developers Tab and Check mark.
- And you will have the Developers Tab visible in the Ribbon.
Once the menu is visible you can select the option to a Combo Box and place the combo box at any desired position. In order to set the options within this combo box, you need to right click the combo box to reach the following dialogue box:
Formatting Combo box
The control tab is used to set the input range and the Link Cell. The Link Cell is the cell that displays the item number when a user select an item from the list, for example if he selects the first option, the cell will display 1.
In the screenshot, the option selected in C which is the third option, hence cell B1 is displaying 3. This number can be used in further calculations. Properties Tab can be used to manage the behavior of the combo box with rest of the sheet i.e. either it has be resized or not with cells.
Protection and size of a combo box describes if it is protected or not and details about the size of the combo box.
The final output of a list with combo box looks like:
We just learned how to use the two different options available for creating the drop down lists. The first one was an easy to use method, where the second one requires a little bit more knowledge and configuration of Excel. We hope you will like the tutorial. Please download the file from this link.