Users frequently need to locate or compare different rows to search for all the duplicates within them in Excel. It’s quite straightforward and easy to compare rows to look for duplicates. You may simply accomplish this by using basic formulas or features available in Excel. I’ll demonstrate how we can easily compare different rows in excel for duplicates with examples.

## 📁 Download Excel File

To prepare this article, we used an Excel workbook, which is available for download. Additionally, you can edit and customize the data you enter while viewing the results.

## Learn to Compare Rows for Duplicates in Excel with These 9 Unique Cases

This article shows you three distinct ways to search for duplicate rows in Excel. While doing so, I’ll show you how to compare these duplicates if they are in one row or in different rows in our datasets. Again, I will show you how to compare different rows to find all the duplicates from the whole dataset at the same time.

I’ll be using this sample dataset for this demonstration.

**Condition 1**### 1. Comparing Duplicates in Rows from Different Columns

First, I’ll show you here how you can find duplicates from different columns. We can use different Excel formulas to do that. Details will be given in each example.

**Different Columns**

#### 1.1. Finding Duplicates Through Equal Operator

Using the equals operator is another easy way to compare columns in Microsoft Excel. You can do it by following these steps.

**⬇️⬇️ STEPS ⬇️⬇️**

- Make a new column for the results and add the formula for comparing each cell, as shown below.

- As shown below, Excel will display the result as
**FALSE**if the comparison doesn’t work, and**TRUE**if it does.

**Different Columns**

**1.2. **Using IF Function

Here, I’ll use **IF function** now to identify which rows have duplicates. The **IF **function lets you compare a value with what you expect in a logical way. Follow the steps below to better understand.

**⬇️⬇️ STEPS ⬇️⬇️**

- First, we introduce a brand new column (D) with the name
**Decision Column**.

- Then we put the below formula:

**=IF(B6=C6,”Same”,”Not Same”)**

- Later we
**AutoFill**the formula down to the end of the column.

**Different Columns**

**1.3. Utilizing VLOOKUP Function**

We can use the **VLOOKUP function** to compare two different cells. Let’s follow the steps below:

**⬇️⬇️ STEPS ⬇️⬇️**

- Add the
**VLOOKUP**Formula to a new result column to compare different cells, as shown below.

**=IFERROR(VLOOKUP(B6,$C$6:$C$22,1,0),”Not Found”)**

- Now, you can copy-paste or drag the updated formula to all the cells to obtain the final, error-free result, as shown below.

**🔨**** Formula Breakdown**

👉 **Vlookup **function will look for the value first from the range of **C6:C22 **for the value of each cell in **B Column**.

👉 **0** is indicating we are looking for the exact match.

👉 Basically, after that **IFERROR **function will return the value if the similarity is found, otherwise, it will show Not Found.

**Different Columns**

#### 1.4. Combining IF and COUNTIF Functions

Again, I’ll add an extra column or make a new one to display rows with duplicates. Here, I’ll use a formula that combines **IF** and **COUNTIF** **functions** to show the results. **COUNTIF **function is used to count how many cells meet certain criteria. For example, to count how many times a certain city shows up on a list of customers. The **IF **function lets you compare a value with what you expect in a logical way. Follow these steps to get a better idea of what’s going on.

**⬇️⬇️ STEPS ⬇️⬇️**

- Let’s select cell
**C6**and insert this formula.

**=IF(COUNTIF($D$6:$D$22,B6)>0,”Match”,”Don’t Match”)**

- Then we put
**Enter**and drag down the cursor to fill up the remaining cells of the column.

**🔨**** Formula Breakdown**

👉 **COUNTIF**** **function will look for the value first from the range of **D6:D22 **for the value of each cell in **B Column**.

👉 **0** is indicating we are looking for the exact match.

👉 Basically, after that **IF **function will return the value as **Match** if the similarity is found, otherwise it will show **Don’t Match**.

**Different Columns**

#### 1.5. Setting New Rule for Conditional Formatting

I won’t put duplicates in between separate column now. Instead, I’ll highlight them here in the main data set. See the steps below for how to do that.

**Using Equal Operator**

Here we will find duplicates in the same row by using the **EQUAL** operator in **New Rule**. For that we will follow steps below:

**⬇️⬇️ STEPS ⬇️⬇️**

- First, choose the range of cells
**B6:C22**from the main data set.

- Second, on the ribbon, select the
**Home**tab and click on**Conditional Formatting**from the**Styles**group.

- After that, choose
**New Rule**from the**drop-down menu**.

- Third, you’ll see a box called “
**New Formatting Rule**.” - In this case, to use a formula Under “
**Select a Rule Type**,” choose “**Use a formula to determine which cells to format**”.

- Then, put the formula in the formula box to type:

**=$B6=$C6**

- Then, after using the above formula, click the
**Format**button to change the way the cells look. - After setting the criteria for highlighting, click
**OK**.

- Last, you’ll be able to identify the duplicates, which will be highlighted now in the data set as shown in the image below.

**Using COUNTIF Operator**

This time we will find all the duplicates that belong to a different row by using **COUNTIF** operator in **New Rule**. For that we will follow the steps below:

**⬇️⬇️ STEPS ⬇️⬇️**

- First, choose the range of cells
**B6:C22**from the main data set.

- Second, on the ribbon, go to the
**Home**tab and click on**Conditional Formatting**. - Then, choose
**New Rule**from the**drop-down menu**.

- Third, you’ll see a box called “
**New Formatting Rule**.” - In this case, to use a formula Under “
**Select a Rule Type**,” choose “**Use a formula to determine which cells to format**”. - Then, put the formula in the formula box to type:

**=COUNTIF($C$6:$C$22,B6)>0**

- Then, after using the above formula, click the
**Format**button to change the way the cells look. - After setting the criteria for highlighting, click
**OK**to close the dialog box.

- Last, you’ll see the duplicates, which will be highlighted in the data set like in the image below.

**Different Columns**

#### 1.6. Embedding VBA

Now we will use VBA to compare duplicate rows. Before talking about the method, first, we have enabled the **Developer **tab on our Ribbons. You can follow the link to see the process of **enabling the Developer Tab by clicking it here**. Let’s follow the steps below.

**⬇️⬇️ STEPS ⬇️⬇️**

- Let’s press
**Alt + F11**to bring up the VBA Window. You can also do this by going to the**Developer tab**and clicking on**Visual Basic**.

- Then we choose
**Module**from**Insert**from the**menu**. This will bring up the window for the**VBA Module**. Here is where we will put our code.

- Now we type the code below:

```
Sub CompareColumns()
For i = 6 To 22
If Cells(i, 2) = Cells(i, 3) Then
Cells(i, 2).Interior.ColorIndex = 37
Cells(i, 3).Interior.ColorIndex = 37
End If
Next
End Sub
```

- Now we save and run the code.

- Now we will get the result.

**Condition 2****📕 Read More: Find Duplicate Rows Based on Multiple Columns in Excel**

### 2. Comparing Duplicate Rows of Same Column

This time I will show you how to locate duplicates within the column itself in this part. I’ll find all the duplicates inside every cell value within the column.

**Same Column**

**2.1. Showing Duplicates in New Column**

I will add a column to the main dataset. Then use a formula that combines **IF function** with **COUNTIF function** to find out which value in a column has been used more than once. **COUNTIF **function is used to count how many cells meet certain criteria. For example, to count how many times a certain city shows up on a list of customers. The **IF **function lets you compare a value with what you expect in a logical way. Follow these steps to get a better idea of what’s going on.

**⬇️⬇️ STEPS ⬇️⬇️**

- First, let’s create a column in C where the formula will be used and the result will be shown.
- Then, type the formula in cell
**C6**.

**=IF(COUNTIF(B$6:B$22,B6)>1,”Have Duplicates”,”Don’t Have Duplicates”)**

- Now press
**Enter**. - Later we
**AutoFill**the cells with the formula to find the results.

**🔨**** Formula Breakdown**

👉 **COUNTIF**** **function will look for the value first from the range of **C6:C22 **for the value of each cell in **B Column**.

👉 **1** is indicating we are looking for the excat match.

👉 Basically, after that **IF **function will return the value as **Match** if the similarity is found, otherwise it will show **Don’t Match**.

**Same Column**

**📕 Read More: 5 Ways to Create a Top 10 List with Duplicates in Excel**

#### 2.2. Highlighting Duplicates in Same Column

Now instead of comparing it with other columns, I’ll use the **Conditional Formatting **feature to mark the cells having duplicates in the column itself. To do that, I have gone through the following steps.

**⬇️⬇️ STEPS ⬇️⬇️**

- First, choose the range of cells
**B6:B22**from the main data set.

- Second, on the ribbon, go to the
**Home**tab and click on**Conditional Formatting**.

- Then, choose
**New Rule**from the**drop-down menu**.

- Third, you’ll see a box called “
**New Formatting Rule**.” - In this case, to use a formula Under “
**Select a Rule Type**,” choose “**Use a formula to determine which cells to format**”. - Then, put this formula in the box:

**=COUNTIF($B$6:$B$22,B6)>1**

- Then, after using the above formula, click the
**Format**button to change the way the cells look. - After setting the criteria for highlighting, click
**OK**to close the dialog box.

- Last, you’ll be able to see the duplicates, which will be highlighted in the data set like in the image below.

**Same Column**

**📕 Read More: 5 Ways to Find Duplicates in Column and Delete Row in Excel**

#### 2.3. Comparing Rows for Duplicates Using Conditional Formatting

I’ll look for duplicates in the whole set of data by using **Conditional Formatting**. See the steps below for how to do that:

**⬇️⬇️ STEPS ⬇️⬇️**

- First, choose the range of data
**B6:B22**to find duplicates in the whole set of data.

- Second, choose
**Conditional Formatting**again from the**Home tab**, and then choose**Highlight Cells Rules**from the drop-down menu.

- Then, choose
**Duplicate Values**from the second drop-down menu.

- Third, in the
**Duplicate Values**dialog box, set the formatting rules and choose the text color and fill color for the final result.

- In the end, you’ll see that the duplicate values in your cells are highlighted, like in the image below:

**📕 Read More: 7 Ways to Find Similar Text in Two Columns in Excel**

## 📄 Important Notes

`🖊️`

In Excel, the ribbon is used to access various commands. You can change a lot of things in the options dialogue window, like the ribbon, formulas, proofing, saving, and so on. So the appearance of worksheets on your device could be different from ours.

`🖊️`

A practice workbook is given so that you can practice yourself.

`🖊️`

All these exercises and tutorials are done on Microsoft Office 365. Some functions may be unavailable in previous versions.

`🖊️`

At the end of the Excel file, there is a sheet where they can practice.

## 📝 Takeaways from This Article

`📌`

The reader can compare various rows for all the duplicates easily in Excel.

`📌`

Readers can find duplicates in the same row but in different columns to compare rows for all the duplicates.

`📌`

Readers can also find duplicates in different rows to compare rows for all the duplicates.

`📌`

Moreover, users can look for duplicates in the whole dataset.

`📌`

Again readers can use the **VLOOKUP** function for finding duplicates.

`📌`

VBA can be useful for the readers to compare rows in excel for duplicates.

## Conclusion

That concludes today’s session. These are the methods for comparing rows for all the duplicates within them in Excel easily. We are hopeful that this article will be extremely beneficial to you. Don’t forget to leave your thoughts and questions in the comments section and explore our website, **ExcelDen**, the best Excel solutions provider.