how to highlight duplicates in excel

How to Highlight Duplicates in Excel: A 2025 Expert Guide

Do you have similar or duplicate data in your Excel sheet that you wish to highlight? Maybe it’s because you want to keep that data separate as part of your business activities. Have you tried many times but could not find a way? We will help you know how to highlight Duplicates in Excel sheet in 2025. Stick to the guide!

In a nutshell, to highlight Duplicate entries in Excel, select the data from your sheet (both rows and columns) and navigate to the home tab, followed by the Conditional Formatting option. After that, select Highlight Cells Rules and Duplicate Values option.

Why Highlighting Duplicates in Excel Matters in 2025

Duplicates aren’t just annoying—they can skew your results. According to a 2025 report by DataQualityPro, 68% of businesses say inaccurate data (including duplicates) costs them up to 20% of their annual revenue. In Excel, duplicates might mean double-counting sales or missing unique customer insights. Highlighting them is the first step to cleaning your dataset and making smarter decisions.

Excel’s got your back with tools like Conditional Formatting, COUNTIF, and even Power Query for the vast data. Whether you’re a beginner or a seasoned user, there’s a method here for you. Let’s break it down step-by-step.

How to Highlight Duplicates in Excel

Using the guide below, you can figure out how to highlight similar data and segregate it easily. For this to be completed, we have methods:

Method 1: Highlight Duplicates with Conditional Formatting

One of the popular ways users try to highlight data in Excel, is by using the in-built feature called Conditional Formatting. Here’s how it works:

Highlight Duplicates with Conditional Formatting

1. Select Your Data:

Click and drag to highlight the range you want to check—say, A1:A100.

Click and drag to highlight the range you want

2. Open Conditional Formatting:

Head to the Home tab on the Ribbon, click Conditional Formatting, then hover over Highlight Cells Rules and select Duplicate Values.

Open Conditional Formatting

3. Set Your Style:

A dialog box pops up. Pick a fill color (I love a bold red) from the Format Cells options, then hit OK.

4. Here’s the result:

Excel instantly highlights all duplicate values in your range.

Pro Tip: If you’re working with a PivotTable, note that this won’t work in the Values area—stick to raw data ranges.

2025 Stat: Per a Microsoft survey, 73% of Excel users rely on Conditional Formatting daily, with “Duplicate Values” being the most-used rule.

Method 2: Highlight Duplicate Rows with COUNTIFS

What if you need to spot duplicate rows—like when two entries have the same name and date? That’s where COUNTIFS comes in. It’s a bit more advanced but super powerful.

  1. Select Your Range:

Highlight the rows you’re checking, like A1:C50.

  1. New Rule:

Go to Conditional Formatting > New Rule > Use a formula to determine which cells to format.

Go to Conditional Formatting

  1. Enter the Formula:

Type =COUNTIFS($A$1:$A$50, $A1, $B$1:$B$50, $B1, $C$1:$C$50, $C1)>1. This checks if the combo of columns A, B, and C repeats.

  1. Format It:

Click Format, choose a fill color (maybe a cool gradient?), and hit OK.

  1. Apply:

Click OK again, and duplicate rows light up.

Expert Insight: In 2025, Excel’s formula engine got a performance boost, making COUNTIFS 15% faster on large datasets, per TechRadar’s testing. That’s ideal for big projects!

Method 3: Power Query for Big Data

Got a massive dataset? Power Query is your friend. It’s built into Excel and perfect for 2025’s data-heavy world.

  1. Load Your Data: Select your range, go to Data > Get & Transform Data > From Table/Range.
  2. Open Power Query Editor: Your data loads in a new window.
  3. Find Duplicates: Click Home > Remove Rows > Remove Duplicates to see unique values, or use Group By to count occurrences.
  4. Highlight Them: Add a Conditional Column with a rule like “If Count > 1, then ‘Duplicate’”.
  5. Load Back: Click Close & Load to bring it back to Excel, then use Conditional Formatting to highlight based on that column.

Why It’s Awesome: Power Query handles millions of rows without breaking a sweat. A 2025 Gartner report says 55% of data analysts now use it for cleaning tasks.

How to Remove Duplicates in Excel

If you want to remove duplicate entries in Excel, here is what you need to do.

  • Removing Entire Duplicate Rows (Most Common):
    1. Select Your Data: Initially, select the entire data you have by clicking anywhere in the table.
    2. Go to the “Data” Tab: Then, click the “Data” tab.
    3. Select “Remove Duplicates”: In the “Data Tools” group, click the “Remove Duplicates” button.  
  • Select Columns (If need arises):
  • You see a dialog box.  
  • Check by clicking “My data has headers.”
  • Select the columns to find duplicates.
  • Finally, tap “OK”:
  • Excel removes duplicate rows, keeping the first occurrence. You’ll get a message about how many were removed.

By following the step guide, you can successfully delete duplicates from Excel.

Highlight Duplicate Values by Keyboard Shortcut

If you are looking for shortcut keys to highlight duplicate values in Excel, that’s a common query people have because it helps speed up the identifying process for similar values.

Here are the sequential shortcut keys to highlight Duplicate Values in Excel:

Press: ALT → H → L → H → D.

Now, you must know the correct way to press the key to get the work done. Follow the step-wise:

  1. First: Press the ALT button, highlighting all ribbons.

First: Press the ALT button, highlighting all ribbons

  1. Second: Tap ‘H’
  2. Third: Press ‘L’ and the ‘Conditional Formatting menu will be opened.
  3. Fourth: Click ‘H’ again to activate the ‘highlight cells rules’ option.
  4. Fifth and final: press the ‘D’ key and ‘Duplicate Values’ will be highlighted.

Key Points:

  • After you follow the step, you reach at the “Duplicate Values” dialog box, where you choose the formatting.
  • Select the entire cell where you want to highlight Duplicates.

The Takeaways!

Highlighting duplicates in Excel doesn’t have to be a chore. Whether you stick with Conditional Formatting for simplicity, level up with COUNTIFS, or harness Power Query, you’ve got options. Pick what fits your skill level and dataset size, and see the task completed within minutes. Also, learn how to delete duplicates in Excel in this complete guide. Thanks for your time and patience!

Frequently Asked Questions:

How do you highlight duplicates in Excel?

You can highlight duplicates using the simple method: select the entire data>select conditional formatting option>click highlight cells rules>and select ‘Duplicate Values.’ You can select the formatting in the dialog box, and tap ok button.

How do you highlight consecutive duplicates in Excel?

To highlight consecutive elements in Excel, first, select conditional formatting under style ribbon tab. And select ‘New Rule.’ Enter the formula: for example, $A1=$A2. Tap ‘Formatting’ option and select color. Finally, press ok.

How do you identify duplicates in Excel without removing them?

There are two straightforward ways to identify duplicate elements in Excel:

  • 1. Conditional Formatting
  • 2. Using CountIf function

Choose either method by learning about them in the blog.

How do you select all duplicates in Excel?

Selecting duplicates is easy in Excel. You can use the filter by color option, in which you get a data ribbon, and select filter (while selecting your data range). Now, you select the filter arrow with highlighted duplicates. Now, select the filter by color and choose the color to highlight.