How to copy duplicates to another sheet

Answer

To copy duplicates to another sheet in Excel, follow these steps carefully:

Step 1: Identify Duplicates

1. Select the range of cells you want to check for duplicates.

2. Go to the Home tab on the ribbon.

3. Click Conditional Formatting → Highlight Cells Rules → Duplicate Values.

4. Choose a formatting color to highlight duplicates and click OK.

5. Duplicates in your selection will now be highlighted.

Step 2: Filter Duplicates

1. Select the column with highlighted duplicates.

2. Go to the Data tab and click Filter.

3. Click the drop-down arrow in the column header.

4. Choose Filter by Color and select the color used for duplicates.

5. Only duplicate values will now be visible.

Step 3: Copy Duplicates to Another Sheet

1. Select all visible filtered duplicate cells.

2. Press Ctrl + C (Windows) or Command + C (Mac) to copy.

3. Go to the sheet where you want to paste the duplicates.

4. Select the starting cell and press Ctrl + V (Windows) or Command + V (Mac).

5. Duplicates are now copied to the new sheet.

Step 4: Optional – Remove Duplicates from Original Sheet

1. Select the original range.

2. Go to Data → Remove Duplicates.

3. Confirm the columns to check and click OK.

4. Only unique values will remain in the original sheet.

Notes

• Filtering by color ensures you copy only duplicates without affecting unique values.

• Conditional formatting does not delete duplicates; it only highlights them.

• Excel for Mac uses the Command key instead of Ctrl for shortcuts.

This method works for both single-column duplicates and multi-column duplicates by selecting all relevant columns before applying conditional formatting.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report