Data Validation feature in Excel allows the user to create a drop down list in Excel. The drop down list created using data validation allows the user to pick and choose a single value from the entire list and thus prevents entry of invalid values. The drop down list can be created by typing in a set of values, using a range of cells or by writing a formula in the data validation option box.
Create a Drop Down List By Typing a List of Values
The simplest way to create a drop down list in Excel using data validation is to type in the values.
To create a drop down list, simply use the ‘Data’ -> ‘Validation’ menu option in Excel 2003 or click on the ‘Data’ tab on the ribbon and then ‘Data Validation’ in Excel 2007 and later. Once you have the data validation option box open, simply type in the values as shown in the image above. Once done, click Ok. You will see that the cell has a drop down which contains the values you had specified.
Create a Drop Down List By Using a Range of Cells in A Sheet
Another common use of the data validation feature is to create a drop down list using values from a range of cells. To use this option, while you are one the ‘Settings’ tab on the data validation options box, select a range of cells from any sheet in the workbook. You can choose any one dimensional range – cells in either a row or in a column. Excel will convert all the values to a drop down from which the user can choose a value.
Create a Drop Down List By Entering a Formula
While creating a drop down list using a range of cells will often suffice, in other cases, you may want to create a list using the OFFSET formula. The syntax for the Offset function in excel is:
OFFSET(range, rows, columns, height, width )
range is the cell (or the range) which will be used as the staring point for the offset. Often it is a single cell but can be a range as well.
rows is the number of rows by which to offset to the specified range above. Use a positive number to offset downwards and negative to offset upwards.
columns is the number of columns by which to offset to the range. Again, use a positive number to offset towards right and negative to offset towards left.
In most cases, the above three parameters should suffice. In most of these these cases the area under the range remains constant although it can be offset up or down and to the right or left. But in a situation where you would like the size of the range itself to change (contract or expand), you can use two more parameters – height and width:
height is the number of rows that you want the returned range to expand or contract by vertically. Use a positive number for expansion and a negative number for contraction.
width is the number of columns that you want the returned range to expand or contract by horizontally. Use a positive number for expansion and a negative number for contraction.
Now let’s create a list using the OFFSET function in the ‘Source’ input field in the data validation option box. Let us assume that a range of cells in ‘Sheet3’, staring from cell C1 and upto cell C5, contains the values that we would like to use in the drop down. Now we can write a formula like =OFFSET(Sheet3!$C$1,0,0,5,1) to generate the drop down. How did the formula work? If you look at the syntax of the formula, we simply took cell C1 and expanded it down 5 rows all the way upto cell C5. The resulting range, spanning cells C1:C5, is then used by Excel to generate a drop-down list.
Create a Drop Down Using A Named Range
A named range is a group of cells that have been assigned a specific name. This name can then be used in formulas and other places to refer to the range. For Example, say you name the range consisting of cells C1:C5 as MY_LIST. You can then write a formula like =COUNTIF(MY_LIST,”<>“) to get the count of non-blank cells in the range.
Now to use this named range to create a drop down, we simply specify the list as =MY_LIST and Excel will automatically pick up the cells referred to by the list.
Read more on Data Validation: Using Data Validation Feature in Excel