Skip to content
Related Articles

Related Articles

How to Compare Two Columns in Excel?

View Discussion
Improve Article
Save Article
  • Last Updated : 02 Jun, 2022
View Discussion
Improve Article
Save Article

Excel provides various functions and methods by which we can compare two columns and identify matching and mismatching data. Working with a very large excel dataset, comparing two different columns, and recording the result as Matched or Not Matched can be very time-consuming. We are required to compare excel columns data because of the following reasons:

  • To find the unique value in the dataset.
  • To remove the identical data values from the dataset.
  • To reduce the size of the dataset.
  • To speed up the processing time for other excel operations by reducing its size and removing identical data values.

How to Compare Two Columns in Excel

In this example, we will be using random cities’ names in two different columns and compare them to find duplicate or unique cities’ names. For this example, we will create the following dataset. The dataset will contain two columns as Cities A and Cities B, which will contain random names of cities. Below is the screenshot of the dataset attached.

Dataset

Fig 1 – Dataset

Method 1: Highlight Duplicate Data

In this method, we will the Conditional Formatting rules provided by excel to compare the two columns and highlight the DUPLICATE DATA between the two columns.

Step By Step Implementation

Step 1: Select the data

First, we will select the data cells of excel for which we want to compare the data.

Selecting-data

Fig 2 – Select the data cells

Step 2: Conditional Formatting

In this step, we will do the conditional formatting for our selected cell’s data and set the rules for highlighting the duplicate values. For this go to Home > Styles > Conditional Formatting > Highlight Cells Rules > Duplicate Values.

Conditional-formatting

Fig 3 – Conditional Formatting

Once we click on Duplicate Values, the excel will pop up a tab asking for a formatting rule. We can select the according to our requirements.

Checking-duplicates

Fig 4 – Formatting Rule

After setting the rule(Here, Light Red color for cells having duplicate data values) we need to click on the OK button.

Step 3: Output

Once we set the formatting rules and click on the OK button, the excel will automatically Highlight the cells according to the formatting rule.

Highlighted-cells

Fig 5 – Output

Method 2: Highlight Unique Data

In this method, we will the Conditional Formatting rules provided by excel to compare the two columns and highlight the UNIQUE DATA between the two columns.

Step By Step Implementation

We are required to repeat the same steps as we did in Method 1. But we need to make sure, to change the formatting rule. This time we need to format the cells that contain unique elements, not a duplicate element.

Note: Keep in mind to change the formatting rule for unique data.

Formatting-unique-element

Fig 6 – Formatting rule for a unique element

Output

Once we will change the rule for unique element comparison and click on the OK button, Excel will automatically update the sheet according to the unique element.

Updating-sheet

Fig 7 – Output

Method 3: Matching Columns With TRUE OR FALSE

In this method, we will create one more column in our existing dataset as Result which will be used for storing the matched and unmatched responses.

Step By Step Implementation

Step 1: Add New Column

In this step, we will add a new column as “Result” for adding the response as TRUE or FALSE.

Adding-result-column

Fig 8 – Add Result Column

Step 2: Adding Comparison Formula

In this step, we will add a formula in our Result column to compare whether the two columns contain equal data or not.

=A2=B2(Here, we are using A2 and B2 cells for comparison, you will need to change according to your own cells.

Comparison-formula

Fig 9 – Adding rules for true or false

After adding the formula for one row, we need to fill the same formula for all the rows. For this, we will select the bottom-right corner of the cell(Here, C2) and pull it down to the cell(Here, C9). The excel will fill the same formula in all the rows.

Filling-formula

Fig 10 – Fill formula in all rows

Step 3: Output

Once we have our comparison formula filled in all the rows, excel will automatically fill TRUE for matched and FALSE for unmatched entities.

Output

Fig 11 – Output

My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!