## Excel Office

Excel How Tos, Tutorials, Tips & Tricks, Shortcuts

# Category: 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 in data validation formulas are…

## 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 is defined, in this case…

## 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. How this formula…

## 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 when the validation rule is…

## 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 dates between two dates, you…

## 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, in this case C5. Explanation…

## 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 are triggered when a user…

## 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 rule when you want to…

## 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 the upper left cell in…

## 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 are triggered when a user…

## Excel Data validation with conditional list

How to create a drop-down and multiple dependent drop-down lists.in Excel To allow a user to switch between two or more lists, you can use the IF function to test for a value and conditionally return a list of values based on the result. Formula =IF(A1=”See full list”,long_list,short_list) Explanation In the example shown above, the data validation applied to C4…

## Excel Data validation must begin with

Using the example below, this tutorial shows how to create Data validation must begin with in Excel. Formula =EXACT(LEFT(A1,3),”XX-“) Explanation To allow only values that begin with certain text, you can use data validation with a custom formula based on the EXACT and LEFT functions. In the example shown, the data validation applied to C5:C9 is: =EXACT(LEFT(C5,3),”MX-“) How this formula works Data…

## Excel Data validation exists in list

Using the example below, this tutorial shows how to create Data validation exists in list in Excel. Formula =COUNTIF(list,A1)>0 Explanation Note: Excel has a built-in data validation rules for dropdown lists. This page explains how to create a your own validation rule for lists when you don’t want the dropdown behavior. To allow only values from a list in a cell, you…

## Excel Data validation allow weekday only

Using the example below, this tutorial shows how to use Excel Data validation allow weekday only. Formula =WEEKDAY(A1,2)<6 Explanation Note: 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 if you need more control or flexibility. To allow a user to enter only dates that are…

## Excel Data validation don’t exceed total

Using the example below, this tutorial shows how to create Data validation don’t exceed total in Excel. Formula =SUM(range)<=1000 Explanation To allow only values that don’t exceed a set sum, you can use data validation with a custom formula based on the SUM function. In the example shown, the data validation applied to B5:B9 is: =SUM(\$C\$6:\$C\$9)<=1000 How this formula works Data validation…

## Excel Data validation date in specific year

Using the example below, this tutorial shows how to create  Data validation date in specific year in Excel. Formula =YEAR(A1)=2016 Explanation Note: 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. To allow a user to…

## Excel Data validation date in next 30 days

Using the example below, this tutorial shows how to create Data validation date in next 30 days in Excel. Formula =AND(A1>TODAY(),A1<=(TODAY()+days)) Explanation Note: 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 when you want more control and flexibility. To allow only a date in the next…

## Excel Data validation allow uppercase only

Using the example below, this tutorial shows how to create Excel Data validation to allow uppercase only. Formula =AND(EXACT(A1,UPPER(A1)),ISTEXT(A1)) Explanation To allow a user to enter only uppercase TEXT,  you can use data validation with a custom formula based on the UPPER, EXACT, and AND functions. In the example shown, the data validation applied to C5:C7 is: =AND(EXACT(C5,UPPER(C5)),ISTEXT(C5)) How this formula works…

## Prevent invalid data entering in specific cells

— Set criteria in your worksheet to accept specific data. Use data validation in Excel to make sure that users enter only values that meet a set criteria into a cell. Steps to navigate to Data Validation icon in Excel Data Tab → Data Tools group → Data Validation Data Validation Example Create Data Validation Rule Input Message Error Alert Data Validation Result Data Validation…