How to Show Only Duplicates in Excel & Google Sheets

This tutorial demonstrates how to show only duplicates in Excel and Google Sheets.

 

excel show duplicate values

 

Show Only Duplicates

Say you have the list of numbers pictured below and want to display only duplicate values.

 

show only duplicates initial data

 

The numbers 5, 2, and 11 are listed twice in Column B. To display only those values, you can use conditional formatting to highlight the cells with duplicate values, then filter by color to extract them.

  1. First, create a conditional formatting rule for duplicate values in the list.
    Select the range with duplicates (here, B2:B12) and in the Ribbon, go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.

 

conditional formatting duplicate values

 

  1. In the pop-up window, leave the default options (Duplicate and Light Red Fill with Dark Red Text) and click OK.

 

conditional formatting duplicate values 2

 

As a result, all cells with duplicate values are highlighted in red.

 

conditional formatting duplicate values 3

 

  1. Next, turn on filtering. Click on any cell in the data range (B1:B12), and in the Ribbon, go to Home > Sort & Filter > Filter.

 

turn on filter

 

  1. Click on the filter button next to Number (cell B1), go to Filter by Color, and choose red.

 

filter by color

 

As a result of this step, only cells with duplicate values are displayed.

 

filter by color 2

 

  1. Now copy only the filtered values to Column C.
    Select all filtered cells and right-click in the selected area, and click Copy (or use the keyboard shortcut CTRL + C).

 

copy visible cells 3

 

  1. Right-click cell C2 and choose Values under Paste Options.

 

copy visible cells 4

 

Finally, all duplicate values from Column B, are copied into Column C.

 

show duplicate values final data

 

Show Only Duplicates in Google Sheets

Let’s use the same example to explain how to show only duplicates in Google Sheets.

  1. Select the data range with numbers (B2:B12) and in the Menu, go to Format > Conditional formatting.

 

google sheets conditional formatting

 

  1. In the Conditional Formatting window, (1) for Format rules choose Custom formula is, then (2) enter the formula:
=COUNTIF($B$2:$B$12,$B2) > 1

This formula checks, for each cell, how many times that value appears in the range B2:B12. If it appears more than once, that means there are duplicate values for the cell, and it should be highlighted in red.
Then (3) set red as the fill color and (4) click Done.

 

google sheets conditional formatting 2

 

When this conditional formatting rule is applied, all duplicate values have a red background color.

 

google sheets conditional formatting 3

 

  1. Now to turn on filtering, click on cell B1 and in the Menu go to Data > Create a filter.

 

google sheets create filter

 

  1. Click on the (1) filter button next to Number (cell B1), (2) go to Filter by color, (3) click Fill Color, and (4) choose red (#EA4335).

 

google sheets filter by color

 

Now only duplicate values, highlighted in red, are displayed. All other cells are hidden.

 

google sheets filter by color 2

 

  1. To copy the filtered cells, select them and right-click anywhere in the selected range, then click Copy (or use the keyboard shortcut CTRL + C).

 

google sheets copy cells

 

  1. Right-click in cell C2, select Paste special, and choose Paste values only (or use the keyboard shortcut, CTRL + SHIFT + V).

 

google sheets copy cells 2

 

As a result, only duplicate values are copied into Column C.

 

google sheets final data