THE EXCEL GUIDE Mastering Data & Spreadsheets

Home
Data Entry and Formatting

Highlight Duplicates in Excel with Conditional Formatting

Learn how to visually identify duplicate values.
By:

What You'll Be Able to See, and What to Have Ready Before You Highlight Duplicates in Excel

Is the duplicate actually there, or did Excel just miss it? That's the question that should be running through your head before you trust any conditional formatting rule on data that matters. When you highlight duplicates in Excel correctly, every repeated value in your range gets a colored fill: instantly visible, no manual scanning. But the built-in tool has one limitation worth knowing before you start. It only works within a single sheet. It can't reach across tabs. If your data lives on one Excel spreadsheet, you're ready to go. If it spans multiple sheets, we'll get to the workaround.

Have your data range selected and your column or columns identified. That's all the prep you need. Let's handle the easy path first, then go deeper for situations where the easy path isn't enough.

Excel spreadsheet with duplicate values highlighted in red fill using conditional formatting
Duplicate values flagged with a colored fill, exactly what the built-in rule produces in seconds.

Step 1: Apply the Built-In Highlight Cells Rule to Flag Duplicate Values Instantly

Once your range is selected, this takes about four clicks. Here's the exact path:

  1. Select the cells you want to check, whether that's a single column, multiple columns, or any range.
  2. Go to the Home tab on the ribbon.
  3. Click Conditional Formatting, then Highlight Cells Rules, then Duplicate Values.
  4. In the dialog that opens, the left dropdown lets you choose Duplicate or Unique. Keep it on Duplicate. The right dropdown sets the fill color. Light Red Fill is the default, but you can pick any preset or define a custom format.
  5. Click OK.

Any value that appears more than once in your selected range now shows a colored fill. Duplicate rows don't get flagged automatically with this method. It highlights the individual cells, not full rows. If you need the entire row shaded, the COUNTIF approach in Step 2 handles that.

Here's the thing: this built-in rule is fast, but it's a blunt instrument. It's case-insensitive, it can't distinguish between duplicate groups, and it applies a single color to everything flagged. For a quick scan before sending a file, it works fine. For reporting work, anything going to a manager, a client, or a board, it's a starting point, not a finished solution.

I've seen this create real problems. In 2016, a conditional formatting threshold set to the wrong value put incorrect color coding in front of a VP during a board presentation. Nobody caught it before the meeting. That kind of error changes how seriously you take rule verification.

Before moving to Step 2, confirm the rule applied correctly by checking the Conditional Formatting Rules Manager (Conditional Formatting → Manage Rules). You should see your new rule listed with the correct range.

Step 2: Use a COUNTIF Formula to Highlight Duplicates Across Multiple Columns in Excel

The built-in rule gets you started. What it can't do is scale cleanly to multi-column detection or let you color duplicate groups differently, which is often exactly what you need.

Write the COUNTIF Rule for Multi-Column Duplicate Detection

To highlight duplicates in multiple columns, you'll build a custom formula rule. Select your entire data range (say, A1:B100), then go to Conditional Formatting → New Rule → Use a formula to determine which cells to format.

In the formula box, enter:

=COUNTIF($A$1:$B$100,A1)>1

The dollar signs on the range lock it in place. The reference to A1 without dollar signs lets the rule shift as it checks each cell. Set your fill color and click OK. Every cell whose value appears more than once anywhere in that two-column range gets highlighted. I've tested this in both Microsoft 365 and Excel 2019, and it behaves consistently in both.

One thing that will burn you if you skip it: trailing spaces. Two cells can look identical and not match because one has a hidden space after the value. Run a quick TRIM on your data before the rule goes live, or you'll get false negatives. The same issue applies to entries pulled from external sources. Inconsistent formatting is more common than people expect, and a missed duplicate in a financial report is a reporting integrity problem, not just a cosmetic one.

Color-Code Duplicate Groups Differently (The Gap Most Guides Leave Open)

Most tutorials stop at one color for all duplicates. But if you have multiple duplicate sets in the same column (say, "Chicago" appears three times and "Atlanta" appears twice) a single red fill doesn't tell you which duplicates belong together.

The fix is stacking multiple conditional formatting rules, each targeting a specific value. Build one rule per group using a formula like =A1="Chicago" with a red fill, and another with =A1="Atlanta" with a yellow fill. It's manual if you have many groups, but for reporting dashboards with known repeat categories, it's the right call. My old mentor Patricia Morales used to say every element on a dashboard should be defensible. A duplicate highlight that's there? The reader will act on it. Make sure it's telling them something true and specific.

Step 3: Remove Duplicates or Prevent Them After You've Found Them

Once the highlights show you where duplicates live, you have two directions: clean them out, or stop them from coming back.

To remove duplicates, go to Data → Remove Duplicates. Select which columns Excel should check, and it'll delete rows where values repeat. Fast, but irreversible, so work on a copy.

The more durable fix is upstream prevention. Data validation for large datasets lets you block duplicate entries at the point of input, before they ever reach your conditional formatting rule. For heavy-volume data where conditional formatting itself starts to drag, Power Query is the better tool for finding and removing duplicates. It handles scale that CF rules weren't designed for.

Common Mistakes When You Highlight Duplicates in Excel, Including the Large-Dataset Slowdown Nobody Warns You About

Three problems come up constantly, and I've seen all three in professional environments.

The first is applying the rule to the wrong range. If you've got headers in row 1 and your rule includes them, Excel might flag the header as a duplicate of a data value below. Always start your range at the first data row, not the header.

The second is blank rows inside the range. A blank cell can be counted as a duplicate of other blank cells, which produces false positives that make the output look broken. Clean your blanks first, or adjust your formula to exclude them with an IF wrapper.

The third, and the one nobody mentions, is performance. Conditional formatting recalculates constantly in Excel. On ranges above roughly 50,000 rows, you'll feel it: slow scrolling, delayed rendering, occasional freezes. If your dataset is that large and you're still relying on CF rules for duplicate detection, switch to Power Query. It's built for volume. Conditional formatting is not.

The thirty seconds you spend checking your formula range and testing against a known duplicate is the thirty seconds that keeps your report honest. Build the rule right, then verify it. That habit is what separates work you can defend from work you hope nobody questions.

For anyone starting out with Excel's data tools more broadly, the Excel beginner's guide covers the foundational concepts that make conditional formatting rules easier to reason through. Worth a read if any of the range-locking logic above felt unfamiliar.

Frequently Asked Questions

How do I highlight duplicates in Excel automatically without a formula?

Select your range, go to Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values, and click OK. Excel applies the color fill automatically with no formula required. The limitation is that it works on a single range only and uses one color for all duplicates.

Can Excel highlight duplicates across different sheets?

Not with the built-in Duplicate Values rule, which is limited to a single sheet's selected range. Cross-sheet duplicate detection requires Power Query or a helper column that consolidates data from both sheets into one range before the rule runs.

Why is conditional formatting slow on large Excel datasets?

Conditional formatting rules recalculate every time the sheet changes, which creates significant overhead on large ranges. Above roughly 50,000 rows, scrolling and editing slow noticeably. For datasets that size, Power Query handles duplicate detection more efficiently without the performance hit.