How To Highlight Duplicates In Excel

Exceloffice workflowhow-to

Finding duplicate entries in a large spreadsheet can be a tedious and error-prone task. This guide shows you how to highlight duplicates in Excel automatically using its built-in tools. Whether you're cleaning a customer mailing list, checking inventory numbers, or verifying data entry, this skill is essential for maintaining accurate and reliable spreadsheets. We'll walk you through the simplest method and then explore more powerful techniques for complex situations, helping you save time and improve your data quality.

Fast Answer

Select your data: Click and drag to highlight the cells, column, or range you want to check.
Go to Home Tab: On the main ribbon, find the 'Styles' group.
Use Conditional Formatting: Click Conditional Formatting > Highlight Cells Rules > Duplicate Values...
Choose Formatting: Pick a colour from the dropdown and click OK. All duplicate values will now be highlighted.

2-5 minutes Time needed

Beginner Difficulty

Data cleanliness Watch out for

Before You Start

Microsoft Excel: This guide is for modern versions of Excel (2016, 2019, 2021, or Microsoft 365) on Windows or Mac. The steps are very similar across these versions.
A Spreadsheet with Data: Have your Excel file open with the data you wish to check for duplicates. This could be a single column of email addresses or a large table with multiple columns.
A Clear Goal: Know what you are looking for. Are you checking for duplicate customer IDs in one column, or duplicate orders across several columns (e.g., same person, same date)?

Check first: If your spreadsheet contains critical data, always work on a copy. Go to File > Save As to create a backup before making changes. This prevents any accidental data loss.

Step-by-Step Instructions

Select the Data You Want to Check

First, you need to tell Excel where to look for duplicates. You can select a single column, a specific range of cells, or multiple columns.

To select a single column: Click the letter at the top of the column (e.g., click 'A' to select all of column A).
To select a specific range: Click on the first cell of your data and drag your mouse down to the last cell.
For non-adjacent columns: Click the first column letter, then hold down the Ctrl key (Cmd on Mac) and click another column letter.

Open the Conditional Formatting Menu

With your data selected, look at the main menu ribbon at the top of the Excel window. Click on the Home tab if it's not already active. In the 'Styles' section of the Home tab, you will find the Conditional Formatting button. Click on it to open a dropdown menu of options.

Tip: The 'Styles' group is usually located towards the middle-right of the Home tab ribbon. The Conditional Formatting icon often has bars of different colours.

Choose the Duplicate Values Rule

From the Conditional Formatting dropdown menu, hover your mouse over the first option, Highlight Cells Rules. This will open a second menu to the side. In this new menu, click on Duplicate Values.... This is Excel's dedicated, pre-built tool for this exact task.

Customise the Highlighting Colour

A new dialog box will appear titled "Duplicate Values". This box has two parts. The first dropdown on the left should already say Duplicate. The second dropdown on the right allows you to choose how the duplicate cells will be formatted.

The default is 'Light Red Fill with Dark Red Text', which is usually very clear. However, you can click this dropdown to select other pre-set styles like 'Yellow Fill' or 'Green Fill'. If you want a specific colour, you can select Custom Format... to choose your own font, border, and fill colours. Once you've chosen your format, click OK.

Finding Uniques: The same tool can find unique values. Simply change the left-hand dropdown from 'Duplicate' to 'Unique' to highlight the entries that appear only once.

Review the Highlighted Duplicates

Instantly, Excel will apply the formatting rule to your selected data. Any values that appear more than once in your selection will now be highlighted in the colour you chose. Scroll through your data to see the results. This visual check is often all you need to identify and correct errors or remove redundant information.

Filter or Sort by Colour (Optional)

Seeing the duplicates is useful, but grouping them together is even better. With your data selected, go to the Data tab and click the large Filter icon. A small dropdown arrow will appear in the header cell of each selected column.

Click the arrow on the column with the highlights, hover over Filter by Colour, and then select the highlight colour you used. Excel will temporarily hide all non-highlighted cells, showing you only the duplicates grouped together. This makes it much easier to decide whether to edit or delete them.

Clear the Formatting When Finished

Once you have finished analysing or cleaning your data, you may want to remove the highlighting to make your sheet look tidy again. To do this, go back to the Home tab, click Conditional Formatting, hover over Clear Rules, and then choose either Clear Rules from Selected Cells or Clear Rules from Entire Sheet. This removes the highlighting rule without changing any of your data.

Quick Reference

Situation	Use this	Why
Simple check in one column	Conditional Formatting > Duplicate Values	It's the fastest and most direct built-in method for basic duplicate finding.
Highlighting the entire duplicate row	Conditional Formatting > New Rule > Use a formula	Gives you more control to format the whole row for better visibility, not just the single cell.
Duplicates across multiple columns (e.g., first and last name)	Formula with COUNTIFS or a helper column	Finds records that are only duplicates when multiple fields match, providing more accurate results.
Removing all duplicate rows permanently	Data > Remove Duplicates tool	A destructive but highly efficient tool designed specifically for deleting, not just highlighting.

Common Problems When You Highlight Duplicates in Excel

Sometimes the tool doesn't work as expected. Here are some common issues and how to fix them.

Leading or Trailing Spaces

The Problem: Excel sees "John Smith " (with a space at the end) and "John Smith" (with no space) as two different, unique values. This is the most common reason duplicates are missed.

The Fix: You need to clean your data first. Create a new, temporary column next to your data. If your names are in column A, in cell B2 of the new column, type the formula =TRIM(A2) and press Enter. Click the small square at the bottom-right corner of cell B2 and drag it down to apply this formula to all your data. This creates a clean version of your list with no extra spaces. Now, run the duplicate check on this new, clean column.

Numbers Formatted as Text

The Problem: Sometimes numbers get imported into Excel as text. Excel will not see the number 12345 as a duplicate of the text '12345'. You might see a small green triangle in the corner of the cell, which is Excel's warning sign.

The Fix: Select the column containing the numbers. Click the small error icon that appears and choose Convert to Number from the dropdown menu. This will standardise the format so that all entries are true numbers, allowing the duplicate check to work correctly.

Inconsistent Casing

The Problem: The standard duplicate-finding tool is not case-sensitive, meaning it correctly sees "apple" and "Apple" as duplicates. However, if you need to find duplicates that are only an exact match for case, the default tool won't distinguish them.

The Fix: This requires an advanced formula. To find case-sensitive duplicates, you would use a Conditional Formatting rule based on a formula like =AND(EXACT(A1,A:A),COUNTIF(A:A,A1)>1). This is covered more in our advanced tips section.

Slow Performance on Very Large Files

The Problem: If your spreadsheet has tens of thousands of rows, applying conditional formatting can sometimes make Excel slow or unresponsive.

The Fix: Instead of selecting the entire column (which includes over a million empty cells), only select the specific range that contains your data (e.g., A1:A50000). This drastically reduces the number of cells Excel has to check. For extremely large datasets, consider using Power Query, which is a more powerful tool designed for handling big data.

Advanced Tips for Highlighting Duplicates in Excel

Once you've mastered the basics, you can use formulas to handle more complex duplicate-finding tasks.

Highlighting the Entire Row of a Duplicate Entry

Often, you don't just want to see the one duplicate cell; you want the entire row highlighted for context.

Select your entire data table, from the top-left cell to the bottom-right.
Go to Home > Conditional Formatting > New Rule.
In the dialog box, select 'Use a formula to determine which cells to format'.
In the formula box, type =COUNTIF($A:$A, $A1)>1.
- Replace $A:$A with the column you want to check for duplicates (e.g., $C:$C for column C). The dollar signs are important—they 'lock' the column.
- Replace $A1 with the first cell in that same column. The dollar sign before the 'A' is crucial, but there should be no dollar sign before the '1'.
Click the 'Format...' button, choose your desired fill colour, and click OK twice. Now, any row containing a duplicate value in your specified column will be fully highlighted.

Finding Duplicates Across Multiple Columns

What if a record is only a duplicate when two or more columns match (e.g., same First Name and Last Name)?

Select your entire data table.
Go to Home > Conditional Formatting > New Rule > Use a formula...
Let's say First Name is in column A and Last Name is in column B. In the formula box, type =COUNTIFS($A:$A, $A1, $B:$B, $B1)>1.
This formula counts how many times the combination of the value in A1 and B1 appears in the entire table. If it's more than once, it's a duplicate.
Set your format and click OK. This will highlight all rows where the first and last names are a duplicate pair.

Highlighting Only the Second, Third, etc., Occurrences

Sometimes you want to keep the first entry and only highlight the subsequent duplicates. This is great for de-duplication tasks.

Select the column you want to check (e.g., A1 to A100).
Go to Home > Conditional Formatting > New Rule > Use a formula...
In the formula box, type =COUNTIF($A$1:$A1, $A1)>1.
Notice the range in this formula: $A$1:$A1. The first part is 'locked' with two dollar signs, but the second part is only locked on the column. As Excel checks down the list, this range expands ($A$1:$A2, $A$1:$A3, etc.), so it only counts how many times a value has appeared *so far*. The first time a value appears, the count is 1, so it is not highlighted. Every time after that, the count will be greater than 1, and the cell will be highlighted.
Set your format and click OK.

How To Highlight Duplicates In Excel FAQ

How do I highlight unique values instead?

In the "Duplicate Values" dialog box (from Conditional Formatting > Highlight Cells Rules > Duplicate Values), there is a dropdown menu on the left. Simply change it from Duplicate to Unique. Excel will then apply the colour format to cells that appear only once in your selected range.

Can I use different colours for different sets of duplicates?

The standard tool applies one format to all duplicate values. To colour-code different sets of duplicates (e.g., all 'John Smith' entries in blue, all 'Jane Doe' entries in green), you would need to set up a separate conditional formatting rule for each specific value you want to highlight, or use a more advanced solution like a VBA script.

Is it possible to highlight duplicates in Google Sheets?

Yes, the process is similar but uses a custom formula. Select your data, go to Format > Conditional formatting. In the "Format rules" sidebar, under "Format cells if...", choose 'Custom formula is'. Then, enter a formula like =COUNTIF(A:A, A1)>1. This will achieve the same result as Excel's advanced formula method.

How do I delete the duplicates once they are highlighted?

While you can filter by colour and then manually delete the visible rows, Excel has a much better tool for this. Go to the Data tab and click Remove Duplicates. A dialog box will appear where you can tick the columns you want to check for duplicate values. When you click OK, Excel will permanently delete the duplicate rows, keeping only the first instance of each unique record. Always back up your data before using this feature.

Final Checklist for Highlighting Duplicates in Excel

Backup Your File: Before making any changes, save a copy of your workbook.
Clean Your Data: Use the `TRIM` function to remove extra spaces from your text. Check for numbers stored as text and convert them.
Select the Correct Range: Ensure you have highlighted only the data you want to check.
Apply the Rule: Use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values for the quickest method.
Choose a Clear Colour: Pick a fill colour that stands out against your existing data.
Review the Results: Scroll through the highlighted cells to confirm the tool has found what you expected.
Use Filters for Analysis: If needed, use the Data > Filter > Filter by Colour option to group duplicates together.
Clear Formatting When Done: Keep your sheet tidy by going to Conditional Formatting > Clear Rules when you're finished.

Reader note

When a page mentions a commercial resource, Hotgingerweb tries to keep the decision criteria visible beside the link. The practical fit matters more than the promotion.