To find duplicates in Excel, select the data that needs to be searched, click on Conditional Formatting from the Home tab, create a new rule, and then format the rule for duplicate values. Select a format for the duplicate cells, and click OK to run the search.
- Select the cells to search
Highlight the cells that you want to search for duplicates.
- Open the Conditional Formatting Rules Manager
Navigate to the Home tab, and select the arrow next to Conditional Formatting, which is in the Style group. Click on Manage Rules from the menu.
- Add a rule
Select New Formatting Rule to open the New Formatting Rule dialog box.
- Choose a rule type
From the Select a Rule Type section, select Format Only Unique or Duplicate Values.
- Update the rule description
Click on Duplicate in the Format All list box, which is under the Edit the Rule Description section.
- Format the duplicate cells
To identify the duplicate data, select the changes that you want to apply to the duplicate cells. Change the color fill of the cells, add a border, or change the font. You can also change the duplicate values to another number. Select as many formatting options as you want. Then click OK to find the duplicate values.