Share via

Data Validation in Excel for Custom formula

SARASWAT Drishti OBS/DIGITAL 0 Reputation points
2026-06-18T08:58:04.1766667+00:00

Hi , I am trying to add a data validation in excel : please suggest correct formula:

  1. Column A , Column B
  2. Data validation to be applied on Column B such that only "No" is allowed if Column B had value = "APPLE".
    1. I am using this formula: in data validation > Custom Formula =OR($A4<>"APPLE" , $B4= "NO")
    2. The above formula is working but its not letting Apple , No value too and showing the error.
Microsoft 365 and Office | Excel | For business | Windows
0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. SARASWAT Drishti OBS/DIGITAL 0 Reputation points
    2026-06-18T09:05:43.6833333+00:00
    1. Wrong active row when entering the formula When setting up data validation, ensure the active cell is the first data row in column B (for example B4) and the formula references that same row ($A4, $B4). Then apply the validation to the whole B range. Ok, i applied to active cell only and its working , how do i apply this to all ?

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. SARASWAT Drishti OBS/DIGITAL 0 Reputation points
    2026-06-18T11:25:27.6333333+00:00

    Hello Henry ,

    Thankyou for answering this. this is resolved. What i was doing wrong : when i clicked on data validation - i added the formula with $A\B4 which was the start column of my excel with Data . But since i wanted to apply the rule on all rows after B4 , somehow excel was auto-incrementing the number starting from B4. So in short , when i am entering the value in A6 (apple) and entering "Yes" in B6-ideally it should show error but it wasnt- it was validating it with (A4+6=A10). and thus not returning correct validation.

    I corrected that by entering $A1 in the data validation . My header is on ROW -3 so i hope this doesnt raises any issue , right ?

    Was this answer helpful?

  3. Henry-N 14,315 Reputation points Microsoft External Staff Moderator
    2026-06-18T10:44:59.78+00:00

    Hi @SARASWAT Drishti OBS/DIGITAL, 

    Thank you for posting your question in the Microsoft Q&A forum. 

    I understand you’re trying to apply conditional data validation and it’s close to working, but not behaving as expected. To help narrow this down and identify the exact issue, could you please help confirm a few details: 

    • In your sheet, is Column A or Column B supposed to contain the value "APPLE"? (your description and formula seem to reference both) 
    • Could you confirm which cell the formula starts from (e.g., row 4)? Data Validation formulas are sensitive to the starting row. 
    • Are there any existing dropdown lists or validation rules already applied to Column B? 
    • When you try to enter "APPLE" in Column A and "NO" in Column B, what exact error message is shown? 
    • If possible could you provide a screenshot of the error ?  

    If you want to apply the data validation for the entire column 

    1. Click on Column B header  User's image
    2. Go to Data > Data Validation 
    3. Enter the same formula: 
    4. Click OK 

    This ensures all current and future rows follow the rule 

    If I’ve misunderstood any part of your requirement, please feel free to clarify so I can assist you more accurately.   

    Thank you again for your time and understanding. I really appreciate your patience, and I’m here to help. Looking forward to your response!      

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".                          

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread    

    Was this answer helpful?

    0 comments

  4. AI answer

    2026-06-18T08:58:35.8866667+00:00

    Use the Custom validation formula on column B like this (assuming row 4 is the active row when you set it up):

    =OR($A4<>"APPLE",$B4="NO")

    This formula is logically correct for the rule:

    • If A4 is not "APPLE", any value in B4 is allowed.
    • If A4 is "APPLE", then B4 must be "NO".

    If the combination APPLE in column A and NO in column B is being rejected, the most common causes are:

    1. Extra spaces or different text in A or B
      • APPLE might actually be APPLE (with a trailing space) or have different case/characters.
      • NO might be No, no, or NO , etc. Data validation compares exact text.
    2. Wrong active row when entering the formula
      When setting up data validation, ensure the active cell is the first data row in column B (for example B4) and the formula references that same row ($A4, $B4). Then apply the validation to the whole B range.
    3. Manual recalculation or formula errors
      If manual recalculation is on or there are formula errors in referenced cells, validation can behave unexpectedly. Ensure calculation is set to Automatic and there are no errors in the cells used by the validation.

    To reapply correctly:

    1. Select the B cells where the rule should apply (for example, B4:B100).
    2. Go to Data > Data Validation.
    3. In Allow, choose Custom.
    4. Enter: 
         =OR($A4<>"APPLE",$B4="NO")
    5. Configure the Error Alert as needed and click OK.

    Now the pair APPLE (in A) and NO (in B) should be accepted, and APPLE with anything other than NO in B should be rejected.

    References:

    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.