Finding Duplicate Cell Values in Excel

Last Updated on May 8, 2026

Managing large spreadsheets is difficult enough without worrying about hidden duplicates skewing your totals and analysis.

Manually reviewing thousands of rows is inefficient and prone to human error.

Fortunately, Excel provides built-in tools to surface these inconsistencies instantly.

In this guide, we will show you how to use Conditional Formatting to highlight duplicate (or unique) values so you can manage your data with confidence.

As datasets grow, even small inconsistencies can quietly undermine accuracy, reporting, and decision-making.

Duplicate entries may not always be obvious at a glance, yet they can distort totals, skew analysis, and create confusion when data is shared across teams.

Having a reliable way to surface and address these issues early can save hours of cleanup later and help maintain confidence in the data you rely on every day.

Manually reviewing long columns row by row is not only inefficient, it also increases the risk of human error.

When similar values appear scattered throughout a worksheet, duplicates can easily be overlooked or removed inconsistently.

This is where built-in Excel tools become especially valuable, allowing you to identify patterns and problem areas quickly without altering the underlying data structure.

Excel makes this easy by allowing you to format the cells using a conditional format.

This specific format will highlight the duplicate cells so you can easily review and remove the duplicates.

It can also be used to highlight unique values if you are looking for standalone data instead.

Note: You cannot apply this specific conditional formatting rule to fields in the Values area of a PivotTable report.

How to Highlight Duplicate Cells

Step 1: Highlight the specific range of cells, columns, or rows you want to check for duplicates.

Step 2: On the Home tab, look for the Styles group and click on Conditional Formatting.

Step 3: Hover over Highlight Cells Rules and select “Duplicate Values…” from the slide-out menu.

Step 4: In the pop-up box, ensure “Duplicate” is selected in the first dropdown menu.

Step 5: In the second dropdown, choose your preferred highlight color, such as “Light Red Fill with Dark Red Text”.

Step 6: Click OK to apply the formatting rule.

All duplicate entries in your selected range will now be highlighted instantly.

Now that the duplicate cells are marked with the color you chose, it makes it incredibly easy to spot them and clean up your data.

How to Quickly Remove Duplicates

If you do not need to see the highlights and just want the duplicates gone completely, you can skip formatting and remove them directly.

Step 1: Select your data range that contains the redundant information.

Step 2: Go to the Data tab on your Excel ribbon.

Step 3: In the Data Tools group, click on the Remove Duplicates button.

Step 4: Choose which columns you want to check and click OK.

Excel will immediately tell you exactly how many duplicate values were found and removed from your spreadsheet.

Maintaining Clean Spreadsheets

In summary, Excel’s conditional formatting tools provide a fast, reliable way to identify duplicate and unique values without disrupting your data.

By visually flagging potential issues, you can maintain cleaner spreadsheets, reduce errors, and spend less time on manual reviews.

This approach not only improves efficiency but also helps ensure that your data remains accurate and trustworthy as your worksheets continue to grow.