How to Find Duplicates in Excel Software

This article of how to find duplicates in Excel, demonstrates how you can use Excel to search for duplicates. You'll learn a few algorithms for detecting duplicate values and finding duplicate rows with or without initial occurrences. You will also learn how to count duplicate records individually and calculate the total number of dupes in a column, as well as how to filter out duplicates.

When dealing with a large Excel worksheet or combining lots of small worksheets into a larger one, you may come across many duplicate rows. In one of our earlier lessons, we examined numerous methods for detecting duplication in two tables or columns. And now, we would like to discuss a couple of simple and practical approaches for detecting duplicates in a tight formation.

How to Find Duplicates in Excel Without Deleting

You can use the conditional formatting method to find your duplicates and then delete them accordingly without any issue. As your Excel abilities improve, you'll most likely find yourself working with massive volumes of data. It's critical to be able to discover duplicates and for that you will need to know how to find duplicates in excel, whether you're utilising it as data or looking for unique responses. You may do the task in a variety of ways, as with many other Excel functions.

Here is the step by step guide.

Step 1: First, select the range of cells that you wish to work on.

Step 2: Next, directly head over to the Home tab and then locate the section that contains the styles option. Tap on Conditional Formatting to open the drop-down menu. 

Step 3: Now, locate the highlight cell rules and tap on the Duplicate Values option. 

Step 4: You will see a pop-up menu. This menu will allow you to decide the format of your duplicate cells. Take, for instance, you are going to choose the blue colour for your duplicates. 

Step 5: Just tap on the Ok button and you can identify all your duplicates. 

How to Find Duplicates in Excel Columns

For example, we work with check orders that arrive at the company by fax and e-mail. It is possible that the identical command was received across both sources of receiving information. If you register the same order twice, the company may face complications. Using conditional formatting, we will analyse the decision below. You may avoid duplicate orders by using conditional formatting, which allows you to rapidly discover duplicate values in an Excel column.

Let’s check out how to find duplicates in excel columns:

Step 1: First, choose the range you wish to highlight and then choose the option instrument from the Home tab to discover styles and finally conditional formatting.

Step 2: Now, choose the ‘use a formula’ to determine the columns which you wish to format.

Step 3: To locate the duplicate values in the excel column, you need to enter the formula in the field of input.

Step 4: Now you just need to press the button format and select the desired column shading and highlight the duplicates with your preferred colour. 

Step 5: Next tap on and you will notice all the windows getting opened.

How to Find Duplicates in Excel Rows

We'll use the sample spreadsheet below to show how to discover duplicate rows in an Excel spreadsheet, which has data split across three columns.

Combining the contents of each column into a single column is the first step in locating the duplicate rows. We will then use two alternative ways to discover duplicate values within the one combined column.

Step 1:  First, you need to concentrate on the columns, for example, A to C columns and then you need to use the operative column M to enter the formula which is =B2 & C2 & D2.

Step 2: Now highlight the duplicate rows by Conditional Formatting or by using the excel ‘countif’ function. 

Step 3: Highlight all the range of cells or rows that need to be formatted and then select Conditional Formatting from the drop-down menu from the Home tab located on the top of the worksheet. 

Step 4: Now find the highlight cells rules option and then tap on the secondary menu which appears to reveal the Duplicate Values option.

Step 5: Tap on the dialogue box and then you will find the duplicate option which will help you reveal all the duplicate rows in your preferred colour.

Note: This method has its limitations too. It will only work if the contents of your cells are less than 256 characters long because Excel functions cannot handle text strings greater than this. The conditional formatting approach discussed above highlights all rows in the sample spreadsheet that appear more than once. However, you may choose to emphasise the second (rather than the first) occurrence of any duplicate rows.

Conclusion

What more can you do now that you know how to spot duplicates in Excel? If you run into scenarios where Microsoft Excel isn't identifying duplicates, you should look into it more to figure out what's going on.

As a general rule, if Excel says it isn't a duplicate, it isn't. Just because you believe it is doesn't imply you're right. This post will show you how to locate duplicate rows in Excel. If you only want to locate duplicate cells (rather than full rows of data), the Excel Duplicate Cells page may be easier to use. Also, keep in mind that this tutorial just demonstrates how to locate duplicate rows in a spreadsheet.

You can reach out to our HubSpot experts to troubleshoot any particular issue you’re facing or have a custom HubSpot development requirement. If you’re having short on resources to take care of the CMS Hub configuration and other day to day HubSpot tasks, check out our HubSpot CMS management service by FThemes.

Talk soon 🖖