How to highlight cells with duplicate values using Excel's inbuilt Conditional Formatting rules, specifically to compare `<` or `>`?
OS: Windows 10 Pro Version 20H2 (OS Build: 19042.1165)
Application: Microsoft Excel for Microsoft 365 Version 2107 (Build 14228.20250)
When using Excel's inbuilt Conditional Formatting rules to highlight cells with duplicate values, specifically to compare < or >, it doesn't seem to work on my end.
I tried searching the internet about this issue to no avail.
Using the table below to visualize an Excel spreadsheet,
| Value | Value |
|---|---|
| < | < |
| > | > |
I highlighted cells A1:B3, clicked on Home Tab>Conditional Formatting>Highlight Cells Rules>Duplicate Values…,>OK. Regardless if I choose Duplicate or Unique on the dropdown options, it just won't work here. Only the first row is being formatted.
It works fine though when using conditional formatting formula.
I'd like to know, with your help, if this issue is isolated, or am I just missing something here?
Thank you all very much..
I'm able to achieve what I believe you are asking for.
I chose the second Rule Type: Format only cells that contain
The editing section changes to have a heading of "Format only cells with" and a row of inputs with the first of four boxes containing "Cell Value". For my test, I chose "greater than" for the second box (starts out with "between"), whereupon the third and fourth boxes became a single box that is intended to take a range.
Well, it won't take a range, not even if you make it a relative one (no $ signs).
But I noted it WOULD take a relative address for a single cell and NOT convert it to an absolute reference. So... maybe that would lead to it being taken as a range in practical operation. And yes, it did.
Your example of A1:B3 suggested to me you wanted the comparison done between, in the example, A1:A3 and B1:B3, presumably row by row. So I entered "A1" (no dollar signs, so relative) for the third box.
And it works. 10 values entered in column A, 10 in column B, and only the direct comparisons (A1 vs. B1, etc.) have the rule applied. By that I mean the relative address DID work: for row 2, the comparison is B2 vs. A2, NOT B2 vs. the A1 I entered.
That should do what you need. There are eight comparisons available in pairs: between/not between, equal to/not equal to, and so on for greater than or equal to and less than or equal to.