How to Find Duplicate Values in Excel & Google Sheets

This tutorial demonstrates how to find duplicate values in Excel and Google Sheets.

 

 

Find Duplicate Values in a Column

1. Select the range of cells that contain duplicates and then, in the Ribbon, go to Home > Styles > Conditional Formatting > Highlight Cells Rules > Duplicate Values.

 

 

2. Select Light Red Fill with Dark Red Text and then click OK.

 

 

You can then use a filter to just show the duplicate values.

1. In the Ribbon, go to Home > Editing > Sort & Filter > Filter.

 

 

2. Select Light Red Fill with Dark Red Text and then click OK.

 

 

3. All the duplicate values are highlighted in red.

Find Duplicate Rows

To find duplicate rows with conditional formatting, first Concatenate the values in the rows together, and then use conditional formatting on the concatenated cell.

1. In the cell to the right of your data, use the CONCATENATE Function to join the values in each cell together.

=CONCATENATE(B3,C3,D3)

 

 

2. Copy the formula down to the rest of the rows.

 

 

3. Then, highlight the concatenated cells, and in the Ribbon, go to Home > Styles > Conditional Formatting > Highlight Cells Rules > Duplicate Values.

 

 

Select Light Red Fill with Dark Red Text and then click OK. Any duplicate value is now highlighted enabling you to easily see the duplicated rows.

 

 

It is also possible to use other functions like VLOOKUP or MATCH to find duplicate values.

How to Find Duplicate Values in Google Sheets

To find duplicate values in Google Sheets, use conditional formatting with a custom formula.

1. Select the range of cells that contain the duplicates, and then, in the menu, go to Format > Conditional Formatting.

 

 

2. In Format Rules, choose Custom formula is and type the formula:

=COUNTIF($B$2:$B$20, $B2) >1

 

 

Select the formatting style and then click Done. All duplicate values are now highlighted.

 

 

You can also use this formula method in conditional formatting in Excel to find duplicate values.