Excel Data Validation: custom formulas and rules

The tutorial shows how to make custom Data Validation rules in Excel. You will find a few examples of Excel data validation formulas to allow only numbers or text values in specific cells, or only text beginning with specific characters, permit unique data preventing duplicates, and more.

In yesterday's tutorial we started to look at Excel Data Validation - what its purpose is, how it works, and how to use built-in rules to validate data in your worksheets. Today, we are going to take a step further and talk about the nitty-gritty aspects of custom data validation in Excel as well as experiment with a handful of different validation formulas.

How to create custom data validation with formula

Microsoft Excel has several built-in data validation rules for numbers, dates and text, but they cover only the most basic scenarios. If you want to validate cells with your own criteria, create a custom validation rule based on a formula. Here's how:

  1. Select one or more cells to validate.
  2. Open the Data Validation dialog box. For this, click the Data Validation button on the Data tab, in the Data Tools group or press the key sequence Alt > D > L (each key is to be pressed separately).
  3. On the Settings tab of the Data Validation dialog window, select Custom in the Allow box, and enter your data validation formula in the Formula box.
  4. Click OK.

Creating a custom formula-based validation rule in Excel

Optionally, you can add a custom input message and Error alert that will show up when the user selects the validated cell or enters invalid data, respectively.

Below you will find a few examples of custom validation rules for different data types.

Note. All Excel data validation rules, built-in and custom, verify only new data that is typed in a cell after creating the rule. Copied data is not validated, nor is the data input in the cell before making the rule. To pin down existing entries that do not meet your data validation criteria, use the Circle Invalid Data feature as shown in How to find invalid data in Excel.

Excel data validation to allow numbers only

Surprisingly, none of the inbuilt Excel data validation rules cater for a very typical situation when you need to restrict users to entering only numbers in specific cells. But this can be easily done with a custom data validation formula based on the ISNUMBER function, like this one:

=ISNUMBER(C2)

Where C2 is the topmost cell of the range you want to validate.
A custom data validation rule to allow numbers only

Note. The ISNUMBER function allows any numeric values in validated cells, including integers, decimals, fractions as well as dates and times, which are also numbers in terms of Excel.

Excel data validation to allow text only

If you are looking for the opposite - to allow only text entries in given range of cells, then build a custom rule with the ISTEXT function, for example:

=ISTEXT(D2)

Where D2 is the uppermost cell of the selected range.
A custom data validation rule to allow text only

Allow text beginning with specific character(s)

If all values in a certain range should begin with a particular character or substring, then do Excel data validation based on the COUNTIF function with a wildcard character:

COUNTIF(cell,"text*")

For example, to ensure that all order id's in column A begin with the "AA-", "aa-", "Aa-", or "aA-" prefix (case-insensitive), define a custom rule with this data validation formula:

=COUNTIF(A2,"aa-*")
Data validation to allow text beginning with specific characters

Validation formula with the OR logic (multiple criteria)

In case there are 2 or more valid prefixes, add up several COUNTIF functions, so that your Excel data validation rule works with the OR logic:

=COUNTIF(A2,"aa-*")+COUNTIF(A2,"bb-*")
Excel data validation formula with the OR logic

Case-sensitive validation formula

If the character case matters, then use EXACT in combination with the LEFT function to create a case-sensitive validation formula for entries beginning with specific text:

EXACT(LEFT(cell, number_of_chars), text)

For instance, to allow only those order ids that begin with "AA-" (neither "aa-" nor "Aa-" is allowed), use this formula:

=EXACT(LEFT(A2,3),"AA-")

In the above formula, the LEFT function extracts the first 3 characters from cell A2, and EXACT performs a case-sensitive comparison with the hard-coded substring ("AA-" in this example). If the two substrings match exactly, the formula returns TRUE and the validation passes; otherwise FALSE is returned and the validation fails.
Case-sensitive validation formula to allow text beginning with specific characters

Allow entries containing certain text

To allow entries that contain specific text anywhere in a cell (in the beginning, middle, or end), use the ISNUMBER function in combination with either FIND or SEARCH depending on whether you want case-sensitive or case-insensitive match:

  • Case-insensitive validation:

    ISNUMBER(SEARCH(text, cell))
  • Case-sensitive validation:

    ISNUMBER(FIND(text, cell))

On our sample data set, to permit only entries containing the text "AA" in cells A2:A6, use one of these formulas:

Case-insensitive:

=ISNUMBER(SEARCH("AA", A2))

Case-sensitive:

=ISNUMBER(FIND("AA", A2))

The formulas work with the following logic:

You search the substring "AA" in cell A2 using FIND or SEARCH, and both return a position of the first character in the substring. If the text is not found, an error is returned. For any numeric value returned as the result of search, the ISNUMBER function yields TRUE, and data validation is successful. In case of an error, ISNUMBER returns FALSE, and the entry won't be allowed in a cell.
Data validation to allow entries containing certain text

Data validation to allow only unique entries and disallow duplicates

In situations when a certain column or a range of cell should not contain any duplicates, configure a custom data validation rule to allow only unique entries. For this, we are going to use the classic COUNTIF formula to identify duplicates:

=COUNTIF(range, topmost_cell)<=1

For example, to make sure that only unique order ids are input in cells A2 to A6, create a custom rule with this data validation formula:

=COUNTIF($A$2:$A$6, A2)<=1

When a unique value is entered, the formula returns TRUE and the validation succeeds. If the same value already exists in the specified range (count greater than 1), COUNTIF returns FALSE and the input fails validation.

Please pay attention that we lock the range with absolute cell references (A$2:$A$6) and use a relative reference for the top cell (A2) to get the formula to adjust properly for each cell in the validated range.
Data validation to allow only unique entries

Note. This data validation formulas is case-insensitive, it does not distinguish uppercase and lowercase text.

Validation formulas for dates and times

Inbuilt date validation provides quite a lot of predefined criteria to restrict users to entering only dates between the two dates you specify, greater than, less than, or equal to a given date.

If you want more control over data validation in your worksheets, you can replicate the inbuilt functionality with a custom rule or write your own formula that goes beyond the built-in capabilities of Excel data validation.

Allow dates between two dates

To limit the entry to a date within a specified range, you can use either the predefined Date rule with the "between" criteria or make a custom validation rule with this generic formula:

AND(cell>=start_date), cell<=end_date)

Where:

  • cell is the topmost cell in the validated range, and
  • start and end dates are valid dates supplied via the DATE function or references to cells containing the dates.

For example, to allow only dates in the month of July of the year 2017, use the following formula:

=AND(C2>=DATE(2017,7,1),C2<=DATE(2017,7,31))

Or, enter the start date and end date in some cells (F1 and F2 in this example), and reference those cells in your formula:

=AND(C2>=$F$1, C2<=$F$2)

Please notice that the boundary dates are locked with absolute cell references.
Data validation to allow dates between two dates

Allow weekdays or weekends only

To restrict a user to entering only weekdays or weekends, configure a custom validation rule based on the WEEKDAY function.

With the return_type argument set to 2, WEEKDAY returns an integer ranging from 1 (Monday) to 7 (Sunday). So, for weekdays (Mon to Fri) the result of the formula should be less than 6, and for weekends (Sat and Sun) greater than 5.

Allow only workdays:

WEEKDAY(cell,2)<6

Allow only weekends:

WEEKDAY(cell,2)>5

For example, to allow entering only workdays in cells C2:C6, use this formula:

=WEEKDAY(C2,2)<6
Validation rule to allow only workdays

Validate dates based on today's date

In many situations, you may want to use today's date as the start date of the allowed date range. To get the current date, use the TODAY function, and then add the desired number of days to it to compute the end date.

For example, to limit the data entry to 6 days from now (7 days including today), we are going to use the built-in Date rule with the formula-based criteria:

  1. Select Date in the Allow
  2. Select between in the Data
  3. In the Start date box, enter =TODAY()
  4. In the End date box, enter =TODAY() + 6

Validating dates based on today's date

In a similar manner, you can restrict users to entering dates before or after today's date. For this, select either less than or greater than in the Data box, and then enter =TODAY() in the End date or Start date box, respectively.

Validate times based on current time

To validate data based on the current time, use the predefined Time rule with your own data validation formula:

  1. In the Allow box, select Time.
  2. In the Data box, pick either less than to allow only times before the current time, or greater than to allow times after the current time.
  3. In the End time or Start time box (depending on which criteria you selected on the previous step), enter one of the following formulas:
    • To validate dates and times based on the current date and time:
      =NOW()
    • To validate times based on the current time:
      =TIME( HOUR(NOW()), MINUTE(NOW()), SECOND(NOW()))

The screenshot below shows a rule that allows only times greater than the current time:
Validating times based on current time

Custom Excel data validation rule not working

If your formula-based data validation rule does not work as expected, there are 3 main points to check:

  • Data validation formula is correct
  • Validation formula does not refer to an empty cell
  • Appropriate cell references are used

Check the correctness of your Excel data validation formula

For starters, copy your validation formula into some cell to make sure it does not return an error such as #N/A, #VALUE or #DIV/0!.

If you are creating a custom rule, the formula should return the logical values of TRUE and FALSE or the values of 1 and 0 equating to them, respectively.

If you use a formula-based criteria in a built-in rule (like we did to validate times based on the current time), it can also return another numeric value.

Excel data validation formula should not refer to an empty cell

In many situations, if you select the Ignore blank box when defining the rule (usually selected by default) and one or more cells referenced in your formula is blank, any value will be allowed in the validated cell.

Here is an example in the simplest form:
Validation formula should not refer to an empty cell

Absolute and relative cell references in data validation formulas

When setting up a formula-based Excel validation rule, please keep in mind that all cell references in your formula are relative to the upper left cell in the selected range.

If you are creating a rule for more than one cell and your validation criteria are dependent on specific cells, be sure to use absolute cell references (with the $ sign like $A$1), otherwise your rule will work correctly only for the first cell. To better illustrate the point, please consider the following example.

Supposing, you want to restrict data entry in cells D2 to D5 to whole numbers between 1 (minimum value) and the result of dividing A2 by B2. So, you calculate the maximum value with this simple formula =A2/B2, like shown in the screenshot below:
Incorrect cell references in a data validation formula

The problem is this seemingly correct formula won't work for cells D3 to D5 because relative references change based on a relative position of rows and columns. Thus, for cell D3 the formula will change to =A3/B3, and for D4 it will become =A4/B4, doing data validation all wrong!

To fix the formula, just type "$" before the column and row references to lock them: =$A$2/$B$2. Or, press F4 to toggle between different reference types.

In situations when you want to validate each cell based on its own criteria, use relative cell references without $ sign to get the formula to adjust for each row or/and column:
Correct cell references in a data validation formula

As you see, there is no "absolute truth", the same formula could be right or wrong depending on situation and your particular task.

This is how to use data validation in Excel with your own formulas. T gain more understanding, feel free to download our sample workbook below and examine the rule settings. I thank you for reading and hope to see you on our blog next week!

Practice workbook for download

Excel Data Validation examples (.xlsx file)

Table of contents

286 comments

  1. Hi, I want to create a validation where each cell cannot be greater than a certain number (say 10) and the sum of certain numbers in the same range also cannot be greater than a certain number. For Eg: User has to input a number from A5 to A10, where each cell value cannot be greater than 10 and the sum of cells A5 to A10 also cannot be greater than 10. Is there a way to do this?

    • Hello! Create a data validation rule in cell A5 using a formula:

      =AND(SUM(A5:A10)<=10,A5<=10)

      Create similar rules in cells A6:A10.

  2. Hi
    I created a drop down (Data validation - list) which is link with a list of companies.
    However, the dropdown menu is not in alphabetical order.
    How can we have it in alphabetical order?

    Thank you
    Melvin

  3. Sir/ma'am,

    I trying to create a drop down list that allows people to insert a hyperlink into a cell and then select a friendly name via drop down list within the same cell.

    What I have encountered is when data validating that cell it works but when a hyperlink is inserted the data validation in that cell is over written and drop list is removed.

  4. Hi, I'm trying to set data validation for a column such that text length is limited to exactly 10 character and allows only whole numbers. is this possible?

    • Hi! I assume you will be entering numbers, not text limited to 10 characters. Then the data validation formula could be like this:

      =AND(INT(A1)=A1,LEN(A1)<=10)

      • Thanks! this worked =AND(LEN(A2)=10,ISNUMBER(A2))

  5. Good day, Please guide me. I want to limit number entries to only allow entry of either a number "0" or a number "2". Should not allow entry of "1". I just cant get it right using Data validation. Please assist me

  6. I have a group of taxi's (each with its own unique number) that check-in and out throughout the day. They are recorded with the date, check-in time and check-out time. I don't want to allow the taxi to be able to check in if there is not a check out date from the previous visit, i.e., a blank check-out date. The validation would need to look at the empty cell of the previous visit, based on the taxi number in the table, and disallow entry if there is an incomplete date for the previous visit. Is this allowable since the validation would need to look for a blank cell?

    • Hi! You can create a Data Validation for only one specific cell, not for a table column. You can use conditional formatting for the range B2:B1000 so that the cell is color-coded if no date in column B is specified for the previous visit. Here is an example of a conditional formatting formula:

      =AND(NOT(ISBLANK(A2)),ISBLANK(INDEX($B$1:B1,XMATCH(A2,$A$1:A1,0,-1))))

  7. I am trying to create a Timesheet in excel, I created a drop list with different options. Now, the point is that I need to enter like a warning for them, every time they entered PTO (Personal Time Off) or if they leave in Blank the cell, do not enter PER DIEM. For example, if they enter PTO (Personal Time Off) they can't enter per diem and the same rule applied if they leave the cell in blank. For any other option from the drop list, they will be able to enter the per diem. Who I can do this?

    Thank you in advance for your help.

    • Hi! I’m sorry, but your task is not entirely clear to me. Could you please describe it in more detail? Give an example of the source data and the expected result.

  8. I'm trying to do a data validation on a date column where the date has to be less than today and in the format mm/dd/yyyy. I've come up with the formula

    =AND (A1<TODAY(), mod(A1,2))

    but I get the error message that there's a problem with the formula. I've tried moving the outer most set of parentheses to different positions, but nothing works. Please help me figure out what is wrong with the formula.

      • Yes, that was the problem. Thank you so much!

  9. H Alexander,

    I hope you had a great week so far.

    I need help with the following:

    I have some data containing different countries and I want to create a data validation+warning.
    So, I have Germany, UK and Spain and their codes are 1, 2 and 3. On the next column I wish to warn the user that they cannot input Germany(this is the main country,1) and they need to choose UK(2) or Spain(3).
    However, I wish to make it so that the warning specifies if it needs to be UK or Spain, like this: WARNING, please input UK. or WARNING, please input Spain
    So I don't want the warning to mention both countries, but the precise one.

    Much appreciated,
    George

    • Hi! If I understand the question correctly, you want to modify the warning on data entry. With Data Validation, you cannot change by condition the warning when data is entered into a cell. You can do that with a VBA macro. I hope I answered your question.

      • You did,

        Much appreciated,
        George

  10. I have several small ranges of cells (5 cells per range), where within each cell of each range uses a simple dropdown (blank, Yes). I would like to restrict the user to only being able to select "YES" for one of the cells within each range. They will be able to select "blank" for multiple cells, but the "Yes" can be selected for only one cell. Do you know of a conditional format formula or other tool to enforce this?

    Thanks,
    Matt

  11. Hi, I need to calculate the incentive according to the review by the HOD. we have 4 category of staff (Cat-A, Cat-B, Cat-C & Cat-D), the review is like A+, A, B & C. Can you help me to calculate the Incentive depend on the review.

  12. Hello,

    I want the column C to only accept English lower case letters, numbers, and the dash (hyphen).
    Thanks.

    • Hello! The answer to your question can be found in this article: Excel Data Validation using regular expressions.
      You can use the following expression to resolve only lowercase letters, numbers, and dashes in Excel Regex:

      ^[a-z0-9-]+$

      This expression starts at the beginning of the string (^) and ends at the end of the string ($). Inside the square brackets are all the allowed characters: lowercase letters (a-z), numbers (0-9), and dashes (-). A plus sign (+) indicates that there must be one or more matches of allowed characters.

  13. In a range 8 cells I want to fill any 5 cells with data. If I try to enter the 6th data is should return an error message indicating already 5 cell entries done. No more data allowed in this range.

  14. Hello,

    Is it possible to create a specific drop down box for a specific value in another drop down box? I have a drop down box for each department and now want to have a specific drop down box for each departments functions for them to select. I appreciate any help you can provide.

    Thank you!

  15. For a single cell, i need to apply multiple data validations. First one is List (set of values) as a drop down. Second one is custom formula checking the length of the previous cell is not equal to zero. How can both be applied at the same time?

  16. Hello,

    Is it possible to create a custom data validation formula so that if data is entered into a row, a certain cell within that row MUST have data inputted?

  17. Hello. Can i ask if posible to make data validation on cell that allow only to input greater than number from the present number from the cell.and show error if i input less than the present number from the cell. In short the input number must be always greater than the present number.without using minimum number.thank you if somebody can help.

  18. Hi, is it possible to have a set of letter values (as in like a regular "List" data validation) + whole numbers only? without having to list down all numbers in the list used in data validation?

    E.g.
    List = A,B,C,D
    Cells should allow, A or B or C or D or any whole numbers but should not allow E or any other words.

  19. Hello
    I am trying to find the correct format to apply a formula from a cell to another cell where the input choice in that cell is from a drop down list.

  20. Good morning, I hope you're doing well. My question is how do I use custom data validation to "only enter currency values without decimals" in a certain column?

    • Hello! Compare the number you enter with the same number rounded to integers.
      For example, use this formula data validation for column C:

      =C1=INT(C1)

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)