How To Highlight Duplicates In Excel

Finding duplicate entries in a large spreadsheet can be a frustrating and time-consuming task. Whether you're cleaning a customer list, tidying up financial records, or preparing data for analysis, repeated information can lead to errors. This guide provides a clear, step-by-step method to automatically find and highlight duplicates in Excel using its powerful Conditional Formatting feature. You'll learn the basic technique for a single column, how to highlight entire rows based on a duplicate value, and how to deal with common data issues that can hide duplicates from view.

Fast Answer

Select Cells: Click and drag to select the column or range you want to check.
Go To: Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values...
Choose Colour: Pick a highlight colour from the dropdown and click OK.

2 Minutes Time needed

Beginner Difficulty

Incorrect Range Watch out for

Before You Start

Microsoft Excel: This guide is for modern versions of Excel (2013, 2016, 2019, 2021, and Microsoft 365). The feature is available in older versions, but the menu location might differ slightly.
Your Spreadsheet: Have the Excel file with the data you want to check open and ready.
A Clear Goal: Know which column(s) are most likely to contain duplicate entries. For example, in a contact list, this might be an email address or a phone number column.

Check first: Always work on a copy of your important spreadsheets. Highlighting duplicates is safe, but it's often the first step before deleting or changing data. A backup ensures you can always return to the original version if a mistake is made.

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, multiple columns, or a specific range of cells.

For a single column: Click the letter at the top of the column (e.g., click 'C' to select all of column C).
For multiple adjacent columns: Click the first column letter, hold down the Shift key, and click the last column letter.
For a specific range: Click the first cell in your desired range, hold down the mouse button, and drag to the last cell.

It's important to select all the cells you want to compare against each other. If you only select half of a column, Excel will only find duplicates within that selection.

Tip: To quickly select a continuous block of data, click any cell within it and press Ctrl + A on your keyboard. This will automatically select the entire data table.

Navigate to the Conditional Formatting Menu

With your data selected, find the Conditional Formatting tool. This feature is located on the main 'Home' tab in Excel's top ribbon menu.

Look for a section of the ribbon labelled 'Styles'. Within this group, you will see a button named 'Conditional Formatting'. Click this button to open a dropdown menu with several formatting options.

Choose the Duplicate Values Rule

In the Conditional Formatting dropdown menu, you'll see a list of categories. Hover your mouse over the first option, 'Highlight Cells Rules'. This will open a second, side menu with more specific rules.

From this new menu, move your mouse down and click on 'Duplicate Values...'. This is Excel's built-in, pre-set rule designed specifically for this task.

Configure and Apply the Highlighting

After clicking 'Duplicate Values...', a small dialog box will pop up. This box has two simple settings.

The first dropdown menu lets you choose whether to format cells that are 'Duplicate' or 'Unique'. For our goal, keep this set to 'Duplicate'.
The second dropdown, labelled 'with', controls the appearance of the highlight. The default is 'Light Red Fill with Dark Red Text', but you can click it to choose other pre-set colours like yellow or green. You can also select 'Custom Format...' to choose any font, border, or fill colour you like.

Once you've selected your desired colour, click the 'OK' button. Excel will immediately apply the rule to the data range you selected in the first step.

Review Your Highlighted Data

Look back at your spreadsheet. Any cells within your selection that contain a value appearing more than once will now be filled with the colour you chose. For example, if the name "Sarah Jones" appears three times in the column you selected, all three of those cells will be highlighted.

This visual cue makes it incredibly easy to scan through hundreds or thousands of rows and spot the repeated entries. You can now decide what to do with them, whether that means correcting a mistake, deleting a redundant entry, or simply taking note of the repetition.

Tip: After highlighting, go to the 'Data' tab and click 'Filter'. A small dropdown arrow will appear on your column header. You can click this arrow, choose 'Filter by Color', and select your highlight colour to temporarily hide all the non-duplicate rows.

Highlight Entire Rows Based on a Duplicate Value

Sometimes, highlighting just one cell isn't enough. You might want to see the entire row for a duplicate entry to get the full context. This requires a custom formula instead of the pre-set rule.

Select your entire data range, including all columns you want to highlight (e.g., cells A2 to E100). Do not select the entire column by clicking the letter.
Go to Home > Conditional Formatting > New Rule...
In the dialog box, select the last option: 'Use a formula to determine which cells to format'.
In the formula bar, type a COUNTIF formula. The formula depends on which column contains the duplicates. If you want to find duplicates in Column C, the formula would be: =COUNTIF($C$2:$C$100, $C2)>1
Breaking down the formula: $C$2:$C$100 is the absolute range to check against (the dollar signs lock it). $C2 tells Excel to check the value in column C for each row (the dollar sign locks the column, but the row number is relative and will change). >1 means "format if this value appears more than once".
Click the 'Format...' button, choose your desired fill colour, and click 'OK' twice to apply the rule.

Now, if a value in column C is a duplicate, the entire row from A to E for that entry will be highlighted, giving you a much clearer view of the redundant records.

Quick Reference

Situation	Use this	Why
You need to find duplicates in a single column quickly.	Standard Rule: `Highlight Cells Rules > Duplicate Values`	This is the fastest and most direct method for simple checks.
You want to find records where a combination of columns is duplicated (e.g., same first and last name).	Helper Column: Create a new column with a formula like `=A2&B2` to combine the names. Then, find duplicates in this new helper column.	Excel's default rule checks one cell at a time. A helper column creates a single unique identifier to check against.
You need to see the entire record for each duplicate.	Formula Rule: `New Rule > Use a formula...` with `=COUNTIF($A:$A, $A2)>1`	This provides better visual context by highlighting the full row, not just the single duplicated cell.
You want to find values that are unique, not duplicate.	Standard Rule: In the 'Duplicate Values' dialog, select 'Unique' from the first dropdown.	This is useful for isolating entries that only appear once in your dataset.

Common Problems When You Highlight Duplicates In Excel

Sometimes, the highlighting feature doesn't seem to work as expected. Here are a few common issues and how to fix them.

Problem: Expected duplicates are not being highlighted.

This is often caused by tiny, invisible differences between cells that look identical. The most common culprits are extra spaces or inconsistent capitalisation.

Hidden Spaces: A cell with "John Smith " (note the space at the end) is different from "John Smith".
Inconsistent Casing: Excel sees "john smith" and "John Smith" as different values.

Solution: Use a 'helper column' to clean your data. In an empty column next to your data, enter the formula =TRIM(PROPER(A2)) (assuming your data is in column A). The TRIM function removes extra spaces, and the PROPER function capitalises the first letter of each word. Drag this formula down for all your rows, then run the duplicate check on this new, clean column.

Problem: Numbers and dates are not being matched correctly.

This can happen if some of your numbers are stored as text. For example, the number 123 is different from the text "123". This often occurs when data is imported from other systems.

Solution: Select the column containing the numbers. Look for a small warning icon that appears next to the selection. If you click it, you may see an option to 'Convert to Number'. Click this to fix the formatting for the entire column. Alternatively, you can use a helper column with the formula =VALUE(A2) to convert the text in cell A2 to a proper number.

Problem: Highlighting is very slow or makes Excel unresponsive.

On spreadsheets with tens of thousands or hundreds of thousands of rows, live conditional formatting can consume a lot of computer resources, as it has to constantly recalculate.

Solution: For very large datasets, consider using a more powerful tool than Conditional Formatting. Excel's Power Query (found under the 'Data' tab) is designed for this. You can load your data into Power Query and use its built-in 'Keep Duplicates' or 'Remove Duplicates' functions, which are much more efficient on a large scale.

Advanced Tips for Highlighting Duplicates in Excel

Once you've mastered the basics, you can use these advanced techniques for more complex tasks.

Highlighting Triplicates (or More)

What if you only want to see values that appear three or more times? The standard rule highlights anything that appears more than once. To be more specific, you must use a formula.

Select your data range (e.g., A2:A100).
Go to Home > Conditional Formatting > New Rule > Use a formula...
Enter the formula: =COUNTIF($A$2:$A$100, A2)>2

This formula tells Excel to only highlight a cell if its value appears more than two times (i.e., three or more times) in the range. You can change the number 2 to any number to find values with that specific frequency.

Finding Duplicates Across Two Separate Lists

Imagine you have a list of registered users in Column A and a list of event attendees in Column B. How do you highlight the users in Column A who also appear in Column B?

Select only the first list (Column A).
Go to Home > Conditional Formatting > New Rule > Use a formula...
Enter the formula: =COUNTIF($B:$B, A1)>0

This formula checks each cell in your selection (starting with A1) and counts how many times it appears anywhere in Column B. If the count is greater than zero, it means the user is in both lists, and the cell in Column A will be highlighted.

Managing Your Formatting Rules

As you add more rules, they can sometimes conflict or become confusing. Excel has a central place to manage them. Go to Home > Conditional Formatting > Manage Rules.... In this window, you can see all the rules applied to your current selection or the entire worksheet. You can edit their ranges, change the formulas, delete old rules, and even change their order of priority.

How To Highlight Duplicates In Excel FAQ

How can I remove duplicates after highlighting them?

Excel has a dedicated tool for this. First, make a backup of your data. Then, select the data range and go to the Data tab on the ribbon. Click the 'Remove Duplicates' button. A dialog box will appear allowing you to choose which columns to consider when identifying a duplicate record. This action permanently deletes rows, so use it with caution.

Can I highlight duplicates that are on two different worksheets?

Yes, this is possible using a formula. Suppose you want to highlight names in 'Sheet1' Column A that also exist in 'Sheet2' Column A. Go to Sheet1, select column A, and create a new conditional formatting rule with the formula: =COUNTIF(Sheet2!$A:$A, A1)>0. This tells Excel to check each cell in Sheet1's column A against the entire column A of Sheet2.

How do I clear or remove the highlighting when I'm finished?

To remove the formatting, go to the Home tab, click Conditional Formatting, then hover over 'Clear Rules'. You'll have two options: 'Clear Rules from Selected Cells' or 'Clear Rules from Entire Sheet'. Choose the one that suits your needs. This removes the rule itself, not the data.

Why isn't my custom formula to highlight entire rows working?

The most common mistake is with the cell references (the dollar signs). In a formula like =COUNTIF($C$2:$C$100, $C2)>1, the first range $C$2:$C$100 must be 'absolute' (dollars on both column and row) so it doesn't change. The cell to check, $C2, must be 'mixed' (dollar on the column but not the row). This allows the rule to correctly check the value in column C for every single row in your selection.

Final Checklist for Highlighting Duplicates in Excel

Backup Your File: Before you start, save a copy of your workbook to prevent accidental data loss.
Select Correct Range: Ensure you've selected the exact column(s) or cells where you want to find duplicates.
Use the Standard Rule: For most cases, navigate to `Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values`.
Choose a Clear Colour: Pick a format that stands out from any existing colours in your spreadsheet.
Use a Formula for Rows: If you need to highlight entire rows, use the `COUNTIF` formula method in a new rule.
Verify the Results: Scan the highlighted cells to confirm they are the duplicates you expected to find.
Clean Data if Needed: If results are missing, use helper columns with `TRIM`, `PROPER`, or `VALUE` to fix hidden errors.
Clear Rules When Done: Once your analysis is complete, use `Conditional Formatting > Clear Rules` to remove the highlights and keep your sheet tidy.