How to apply duplicate conditional formatting rule

Answer

To apply a duplicate conditional formatting rule in Excel, follow these steps:

1. Select the Range
   Highlight the cells where you want to check for duplicates. This can be a single column, multiple columns, or an entire table.

2. Open Conditional Formatting
   Go to the Home tab on the ribbon. Click Conditional Formatting in the Styles group.

3. Choose Highlight Cell Rules
   Click Highlight Cells Rules and select Duplicate Values from the dropdown menu.

4. Set Formatting Options
   A dialog box will appear.

   • Ensure Duplicate is selected in the first dropdown.

   • Choose the formatting style from the second dropdown, such as a color fill or bold text. You can click Custom Format to choose a specific font, border, or fill color.

5. Apply the Rule
   Click OK. All duplicate values in the selected range will now be highlighted according to the format you chose.

6. Edit or Manage the Rule (Optional)
   To modify the rule, go back to Conditional Formatting → Manage Rules. Here you can:

   • Edit the rule to change the range or formatting.

   • Delete the rule.

   • Change the order if multiple rules exist.

Notes for Mac vs Windows

• On a Mac, the path is similar: Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values.

• Some Mac versions may label Duplicate Values as Duplicates.

Example

If you have a list of product IDs in column A from A2:A20 and want to highlight duplicates:

1. Select A2:A20.

2. Go to Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values.

3. Choose a red fill color.

4. Click OK.
   Any product ID that appears more than once will now be highlighted in red.

This method helps quickly identify repeated entries in large datasets without using formulas.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report