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. Leave the two default drop downs: Duplicate and Light Red Fill with Dark Red Text, and then click OK.

All the duplicate values are highlighted in red.

Find Duplicate Rows

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

1. In the cell directly 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 select the concatenated cells, and in the Ribbon, go to Home > Styles > Conditional Formatting > Highlight Cells Rules > Duplicate Values.

4. Choose Light Red Fill with Dark Red Text, and then click OK. Any duplicate values are highlighted, so you can easily see the duplicate rows.

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

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 the Conditional format rules window, choose Custom formula is and type in the formula:

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

3. Choose a formatting style, and then click Done. All duplicate values are now highlighted.

Tip: You can the same formula method above in Excel.