Skip to main content

Easily find duplicates in Excel: select, hide, identify, highlight

Spotting duplicates in your Excel sheets is key to keeping things tidy and correct. But, let's be honest, with so many cells and rows, this can be like finding a needle in a haystack. Lucky for us, Kutools for Excel’s Find Duplicates tool makes this way easier. With just a few clicks, you can promptly select, hide, identify, or highlight duplicates excluding their first appearances effortlessly. 

Pick the approach you prefer for step-by-step instructions on handling duplicate entries:


Video: Find duplicates in Excel


Select duplicates

In this section, as an example, I will select duplicate rows that have entries matching across all columns within the dataset, and exclude their first occurrences.

Step 1: Select the cell range from where to find duplicates

  • You can select a whole dataset as shown below.
  • If your data is too large, you can click on any single cell inside the dataset, Kutools will automatically select the dataset in the next step.

Step 2: Select Kutools > Find > Find Duplicates

Step 3: Specify settings in the Find Duplicates dialog box

  1. In the Columns section of the pop-up dialog box, leave all checkboxes selected to find duplicate rows with completely identical values across all columns.
  2. In the Operation Type section, select Select Duplicates to select the duplicate rows within your dataset.
  3. Click OK.

Result

As shown below, the duplicate rows within the dataset are selected, excluding their first occurrences.

Notes:

  • To select the entire rows containing duplicates as shown below, tick the Applies to the entire row checkbox.
  • To find duplicate rows with the same values in certain columns instead of all columns, check those columns only in the Columns section of the Find Duplicates dialog box. (The image below displays the result when only the Fruit column is checked, resulting in the selection of rows with duplicate fruit entries.)
  • If your data has column headers, check the My data has headers option, so that the header (first row) will not be considered for finding duplicates.
  • The feature supports undo Ctrl + Z.

Hide duplicates

In this section, as an example, I will hide the rows with duplicate entries that match entirely across all columns, keeping only their first occurrences visible.

Step 1: Select the cell range from where to find duplicates

  • You can select a whole dataset as shown below.
  • If your data is too large, you can click on any single cell inside the dataset, Kutools will automatically select the dataset in the next step.

Step 2: Select Kutools > Find > Find Duplicates

Step 3: Specify settings in the Find Duplicates dialog box

  1. In the Columns section of the pop-up dialog box, leave all checkboxes selected to find duplicate rows with completely identical values across all columns.
  2. In the Operation Type section, select Hide Duplicates to hide the entire rows where duplicates are found.
  3. Click OK.

Result

As shown below, the rows with duplicate values are hidden, excluding their first occurrences.

Notes:

  • To hide duplicate rows with the same values in certain columns instead of all columns, check those columns only in the Columns section of the Find Duplicates dialog box. (The image below displays the result when only the Fruit column is checked, which results in hiding rows with duplicate fruit entries.)
  • If your data has column headers, check the My data has headers option, so that the header (first row) will not be considered for finding duplicates.
  • The feature supports undo Ctrl + Z.

Identify duplicates

In this section, I will add a "Duplicate" mark to the right of the duplicate entries that are completely identical across all columns excluding their first appearances as an example.

Step 1: Select the cell range from where to find duplicates

  • You can select a whole dataset as shown below.
  • If your data is too large, you can click on any single cell inside the dataset, Kutools will automatically select the dataset in the next step.

Step 2: Select Kutools > Find > Find Duplicates

Step 3: Specify settings in the Find Duplicates dialog box

  1. In the Columns section of the pop-up dialog box, leave all checkboxes selected to find duplicate rows with completely identical values across all columns.
  2. In the Operation Type section, select Identify Duplicates to add a "Duplicate" mark to the right of the duplicate entries.
  3. Click OK.

Result

As shown below, the duplicate rows are identified with a "Duplicate" mark added to the Status column, excluding their first occurrences.

Notes:

  • To find duplicate rows with the same values in certain columns instead of all columns, check those columns only in the Columns section of the Find Duplicates dialog box. (The image below displays the result when only the Fruit column is checked, which results in identifying rows with duplicate fruit entries.)
  • If your data has column headers, check the My data has headers option, so that the header (first row) will not be considered for finding duplicates.
  • The feature supports undo Ctrl + Z.

Select and highlight duplicates

In this section, as an example, I will select and highlight duplicate rows that have entries matching across all columns within the dataset, and exclude their first occurrences.

Step 1: Select the cell range from where to find duplicates

  • You can select a whole dataset as shown below.
  • If your data is too large, you can click on any single cell inside the dataset, Kutools will automatically select the dataset in the next step.

Step 2: Select Kutools > Find > Find Duplicates

Step 3: Specify settings in the Find Duplicates dialog box

  1. In the Columns section of the pop-up dialog box, leave all checkboxes selected to find duplicate rows with completely identical values across all columns.
  2. In the Operation Type section, select Select and Highlight Duplicates to select and highlight the duplicate rows within your dataset. Tip: Duplicates will be highlighted in pink by default. To select your preferred color, click the drop-down arrow next to the color bar.
  3. Click OK.

Result

As shown below, the duplicate rows, excluding their first occurrences, are selected and highlighted with the selected fill color within the dataset.

Notes:

  • To select and highlight the entire rows containing duplicates as shown below, tick the Applies to the entire row checkbox.
  • To find duplicate rows with the same values in certain columns instead of all columns, check those columns only in the Columns section of the Find Duplicates dialog box. (The image below displays the result when only the Fruit column is checked: rows with repeated fruit entries get selected and highlighted.)
  • If your data has column headers, check the My data has headers option, so that the header (first row) will not be considered for finding duplicates.
  • The feature supports undo Ctrl + Z.