Excel help to identify duplicate values with a funciton or conditional format

BajanPaul used Ask the Experts™
Hello to all excel gurus,
I am trying to write a formula that will check 3 columns for duplicate data and return a "Duplicate" value.  I am hoping to do this with either a formula or conditional formatting.

The existing formula I have is as follows:

I know this will only identify duplicate values in one column, how do I expand the formula to include additional criteria.

Please see my attached excel file.

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
JPIT Director

I'm not certain if it can be done with a single formula or not but I don't believe so. What I would do is place your formulas in concurrent columns K:M or wherever you want and if you really want the result in on column you could hide K:M and place a formula in "N" to merge or concatenate the data =K1 & " " & L1 & " " & M1
You can create a new column that is a concatenation of the columns you want to check as duplicates.

I added new column as column I... which is concatenation of B/C/E
Then used same logic to check for Dups in Column I.
Top Expert 2010

This works for me:

1) Make all formatting "plain"

2) Select A2:H19

3) Add a formula-based Conditional Formatting rule using this formula:


Choose formatting for this rule as desired.

Any rows within that range for which there are duplicates across all three columns will receive the formatting.


Worked as suggested.  Thanks for the help!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial