Find Duplicate or Unique Data in Excel with Conditional Formatting

01
of 01

Excel Conditional Formatting

Find Duplicate and Unique Data with Conditional Formatting in Excel
Find Duplicate and Unique Data with Conditional Formatting. © Ted French

Conditional Formatting Overview

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.

Find Duplicates with Conditional Formatting

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.

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, not surprisingly, as Remove Duplicates.

This data tool can be used to find and remove partially or completely matching data records from a worksheet.

Find Duplicates with Conditional Formatting Example

Below are listed the steps used to find duplicate cells of data for the range E1 to E6 (green formatting) 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
  1. Click OK to accept the selections and close the dialog box
  2. Cells E1, E4, and E6 should be formatted with a light green background color and dark green text since all three contain duplicate data - the month January

Find Unique Data with Conditional Formatting

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 - as shown in the lower range of cells (red formatting) in the image above.

This option is useful for those situations where duplicate data is expected - such as if employees are expected to submit regular reports or forms or students submit multiple assignments - that are tracked in a worksheet. Finding unique fields makes it easy to determine when such submissions are missing.

To find only unique fields of data choose the Unique option from the Format cells that contain: drop down list as shown in the image above.

Below are listed the steps used to find unique cells of data for the range F6 to F11 (red formatting) seen in the image above.

  1. Highlight cells F6 to F11 in 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. Click on the down arrow under the Format cells that contain: option to open the drop down list - Duplicate is the default setting
  6. Choose the Unique option in the list
  7. Select Light Red Fill with Dark Red Text from the list of pre-set formatting options
  8. Click OK to accept the selections and close the dialog box.
  9. Cells E7 and E9 should be formatted with a light red background color and dark red text since they are the only unique cells of data in the range
Format
mla apa chicago
Your Citation
French, Ted. "Find Duplicate or Unique Data in Excel with Conditional Formatting." ThoughtCo, May. 3, 2017, thoughtco.com/duplicate-or-unique-data-conditional-formatting-3123339. French, Ted. (2017, May 3). Find Duplicate or Unique Data in Excel with Conditional Formatting. Retrieved from https://www.thoughtco.com/duplicate-or-unique-data-conditional-formatting-3123339 French, Ted. "Find Duplicate or Unique Data in Excel with Conditional Formatting." ThoughtCo. https://www.thoughtco.com/duplicate-or-unique-data-conditional-formatting-3123339 (accessed January 19, 2018).