Excel Data Validation enables user set rules to control the type of data or the values that users enter into a cell
To work more efficiently and reduce typo errors in worksheets use drop-down lists in cells. The drop-down lists in Excel are helpful if you want to be sure that users select an item from a list, instead of typing their own values. Create a Drop-down List To create a drop-down list in Excel, execute the following steps. 1. On …
— 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 …
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 …
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 …
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 …
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 …
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, …
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 …
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 …
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 …
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. …
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 …
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 …
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-“) …
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: …
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 …
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 …
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 …
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 …
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 …