Find Duplicate or Unique Data in Excel With Conditional Formatting

Full Frame Shot Of Blue Seats surrounding one red seat in an arena

Marco Bicci/EyeEm/Getty Images

Adding conditional formatting in Excel allows you to apply different formatting options to a cell or range of cells that meet specific conditions. The formatting options that can be applied include font and background color changes, font styles, cell borders, and adding number formatting to data.

These instructions apply to Excel 2019, 2016, 2013, 2010, 2007, and Excel for Office 365.

Finding Duplicate Data in Excel

Excel has several preset conditional formatting options for commonly used conditions such as finding numbers that are greater than or less than a specific value or that are above or below the average value.

One of Excel's preset options is to find and format duplicate data with conditional formatting whether the duplicate data is text, numbers, dates, formulas, or entire rows or data records.

Screenshot of Excel showing how to highlight duplicate data

Conditional formatting also works for data added after conditional formatting has been applied to a range of data, so it is easy to pick out duplicate data as you add it to a worksheet.

Find Duplicates with Conditional Formatting

Here's how to find duplicate cells of data for the range E1 to E6 as seen in the image above.

  1. Highlight cells E1 to E6 on the worksheet.

  2. Click the Home tab of the ribbon.

  3. Click Conditional Formatting in the ribbon to open the drop-down menu.

  4. Choose Highlight Cell Rules > Duplicates Values to open the ​duplicate values formatting dialog box.

  5. Select Green Fill with Dark Green Text from the list of preset formatting options.

  6. Click OK to accept the selections and close the dialog box.

Cells ​E1E3, E5, and E6 should have a light green background color, and dark green text since all four contain duplicate data — the months of January and March.

Remove Duplicates Data in Excel

If the goal is to remove duplicate data not just find it, whether it is single cells or entire data records, instead of using conditional formatting, Excel provides another option known as Remove Duplicates; this data tool can be used to find and remove partially or completely matching data records from a worksheet.

Find Unique Data with Conditional Formatting

Another option with conditional formatting is to look for unique fields of data, those that contain data appearing only once in a selected range. This option is helpful for situations where you expect to see duplicate data. Finding unique fields makes it easy to determine when such submissions are missing. Below are the steps to find unique cells of data for the range E1 to E6 as seen in the image above.

Screenshot of Excel showing the highlighting of unique data

Here's how to find unique cells of data for the range E1 to E6 as seen in the image above.

  1. Highlight cells E1 to E6 on the worksheet.

  2. Click the Home tab of the ribbon.

  3. Click Conditional Formatting in the ribbon to open the drop-down menu.

  4. Choose Highlight Cell Rules > Duplicates Values to open the duplicate values formatting dialog box.

  5. In the dialog box, click the drop-down box labeled duplicate and select unique.

  6. Select Light Red Fill with Dark Red Text from the list of preset formatting options.

  7. Click OK to accept the selections and close the dialog box.

Cells E2 and E4 should be formatted with a light red background color and dark red text since both contain unique data that is not present in the rest of the data — February and April.