To highlight duplicate values, click Home > Conditional Formating > Highlight cells rules > Duplicate Values
select the content area, where you want to identify/remove data.
Highlight Duplicate Rows rather Single Cell
This time we will have to apply a few tricks because we will have to select rows based on the combination of cells in the row.
Step1
for example, we've three columns first, we'll have to combine all three columns with this manual formula:
=(A2&B2&C2)
apply this formula to each row on the same column.
=(A3&B3&C3)
=(A4&B4&C4)
=(A5&B5&C5)
this is how all first three column combinations will be looking as in the fourth column.
Step2
Select all the available rows and:
This time we'll create NEW Rule
Home > Conditional Formatting > New Rule...under new rules Click on Use a formula to determine which cells to format. and apply formula =COUNTIF($D$2:$D$11,D2)>1 as in the picture below:
in this special formula, we have to select all the columns we've combined from our rows columns and lock them to do that you can click F4 also then click on the cell you want to search in that whole combined combination.
Step3
Click on Format... Button at the right below side of the dialogue box and specify special formation to our duplicate rows.
the click OK button. that's it! you will get all repeated rows highlighted with the formation you will specify for your special formula.
Download the Excel file for Edits and Tests:
Download
