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 Values - Microsoft Excel

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.

Identify / Highlight Duplicate Data in Microsoft Excel File

Download the Excel file for Edits and Tests:
Download

Tags:

Leave a Reply

Your email address will not be published. Required fields are marked *