How to correctly apply conditional formatting to two columns to identify unique values?

Question

HELP!! I am trying to identify unique values between two columns. Attached is a screenshot of the issue I am facing. For some reason, some of the cells are not being highlighted. I have my conditional formatting rule set up to apply to unique values in this range: =$B$2:$B$1048576,$F$2:$F$1048576

What am I doing wrong?? the entire file was too large to upload. Thanks in advance!!

SCREENSHOT 1.png

Answer 1

In Excel, “Unique values” in Conditional Formatting does not mean “values that appear in one column but not the other.” It means “values that occur only once within the combined range you selected.” This is why some of your cells are not being highlighted — they actually occur more than once somewhere in the full combined range.

When you select:

$B$2:$B$1048576,$F$2:$F$1048576

Excel treats that as one giant list, and highlights only the values that appear exactly once anywhere in that entire list. If a value appears twice (once in B and once in F), Excel considers it not unique.

To fix this, highlight values in B that do not exist in F (or vice-versa):

• Highlight items in Column B that are NOT in Column F
• Use a formula rule:
• Select B2:B1048576
• Conditional Formatting → New Rule → “Use a formula”

Formula:

=COUNTIF($F:$F,B2)=0

• Choose your format → OK.

Next, highlight items in Column F that are NOT in Column B

Repeat for Column F:

Select F2:F1048576

Conditional Formatting → New Rule → “Use a formula”

Formula:

=COUNTIF($B:$B,F2)=0

• Choose format → OK.

---

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Answer 2

I need to make sure those columns match up exactly so I am trying to weed out the ones that are wrong.

Hi. I'll take a chance here. You don't need to post 1 million rows. Just a few.

I selected B2:B8 and conditionally formatted as B2 <> F2.

Then, you do it a second time: Select F2:F8, with F2 <> B2.

.. I am trying to weed out the ones that are wrong.

Just my guess: I would doubt that conditionally formatting rows 753,429 to help identify them will help if you want to weed them out. You will never see them, and it appears there are too many to do by hand. Other methods would be better with a large file, and you want to remove certain rows.