Highlight all duplicate cells ignoring blank cells

[Vivek2705]

Hii...

I wants to highlight all duplicate values in a column ignoring blank cells or cells containing zero..Tried conditional formatting>Highlight duplicate values but it also highlight all blanks/zero value cells as it considers them to be all duplicates...what is the solution?

[Keebellah]

Hi,
Change the conditional format formula and add the condition if not "" and if <> 0

[Vivek2705]

Hii..

I have not used formula in conditional formatting to highlight duplicates..its a inbuilt option in conditional formatting to do the same..So how I can use condition if not "" and if <> 0 while highlighting duplicates only..??

[Keebellah]

I know that's a built in option but then you use the option with a formula
I would expect you know that, take a look here
https://support.office.com/en-us/art...b-f1951ff89d7f
You will never learn if you don't try
We can always help if you get stuck, but you will remember it better if you resolve it yourself

[Glenn Kennedy]

See the attached sheet. I used this formula:

=COUNTIFS(A:A,A1,A:A,"<>0")>=2

and applied it to the light grey cells in A1:A20. See if it does what you want.

[Z-Yogi]

Here is how I made Excel duplicate check ignore blank cells. It also
works for ignoring pretty much ANY type of cell value you want to ignore.

This example shows how to make it ignore blank cells:
1. Select the range you wish to highlite dupes, and make the simple dupe rule as usual.
2. Select the same range, and select "Conditional Formatting > Manage Rules".
3. Select "New Rule > Use a formula ...".
4. If (for example) your selected range is "$C$3:$C$12" enter this formula: =C3=""
and then hit "Enter" (you are done! Leave formatting unset!)
5. The new rule will be placed above your older (check duplicate) rule.
Select the "Stop if True" check box to the far right of the new rule.
6. That's it! Done. If the cell is blank, the "check duplicate" rule is ignored.

[Condor2126]

1. Highlight all duplicates as you normally would
2. Create a new rule to format only cells that contain blanks and apply no formatting to these cells
3. Go to manage rules and for the blanks, check off "Stop if true"

[CharmedRose]

Yay! Just solved this too.

First: Select the column, then go to conditional formatting and select: Format only cells that contain > then choose Blanks (no format set).
Then: Create a new condition back in conditional formatting and select: Format only unique or duplicate values > then choose duplicate (choose the format u want say background yellow)
Finally (very important): Tick the "Stop IF True" next to the first condition: Cell contains a blank value

Hope this helps you too! Let me know

[CharmedRose]

Re: Highlight all duplicate cells ignoring blank cells

Yay

[Jeff2554]

All,

I have gone through this whole thread and tried various ways of doing this. I have six columns that I need formatted with duplicates being highlighted.
Every time I add conditional formatting the duplicates highlight as well seemingly no matter what I do.

Help would be much appreciated.
I attached the sheet with some example data on it.
Any duplicate numbers in columns G,I,K,H,P,R need highlighted.

Thanks in advance.

PS
I do not post very often, I do believe I have followed all rules and guidelines but please let me know if I need to fix anything.

[AliGW]

Administrative Note:

We are happy to help, however whilst you feel your request is similar or even the same as this thread, we have a rule that you open your own thread on the issue and do not piggy back another member's thread.

Please see Forum Rule #1 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread