How to Highlight Duplicate Entries in Excel 2016

When you have a lot of data to work with, having duplicate entries in Excel can become very common. Fortunately, Excel 2016 has a feature to highlight duplicate entries using conditional formatting. Duplicate entries can be highlighted in a range of cells using a different colour, pattern, or border. This article discusses how to find and highlight duplicate entries or values in Excel 2016 using conditional formatting.

Highlight Duplicate Entries in Excel

Duplicate entries can be highlighted within a single row, single column or across multiple rows and columns. For this example, we will use a single column to demonstrate how it’s done. Here are the steps.

Step 1: Select the Range of Cells

The first step is to select the range of cells where we want to find the duplicates and highlight them in a special way. Selecting is done by clicking and dragging the mouse.

Step 2: Apply Conditional Formatting

Next step is to apply conditional formatting to the column which was just selected. Conditional formatting option is available through the Home tab as shown below.

Click on the Conditional Formatting icon and select Highlight Cell Rules > Duplicate Values. 

Step 3: Apply a Style for Duplicate Values

Now, select a style to apply for duplicate entries or values. There are some preset options to choose from or you can create your very own custom style. Click OK to close the dialog box.

For our example, we have selected Light Red Fill with Dark Red Text. As you can see on the left side, the duplicate values have been highlighted using this colour scheme. You can also choose Custom Format option and choose different colours or fonts to distinguish the duplicate values. You may also choose to apply a border to the duplicate entries.

The above method can be used to find duplicates across a row or multiple rows and columns.