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.

Comments and Trackbacks

Raj wrote:

gr8 article………..
I’m using the first technique “Create a Drop Down List By Typing a List of Values” programtically(c#) and is working fine….
I have one doubt…… I need to add an item as “blank/empty” along with the list of values. Even though the source contains blank, cell displays only the values and not able to blank which i’m trying to add as first element.
I have even unchecked the “Ignore blank” also.
Is there any other option so that i can add empty item as the first item in the list using First technique.

If you a cell that already contains one of the options from the drop down then this value will be hilighted when you click on the menu.

- You’ll may want to leave a blank row to give users the option of returning the drop down to empty. If you’re using cells as a reference for the options in the drop down then put a blank row at the top of the range. If you were to add it to the bottom then the drop down will start with this hilighted and require you to scroll up.

for yes/no drop downs you can have a blank field between the two so that you scroll up for yes and down for no. I find this difference in behaviour between the two options can be helpful.

Similarly you can lay out +2, +1, 0, -1, -2 style lists with a blank cell either above or below the zero so that you scroll up for +ve value selections and down for -ve values.

If you have a long list then you can help reduce scrolling by adding a kind of basic search function. This can be achieved by putting the references into alphabetical order and adding grouping cells to for eg. ‘A’, ‘apple’, ‘ant’ ,’B’ ,’C’. (or even ‘A’,'aa’, ‘ab’, ‘ac’ if the data justifies it). When entering data type c into the cell, then click the drop down and it will start at c without the need to scroll down.

[...] the current state of the resolution – whether the issue has been resolved or not. We can use data validation to prepare a drop-down of the various types of issues. In this case, I’ve used the [...]