Comments on: Excel Data Validation: custom formulas and rules

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. Continue reading →

Comments page 2. Total comments: 286

  1. 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

    1. Hi! To allow only 2 values to be entered, use the OR formula in the data validation. See the article above. For example:

      =OR(A1=0,A1=2).

      You can also use a drop down list with two values - 0 and 2.

  2. 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?

    1. 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))))

  3. 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.

    1. 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.

  4. 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.

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

  5. 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

    1. 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.

      1. You did,

        Much appreciated,
        George

  6. 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

    1. Hi! You can use the Data Validation tool for a single cell only. For a range of cells, use the VBA macro.

  7. 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.

  8. Hello,

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

    1. 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.

  9. 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.

  10. 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!

  11. 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?

    1. Hi! Two data validations cannot be applied to a single cell. You can use a VBA macro for the second validation.

  12. 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?

  13. 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.

    1. Hi! A formula can't refer to the cell in which it is written. So your data validation can only be done with a VBA macro.

  14. 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.

    1. Hi!
      Specify all conditions using the OR operator. To define integers, use the INT function.
      Try this formula:

      =OR(A1="A",A1="B",A1="C",A1="D",IF(ISNUMBER(A1),A1=INT(A1)))

  15. 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.

  16. 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?

    1. 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)

  17. Hello, I made a formula using data validation where I am going to validate one cell if it contains the exact word then you won't be able to edit the rest of the cells however I need to copy this formula to other group of cells but it will only validate the original cell because it won't work if I don't write $ on it so instead I need to manually rewrite the formula after copying it. Is there any way to copy the formulas automatically without needing to rewrite the formula to validate the new cell? My explanation might be confusing lol.

    What I'm doing is I have a cell that has a dropdown list of "Open" and "Closed". If I select Closed in the dropdown it will validate it and I won't be able to edit the group of cells I selected, if it's Open then I will be able to edit it.

    Now I need to copy the whole thing to make a new one but I can't copy the exact result because in the formula it has $ sign where it will be permanent. So even though it is copied it is still validating the same cell. So I need to rewrite the formula with a different cell number on it.

    My question is if there's any way to work around it. I tried all the copy pasting method but it won't work. If I don't put dollar sign then the formula won't work, the result is no matter if I selected Open or CLosed I won't be able to edit it if I write it without the $ sign.

  18. I am looking for a validation rule to do this. The custom
    We are currently using Excel Shortcuts to enter the dates in the cell (Date & Time Shortcut = Ctrl+; then Spacebar Ctrl+Shift+;)
    The date is always displayed as 12 04 2023 12:19. I have even tried to write a macro to run when you save the file to change the Date Columns to the correct format, to no avail!!
    Will it be possible to do this with Data Validation??
    And how will I go about it? I understand after looking at your explanations it will need to be a Custom Validation, but I have no idea how to do it or where to begin.
    I have made an Input form on an Excel sheet to capture the data and transfer it to the correct worksheet. Al that is working.
    My challenge is the date/Time format on the input form.
    Regional Settings on PC
    Format - English (South Africa)
    Short Date - dd MMM yyyy
    Long Date - dd MMMM yyyy
    Short time - HH:mm
    Long Time - HH:mm

    Thanx

  19. How do i create data validation for range of cells where sum of all the cells in that range not to exceed a particular value

    1. Hello!
      Select a range of cells (e.g. A2:A10). Create a data validation rule for this range with the formula SUM (for example SUM(A2:A10)<20)
      I hope it’ll be helpful.

  20. I have a sheet with all the users assigned to a project. Now, this is a live sheet which needs to be updated periodically. Is there any way, I can add a restriction, so that an user can only edit their own data (perhaps using their own User ID as a key)?

    Thanks!

  21. I want to creat a custom date like mm-dd-yyyy is possible

  22. Good day

    How would I go about to allow only a-z or A-Z (no numeric in the text) and also that the entry may not exceed 40 characters. With ISTEXT it allows abc123. Thanking you in advance.

  23. Without wanting to push my luck (because I already had a question answered here in record time)... :-)

    Is it possible to automatically hide/unhide rows/columns depending on the answer in a validated list?

    Example:

    A1 contains a validated drop down list of nationalities (100+ options)

    In Row 2 I have written (field B1): ID number. But that entire line (where the answer has to be given in B2 what the ID number is) should only be visible if in A1 the Answer "Dutch" was selected.

    If any other value that "Dutch" was selected in A1, then row 2 should be invisble but row 3 (where the person has to fill in his Social Security number should become visible.

    So in short: if A1 = "Dutch" then make row 2 visible and row 3 invisble. If A1 = any other value than "Dutch" in the dropdown menu, then make row 2 invisble and row 3 visible.

    Thanks again!

  24. Hi,
    I need to find a solution to the following challenge

    Cell B2 has a dropdown list of 5 choices (A/B/C/D/E as an example)

    Cell C2 now needs to contain dropdown lists which are particular to what is in B2. So If B2 = "A" then the dropdown in C2 needs to contain a certain amount of options in a dropdown menu which is tied to "A" (these values that are linked to A/B/C/D/E are in a separate tab and are each in a different column in that tab sheet).

    I tried "custom" and then went for =if(B2="A"; [target fields for dropdown linked to A here]); if(B2="B"; [target fields for dropdown linked to A here]); ... and so on.

    But that does not work. :-(

    Many thanks!

      1. Hi Alexander,

        Yes, that's it! Many thanks!!! (also for the lightning-fast reply :-)

        Kind regards,
        Philip

  25. Hello,
    Is it possible to create the drop down (data validation) using formula? I need to create the excel file in email attachment in SAP environment and I can only create the data validation in excel file using formulas (in development perspective).

  26. Hello,
    Is there a possibility to set up a different areas to search in based on data in a specific cell?
    Imagine you have ten customers. In one cell (B11), you use data validation - list - source (K10:K20) to choose the customer from the list.
    Any idea how to arrange that in another cell (B21) I could choose from the list of different sides that every customer has, but only from the list that belongs to the particular customer?
    Thank you,

  27. Using custom validation, the cell can only show "0" or values between 10,000 - 36,000. How do I do this?

  28. Is it possible to create a data validation formula that returns only the distinct values from a table column?

      1. Sorry Alexander, I did not qualify the challenge well. The end game is to have a cell with a drop-down data validation list. The content of the list contains only the distinct values contained in a column of a table. The table rows are dynamic based on the data query that populates the table.

  29. Sir, how I write a data validation formula for entering a text and number combination format of 320.6 ABC.
    It should always be 3 digits, 1 decimal, then space and 3 English characters. format should be 320.6 ABC

    Thanks

  30. Hey!

    This was awesome. If I want to have a drop down of the numbers 1-50 and they can only select each number once (for example, identifying a list of their top 50 out of 2000) what formula would I use?

  31. Hello,
    Could you tell me please how to set up a column where only a four digit number is allowed (a year)? Thank you.

  32. Hi, thank you for your valuable help. Could you please tell me how I can ensure that my user enters Text first then numbers? Examples: ABC123 or ABCD12 or AB1234 etc, there is no set rule to 'count' of alphabet or numbers.

  33. Hey! So I'm trying to do a nested if function for a list dropdown and my issue is that I have too many options and it exceeds the character limit of the source bar in the data validation popup. Is there any way to get around this?

  34. Hi,

    I have a drop down menu with a list of percentage ranges and I want a different calculation dependent on each selection:

    < 90% then deduct 10% from cell E9 and update total in cell L9

    90-95% then deduct 5% from cell E9 and update total In cell L9

    96-99% then deduct 2.5% from cell E9 and update total in cell L9

    I initially tried IF statements based on the ‘text’ selected but it is only updating the total based on each individual cell with a drop down list, and I need each cell with the drop down menu to keep updating one cell total collectively.

    Can you help at all please?

    1. Hi!
      If a value is written in cell E9, then you cannot change it using an Excel formula. We have talked about this many times in our blog. This can be done using a VBA macro. You can write an IF formula with nested conditions in another cell based on the selected text.

  35. HI,
    I have a data set with a validation rule which does not allow names to be duplicated across the row. I would like to add a drop down list for the names to be selected from but when I try to do this it erases the previous validation rule which prevents the duplication. Is there any way I can add these two rules together?
    Thanks

  36. Hello,

    Please, I need help in excel coding:
    first, I want to know the coding formula for phone number validation to be equal to 8 characters e.g.: 65000000
    second, I used the formula "${hh_depend}+${hh_workf}" to calculate the sum of 2 variables. And I need validation coding which allows this total value to be strictly less than the answer of a previous variable ${hh_size} value.

    Thank you

  37. Hi,

    I created a data validation drop down list.

    A: Date not synced
    B: Term
    C: Item
    D: Amount
    E: Combination: ________

    Like to ask your help on how to put a formula, when I pick "E" I can write a comment on the it. Like for example, combination of A&B or C&D or ABC or combination of everything

    1. Hi!
      You cannot change the values that are inserted into a cell using a drop-down list. You can create in your dropdown list more options for item e and write down all the possible combinations.

  38. I need to restrict a field using Data Validation so that the user can only enter a Proper Name which means it must be entered using uppercase letters, numbers and only the following 4 special characters
    ,(comma)
    -(dash)
    & (Ampersand sign)
    ' (Apostrophe)
    How would I do this? in a long statement specifying all the valid characters, or in a table that is 1 column with all the valid characters?

  39. Hi!! I'm really dumb at such things like this, I'm in my 1st yr of college and we need to do data validation. C2:C7 contains the salary ( which the criteria, the amount must be 12, 000-35,000 ), and the next cell D2:D7 contains the bonus salary. The bonus salary must be 12% of the salary or cannot be greater than 12%. I know I need to make a custom formula but I'm really dumb at logic and maths, please help. Thank you and I apologize for my dumbness, have a great day <3

    1. Hi!
      The formula returns TRUE if the bonus salary does not exceed 12% of the basic salary:

      =IF(D2/C2<0.12,TRUE,FALSE)

      or

      =D2/C2<0.12

      Please check out this article to learn how to create conditions with IF function in Excel.

  40. Hi,
    How to restrict any profile in validation rule formula, that means I don't need to run that user in validation rule.. So any formula

  41. In my drop-down list i have 3 options (1,2.3), from this when i have choose 1 and 3 i should receive error message. when choose 2 i don't want error message. can you help me.

  42. One more item in the list can be validating field based on conditional values from other fields.

    For example:
    1. Value in Sheet2!B2 should be a date greater than or equal to the date found using vlookup of Sheet2!A2 on Range Sheet1!A:D for D column, and less than similar vlookup on another range.
    2. Value in B2 should be some percentage of sum of value from some other range.
    3. Value in B2 should be either from the list from some other range, or should be from a list from some other range 2.
    4. Value in B3 should be in (the list/range + B2), and so on... That is, adding previous value to the list in the data validation.

    These kind of validations are more common in our day to day work. Simple list validations or date validations are very simple to use.

  43. Hi Alexander, thanks for your answer.

    I have done the regular drop-down list.
    Now, what I need is to be able to select data from the list in cell A1 and get it to show me "blue", whilst on cell B1, I select the same item on the same list but I get it to show me "1".
    Do you understand what I mean?

    Thanks for your help.

  44. Hi there,

    I have a cell with a data validation list dropdown. I want to make a rule to get the cell to show something different when a particular value of the list is selected. Is this possible?

    For example, let's say I have a list from 1 to 10. I want the list to show all the numbers normally, but I want the 1 to show "Improvement needed!" and the 10 to show "Awesome!", however, I need the list to still be 1 to 10, as I would need to do other operations with the data of that list.

    Thanks for your help.

    1. Hello!
      A regular drop-down list inserts the data it shows into the cell.
      An ActiveX ComboBox (Developer - Insert menu tab) may be suitable for your task.

  45. I want the cell to except only Capital M. How would I that?

    Thanks

    1. Hello!
      I hope you have studied the recommendations in the tutorial above. It contains answers to your question.
      Formula =A1="Capital M"

  46. Can I restrict the entry to one decimal point? The problem is even if I set the format of cell as number with one decimal point, when I enter 0.12, the value is still stored as 0.12 despite the number show in the cell is 0.1.

      1. thanks a lot

  47. Column D has data validation rule to select from a drop down list.

    I wish column E to automatically fill based on a custom rule:

    =VLOOKUP(D2,Suppliers!A:B,2,FALSE) /* No errors when setting up the custom rule.*/

    But when i select a value from the drop down list for cell D2, nothing fills in cell E2.

    Thanks for your feedback.

    1. Hello!
      Unfortunately, without seeing your data it is difficult to give you any advice. Please check out the following article on our blog, it’ll be sure to help you with your task: Excel VLOOKUP not working.
      If there is anything else I can help you with, please let me know.

      1. Hello,

        This is the dropdown list worksheet called Suppliers:
        ----------------------------------------------------------------------------
        Allen & Sons Ltd Water
        Beacham & Sons Ltd Electricity
        Chisholm & Sons Ltd Medical Equipment
        Davidson & Sons Ltd Building Maintenance
        Elias & Sons Ltd Computer Equipment
        Fanshaw & Sons Ltd Window Cleaning
        George & Sons Ltd Gardening

        This is the data entry worksheet:
        -----------------------------------------------
        Supplier (col D) Supplies (col E)
        Allen & Sons Ltd
        Beacham & Sons Ltd
        Chisholm & Sons Ltd
        Davidson & Sons Ltd
        Elias & Sons Ltd

        When I select Allen & Sons Ltd in the Supplier column D, I expected column E to fill automatically with the word Water because of the custom data validation formula, but it remains blank.
        =VLOOKUP(D2,Suppliers!A:B,2,FALSE)

        I know I can make cell E do a VLOOKUP, but I was testing to see if the VLOOKUP function could be incorporated as part of a custom data validation formula.

        This is not a problem, it's just a technical challenge.

        Thank you.

        1. Hello!
          Dropdown list in D1 cannot insert value in E1. In cell E1, you can write down the VLOOKUP formula. Once you select a value in D1, it will find the value you want.
          I hope I answered your question. If something is still unclear, please feel free to ask.

  48. Hi
    Using data validation, I know it is possible to do the below separately:

    1. Create a data validation drop down list (ALT + A + V + V then select list from the 'Allow' field and denote the cell range in the 'Source' field)
    2. Prevent users from entering data into one cell (e.g. cell A2) if another cell (e.g. cell A1) has not yet been populated (ALT + A + V + V then select Custom from the 'Allow' field and insert '=NOT(ISBLANK(A1))' in the 'Formula' field)

    However, I am in need of a combination of both. I need to customise a cell such that if cell A1 is not populated, then cell A2 cannot be populated but, when cell A1 has data inserted, A2 should be able to be populated but only through a drop-down list. May you assist

    1. Using data validation, I know it is possible to do the below separately:
      ...
      2. Prevent users from entering data into one cell (e.g. cell A2) if another cell (e.g. cell A1) has not yet been populated (ALT + A + V + V then select Custom from the 'Allow' field and insert '=NOT(ISBLANK(A1))' in the 'Formula' field)
      ...

      How do you know that?? It's NOT working!

      ...Does anyone know if it's possible to use as a condition in formula of Data Validation empty cells??

      1. Hello!
        Use the following formula for data validation

        =NOT(ISBLANK(A1))

        Uncheck "Ignore blank". It works.

  49. Does data validation does not read result in formula result?

  50. Hi,

    Just wondering if you can help me with something. I'm trying to create a variable list dependent on a variable value. E.g. If cell equals a number then give me list of job titles relating to that number.
    I say its variable because the number depends on which department is selected. The job titles are on a separate worksheet within the workbook. The list is variable because job titles can be added or removed to the list so I can't label specific ranges.
    Essentially I want to do a vlookup but the result being a list rather than a single value... is this possible and if so please let me know how to do this!

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 :)