How to Find Duplicates in Excel?

Microsoft Excel includes a useful function to search for duplicate data in the spreadsheets and highlight them for easy identification. Specifically, this option automatically finds duplicate cells and highlights them with a colour we can choose later. This feature in Excel is especially useful in spreadsheets with a lot of data, although it can be applied in any case, even when there is little. In this blog, we will discuss two methods of process of how to find duplicates in Excel.

 

1. Finding Duplicates in Excel Using Conditional Formatting
2. Using the COUNTIF Function to Find Duplicates in Excel

Must Read – What is MS Excel?

1. Finding Duplicates in Excel Using Conditional Formatting 

1. Select the range of cells where you want to find and highlight duplicates. Select single or multiple columns.
2. Once you have selected the data range, go to the “Home” tab in the Excel ribbon.

3. In the “Home” tab, you will find the “Conditional Formatting” option in the “Styles” group. Click it to open a dropdown menu.

4. Select “Highlight Cell Rules” from the dropdown.
5. Another dropdown menu will appear. Select “Duplicate Values.”

6. A dialogue box will appear where you can set the formatting options for highlighting duplicates. You can choose the format style (e.g., font color, fill color).

7. If you want to use a custom format for highlighting duplicates, click the “Format” button in the dialogue box. This allows you to specify a custom font color, fill color, and other formatting options.

8. After setting your formatting options, click “OK” in the “Duplicate Values” dialogue box.
9. Excel will apply the selected formatting to the duplicate values within the specified range, making them easy to identify.

You can always go back and adjust the formatting or clear the formatting if needed by revisiting Conditional Formatting.

2. Using the COUNTIF Function to Find Duplicates in Excel

You can use Conditional Formatting with a custom formula to highlight cells that contain exact duplicates (i.e., cells with the same content). Here’s how to do it:

1. Select the cell range where you want to find and highlight exact duplicates.
2. Go to the “Home” tab in the Excel ribbon.
3. Choose “Conditional Formatting” in the “Home” tab. Click it to open a dropdown menu.

4. In the “Conditional Formatting” menu, select “New Rule.” This will open the “New Formatting Rule” dialog box.

5. In the “New Formatting Rule” dialogue box, select “Use a formula to determine which cells to format.”

6. Enter a formula that checks for exact duplicates. For example, if you want to check for duplicates in column A, you can use the following formula:

=COUNTIF($A:$A, A1)>1 

7. This formula checks if the value count in column A’s current cell (A1) is greater than 1, which means it’s an exact duplicate.
8. Click “Format” to specify the formatting you want to apply to the exact duplicates. You can choose font color, fill color, and other formatting options.

9. Click “OK” in the “Format Cells” dialogue box.

10. In the “New Formatting Rule” dialogue box, you can see a preview of how the formatting will look. Click “OK” to apply the rule.

Excel will now highlight the cells that contain exact duplicates within the selected range.

Now, Excel will apply your chosen formatting to any cells with exact duplicates within the selected range. You can customize the formatting and the formula to suit your specific needs. 

We hope this blog on how to find duplicates in Excel will help you manage your huge datasets in a simplified manner.