How to Compare Rows for Duplicates in Excel (3 Easy Methods)

Sometimes, while working in Excel, users need to find or highlight duplicates along the rows or columns in a data set based on duplicate values. Comparing rows for duplicates in Excel is quite easy and simple. By applying some Excel formulas or using Excel features, you can do it quite easily. In this article, I will show you how to compare rows for duplicates in Excel.

Download Practice Workbook

You can download the free Excel workbook here and practice on your own.

3 Easy Ways to Compare Rows for Duplicates in Excel

In this article, you will see three different ways to compare rows for duplicates in Excel. I will demonstrate the ways to compare duplicates that are in the same row or different rows of a data set. I will also cover the procedure to compare rows for duplicates in the whole data set at once.

To illustrate my article further, I will use the following sample data set.

Case 1: Find Duplicates in Same Row but Different Columns to Compare Rows for Duplicates

In the first case, I will show you how to find duplicates that are in the same row of a data set but in different columns. For that, I will apply some Excel formulas and Conditional Formatting.

Read more: Find Duplicates in Two Columns in Excel

Option 1: Decide in New Column to Show Duplicates for Same Row

In the first option, I will compare rows for duplicates and show the result in a new column beside the primary data set. Here, I will use the IF function to find out which row contains duplicates. See the following steps for a better understanding.

Steps:

• First of all, under column header F, make a new column for showing the result after applying the formula.
• Then, use the following formula in cell F5.

• Secondly, press Enter to see the comparisons for row 5.
• Then, to get the desired results for the rest of the cells use AutoFill.

• Finally, after applying the formula you will be able to see which particular row contain duplicates by analyzing the result.

Option 2: Highlight Particular Rows to Show Duplicates for Same Row

In the second option of case 1, rather than showing duplicates in a different column, I will highlight them in the primary data set. For that, see the following steps.

Steps:

• First of all select cell range D5:E21 from the primary data set.

• Secondly, go to the Home tab of the ribbon and select Conditional Formatting.
• Then, from the dropdown, choose New Rule.

• Thirdly, you will see the New Formatting Rule dialog box.
• Here, to apply a formula select Use a formula to determine which cells to format under the Select a Rule Type labels.
• Then, in the type box, insert the following formula.

• After that, click on the Format command to format the cells after applying the above formula.

• Fourthly, after configuring the highlighting criteria select OK to close the dialog box.

• Finally, you will be able to see the duplicates, highlighted in the data set like the following image.

Case 2: Find Duplicates in Different Rows to Compare Rows for Duplicates

For the second case, I will show to determine duplicates that are not in the same rows. Like the previous method, here I will also demonstrate two ways to find duplicates in different rows.

Option 1: Create a Helper Column to Show Duplicates for Different Rows

Again, I will use an extra or new column to show rows containing duplicates. Here, to show the results, I will use a combination formula of the IF function and the COUNTIF function. For a better understanding, go through the following steps.

Steps:

• Firstly, show which cell values have duplicates in different rows, and type the following formula in cell C5.

• By using the above formula, I want to find out cell values that are present in both columns C and D but in different rows.

• Secondly, after pressing Enter and using Fill Handle, you will get your desired result.
• Then, from the following result, you will be able to compare rows that contain duplicates.

Option 2: Highlight Cells to Show Duplicates for Different Rows

In the second option of this case, I will demonstrate the way to highlight different rows that contain duplicates by using conditional formatting. To do that, the below-given steps will guide you.

Read more: How to Find, Highlight & Remove Duplicates in Excel

Steps:

• First of all, select cell range B5:B14 and go to the New Rule of the Conditional Formatting just like the previous method.

• Secondly, in the type box for applying a formula, insert the following formula.

• Then, set the highlighting criteria and press OK.

• Consequently, the above procedures will highlight the values of column B that are also in column C.

• Fourthly, to highlight the duplicates in column C, select cell range C5:C14 and again choose New Rule like the first step of this procedure.

• Then, again modify the formatting rule box and in the type box to apply the condition, insert the following formula.

• Lastly, after finalizing everything, press OK.
• Finally, you will be able to see the duplicates in column C as well.

Case 3: Look for Duplicates in the Whole Dataset

In the last method of this article, I will look for duplicates in the whole data set, irrespective of rows. Here, I will use Conditional Formatting to complete the task. For doing that, see the following steps.

Steps:

• Firstly, select the data range B5:B21 to look for duplicates in the whole data set.

• Secondly, again choose Conditional Formatting from the Home tab, and from the dropdown, select Highlight Cells Rules.
• Then, from the second dropdown, choose Duplicate Values.

• Thirdly, in the Duplicate Values dialog box, set the criteria of formatting and select the text and fill color for the final result.

• Finally, you will see the duplicate values in your cells will be highlighted like the following image.

Find Duplicates in the Same Column in Excel

Additionally, in this section, I will demonstrate the process of finding duplicates in the same column. By going through each cell value in a single column, I will determine or find the duplicates.

Option 1: Decide in a New Column to Show Duplicates

Here, like in the previous methods, I will create a new column beside the primary data set, and with a combination formula of the IF function and the COUNTIF function, I will see which value has duplicates in a single column. Let’s go through the following steps.

Steps:

• In the beginning, create a new column under column D for applying the formula and showing the result.
• Then, in cell D5, type the following combination formula.

• Secondly, hit the Enter button to get the result for the first cell value of column B.
• Then, drag the Fill Handle to show the results for the lower cells of the same column.

Option 2: Highlight Cells to Show Duplicates

Lastly, instead of using formulas in another column, I will apply Conditional Formatting to highlight the cells that contain duplicates under a single column. For that, follow the below-given steps.

Steps:

• Firstly, select the cell range B5:C21 which includes both columns B and C.

• Secondly, open the New Formatting Rule box from Conditional Formatting, as I have shown in all of the previous methods.
• Then, in the type box use the following formula.

• After that, do all the formatting regarding cells and press OK.

• Finally, you will find your selected data range highlighted like the following image where any duplicates in a single column will be highlighted.

Things to Remember

• While inserting formulas in the assigned cells or in the dialog box of Conditional Formatting, give proper cell reference. Otherwise, you will not get the desired result.
• After inserting formulas in Conditional Formatting, remember to format cells to highlight the result after finishing the entire process.

Conclusion

That’s the end of this article. I hope you find this article helpful. After reading the above description, you will be able to compare rows for duplicates in Excel. Please share any further queries or recommendations with us in the comments section below.

The ExcelDemy team is always concerned about your preferences. Therefore, after commenting, please give us some moments to solve your issues, and we will reply to your queries with the best possible solutions.

Further Readings

• How to Find & Remove Duplicate Rows in Excel
• How to Highlight Duplicate Rows in Excel (3 Ways)
• How to Find Matching Values in Two Worksheets in Excel
• Excel Find Similar Text in Two Columns
• Excel Top 10 List with Duplicates
• Find Matches or Duplicate Values in Excel
• Excel Formula to Find Duplicates in One Column
• Finding out the number of duplicate rows using COUNTIF formula