How to keep duplicates in excel

Answer

To keep duplicates in Excel, the goal is to identify rows or values that appear more than once and isolate them without removing them. Excel provides several methods to achieve this, depending on whether you want to highlight, filter, or extract duplicates.

Method 1: Using Conditional Formatting

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

2. Go to the Home tab.

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

4. In the dialog box, choose the formatting style to highlight duplicates, such as a color fill.

5. Click OK.

All duplicates in the selected range will now be highlighted. Unique values remain unformatted.

Method 2: Using the COUNTIF Function

1. Suppose your data is in column A (A2:A100).

2. In column B, enter the formula:

   =IF(COUNTIF($A$2:$A$100, A2)>1, "Duplicate", "Unique")

3. Drag the formula down for all rows.

All duplicate values will be marked as Duplicate while unique ones will show Unique.

Method 3: Filtering Duplicates

1. Select the range containing your data.

2. Go to the Data tab and click Filter.

3. Click the filter dropdown for the column you want to check.

4. Choose Filter by Color if using conditional formatting, or sort by the column containing the Duplicate labels from the COUNTIF formula.

This allows viewing only the duplicate values while keeping the rest intact.

Method 4: Extracting Duplicates to a Separate Sheet

1. Apply the COUNTIF method to mark duplicates.

2. Use Filter to select only rows marked as Duplicate.

3. Copy the filtered rows and paste them into a new sheet.

This creates a separate list of duplicates without removing any data from the original dataset.

Notes on Windows vs. Mac

• On Windows, Conditional Formatting and Filter options are under the Home and Data tabs, respectively.

• On Mac, the location is the same, but the interface may slightly differ; dropdown menus might appear as icons instead of text.

This approach ensures all duplicate values are retained for analysis, reporting, or validation purposes.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report