You need to find and mark repeated entries in your Excel data quickly. Conditional formatting is a built-in feature that applies visual formats to cells based on rules. This article explains how to use it to highlight duplicate values in a single column or across multiple columns.
Key Takeaways: Highlight Duplicate Values in Excel
- Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values: The primary method to apply a default color format to all duplicate entries in a selected range.
- Home > Conditional Formatting > New Rule > Use a formula: Creates a custom rule for more complex scenarios, like highlighting duplicates across two columns.
- Home > Conditional Formatting > Manage Rules: Opens a dialog to edit, delete, or change the priority of all formatting rules applied to the sheet.
Understanding Conditional Formatting for Duplicates
Excel’s conditional formatting feature automatically changes a cell’s appearance when its content meets specific criteria. The dedicated “Duplicate Values” rule is designed to compare all cells within a selected range. It then applies a fill color, font color, or border to every cell that has a matching value elsewhere in that range.
You must select the data range before applying the rule. The rule evaluates values, not formulas. If a cell contains a formula, its resulting value is used for the duplicate check. The formatting is dynamic and will update immediately if you change data that creates or removes a duplicate.
What Counts as a Duplicate
The rule is case-insensitive. “TEXT” and “text” are considered duplicates. It also treats numbers and their text equivalents as different. The value “100” in a number-formatted cell is not a duplicate of “100” in a text-formatted cell. Leading or trailing spaces can cause mismatches, so data cleaning might be needed first.
Steps to Highlight Duplicates in a Single Range
This is the standard method for finding repeats within one list, like a column of email addresses or invoice numbers.
- Select your data range
Click and drag to select the cells you want to check. This can be a single column, a row, or a rectangular block of cells. - Open the Conditional Formatting menu
Go to the Home tab on the ribbon. In the Styles group, click the Conditional Formatting button. - Choose the Duplicate Values rule
In the dropdown menu, hover over Highlight Cells Rules. Then select Duplicate Values from the submenu. - Set the formatting style
A dialog box will appear. The left dropdown should already say “Duplicate.” Use the right dropdown to select a preset format like “Light Red Fill with Dark Red Text” or “Yellow Fill with Dark Yellow Text.” You can also click Custom Format to choose your own font, border, and fill colors. - Apply the rule
Click OK in the dialog box. Excel will immediately highlight all duplicate cells in your selected range with the chosen format.
Using a Formula to Highlight Complex Duplicates
For more control, use a formula-based rule. This is necessary for tasks like highlighting a value in Column A only if it also appears in Column C.
- Select the target range
Select the cells you want to format. For example, select cells A2:A100. - Create a new rule
Go to Home > Conditional Formatting > New Rule. In the New Formatting Rule dialog, select “Use a formula to determine which cells to format.” - Enter the formula
In the formula box, type a logical test. To highlight a value in A2 that appears anywhere in column C, use: =COUNTIF($C:$C, $A2)>0. The dollar signs lock the column references. The formula tests each cell in your selected range (starting with A2) against the entire column C. - Set the custom format
Click the Format button. In the Format Cells dialog, go to the Fill tab and choose a highlight color. Click OK. - Finish and apply
Click OK in the New Formatting Rule dialog. The selected cells in column A will now be highlighted if their value is found in column C.
Common Mistakes and Limitations to Avoid
Conditional Formatting Not Highlighting Correct Duplicates
Check for extra spaces or different data types. Use the TRIM function to remove spaces and the VALUE function to convert text to numbers. Ensure your selection includes all cells you intend to compare. The rule only works within the selected range.
How to Highlight Only the Second or Third Duplicate
The built-in rule highlights all duplicates. To highlight only subsequent occurrences, use a formula rule. For a list in A2:A100, select that range and create a new rule with this formula: =COUNTIF($A$2:A2, A2)>1. This counts occurrences from the top of the range down to the current row, highlighting only the second, third, etc., instance.
Removing or Editing Duplicate Highlighting
Go to Home > Conditional Formatting > Manage Rules. In the Conditional Formatting Rules Manager dialog, select the rule you want to change or delete. Use the Delete Rule button to remove it, or the Edit Rule button to change its format or range.
Built-in Rule vs. Formula Rule: Key Differences
| Item | Built-in Duplicate Values Rule | Custom Formula Rule |
|---|---|---|
| Ease of use | Fast, one-click setup with preset formats | Requires writing an Excel formula |
| Flexibility | Highlights all duplicates within one selected range only | Can compare across separate ranges or sheets |
| Logic control | Highlights every matching cell | Can highlight first, last, or nth occurrence only |
| Format options | Limited to a few presets or basic custom format | Full access to all Format Cells dialog options |
| Best for | Simple, quick duplicate checks in one data set | Complex scenarios, cross-reference checks, specific duplicate instances |
You can now use conditional formatting to visually identify duplicate data in your worksheets. Try using the formula method to highlight rows where a customer ID in one table appears in a separate log. For advanced analysis, combine the COUNTIF formula with the AND function to highlight duplicates that also meet another condition, like a value over a specific amount.