Forum Discussion

Tracy7212's avatar
Tracy7212
Tin Contributor
Oct 07, 2026

Find mismatches

I want a way to flag or highlight a cell in Column B if the content does not exactly match the content on the same row in Column A. My spreadsheet has hundreds of rows of data, and just a few mismatches, and I'd like an easy way to find them. Seems like Conditional Formatting would offer an option, but I don't see one. Is there a way to do this?

5 Replies

  • Tracy7212's avatar
    Tracy7212
    Tin Contributor

    I simplified all my data and removed all the offending numerals - I hope this won't be rejected.  Harun, thank you, but I used Laura's instructions because they look simpler and I am not a sophisticated Excel user at all.  I really need step-by-step help.  Thanks in advance.

    Here is my data:

    Using Laura's instructions, I selected cells B2 to B16 and entered this formula:

    =$A2:$A16<>$B2:$B16   

     and got these results as expected:

    Then I highlighted cells B2 and C2 and right-clicked Insert then Shift cells down and got this:

    The results I want would have no fill in B3 and B4.  The rest of Column B would have the pink fill.

    My goal is as I insert blank row(s) in Column B and C, the rule compares the values as they exist after the insertion.  If it worked the way I want, the final results after I insert all the necessary blank rows would look like this:

    Is there a way to do this?  There are times I use the boilerplate duplicate value conditional formatting, and inserting blank rows successfully readjust all the ones below it.  But this is a more complicated operation.  Any help is much appreciated.

  • Tracy7212's avatar
    Tracy7212
    Tin Contributor

    I guess there was a forbidden number combo hidden in my data that caused it to be rejected, so here is bare-bones clean data:

     

  • Tracy7212's avatar
    Tracy7212
    Tin Contributor

    I have tried 3 times to post followup questions but I keep getting this: "Content Not Permitted, two-four-seven is not allowed in the community. Remove it and try again." (Except it uses the numerals, not the spelled-out numbers.)  I don't know what it is that's triggering this denial, so I will describe what I want to do.  I have Column A, ID numbers, Column B, the same type of ID numbers, and Column C, data that goes with the Column B ID numbers.  The whole universe of ID numbers is in Column A.  Most of those facilities have data for Column C, but some do not.  I want to be able to spot the mismatches between Column A and B, and insert blank cell(s) in Column B and C such that Column A and B match.  When I do that, I want the entire rest of Column B to automatically adjust to meet the rule, in which mismatches are highlighted by color fill.  Does that make sense?  

  • LauraBennett's avatar
    LauraBennett
    Iron Contributor

    Yes—use a formula-based conditional formatting rule.

    1. Select your data in column B, for example B2, with B2 as the active cell.
    2. Go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
    3. Enter:=NOT(EXACT($A2,$B2))
    4. Click Format, choose a fill color, and click OK.

    Each B cell will be highlighted if it doesn’t exactly match column A on the same row. EXACT catches differences in capitalisation and spaces, including trailing spaces.

    If you don’t care about upper/lowercase differences, use this instead:

    =$A2<>$B2

    Make sure the row number in the formula matches the first row of your selected range. These rules compare cell contents, not visual formatting such as font or fill color.

  • Harun24HR's avatar
    Harun24HR
    Silver Contributor

    There are few of a ways to do that. By CF

    =$B2<>$A2

    To get Mismatch values list, you may try-

    =FILTER(B2:B10,A2:A10<>B2:B10)
    =FILTER(B2:B10,COUNTIFS(A2:A10,B2:B10)=0)