Find Duplicates in Excel Using Conditional Formatting
Trying to manually spot duplicate values in an Excel worksheet containing a large amount of data can be a tedious task. Instead of going through and scanning the data manually, you can simply select the data range and invoke the conditional formatting feature in Microsoft Excel to highlight duplicate data. Once duplicate values are highlighted, it makes it easy to correct those values by deleting or modifying the duplicate values as required. So let us go ahead and take a look at the steps to find duplicates in Excel using Conditional Formatting.
1. Find Duplicates in Excel
Open the Excel File containing data that you want to check for duplicates.
Using Mouse, select the entire section of data that you want to check for duplicate values.
After selecting data, make sure you are on the Home tab > click on Conditional Formatting > Highlight Cells Rules and select Duplicate Values option.
In Duplicate Values dialog box, select the Conditional Format that you want to apply for cells with duplicate values and click on OK.
After this, Cells with duplicate values will become highlighted, making it easy for you to change or delete the Cells containing duplicate data.
2. Remove Duplicate Values in Excel
If you only need to remove duplicate values, you can remove them all at once by following the steps below.
Click on the Data tab in top menu bar and select Remove Duplicates option in ‘Data Tools’ section.
On the next screen, select the Columns from which you want to remove duplicates and click on OK.
Note: All Columns will be Pre-selected in Remove Duplicates dialog box. Make sure you select only those columns from which you want to remove duplicate values.
3. Clear Formatting From Duplicate Data
If you find that certain duplicate values are valid and need to remain in the data, you can easily clear the formatting of the duplicate cells.
- Select the Cell or Cells from which you want to remove Conditional Formatting.
- Make sure you are on the Home tab > click on Conditional Formatting > Clear Rules and select Clear Rules from Selected Cells option.
Note: You can select ‘Clear Rules from Entire sheet option to clear conditional formatting from the entire work sheet. This will immediately remove the highlighting of cells with duplicate values and the worksheet will be back to its original format.
How to Create Pivot Table in Excel How to Lock Cells In Excel to Protect Them
