Categorized | data validation

Data Validation List in Excel – Create Drop Down List Using Data Validation in Excel




TumblrStumbleUponRedditLinkedInAIMDiggEvernoteWhatsAppMySpaceKindle It

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.

type-in-a-drop-down-list-data-validation

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.

a-simple-drop-down-list-in-excel

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.

drop-down-list-using-a-range-of-cells-data-validation

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.

drop-down-list-using-formula-data-validation

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

create-drop-down-using-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

TumblrStumbleUponRedditLinkedInAIMDiggEvernoteWhatsAppMySpaceKindle It

Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?


XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>


Comments and Trackbacks

  1. 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.

  1. Data Validation in Excel - How to Use Data Validation Feature in Excel | Excel & VBA - da Tab Is On wrote:

    [...] Read more on Data Validation: Using Data Validation to Create a Drop Down List [...]

  2. Pareto Chart Table - A More Meaningful Analysis (Download & Template) | Excel & VBA - da Tab Is On wrote:

    [...] 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 [...]

  3. Excel Dashboard using Pivot Table | Excel & VBA - da Tab Is On wrote:

    [...] going the extra step, feel free to insert a few charts as well. Read this article to know how to create drop down lists using data validation in [...]

Subscribe

Keep up with the latest stories - Delivered right to your inbox
feedburner

Translate

English flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagDutch flagHindi flagPolish flagSwedish flagFilipino flagHebrew flagIndonesian flagUkrainian flagThai flagTurkish flag
treeemap software for excel