Quick answer: To highlight duplicates in Excel, select your data, go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values, pick a color, and click OK.

If you want to know how to highlight duplicates in Excel for full rows, other sheets, or later occurrences only, a formula rule is the way to go. This guide covers both methods, plus the fixes for the most common problems.

What Does “Highlight Duplicates” Mean in Excel?

It means using Excel’s Conditional Formatting to color cells or rows that contain repeated values. The color is applied automatically, so you don’t need to scan or sort your data by hand.

Highlighting is not the same as deleting. Your data stays exactly as it is. You only add a visual flag so you can review the repeats and decide what to do with them.

Why You’d Want to Highlight Duplicates

You highlight duplicates to catch mistakes before they spread. Repeated customer emails, product IDs, or transaction records can break reports and give wrong totals.

Common reasons include:

• Cleaning and validating data
• Checking a report or dashboard before sharing it
• Saving time compared with manual checks

Surveys of Excel users suggest data cleaning, including duplicate handling, takes about 30% of typical spreadsheet time. A quick color rule is a small fix with a big payoff.

The Easiest Method: Built-in Conditional Formatting

The easiest method is the built-in Duplicate Values rule. It works in Excel for Windows, Mac, and the web, and Microsoft still supports it as of 2026.

How Do I Use the Duplicate Values Rule?

Follow these steps:

1. Select the range you want to check, such as A2:A100.
2. Go to Home > Styles > Conditional Formatting.
3. Choose Highlight Cells Rules > Duplicate Values.
4. Make sure the first dropdown says Duplicate.
5. Pick a fill or font color.
6. Click OK.

Every repeated value now shows up in color. If you pick Unique in the dropdown instead, Excel highlights the values that appear only once.

This rule is fast and needs no formulas. But it only works on the cells you select, and you can’t change its logic. For anything more specific, use a formula.

Highlight Entire Rows (Not Just Cells)

Yes, you can color a full row if one cell is a duplicate, but you need a formula rule. The built-in rule only colors single cells.

Say your data runs from A2 to D100 and column A holds the email addresses. Do this:

1. Select A2:D100.
2. Go to Conditional Formatting > New Rule > Use a formula to determine which cells to format.
3. Enter =COUNTIF(A2:A100,$A2)>1
4. Click Format, choose a fill color, and click OK.

The dollar sign before A locks the column, so every cell in the row checks the same value. Without it, the rule drifts and colors the wrong cells. Using a fixed range like A2:A100 also runs faster than a whole-column reference like A:A.

Advanced: Custom Formulas for Flexibility

A custom formula lets you control exactly which duplicates get highlighted. COUNTIF is the go-to function for this. The two most common needs are skipping the first occurrence and ignoring blank cells.

How Do I Exclude the First Occurrence?

Use =COUNTIF(A2:A2,A2)>1. The range starts locked at A2 but ends at the current row, so it only counts values seen so far. The first “Apple” stays plain, and every later “Apple” gets colored.

This is handy when you want to keep one original and flag only the extra copies for deletion.

How Do I Ignore Blank Cells?

Use =AND(A2<>””,COUNTIF(A2:A100,A2)>1). Excel treats empty cells as identical, so blanks can light up as duplicates. The AND condition tells Excel to check only cells that have something in them.

Multi-Column and Cross-Sheet Highlighting

For rows that repeat across several columns, use COUNTIFS. For other sheets, point the formula at the other sheet. Both need a formula rule because the built-in option can’t handle them.

Can I Highlight Duplicates Across Multiple Sheets?

Yes. On Sheet1, select your range and add this rule:

=COUNTIF(‘Sheet2’!A:A,A2)>0

Any value in Sheet1 that also appears in Sheet2 gets colored. Repeat the rule for each additional sheet. This needs no VBA, but it can be slow on large workbooks.

To catch rows where two columns match together, try:

=COUNTIFS(B2:B100,B2,C2:C100,C2)>1

This only flags a row when both the B and C values repeat.

Which Method Should I Use?

Method	Best for	Pros	Cons
Built-in Duplicate rule	Quick single-column checks	Fast, no formulas	Can’t customize logic
COUNTIF formula	Excluding first occurrence, ignoring blanks	Flexible	Needs formula knowledge
COUNTIFS	Duplicate rows across columns	Handles several criteria	Formulas get long
Cross-sheet COUNTIF	Multi-sheet workbooks	No VBA needed	Slower on big data
Power Query	Repeatable, automated cleanup	Scalable	Steeper learning curve
VBA macro	Custom colors, bulk jobs	Fully customizable	Needs coding, security risks

In short: use the built-in rule for one-off checks, COUNTIF for custom logic, and Power Query when you repeat the same cleanup often. Advanced users in Excel 365 also pair Conditional Formatting with dynamic array functions like UNIQUE and FILTER.

Troubleshooting: Why Isn’t My Highlighting Working?

Highlighting usually fails because of messy data or a wrong rule range. Microsoft Q&A forums show “conditional formatting not working” is one of the most common Excel complaints, and reports of the Duplicate Values rule failing after updates rose in early 2026.

Check these in order:

1. Open Manage Rules and confirm the Applies to range covers all your data.
2. Look for extra spaces. Clean them with =TRIM(CLEAN(A2)).
3. Check data types. Text “123” and the number 123 are not treated as matches.
4. Check rule priority if you have several formats on the same cells.
5. Confirm you are in Excel desktop. Some options differ in Excel for the web.

If the rule broke after an update, resetting the range in Manage Rules usually fixes it.

Problem	Likely cause	Fix
No highlighting appears	Wrong “Applies to” range	Edit the rule in Manage Rules
Blanks are highlighted	Blank equals blank	Add AND($A2<>””, …)
“Apple” and “apple” match	Case-insensitive by default	Use EXACT with SUMPRODUCT
Works on one sheet only	Formula not adjusted	Use sheet references like ‘Sheet2’!A:A

Why Are “Apple” and “apple” Treated the Same?

Excel’s duplicate checks ignore letter case by default. That means “Apple” and “apple” count as the same value.

For a case-sensitive check, use this formula rule:

=SUMPRODUCT(–EXACT(A2:A100,$A2))>1

EXACT compares text letter by letter, including case. It is a good fit for codes, usernames, or IDs where capitals matter.

Performance Tips for Large Datasets

Apply rules to a specific range, not a whole column. A rule on A2:A1000 is much lighter than one on A:A, especially past 50,000 rows.

Two more habits help:

• Turn your data into an Excel Table with Ctrl+T. The rule then expands automatically when you add rows.
• Name your ranges to keep long COUNTIFS formulas readable.

Highlight vs. Remove: What’s the Difference?

Highlighting only flags duplicates. Removing deletes them for good. Use highlighting to review, and use Data > Remove Duplicates only after you’ve checked the results.

Make a backup copy of your sheet before you remove anything. Many people delete data by accident because they confused the two tools.

Expert Tips and Shortcuts

• Test your COUNTIF formula in a normal cell before putting it in Conditional Formatting.
• Run TRIM, CLEAN, and VALUE first to avoid missed matches.
• Add a small legend sheet that explains what each color means.
• In Excel 365, you can ask Copilot something like “Highlight duplicate emails in column B” for help building the rule.
• Excel can’t assign a different color to each duplicate group on its own. Sort the data and color groups manually, or use VBA.

Common Mistakes to Avoid

• Selecting only part of your data
• Forgetting the $ signs in formulas
• Missing hidden spaces
• Assuming Excel is case-sensitive
• Using the built-in rule for multi-column or cross-sheet checks
• Applying rules to entire columns on large files

Conclusion

For a quick check, the Duplicate Values rule is all you need. For full rows, blanks, first occurrences, or other sheets, a COUNTIF or COUNTIFS formula gives you control. Clean your data first, keep your ranges tight, and highlight before you delete.

FAQs

How do I highlight duplicates in Excel?
Select your range, then go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Choose a color and click OK.

Can I highlight an entire row if one cell is a duplicate?
Yes. Select the full data range and use a formula rule such as =COUNTIF(A2:A100,$A2)>1.

Why is my duplicate highlighting not working?
The usual causes are extra spaces, mixed text and number types, or a wrong “Applies to” range. Use TRIM and check Manage Rules.

Does Excel highlight duplicates case-sensitively?
No. “Apple” and “apple” match by default. Use =SUMPRODUCT(–EXACT(A2:A100,$A2))>1 for case-sensitive checks.

How do I highlight duplicates across multiple sheets?
Use =COUNTIF(‘Sheet2’!A:A,A2)>0 on Sheet1 and repeat for other sheets.

Can I highlight only the second or later occurrence?
Yes. Use =COUNTIF(A2:A2,A2)>1.

How do I ignore blank cells?
Use =AND(A2<>””,COUNTIF(A2:A100,A2)>1).

What’s the difference between highlighting and removing duplicates?
Highlighting marks duplicates for review. Removing deletes them permanently.

Can I use this in Excel for Mac or Excel Online?
Yes, the Duplicate Values rule works on Windows, Mac, and the web. Some options may look different in Excel for the web.