See all How-To Articles

How to Find Duplicate Values in Excel & Google Sheets

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

 

filterduplicates showduplicates

 

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.

 

filterduplicates cond formatting

 

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

 

filterduplicates duplicates

 

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

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

 

filterduplicates filter

 

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

 

filterduplicates duplicates

 

  1. 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)

 

findduplciate contatenate

 

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

 

findduplicates copydown

 

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

 

findduplicates rows

 

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.

 

findduplicates highlighted

 

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.

 

findduplicates gs menu

 

  1. In Format Rules, choose Custom formula is and type the formula:
=COUNTIF($B$2:$B$20, $B2) >1

 

findduplicates gs conditional formatting

 

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

 

findduplicates showduplicates

 

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

See all How-To Articles