I have a workbook with 3 sheets. Sheet "PS", column A contains a list of player names that stays static. Each week, each person in column A sends me a name from an available list. They are allowed to use each name only one time per year. The first name they send I put in column B, the second in column D, the next in column F, etc. Sheet "Data" has each player in a column. I have a formula (=PS!$B$4) etc. in each cell going down to pull the name from sheet "PS" corresponding to that week. I want to easily see duplicates, (which is against the games rules). I can use conditional formatting to change the font color and that does work. I have my Excel set to not show anything in 0 value cells. Because of the formulas, each cell below the entered names has a zero value which makes it a duplicate. I want to highlight the cells that contain a duplicate name. I just can't figure out the formula so I don't highlight all the cells with zeros as well. I'm very new to Excel so I apologize for the misuse of the proper terminology. I can certainly attach this workbook if it would help.

Thank you in advance for your help.


To my knowledge there is no built in function to determine if a cell contains a formula or a value.

One way to go is to create a simple UDF in VBA to make this determination for you, such as

Function IsFormula(rng As Range) As Variant
    IsFormula = rng.Cells(1, 1).HasFormula
End Function

and use this function in your conditional format


If I'm understanding you correctly then you can just add another condition to your conditional formatting e.g. for highlighting duplicates in column B try this formula


That will highlight any entry in column B that has a count of more than 1 (i.e. there's a duplicate) but also isn't zero, hence not formatting your zeroes

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.