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.
- Enter the following data into cells A1 to A6:
11, 12, 16, 12, 15, 12
- Drag select cells A1 to A6 on the spreadsheet.
- Click on the Home tab.
- Click on the Conditional Formatting icon on the ribbon to open the drop down menu.
- Choose Highlight Cell Rules > Duplicates Values... to open the conditional formatting dialog box.
- Select Light Red Fill with Dark Red Text from the list.
- Click OK to accept the selections and close the dialog box.
- 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.