Excel Tutorials: How to Find Duplicate Data in Excel with Conditional Formatting

Find Duplicate Data in Excel with Conditional Formatting

(Use Conditional Formatting to find Duplicate Data in Excel)
For more Excel tips and tricks go to my static page:  Excel Tutorials and Tricks Series.


It is easy to find a variety of duplicate data - formulas, numbers, dates, data records - in Excel 2007 using conditional formatting. The article includes an example of using conditional formatting in Excel 2007 to find duplicates.

 

Find Duplicate Data with Conditional Formatting Example

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.