Validate / Validation

Validation scope

The following concepts are the key to validation usage.
The scope of the cells to be tested for validation is determined by the selection area when you create the validation, and all validation testing formulas created are based on the formula you enter which is relative to the activecell when you create the validation -- watch your absolute and relative references relative to the activecell. 

Filters, Validation, and Conditional Formatting are very similar to one another.  The purpose of the formula is test a condition and return either True or False, and act upon it if the condition is True.

Validate on Data menu

Your choices are:  Any value, whole number, decimal, list, date, time, text length, custom.

Most of those choices are obvious.  List is shown below.  Custom means using a formula and some examples are shown below under Validation Formulas.

For the unfortunate users of Excel 2007, you will find Validation in the Ribbon under Data then by Data Tools.  Or as Microsoft would say "Data validation options are located in the Data Tools group.".  Of course you practically have to start from there to get to Help, which is downloaded and not on your system. Nothing hurts like doing away with menus and in application help.  You can't read HELP like a book anymore, and search brings up articles not the main topics in Table of Contents.

Validate on Data menu - Validation list

You can place specific values in a named range and use that list for acceptable values. 
    Data, Validation, Settings Tab, Allow: List, Source: =namedrange, OK
    (be sure to include that = sign in front of your named range)

Validate on Data menu - Validation formulas

When doing Data Validation you select a range, the formulas will be applied to the entire range based on the formula in the active cell, which would normally be the first cell in the range.  A1 will be used for these examples.

Data, Validation, custom, formula is:

• Basic entries
  Any value - range, whole number - range, Decimal- range, List, Date - range, Time -range, Text length - range
• Disallow spaces     Dave Peterson, misc, 2001-04-28
  =LEN(A1)=LEN(SUBSTITUTE(A1," ",""))
  =ISERROR(FIND(" ",A1))
• Disallow leading or trailing spaces, also disallows consecutive internal spaces
  =LEN(A1)=LEN(TRIM(A1))
• Disallow ONLY CAPS
  =NOT(EXACT(A1,UPPER(A1)))
• Allow only those found in a list
  =NOT(ISERROR(MATCH(C1,$A$1:$A$5,0)))
• Allow only the negative amount of another cell
  =ROUND(A1-A2,2)
• Allow only specific text choices
  =VLOOKUP(CityGiven,StateFind,2)      -- [more detail]
    warning: "City not listed in table. Update the table and then re-enter the city on this sheet"
• Either of two choices
  =OR(A1=1,A1=2)
• values 1 through 500, or 700 through 799
  =OR(AND(A1>=1,A1<=500),AND(A1>=700,A1<=799))
• Unique value in a column       David Hagar EEE.004   (No Duplicates,  Disallow Duplicate)
  =COUNTIF($A$1:A1,A1)=1       actually insufficient because it does not check the entire range, see next example
• Unique value in a range       Chip Pearson, Preventing Duplicates On Entry
  =COUNTIF($A$1:$A$10,"="&A1)=1
  =COUNTIF(tablename,A1)=1   -- equal is assumed «
• Prevent Duplicate Entries, Q313185 -- XL2000: How to Use Data Validation to Prevent Duplicate Entries
  =ISERROR(MATCH(A1,A2:A50,0))   in formula, and
  =ISERROR(MATCH(A2,INDIRECT("$A$1:$A$"&ROW()-1),0))   in settings
  Haven't really looked at this but it requires two parts to do what David Hagar accomplishes with a single formula.  Chip's solution/example is for a fixed range and as such is not applicable to more generic usage.
• Require Entry in a Specific Column before allowing entry.
  =NOT(TRIM(A1)="")
• Limit up to 25 characters
  =LEN(A1)<=25       -- same as basic length validation
• Choose from one of three lists based on value in A1 -- Alan Linton, programming, 2000/10/02
  =Choose(A1,$A$3:$A$5,$B$3:$B$5,$C$3:$C$5)
  =Indirect($c$1)     where c1: =If(A1=1,"$A$3:$A$5","$B$3:$B$5","$C$3:$C$5")
  =list1         where list1 refers to defined name formula: =IF(A1=1,$A$3:$A$5,$B$3:$B$5,$C$3:$C$5)
• Until SUM reaches a value
  =SUM($A$1:$A$10)< 0       -- Harlan Grove 2000-01-11
• Weekday (Mon-Fri only)
  =AND(WEEKDAY(B2)<>1,WEEKDAY(B2)<>7)       -- Debra Dalgleish, site

Credit Card validation, mod 10, check digit

Avoid confusing identifications with actual numbers.  If you leave out a leading zero on a US zip code it will be incorrect if you leave out a leading zero on a catalog number it will be incorrect.

Trying to treat a 16 digit credit card number as an actual number which Excel limits to 15 digits is the most frequently encountered problem with 15 digit specification Limit (see Help) in Excel. Treat your identification data as text.  You can make up a validation rule to verify that you have a Credit Card Number with a correct syntax 16 digits with a valid check digit in the last position.

Format the column as Text or preceed the identification with a single quote.

Myrna Larson on Credit Card mod 10 check digits   =ValidCard(F1) code below
  http://google.com/groups?selm=e1qj7tg5utj42pvmtljd8rms91ddqqkmb2%404ax.com
  http://google.com/groups?selm=ap314u4unls15otfk1fbggsb5tstqi6nn6%404ax.com

Additional information on Credit cards - some credit cards have less than 16 digits
  http://www.beachnet.com/~hstiles/cardtype.html (found on a Google search)

Postal and Zipcode validations

• reserved for validations of US zip codes, Canadian postal codes, UK postal codes
• =AND(OR(Len(D4)=5,len(d4)=9),ISNUMBER(left(D4,5)& right(d4,4))) -- US
• =AND(LEN(D4)=7, left(d4)>="A", ISNUMBER(mid(d4,2,1)& mid(d4,5,1) & right(d4)) -- Canadian
• =AND(left(d4)>="A", ...)

Some notes on Validation

Data validation only applies to keyed in entry, at the time of entry.  Constants are checked at entry.  Formulas entered are evaluated only at the time of entry in regards to validation.  [Excel 2000 is what I use]

Pasting a value into a cell with validation will not be validation checked, nor will cell changed with Replace (ctrl+h).

Validation in VBA

Related   (#related)

Duplicate Entries (identification, prevention, elimination, deleting, filtering)

• Count of unique entries in a range
  =SUMPRODUCT(1/(COUNTIF(A1:A15,A1:A15))) -- Tom Ogilvy ( Feb 10 2001) -- must be no blank cells
• Identification, see Chip Pearson's Duplicate and Unique Items in Lists
    http://www.cpearson.com/excel/duplicat.htm
    http://www.mvps.org/dmcritchie/condfmt.htm (Conditional Formatting)
• Prevention with validation,
   http://www.cpearson.com/excel/NoDupEntry.htm
   http://www.cpearson.com/excel/named.htm#Cheat
   http://www.mvps.org/dmcritchie/excel/validation.htm
• Delete Duplicate Rows
    http://www.cpearson.com/excel/deleting.htm
• Filtering for Unique entries, You can eliminate duplicate by use of a Filter
        Data, Filter, Advanced, Unique only
    Filtering does not eliminate duplicates but the filtered cells can be pasted, printed, as if they are the only data.
• Restricting Input before bringing into Excel
    From a database use of Distinct in SQL, an article Eliminating duplicate rows in Excel - TechSpace - Express Computer India, Dr. Nitin Paranjpe, article uses little used feature in Excel ODBC to enter SQL, another part of the artilce uses ODBC to eliminate ASCII unwanted data.
    Importing ASCII (flat file) using a macro and eliminating unwanted data.

Validation Related Articles

• There is a good example of simple cell validation in the "Data Validation" sheet of samples.xls that came with your intallation of Excel.
• DataVa101 and DataVa1021 «, Data Validation lists, Debra Dalgleish, Contextures.com -- http://www.contextures.com/xlDataVal01.html
• Excel -- Data Validation -- AdvancedTechniques, Debra Dalgleish, make the font larger, or validation drop down wider.
• Data Validation, Using Cells On Other Sheets - Chip Pearson, to get around that validate won't allow you to supply a range on another sheet.
• Excel Developer Tip: Playing a Sound Based on a Cell's Value, tip 87, John Walkenbach
• Excel Developer Tip: Ensuring that Data Validation is Not Deleted «, tip 98, John Walkenbach
• Validate, search entered on John Walkenbach's Excel site.  (an interesting sidetrip checklist for creating a form:  tip 05).
• XL2000: How to Use Data Validation to Prevent Duplicate Entries, MSKB 213185
• Two validation lists used together, in cell B1 be able to pick the category "Flooring", then in C1 have the list of the Flooring contractors displayed for the user to pick.  Posted Code by Damon Ostrander, 2002-04-29.
• How to get data validation messages to pop up automagically, Frank Isaacs,Dolphin Technologies.
• Event handlers could be disabled by disabling macros generally or disabling only event handlers.  Harlan Grove, 2004-03-26 provides a means of determinining if macros are disabled and specifically if Event Handlers are disabled.
• Using a validation list in another worksheet or workbook, Microsoft Office Assistance.
• Validation into Comments and Comments to Validation Message conversions, for those who don't want to see comment indicator.  Not recommended but can be done.

Visit [my Excel home page]   [Index page]   [Excel Onsite Search]   [top of this page]

Please send your comments concerning this web page to: David McRitchie send email comments

Copyright © 1997 - 2004,  F. David McRitchie,  All Rights Reserved