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…