How to make excel highlight duplicates

To make Excel highlight duplicates, use Conditional Formatting through the built-in Duplicate Values rule (Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values) or create custom formula-based rules using COUNTIF or COUNTIFS for advanced control. These methods dynamically update as data changes and work across single columns, multiple columns, or entire rows.

What methods does Excel offer to highlight duplicates?

Excel provides three primary methods to highlight duplicates, ordered by popularity and ease of use:

1. Conditional Formatting (most popular): The fastest and most intuitive method uses Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values, which automatically shades all instances including the first occurrence. Custom rules using COUNTIF formulas like =COUNTIF($A$2:$A2,$A2)>1 provide control for excluding first occurrences. This method dynamically updates as data changes.
2. Formula-based highlighting: Helper columns with formulas like =IF(COUNTIF(A:A,A2)>1,"Duplicate","Unique") label duplicates, then Conditional Formatting applies visual highlighting to those labels. Multi-column checks use COUNTIFS (e.g., =COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2)>1) for complex data requiring precise logic.
3. Power Query and Remove Duplicates: Power Query (Data > Get Data > From Table/Range) loads data, detects duplicates via Group By or Remove Duplicates, and outputs highlighted results. Microsoft's Remove Duplicates tool (Data tab) identifies duplicates but does not highlight them directly. These methods suit large-scale cleaning and automated, repeatable processes.

How to use the built-in duplicate values rule

To use the built-in Duplicate Values rule, access Conditional Formatting from the Home tab and select a preset highlighting style. This feature identifies all duplicate entries including the first occurrence and updates dynamically when data changes.

Apply the rule

1. Select the range of cells to check, such as a column of IDs or values.
2. Navigate to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
3. In the dialog box, choose a format like Light Red Fill with Dark Red Text (default) or select Custom Format for specific colors.
4. Click OK to apply.

Key characteristics

The rule highlights all duplicates instantly based on exact matches, ignoring case by default. The highlighting updates automatically when data changes. To check entire rows, select the full range before applying. Edit or clear rules through Conditional Formatting > Manage Rules.

How to highlight duplicates excluding the first occurrence

To highlight duplicates while excluding the first occurrence, create a custom Conditional Formatting rule using a COUNTIF formula with a growing range reference. This approach distinguishes initial entries from subsequent repeats.

Create the custom rule

1. Select the range starting from the first data cell (e.g., A2:A100), ensuring the active cell is A2 for relative referencing.
2. Go to Home > Conditional Formatting > New Rule.
3. Select "Use a formula to determine which cells to format."
4. Enter the formula =COUNTIF($A$2:$A2,$A2)>1 where the $ anchors the start of the range and $A2 creates a growing range as the formula moves down.
5. Click Format, choose a fill color like red, and click OK.
6. Click OK again to apply the rule.

Adjust the range

The formula =COUNTIF($A$2:$A2,$A2)>1 starts checking from row 2 and expands downward. The first occurrence returns FALSE (count equals 1), while subsequent matches return TRUE (count exceeds 1). Edit the rule via Manage Rules when data expands beyond the original selection.

How to highlight duplicate rows across multiple columns

To highlight duplicate rows across multiple columns, use COUNTIFS in a custom Conditional Formatting rule with criteria for each column. This method identifies rows where all specified values match another row in the dataset.

Create the multi-column rule

1. Select the full range of rows, such as A2:D100, ensuring the active cell is in row 2 (e.g., A2).
2. Go to Home > Conditional Formatting > New Rule.
3. Select "Use a formula to determine which cells to format."
4. Enter =COUNTIFS($A$2:$A$100,$A2,$B$2:$B$100,$B2,$C$2:$C$100,$C2,$D$2:$D$100,$D2)>1
5. Use absolute references ($) for ranges and relative references for row checks.
6. Extend criteria for additional columns as needed.
7. Select a fill color and click OK twice to apply.

Include or exclude first occurrences

The formula above highlights all duplicate rows including first occurrences. To exclude first occurrences, use growing ranges: =COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2,$C$2:$C2,$C2,$D$2:$D2,$D2)>1. For dynamic tables, use structured references like Table1[Column1] in formulas to expand the rule automatically.

What formatting options are available for highlighting duplicates?

Excel provides preset styles and fully customizable formatting options for duplicate highlighting, accessible during rule creation through the Format button or dialog presets.

Preset color options

The Duplicate Values dialog offers 10+ preset combinations:

1. Light Red Fill with Dark Red Text (default)
2. Yellow Fill with Dark Yellow Text
3. Green Fill with Dark Green Text
4. Light Red Fill
5. Red Text
6. Red Border

Select presets directly from the dropdown before clicking OK. Custom formula rules display similar presets after entering the COUNTIF or COUNTIFS formula.

Custom formatting options

Click Custom Format in the dialog to open the Format Cells window with these tabs:

1. Font: Choose text color, style (bold, italic), underline, strikethrough, or font type.
2. Border: Set line style, color, and placement such as outline or specific edges.
3. Fill: Select solid colors, gradients, or patterns using the color picker or standard palette.
4. Number: Adjust decimal places or number format (less common for duplicate highlighting).
5. Alignment: Modify text wrap or orientation.

Layer multiple rules

Create separate rules for different duplicate scenarios (e.g., first occurrences in blue, subsequent repeats in red). Prioritize rules through Manage Rules > Move Up/Down. Enable "Stop If True" to prevent lower-priority rules from applying when a higher-priority rule matches.

How to use helper columns to highlight duplicates

To use helper columns for highlighting duplicates, create a formula column that flags duplicates with TRUE or FALSE, then apply Conditional Formatting based on those results. This method provides flexibility for complex multi-column checks and allows filtering or sorting by duplicate status.

Create the helper column

1. Insert a new column next to the data (e.g., column E for data in columns A through D starting at row 2).
2. Enter =COUNTIF($A$2:$A$100,A2)>1 in E2 for single-column duplicate detection.
3. For row-level duplicates across multiple columns, use =COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)>1 instead.
4. Copy the formula down to cover all data rows.
5. Results display TRUE for duplicates and FALSE for unique values.

Apply highlighting based on the helper column

1. Select the original data range (e.g., A2:D100).
2. Go to Home > Conditional Formatting > New Rule.
3. Select "Use a formula to determine which cells to format."
4. Enter =$E2=TRUE where E is the helper column.
5. Choose a fill color or formatting style.
6. Click OK to apply.

Review duplicates

Filter or sort the helper column for TRUE values to isolate and review all duplicate entries. The helper column remains editable for adjusting duplicate logic without modifying the Conditional Formatting rule. Delete the helper column after completing your review, or hide it to preserve the underlying data.