How to show duplicates in excel

Answer

To show duplicates in Excel, you can use Conditional Formatting or formulas, depending on the requirement.

Using Conditional Formatting

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. In the Duplicate Values dialog box:

   • Ensure Duplicate is selected in the first dropdown.

   • Choose a formatting style (e.g., light red fill with dark red text).

5. Click OK.

Duplicates in the selected range will now be highlighted with the chosen format.

Variation for Macbook: The steps are the same, but the Conditional Formatting menu is under Format → Conditional Formatting.

Using a Formula

1. Select a blank column next to your data.

2. Enter the following formula for the first row (assuming your data is in column A starting at A2):  

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

3. Drag the formula down the column to apply it to all rows.

4. This will show "Duplicate" for repeated values and "Unique" for values that appear only once.

Using the Remove Duplicates Feature to Identify

1. Copy your original column to a new sheet or column to preserve data.

2. Go to the Data tab → Remove Duplicates.

3. Check the column(s) to analyze.

4. Excel will display a message indicating how many duplicates were removed and how many unique values remain.

Notes

• Conditional Formatting works for text, numbers, and dates.

• Formulas give more control if you want to create filters, reports, or further calculations based on duplicates.

• Conditional Formatting updates dynamically if you add or change data.

• Formulas allow sorting and filtering based on the "Duplicate" label.

This method highlights duplicates efficiently and is widely used in data cleaning and analysis tasks.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report