How to Highlight Duplicates but Keep One in Excel (4 Methods)

We have a large Excel worksheet that contains information about several sales representatives of the Armani Group. The name of the Product and the Product ID are given in Columns D and C.

Method 1 – Use the Conditional Formatting Command 

Steps:

• Select cells that we want to highlight duplicates but keep one. We selected cells from B5 to B16.
• From your Home tab, go to:

Home → Styles → Conditional Formatting → Highlight Cells Rules → Duplicate Values

• A dialog box named Duplicate Values pops up.
• From the Duplicate Values dialog box, select the Duplicate values with Light Red Fill with Dark Red Text option from the Format cells that contain.
•  Press OK.

• After pressing OK, you will be able to highlight the duplicate value that has been given in the below screenshot.

• We will keep one duplicate.
• To do that, from your Data tab, go to:

Data → Data Tools →  Remove Duplicates

• A warning dialog box named Remove Duplicates Warning will appear.
• From that warning box, select Continue with the current selection.
• Press Remove Duplicates.

• A Remove Duplicates window pops up.
• From that window, press OK.

• After completing the above process, you will be able to highlight duplicates but keep one that has been given in the screenshot below.

Read More: How to Highlight Duplicates in Two Columns Using Excel Formula

Method 2 – Highlight Duplicates or Unique Values Using Conditional Formatting

Steps:

• Select cells from B5 to B16.
• From your Home tab, go to:

Home → Styles → Conditional Formatting → Highlight Cells Rules → Duplicate Values

• A dialog box named Duplicate Values will appear.
• From the Duplicate Values dialog box, select the Unique values with Green Fill with Dark Green Text from the drop-down box.
• Press OK.

• While pressing OK, you will be able to highlight the unique values that have been given in the below screenshot.

Method 3 – Using a New Rule of Conditional Formatting

3.1 Duplicates without First Occurrence

Steps:

• Select cells from B5 to B16.
• From your Home tab, go to:

Home → Styles → Conditional Formatting → New Rule

• A dialog box named New Formatting Rule will appear.
• Follow the steps for the New Formatting Rule dialog box.
• Select Use a formula to determine which cells to format from the Select a Rule Type:
• Enter the below formula in the Format values where this formula is true:. The COUNTIF function is:

• Press the Format option.

• After clicking on the Format option, a Format Cells dialog box pops up.
• From that dialog box, select the Fill.
• Select any color from the Background Color menu. We have chosen a light golden color.
• Click OK.

• You will go back to the New Formatting Rule dialog box.
• Click OK.

• While completing the above process, you will be able to highlight duplicates without the first occurrence given in the screenshot below.

3.2 Second Occurrence of Duplicates Only

Steps:

• Select cells D5 to D16.

• Enter the below formula in the box:

• After repeating the sub-method, you will get your desired output, which has been given in the screenshot below.

3.3 Values Occurring Third Time

Steps:

• Select cells from B5 to B16.
• From your Home tab, go to:

Home → Styles → Conditional Formatting → New Rule

• A dialog box named New Formatting Rule will appear.
• Follow the steps for the New Formatting Rule dialog box.
• Select Use a formula to determine which cells to format from the Select a Rule Type:
• Enter the below formula in the Format values where this formula is true:

• Press the Format option.

• After clicking on the Format option, a Format Cells dialog box pops up.
• From that dialog box, select the Fill.
• Select any color from the Background Color menu. We have chosen a light golden color.
• Click OK.

• You will go back to the New Formatting Rule dialog box.
• Click OK.

• After completing the above process, you will be able to highlight duplicates without the third occurrence that has been given in the below screenshot.

Method 4 – Using Multiple Columns to Highlight Duplicates but Keep One

Steps:

• Select cells B5 to D16.

• Perform similarly according to sub-method 3.1 except the formula applied in the Format values where this formula is true: Enter the below formula in that type box:

• After repeating the sub-method, you will get your desired output, which has been given in the screenshot below.

Read More: How to Highlight Duplicates in Multiple Columns in Excel

Things to Remember

To acquire the desired outcome, you must carefully follow all of the procedures and make any required adjustments to the cell references.

Download the Practice Workbook

Download this workbook practice.

Related Articles

• Highlight Cells If There Are More Than 3 Duplicates in Excel
• How to Highlight Duplicates in Excel with Different Colors
• How to Highlight Duplicates in Two Columns in Excel
• How to Highlight Duplicate Rows in Excel
• Highlight Duplicates across Multiple Worksheets in Excel
• [Fix:] Highlight Duplicates in Excel Not Working

<< Go Back to Duplicates in Excel | Learn Excel