Find duplicate or unique data in Excel with conditional formatting

By adding conditional formatting in Excel, you can apply different formatting options to a cell or range of cells that meet certain conditions. Formatting options you can apply include changes to font and background color, 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.

Contents
  1. Find duplicate data in Excel
  2. Find duplicates with conditional formatting
  3. Remove duplicate data in Excel
  4. Find unique data with conditional formatting

Find duplicate data in Excel

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

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 records of data.

Conditional formatting also works for data added after conditional formatting has been applied to a range of data, so it’s easy to select duplicate data when adding it to a worksheet.

Find duplicates with conditional formatting

To find duplicate data cells for a range of ., please do as follows E1 before E6 as shown in the image above.

  1. highlight cells E1 on the E6 on the sheet.

  2. Click At home tab on the adhesive tape

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

  4. Select Highlight cell lines Double values to open the Format Duplicate Values ​​dialog box.

  5. Select Green filling with dark green text in the list of predefined formatting options.

  6. Click Okay to confirm the selection and close the dialog box.

cells E1 E3 E5 And E6 should be light green. background color and dark green text because all four of them contain duplicate data – the months of January and March.

Remove duplicate data in Excel

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

Find unique data with conditional formatting

Another option with conditional formatting is to search for unique data fields that contain data that occurs only once in the selected range. This option is useful in situations where you expect duplicate data. By finding unique fields, you can easily determine when such views are missing. Following are the steps to find unique data cells for a range of: E1 before E6 as shown in the image above.

Here’s how to find unique data cells for a range of E1 before E6 as shown in the image above.

  1. highlight cells E1 on the E6 on the sheet.

  2. Click At home tab on the adhesive tape

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

  4. Select Highlight cell lines Double values to open the Format Duplicate Values ​​dialog box.

  5. In the dialog box, click the dropdown labeled duplicate and choose unique

  6. Select Light red filling with dark red text in the list of predefined formatting options.

  7. Click Okay to confirm the selection and close the dialog box.

Cells E2 And E 4 should be formatted with a light red background color and dark red text as both contain unique data not found in the rest of the data – February and April.

Leave a Reply

Your email address will not be published.