1. Technology
You can opt-out at any time. Please refer to our privacy policy for contact information.

Find Duplicates in Excel with Conditional Formatting

Use Conditional Formatting to find Duplicate Data in Excel


Find Duplicates in Excel with Conditional Formatting

Find Duplicates in Excel

© Ted French

Find Duplicate Data in Excel with Conditional Formatting

Related Tutorial: Remove Duplicate Data Records in Excel

Finding duplicates - whether they be formulas, numbers, dates, or entire data records - is easily accomplished in Excel using conditional formatting.

What conditional formatting does is apply specific formatting options - such as a change in font or cell background color - when certain conditions are found in your data.

In the case of duplicate data, you can have all the duplicate information change to the same color so it is easy to find.

Example: Find Duplicates with Conditional Formatting

For help with this example see the image above.

  1. Enter the following data into cells A1 to A6:

    11, 12, 16, 12, 15, 12

  2. Drag select cells A1 to A6 on the spreadsheet.

  3. Click on the Home tab.

  4. Click on the Conditional Formatting icon on the ribbon to open the drop down menu.

  5. Choose Highlight Cell Rules > Duplicates Values... to open the conditional formatting dialog box.

  6. Select Light Red Fill with Dark Red Text from the list.

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

  8. Cells A2, A4, and A6 should be formatted with a light red background color and dark red text since all three contain duplicate data - the number 12.

The same steps can be followed to use conditional formatting to highlight duplicate data records in an Excel database, as well as duplicate formulas, dates, or other values in your data range.

Related Tutorials
Related Video
Excel Lists- Remove Duplicates

©2014 About.com. All rights reserved.