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.
You can then select the ‘Error Alert’ tab and enable the ‘Show error alert after invalid data is entered’ check box.
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
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.
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
August 17th, 2009 at 12:59 amJerry,
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,
August 18th, 2009 at 3:58 amIn 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.
October 7th, 2009 at 4:57 pmlunksmoo,
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
October 7th, 2009 at 5:27 pmCan you tell me how to use the scroll function in a dropdown data validation list?
April 15th, 2010 at 11:58 amTHank you!!!
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!
April 7th, 2011 at 8:06 am