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.

  2. metamagical wrote:

    A couple of points on how the data validation drop down menus work:

    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.

  3. Asics Gel Lyte 3 wrote:

    It says all three had quite negative comments about the crown prince and two of them – while asserting that the crown prince will become king – implied that the country would be &quot,Michael Kors Watch;better off if other arrangements could be made”.

  4. Nike Air Max 2012 wrote:

    and two other men. is accused of assisting boyfriend Pierre Lewis, a correspondent with the national newspaper, Also in the convoy was a large group of journalists.18 March 2013Last updated at 00:43 GMT Iraq: The spies who fooled the world By Peter TaylorBBC News The lies of two Iraqi spies were central to the claim – at the heart of the UK and US decision to go to war in Iraq – that Saddam Hussein had weapons of mass destructionIn those early days.

  5. Register wrote:

  6. Nike Air Max Excellerate wrote:

    But we as doctors need to up our game,Michael Kors Outlet.

  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