# How to Compare Rows in Excel for Duplicates [6 Examples]

**Follow these steps to compare rows in Excel for duplicates:**

- Select cell
**C2.** - Write the following formula:
**=IF(A2=B2,”Same”,””)** - Press
**ENTER**to see the result. - Now drag the
**Fill Handle**icon from cell**C2**to**C15.**

This formula returns **“Same”** if it finds any duplicates. Otherwise, it leaves the cell blank.

**Compare Rows in Excel for Duplicates with IF Function **

I will use an additional column adjacent to the main data table to show results as **‘Same’** (if the cell contents within a single row are similar) or **‘Unique’** (if the cell contents of the same row are different).

The above dataset contains the employee salaries for the months of January and June.

=IF(A2=B2,"Same","Unique")

**Formula Breakdown**

**A2=B2**is a logical test. You have to modify the cell addresses while you apply this formula in your Excel sheets.- If the logical test becomes true, the result will be ‘
**Same’,** - If the logical test becomes false, the result will be ‘
**Unique’.**

To find the duplicates in this table by comparing rows in Excel, follow these steps:

- Create a new column (
**Result**) right next to the existing columns. - Then write the formula in the top cell (
**C2**) of that column:**=IF(A2=B2,”Same”,”Unique”)** - Press
**ENTER**to see the result. - Now put the mouse cursor on the
**Fill Handle**icon and drag it to the end of the column (**C2-C15**).

As a substitute for the words **‘Same’ **and** ‘Unique’**, you can use other words also. For example ‘**Match and Mismatch**’, ’**Duplicate and Different**’, ‘**Positive and Negative**’ etc.

**How to Find Similar Text in Two Columns in Excel (5 Ways)****How to Find Duplicates in Two Columns in Excel (7 Methods)**

**Compare Rows for Duplicates in Excel Using Conditional Formatting**

Among all other techniques, highlighting the rows is my personal favorite technique for finding duplicates. Because it makes the results very easy to find. You can also save a lot of time if your worksheet is very long.

To highlight the duplicate cells in the same row, perform the following steps:

- Select your target columns first.
- Then go to the
**Home**tab from the main ribbon. - From the
**Styles**group, click on the**Conditional Formatting**drop-down menu. - Now select
**‘New Rules’**from the**Conditional Formatting**drop-down list. - From the
**New Formatting Rule**dialog box, choose to**Use a Formula to Determine Which Cells to Format**. - Then copy this formula inside the
**Format Values Where This Formula Is True**bar:**=$D2=$E2** - Below there hit the
**Format**button. - In the
**Format Cells**dialog box, go to the**Fill**tab and choose a**Background Color**as per your preference. - Hit
**OK**to save the changes. - Again press
**OK**in the**New Formatting Rule**dialog box to save all the changes.

Here is the final result in the image below.

**Compare Two Columns Across the Rows for Duplicates with IF Function **

One common task while working with a spreadsheet is to compare all the rows from a long list. This task gets easy when you know how to use the **IF **function for comparing two columns row-by-row.

Follow these steps to compare two columns across the rows and find duplicates with IF function in Excel:

- First, create a new column beside the other columns.
- Insert the formula of the
**IF**function in the top cell of that column (**C2**):**=IF(A2=B2,”Same”,””)** - Press
**ENTER**to see the result. - Now drag down the
**Fill Handle**icon over the range of cells**C2:C15.**

Here is the final result:

**5+ Formulas to Find Duplicates in One Column in Excel**

**5 Ways to Find Matching Values in Two Worksheets in Excel**

**Compare 3 Columns Across the Rows for Duplicates with IF & AND functions**

You can compare multiple cells within the same row for matches using a formula combining the **IF **and **AND** functions in Excel.

**Formula**

=IF(AND(A2=B2, A2=C2), "Fully matched", "")

**Formula Breakdown**

**AND(A2=B2, A2=C2)**returns**TRUE**if both conditions are true. If both or at least one of the conditions becomes false then the formula returns**FALSE**.**IF(AND(A2=B2, A2=C2), “Fully matched”, “”)**returns**“Fully matched”**if**AND(A2=B2, A2=C2)**returns**TRUE**. Otherwise, it returns blanks.

To compare three columns across the rows and find duplicates in Excel using **IF** and** AND** functions, follow these steps:

- Create a new column (
**Fully Matched/unmatched**) adjacent to the existing columns**(Salary in December).** - Write the following formula in cell (
**D2**):**=IF(AND(A2=B2, A2=C2), “Fully matched”, “”)** - Press
**ENTER**. - Drag the
**Fill Handle**over the range of cells**D2:D15**.

And there it is!

**Note:** If you have a larger datasheet than the one I have shown you (a list of three columns), don’t worry. Add more commands in the formula this way:

**=IF(AND(A2=B2, A2=C2,A2=D2),"Fully matched","")**

**How to Remove Duplicates in Excel [13 + Different Methods]****4+ Ways to Find Duplicates in a Column and Delete Rows in Excel****Vlookup for Duplicate Values and Return the Matches in Excel [8 Cases]**

## Compare 4 Columns Across the Rows for Duplicates by Conditional Formatting

To find the matches in a row that have the same values in all its cells, take the following steps:

- Select the entire table.
- Click on the
**Conditional Formatting**drop-down. - Then select
**New Rule**. - Select
**Use a Formula to Determine which Cells to Format.** - Now write the following formula in the
**Format Values Where This Formula Is True**bar:**=AND($A2=$B2,$A2=$C2,$A2=$D2)** - Click on the
**Format**button. - Go to the
**Fill**tab and pick a background color in the**Format Cells**dialog box.

I have chosen light blue.

**Find, Highlight, and Remove Duplicates in Excel [Step-by-Step]****4+ Methods to Filter Duplicate Values in Excel**

## Compare 4 Columns in Rows for Duplicates with IF & SUM Functions

To find if all cells have the same values or not within a row, we are using the previous list of salaries from different months of some employees.

To compare four columns in the rows for duplicates using **IF **&** SUM** functions in Excel:

- Create a new column (
**Result**) beside the existing columns. - Then write this formula in the top cell of that column (
**F2**):**=IF(SUM(B2:E2)/4=B2,”All equal salaries”, “Imbalanced salaries”)** - Then press
**ENTER**. - Now pull down the
**Fill Handle**icon to the end of the column (**F2:F15**).

**Note:** If all the cells match, the result will be a **Match**. If there is a mismatch, the result will be a **Mismatch**.

## Conclusion

I have tried to cover all possible methods to compare two columns or entire rows for detecting duplicates in Microsoft Excel. But of course, there are many other ways to find the matching cells in rows. If you know of any other easy solutions, feel free to share them with us in the comment section. Also can inform us if you have any confusion regarding this topic?

## Frequently Asked Questions

### How do I find matching cells in Excel?

To find matching cells in Excel, follow these steps:

- Click on the first cell of the range where you want to find matching cells.
- Go to the
**Home**tab in the Excel ribbon. - Click on
**Conditional Formatting**in the toolbar. - Hover over
**Highlight Cells Rules**in the menu. - Select
**Duplicate Values**from the submenu. - In the
**Duplicate Values**dialog box, you can choose the formatting options for the matching cells. For example, you can choose a fill color. - Click
**OK**to apply the formatting.

Excel will highlight all the cells with duplicate values in the selected range according to the formatting you specified.

This method allows you to quickly identify and visually locate matching cells in your Excel worksheet.

### How do I count rows without duplicates?

To count unique rows in Excel and exclude duplicates, you can use the following steps:

- Click on the first cell of the column containing the data you want to count without duplicates.
- Go to the
**Data**tab in the Excel ribbon. - Click on
**Remove Duplicates**in the toolbar. - In the
**Remove Duplicates**dialog box, select the column or columns that you want to check for duplicates. - Click
**OK**to remove duplicates. - In an empty cell, use the
**COUNTA**function to count the remaining rows. For example, if your data was in column**A**, enter the formula**=COUNTA(A:A)**in a different cell. - Press
**ENTER**to execute the formula, and it will display the count of unique rows.

This method efficiently counts rows without duplicates by first removing duplicate values and then using the **COUNTA** function to count the unique entries in the specified column.

### How do I find and delete duplicate rows?

To find and delete duplicate rows in Excel, follow these steps:

- Click on any cell within the range that contains the data with duplicate rows.
- Go to the
**Data**tab in the Excel ribbon. - Click on
**Remove Duplicates**in the toolbar. - In the
**Remove Duplicates**dialog box, select the column or columns that you want to check for duplicates. - Click
**OK**to identify and remove duplicate rows based on the selected columns.

Excel will show a preview of the duplicate values. Review the selection to ensure it includes the correct columns. - Click
**OK**to delete the duplicate rows.

Excel will provide a confirmation message indicating the number of duplicate rows removed. This method efficiently finds and deletes duplicate rows in your Excel dataset, helping to streamline and organize your data.

### How do I compare two cells in Excel?

To compare two cells in Excel, you can use simple formulas or functions. Here’s a step-by-step guide:

- Click on the cell where you want to display the result of the comparison.
- Type the following formula:
**=IF(A1=B1, “Match”, “No Match”)**

Here**A1**and**B1**are the cells you want to compare. - Press
**ENTER**.

If the content of cells**A1**and**B1**is the same, the formula will display**“Match”**; otherwise, it will show**“No Match”**. Alternatively, you can use other comparison operators in the**IF**function, such as <, >, <=, or >=, depending on the nature of your comparison.

This method provides a quick way to compare two cells in Excel and display a result based on their equality or inequality. Adjust the formula as needed for your specific comparison requirements.

### How do I filter duplicates in Excel?

To filter duplicates in Excel, follow these steps:

- Click on any cell within the range that contains the data with duplicates.
- Go to the
**Data**tab in the Excel ribbon.

Click on**Filter**in the toolbar. This will add filter arrows to the headers of your selected range. - Click on the filter arrow in the column header where you want to identify duplicates.

In the filter options, unselect**Select All**and then choose**Duplicates**.

Click**OK**or**Filter**to apply the filter. - Excel will display only the rows with duplicate values in the selected column. You can now review and work with the filtered data.
- To revert to the original view, click on the filter arrow again and choose
**Clear Filter**.

This method allows you to quickly identify and work with duplicate values in a specific column by using Excel’s built-in filter functionality.