How to Highlight Duplicates in Excel

When you are working with large quantities of data, it’s easy to lose track of data and see them appear multiple times in the table. Some duplicates are placed intentionally while others are mistakes. Whatever the cases, you may want to have these duplicates automatically highlighted to you.

Finding duplicate cells in a small spreadsheet is easy but when dealing with large, complex datasets, it can be quite difficult to do it manually. Fortunately, there are built-in tools in Excel that lets you highlight duplicates values. In this tutorial, we are going to show you how to highlight duplicate data with the conditional formatting feature in Excel.

Highlight Duplicates with Conditional Formatting in Excel. Generally, you may want to find duplicates in Excel, because more than often duplicates are there by mistake and should be deleted or duplicates are important for analysis and should be highlighted in Excel.

There are two methods to find duplicate values with conditional formatting in Excel. They are:

Highlight duplicates using the Duplicate Value rule
Highlight duplicates using Excel custom formula (COUNTIF and COUNTIFS)

Highlight Duplicates Using Duplicate Value Rule

Let’s assume we have this data set:

First, select the range of cells that contains duplicate values. Then go to the ‘Home’ tab, click ‘Conditional Formatting’ in the Styles section of the ribbon. In the drop-down, move your cursor over the first option for ‘Highlight Cell Rules’ and it will again display a list of rules in a pop-out box. Choose the ‘Duplicate Values’ option here.

Once you click on Duplicate Values, the Duplicate Values dialogue box will appear. Here you can select the formatting type for the duplicate values. You can select from colors to only fill the cells, only for the font, as a border, or a custom format if you prefer. Then click ‘OK’ to close the dialog box.

Here, we are selecting ‘Green Fill with Dark Green Text’ for our example.

Once you select the formatting type, it will highlight all the duplicate values in the selected range as shown below.

Highlight Duplicates Using COUNTIF Formula

Another method to highlight duplicates values is to use conditional formatting with simple COUNTIF formula in a single column or across multiple columns.

Select the data range where you want to highlight the duplicates. Then on the ‘Home’ tab and click on the ‘Conditional Formatting’ option. In the drop-down, click on the ‘New Rule’ option.

This will open up a New Formatting Rule dialog box.

In the New Formatting Rule dialog box, select the ‘Use a formula to determine which cells to format’ option under the Select a Rule Type list box, then enter the following COUNTIF formula to count duplicates.

=COUNTIF($A$1:$C$11,A1)>1

Then, click the ‘Format’ button to go to the Format Cells dialog box. In the Format Cells dialog box, you can choose the Fill color from the color palette for highlighting the cells and then click ‘OK’. Here we’re choosing blue fill color to format the duplicates.

Then, click ‘OK’ again to close the dialog box. The formula will highlight all the cell values that appear more than once.

Always enter the formula for the upper-left cell in the selected range (A1:C11). Excel automatically copies the formula to the other cells.

You can also define rules however you want. For example, If you want to find values that appear only two times in a table, then enter this formula instead (In the New Formatting Rule dialog box):

=COUNTIF($A$1:$C$11,A1)=2

The result:

Sometimes you may want to see only the duplicates and filter out unique values. To do this, select the range, go to the Home tab, click the ‘Sort & Filter’ option at the top right corner of excel and select the ‘Filter’ option.

The first cell of every column will then show a drop-down menu where you can define the filter criterion. Click on the drop-down in the first cell of the column and select ‘Filter by Color’. Then choose the blue color.

Now, you’ll see only the highlighted cells and you can do whatever you want with them.

Find and Highlight Duplicate Rows in Excel using COUNTIFS Formula

If you want to find and highlight duplicate rows in Excel, use COUNTIFS instead of COUNTIF.

Select the range, go to the ‘Home’ tab and click ‘Conditional Formatting in the Styles group. In the drop-down, click on the ‘New Rule’ option.

In the New Formatting Rule dialog box, select the ‘Use a formula to determine which cells to format’ option under the Select a Rule Type list box, then enter the below COUNTIFS formula:

=COUNTIFS($A$1:$A$20,$A1,$B$1:$B$20,$B1,$C$1:$C$20,$C1)>1

In the above formula, the range A1:A20 refers to column A, B1:B20 refers to column B and C1:C20 refers to column C. The formula counts the number of rows based on multiple criteria (A1, B2, and C1).