Excel Conditional Formatting - highlight duplicates and suppress empty cells and zeros

Question

I'm trying to create a Conditional Format that highlights duplicate entries in a worksheet.

I created a worksheet with multiple columns in order to identify duplicates in all the other worksheets in the excel file. Each column in the worksheet I'm using to identify duplicates contains content from a different worksheet in the excel file.

If I just use the default Conditional Format: Highlight Cells Rules > Duplicate Values, then all the blank cells (and zeros if there are any) are highlighted (see image 1).

I tried creating my own formula (see Image 2) but can't highlight with a background color without running into the same issue as in Image 1. The formulas I created to pull the values into the worksheet don't display zeros, so all I can do is use a font color with no background color to highlight duplicates.

So what I'm trying to achieve is to highlight duplicates with the same styling as in Image 1 but suppress the highlight if the cell is empty or has a value of zero.

Image 1 (Default Conditional Format: Duplicate Values)

Image 2 (My formula for highlighting duplicates)

If I use a background color I'll run into the same issue as in Image 1 above.

Answer 1

Re:  CF formula

Give this a try... 

=(COUNTIF($A$4:$AA$83,A4)>1)*(A4<>"")*(A4<>0)

'---

Jim Cone

Portland, Oregon USA

free Excel Date Picker add-in

https://goo.gl/IUQUN2 (Dropbox)

Answer 2

Hi,

So you want your formula to colour the background (not the font).  If that is the case, then in the conditional formatting dialog box, go to Format > Fill and there choose your desired colour.

Hope this helps.

Answer 3

James, You are THE MAN! The formula rule you suggested works perfectly.

I spent a couple hours looking for a solution and trying to get this to work, so I'm totally stoked to finally have the answer.

Thanks much for your help.

Answer 4

The problem was when I used a Fill color with the formula I created empty cells were considered duplicates and received the Fill color.

The formula James Cone provided is the solution I needed. Now I can use a Fill color and empty cells and cells with zero values are not considered duplicates.