Data Validation

Excel Data Validation enables user set rules to control the type of data or the values that users enter into a cell

Excel Data validation whole percentage only

Set criteria to allow whole percentage only without decimals. To allow only whole number percentages like 5%, 10% and not 5.5%, 10.25%, etc. you can use data validation with a custom formula based on the TRUNC function. Formula =TRUNC(A1*100)=(A1*100) Notes: Data validation rules are triggered when a user adds or changes a cell value. Cell references… read more »

Excel Data validation unique values only

Reject duplicate data entry in a cell range. To allow only unique values in a given range, you can use data validation with a custom formula based on the COUNTIF function. Formula =COUNTIF(range,A1)<2 Note: Cell references in data validation formulas are relative to the upper left cell in the range selected when the validation rule… read more »

Excel Data validation specific characters only

Set criteria to accept specific characters only To use data validation to allow a list of specific characters only, you can use a rather complicated array formula based on the COUNT, MATCH, and LEN functions. Formula =COUNT(MATCH(MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1),allowed&””,0))=LEN(A1) Explanation In the example shown, data validation is applied with this formula: =COUNT(MATCH(MID(B5,ROW(INDIRECT(“1:”&LEN(B5))),1),allowed&””,0))=LEN(B5) where “allowed” is the named range D5:D11…. read more »

Excel Data validation require unique number

Set criteria to accept only unique number, ie no duplicate. To allow only unique numbers in a given range, you can use data validation with a custom formula based on the AND, ISNUMBER, and COUNTIF functions. Formula =AND(ISNUMBER(A1),COUNTIF(range,A1)<2) Cell references in data validation formulas are relative to the upper left cell in the range selected… read more »

Excel Data validation only dates between

Set criteria to accept only dates in a particular period Excel has several built-in data validation rules for dates. This page explains how to create a your own validation rule based on a custom formula in case you want or need more control and flexibility. Formula =AND(A1>=date1),A1<=date2) Explanation To allow a user to enter only… read more »

Excel Data validation number multiple 100

Accept only numbers that are multiple of 100 To allow only numbers in multiples of 100, you can use data validation with a custom formula based on the MOD function. Formula =MOD(A1,100)=0 Note: Cell references in data validation formulas are relative to the upper left cell in the range selected when the validation rule is defined,… read more »

Excel Data validation no punctuation

Reject data containing punctuation mark To use data validation to restrict punctuation, you can use a named range and a formula based on the FIND and COUNT functions. Formula =COUNT(FIND(xlist,A1))=0 Explanation In the example shown, the data validation applied to C5:C10 is: =COUNT(FIND(xlist,B5))=0 where xlist is the named range D5:D11. How this formula works Data validation rules… read more »

Data validation must not exist in list

Accept data that are not included in a specific range of records To allow only values that do not exist in a list, you can use data validation with a custom formula based on the COUNTIF function. Note: Excel has a built-in data validation rules for dropdown lists. This page explains how to create a custom validation… read more »

Excel Data validation must not contain

Set criteria to reject specific data in Excel To disallow input that contains one of many things, you can use a custom data validation rule based the SEARCH function. Formula =SUMPRODUCT(–ISNUMBER(SEARCH(list,A1)))=0 Explanation In the example shown above, the data validation applied to B5:B11 is: =SUMPRODUCT(–ISNUMBER(SEARCH(list,B5)))=0 Note:  Cell references in data validation formulas are relative to… read more »

Excel Data validation must contain specific text

How to set criteria in Excel to accept specific text. To allow only values that contain a specific text string, you can use data validation with a custom formula based on the FIND and ISNUMBER functions. Formula =ISNUMBER(FIND(“txt”,A1)) Explanation In the example shown, the data validation applied to C5:C9 is: =ISNUMBER(FIND(“XST”,C5)) Note: Data validation rules… read more »

Sidebar