Highlight duplicate rows in Excel

Miasha

New Member
Joined
Oct 26, 2010
Messages
15
I am trying to highlight rows in my Excel spreadsheet that contain the same data. I have tried using 'Conditional Formatting,' 'Highlight Cells Rules,' 'Duplicate Values' option. However, this highlights any cells that contain the same information. I only want the entire rows highlighted that match exactly. For example:

Mary Smith 1234 Main St.
Jack Smith 349 Willow Rd.
Mary Rogers 16th St.
Jack Smith 349 Willow Rd.

Using the above stated option gives me the following results which I do not want:

Mary Smith 1234 Main St.
Jack Smith 349 Willow Rd.
Mary Rogers 16th St.
Jack Smith 349 Willow Rd.

I am using Excel 2010. Thanks for your input.
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Boller

Banned
Joined
Apr 11, 2006
Messages
2,328
Assuming you have a fixed number of columns that can contain data, use another column (hidden if you like) to concatenate that data from all the other data columns, and then use conditional format on the concatenated column.
 

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
How many criteria would define a "duplicate row"? It is possible, but very ineffecient, to test an ENTIRE row against all other rows.
 

Miasha

New Member
Joined
Oct 26, 2010
Messages
15
Assuming you have a fixed number of columns that can contain data, use another column (hidden if you like) to concatenate that data from all the other data columns, and then use conditional format on the concatenated column.

Thank you. This will work!
 

Watch MrExcel Video

Forum statistics

Threads
1,109,204
Messages
5,527,405
Members
409,760
Latest member
zeeshansyed

This Week's Hot Topics

Top