Find Duplicate or Unique Data in Excel with Conditional Formatting

Full Frame Shot Of Blue Seats
Find duplicates or unique occurrences in your data. 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 that you set. The formatting options are only applied when the selected cells meet these set conditions.

The formatting options that can be applied include font and background color changes, font styles, cell borders, and adding number formatting to data.

Since Excel 2007, Excel has had a number of pre-set conditional formatting options for commonly used conditions such as finding numbers that are greater than or less than a certain value or finding numbers that are above or below the average value.

01
of 02

Finding Duplicate Data in Excel

Screenshot of Excel showing how to highlight duplicate data

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

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 it is added to a worksheet.

Find Duplicates with Conditional Formatting

Below are listed the steps used to find duplicate cells of data for the range E1 to E6 seen in the image above.

  1. Highlight cells E1 to E6 on the worksheet.
  2. Click on the Home tab of the ribbon.
  3. Click on the Conditional Formatting icon 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 pre-set formatting options
  6. Click OK to accept the selections and close the dialog box.

CellsE1, E3, and E6 should be formatted with a light green background color and dark green text since all three 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.

02
of 02

Finding Unique Data in Excel

Screenshot of Excel showing the highlighting of unique data

Another option with conditional formatting is to not find duplicate fields of data, but unique fields, those that contain data appearing only once in a selected range; this option is useful for those situations where duplicate data is expected. Finding unique fields makes it easy to determine when such submissions are missing.

Find Unique Data with Conditional Formatting

Below are listed the steps used 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 on the Home tab of the ribbon.
  3. Click on the Conditional Formatting icon 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, select the drop-down box labeled duplicate and select unique.
  6. Select Red Fill with Dark Red Text from the list of pre-set 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 all three contain unique data that is not present in the rest of the data — February and April.