How to find Duplicate Rows in Excel: Fastest Ways to Do it

As we all know, there can be a massive amount of data in a single Excel worksheet. Be it a list of employee data or an array of student information in a school, there can be repetitions of the same thing. In some cases, rows can repeat themselves multiple times.

Now, the question is, is it possible to detect these repetitions?

Yes! It definitely is. In fact, in this article, we’re going to discuss more than one way to do it. So, if you’re looking for simple and easy ways to find duplicate rows in Excel, I’d suggest you to read every bit.

Enough with the introductions, let’s hop in!

The Limitation of Conditional Formatting

Conditional formatting is an option you can find under the home tab. It belongs to the Styles group. This is a really handy tool if you want to identify duplicate cells. Before we get into the main bit, let’s share the reason behind Conditional Formatting not being appropriate to identify duplicate rows.

Let’s clarify using the set of data given below:

The limitation of conditional formatting

  • Here, we have to select the cell range A2:D9.
  • Then we’ll have to select Conditional Formatting under the Styles group of the Home Tab.
  • There, you’ll want to select Highlight Cells Rules, and go to Duplicate Values.

The limitation of conditional formatting 2

  • Once the popup window appears, just hit OK.

The limitation of conditional formatting 3

  • Here, you can see, apart from row 6, every row is marked.
  • However, row 8 has no duplicate.
  • This proves that Conditional Formatting alone works only on cell level.

So, this method cannot be used to identify duplicate rows.

Super Quick Methods to Find Duplicate Values

Now that we’ve discovered the limitations of Conditional Cell Formatting, let’s look into the methods that can easily find duplicate rows within an Excel spreadsheet. Let’s begin.

Using CONCATENATE and Conditional Formatting

Since we’ve already found out that Conditional Formatting alone isn’t enough to find duplicate rows within a bunch of data in Excel, let’s combine it with the usage of the CONCATENATE function. Take a look at the steps below.

  • Here, you have to create a helper column and use the CONCATENATE function on the first cell.

Using CONCATENATE and Conditional Formatting 1

  • As you can see, we’ve used spaces “ “ between the arguments of the CONCATENATE function: =CONCATENATE(A2,” “,B2,” “,C2,” “,D2,” “)
  • The result combined all the data in A2, B2, C2, and D2 in Helper column Cell E2.
  • Now, it’s time to use the fill handle.

Using CONCATENATE and Conditional Formatting 2

  • We dragged the Fill handle from cell E2 to cell E9 to get the similar results.
  • Now, if we apply the same Conditional Formatting on the data in Row E, we should get our results.

Using CONCATENATE and Conditional Formatting 3

  • As you can see, after selecting Conditional Formatting and setting the New Rule to Duplicate Values, the actual duplicate rows have been marked.

This procedure is one of the fastest possible one and can be used in most office or home scenarios.

Using TEXTJOIN

Now, remember, this method might be really simple and easy, but there’s a tiny limitation. It only works on Office 365, Office 2016 and above. Now that we’ve cleared that up, let’s look at how it works.

  • The first thing you want to do is, put in the TEXTJOIN function in the formula bar of the first cell in the helper column.
  • The formula should look like this since we’ll be using cell range A2:D2 and place commas between them: =TEXTJOIN(“,”,TRUE,A2:D2)

Using TEXTJOIN 1

  • Now, drag the cursor to the corner of the cells and when it turns into crosshairs, double click.
  • Voila, the results should be as follows:

Using TEXTJOIN 2

  • Apply the Conditional Formatting Method as before, and that’s it.
  • As you can see, the duplicate rows have been highlighted.

Using COUNTIF Function

This time, we’ll only be using the COUNTIF function to find the duplicate rows in Excel. It’ll basically give a number count through which we’ll be able to detect the duplicates. Let’s see how it works.

  • Here, in cell F2 we’ll have to place the formula =COUNTIF(E2:E9,E2)

Using COUNTIF Function 1

  • The next thing you’ll want to do is, fill the bottom rows using Autofill.

Using COUNTIF Function 2

  • As we can see, only the second row shows a different value.
  • This means, only the second row is duplicated.

Using IF and COUNTIF Function

Let’s combine the COUNTIF Function with the IF function this time and see what happens.

  • In the datasheet that we’re working on, the first thing we’ll have to do is, insert this formula:

=IF(COUNTIF($D$5:$D5,D5)>1,”Duplicate”,””). Then we’ll have to drag the fill handle to fill up the bottom cells to F9.

Using IF and COUNTIF Function

  • As we can see, the duplicate row has been identified.

Explanation:

In this case, what the COUNTIF function does is, it checks the matched number and decides if it’s greater than 1. If yes, then it’ll show TRUE or else, it’ll show FALSE. Thus, it will return as: {FALSE}

Thereafter, the IF is there to show “Duplicate” if it’s greater than 1 or else, it will show empty. Which will return as: { }

Using IF and SUMPRODUCT

Let’s see how the combination between IF and SUMPRODUCT works to find the duplicates within the given rows. Let’s observe step by step.

  • The first thing you want to do is, place this formula on the first cell of the helper column.

Using IF and SUMPRODUCT 1

  • Now, drag and fill the results using the fill handle to cell C9.

Using IF and SUMPRODUCT 2

  • There you go.
  • The duplicates have been detected.

Using KUTOOLS

The first thing you’ll want to do if you’re about to use KUTOOLS for this is, go to: Kutools > Select > Select Duplicate & Unique Cells

Then just follow the steps below:

  • Firstly, go to the Select duplicate & unique cells dialogue box.
  • Click the button in order to select the range of data you want to work with.
  • Then, click on Each row in the Based on section;
  • After that, check All duplicates (Including the 1st one) or Duplicates (Except the 1st one) option which are under the Rule section.
  • Finally, you can specify a particular background color or the font color of your choice to assign for the resulting duplicate rows. Find these under Processing of Results.

Final Words

Now that we’ve discussed all the procedures, it’s time to bid farewell. The first two methods are the fastest ways to find duplicate rows in Excel. However, if you were looking for other methods, I’ve given quite a few to choose from. I really hope the information helped.

See you in the next blog!

Leave a Comment