Data Validation in Excel




TumblrStumbleUponRedditLinkedInAIMDiggEvernoteWhatsAppMySpaceKindle It

Data Validation in Excel is a feature that allows you to control or restrict the type of data that you can enter in to a cell in a workbook. When used properly, data validation can help you prevent users from entering invalid values in an excel workbook. This reduces potential errors and can save you a lot of time.

You can download an excel workbook with various examples of data validation in excel using simple, named lists and dynamic named lists.

A simple data validation example in Excel

Select a cell where you want to restrict the entry of data and then simply go to the menu bar and choose ‘Data’ and then ‘Validation’. You can then choose the type of criteria to check against when the user enters a value in the cell. Using the options provided, you can restrict the entry to whole numbers, dates, strings and my personal favorite – a custom list.

In our example, we will take the case of a custom list. We want to restrict the entry of data in cell C9 to only a list of names. Well, choose ‘Data’ and then ‘Validation’ from the menu and enter a list of names in the Data Validation drop-down as shown in figure.

using-the-data-validation-error-alert-in-excel

You can then select the ‘Error Alert’ tab and enable the ‘Show error alert after invalid data is entered’ check box.

a-data-validation-error-alert-in-excel1

Now whenever the user tries the enter anything apart from the list of names (that you entered in the data validation box above), the system will show an error message to the user and prevent wrong data entry in that particular cell.

Data Validation in Excel using Named (drop-down) Lists

If you are not too familiar with what a named list is, I suggest that you learn about them in the article dealing with creating chart using Named Range in Excel. Once we know how to define static and dynamic named lists, we can use them to restrict entry of data in the cells we want. In our example, we have named lists defined for name, country and food. In our example, we want to show only the list of names to the user. But that’s not all, we also want to restrict the number of names he/she can see. So we create a dynamic named list using the OFFSET function which refers to another cell (which in this case is cell M2) for knowing how much of the data to show. In our example, we want to show the user only two names, so we simply enter 2 in cell M2. The OFFSET function then takes the first cell in the list of names (cell B3) and creates a range of cells between that cell and the one is offset from B3 by two rows (cell B4). The resulting range thus is cell B3:B4.

How to set-up data validation in Excel using Named Lists

data-validation-using-dynamic-named-list-in-excel

1. Lay out the data as shown in figure above.
2. Create a new named range called ‘dynamicnames1’ using ‘Insert’ -> ‘Name’ -> ‘Define’ from the menu bar.
3. Enter the formula ‘=OFFSET(validation!$B$3,0,0,validation!$M$2,1)’ as the criteria for the range in the form that appears.
4. Now select any cell and cell and click on the menu options – ‘Data’ and then ‘Validation’.
5. In the ‘Source’ enter the name of the above list as ‘=dynamicnames1′.

A quick tip – Ensure that whenever you create a named range, reference to any cell should preferably be absolute, ie. $M$2 rather than M2. This can prevent a lot of headache later if you add new columns and cells to the worksheet.

You can now go back to the excel worksheet and check the cell where you restricted the data entry using data validation. In the drop-down, you will find only two names appearing which means that the data validation is working.

In one of the other examples, using data validation and a named range, we not only decide how many values to show in the drop-down but also which of the pre-defined lists to show as drop-down. You can also use conditional formatting in excel along with data validation to achieve great results.

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. Jerry wrote:

    How to restrict cells in a column from entering wrong figures e.g. I have 31 cells in a column each cell should contain figure by entering it manually i.e. 62,010.00 but not 585*106.

    What i mean anyone can enter figures such as this 62,010.00 manually but if he entered 585*106 it will not be accepted and STOP— shows error. Can you help me how to this?

    Many thanks for your kind help and support.

    Regards

    Jerry

  2. Ajay wrote:

    Jerry,

    The simplest way to achieve that would be VBA. But if you want to do it using data validation (and assuming that’s possible), can you give me an example as to why would you need to have a validation like that? Is there a specific character like * or / that you would like to avoid or is it to avoid all non-numeric characters?

    Regards,

  3. lunksmoo wrote:

    In Excel 2003, how do you get it to format a cell from a drop down data validation list with more than 3 colors? I have a drop down list of 16 items, and have 16 different colors for each one. Cell formatting will not cross over to data validation list.

  4. Ajay wrote:

    lunksmoo,
    That’s a pretty interesting question. In Excel 2003, conditional formatting will support only 3 conditions. So if the list that you created using data validation has more than 3 values, you can try using a combination of conditional formatting and custom format you can use about 7 different colors for a mix of numbers and text (6 for numbers and 1 for text). The other solution (to go beyond 7) is to use the Worksheet.Change function in VBA using which you can change the color programatically when the user selects a value from the data validation list.
    Hope that helps. Let me know if you find a better alternative.

    Ajay

  5. Sharon wrote:

    Can you tell me how to use the scroll function in a dropdown data validation list?
    THank you!!!

  6. Craig Senior wrote:

    For Data Validation lists, you might find it easier to put all Named drop-down lists onto a separate sheet. I call that sheet Lookups.

    Put a list in a column. You ~could~ put lists in rows, but the visual queuing is more difficult and sorting becomes impossible.

    Assign a Name to the list of values. If you do not name the range, you cannot put it on a separate sheet. No need to use the Offset function – not sure why you did that, although it works. I haven’t found a need (yet) for controlling the number of items in a dropdown.

    Put separate lists below, not next to each other. That way, if you need to insert an item in a list, nothing else is affected.

    If two lists are actually related (for every one item in a list, there is a unique item in another list, you can combine the two lists into one table. Now that you have a table, you could use it to do VLookups.

    With a list created, say in range “NiceList”, to use it, in Excel 2003, from the menu, Data > Validation.
    Allow: List
    Data: =NiceList
    Click Ok

    That’s it.

    I have found a need, albeit rare, to vary the items in the drop-down list, based on another value. That can be accomplished with formulas in the cells in the Named List.

    Cheers!

  1. How to synchronize two or more pivot tables using VBA | da TaB is On wrote:

    [...] in an Excel worksheet. To synchronize two or more pivots we simply create a master drop-down using data validation. We then use this cell drop-down to choose between the pivot tables [...]

  2. How to Create a Dashboard in Excel | Excel & VBA - da Tab Is On wrote:

    [...] range when used with data validation is an amazing way of presenting and organizing data in an excel dashboard. You can read about data [...]

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

    [...] that we use for this purpose need not be a part of the pivot table – a simple drop-down using data validation or a combo box can also serve the purpose equally well. If you feel like going the extra step, feel [...]

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