Updated on 02nd Jan, 24 9.1K Views

In this blog, we will understand the reasons to compare two columns in Excel and how to do it. We will also look into the steps of implementing different conditions in comparison, such as the IF condition, equal operator, conditional formatting, VLOOKUP, and EXACT function for smooth analysis of the data in Excel.  

Table of Contents

Watch this video to learn Excel in detail:

Why Do We Compare Two Columns in Excel?

Comparing two columns in Excel serves various purposes. It identifies the differences or similarities between the columns, which helps in making the data consistent. This comparison is very useful in data validation, spotting errors, finding duplicates, and making good analysis of data. It ensures accuracy and reliability in data-driven decision-making.

Know more about data analytics concepts through this Data Analytics Course under the guidance of industry experts.

How Do We Compare Two Columns in Excel?

We compare two columns in Excel which helps us in doing the comparative analysis. Due to this, we can compare the performance of two employees or the performance of an employee in consecutive years/months. 

Comparing two columns in Excel involves various methods. One way is to use conditional formatting, which visually highlights matching or differing cells. We can utilize simple Excel formulas like the IF condition and the Exact() function to match the exact values.

Let us understand these comparison types in detail:

IF Condition

We can compare two columns in Excel using the IF() condition. It finds the matching and non-matching cells. If the condition matches, it will show the result as a match that contains the matching rows, and non-matching rows are left empty without any output.

Formula format for the IF match condition:

=IF(A2=B2,”Match”,” ”)

Example:

IF Condition
  • If the condition is not matched, it shows not match as output and leaves the matching values empty. The equal to (=) operator is changed to the not equal to (<>) operator.

The formula format of the IF (not match) condition is as follows:

=IF(A2<>B2,”Not Match”,” ”)

Example:

Get 100% Hike!

Master Most in Demand Skills Now !

Using Equal Operator 

We can compare two columns in Excel using the equal operator, row by row. It compares each cell and returns true if the value matches and false if the value does not match.

Formula format for equal operators:

=A2=B2

Example:

Using Equal Operator 

Insert the formula in the C2 column and press Enter. Upon dragging it down to the end of the table, it displays the result as TRUE or FALSE as per the given condition.

Conditional Formatting

Conditional formatting is used to compare two columns based on a condition. We can find the unique and duplicate values in the dataset, and it automatically highlights the cells as per the condition. We can also create a custom rule for conditional formatting in Excel. 

Let us understand the steps to perform conditional formatting in detail to compare two columns in Excel:

Step 1: Select the columns or data for which you wish to implement conditional formatting.

Conditional Formatting

Step 2: Go to Home tab > Conditional Formatting > Highlight Cell Rules > Duplicate Values

Duplicate Values

Step 3:  Duplicate Values dialog box appears; select the format and click OK. You can also give the custom format. It will highlight the cells with duplicate values when comparing columns, as shown below.

Duplicate Values

Step 4: From the drop-down menu, you can also select the Unique constraint, which highlights all the unique values present in the selected dataset.

Duplicate Values

You can also make a new rule for custom conditional formatting as shown below:

Home > Conditional Formatting > New Rule. A dialog box appears as New Formatting Rules > Select a Rule Type, and you can set the custom rule.

Duplicate Values
  • If you want to clear the formatting rule you performed on the cells, click Conditional Formatting > Clear Rules > Clear Rules from Selected Cells.

Highlight Row Matches and Differences

We can create a conditional formatting formula that highlights the rows that include identical values in all the columns. 

Formula Format:

=AND($A2=$B2, $A2=$C2)

or

=COUNTIF($A2:$C2, $A2)=3

Here, 3 represents the number of columns.

A2, B2, and C2 are the comparison cells.

We can also use the following steps to find and highlight the matches and differences in Excel:

1. Select the columns with the dataset you want to compare. 

2. Go to the editing group section on the Home tab, click the Find and Select drop-down, and choose Go To Special. Select Row differences and click OK.

Example:

Output: 

To ace your interview preparation, go through these top Excel Interview Questions prepared by industry experts.

Career Transition

Intellipaat Job Guarantee Review | Intellipaat Job Assistance Review | Data Engineer Course
Got Job Promotion After Completing Artificial Intelligence Course - Intellipaat Review | Gaurav
How Can A Non Technical Person Become Data Scientist | Intellipaat Review - Melvin
Artificial Intelligence Course | Career Transition to Machine Learning Engineer - Intellipaat Review
Non Tech to Data Scientist Career Transition | Data Science Course Review - Intellipaat

VLOOKUP Function

To show the comparison between two columns, we can also use the VLOOKUP function. V in VLOOKUP stands for Vertical in Excel. It searches for particular values in a row and returns the value from the corresponding rows or columns.

Formula format for VLOOKUP:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Example:

In the above example, it shows the corresponding value for Mark. If we change the Emp_Name, it will automatically update the value according to the formula.

  • If you want to see the results in a new column, VLOOKUP can be used as in the table below to compare individual cells. When the condition is matched, it shows the matched output; otherwise, it will show #N/A in the output cell.

EXACT Function

The Excat() function in Excel is used to find the exact match of the data present in the cell. It is case-sensitive but ignores the formatting differences. It compares the strings and displays the results as true if there is an exact match; otherwise, it is false. 

Formula format:

=EXACT( text1, text2)

Example:

In the above example, we can see that the strings that are exactly matching give the results as TRUE or FALSE. In C6, we can see an example of the case sensitivity of the EXACT() function in Excel.

Conclusion

In conclusion, comparing two columns in Excel is very useful to make informed decisions based on the highlights made using comparison techniques. Using different conditions, like an exact match of the string and VLOOKUP for finding values in the dataset, makes data-driven decision-making easier for the user. Comparing two columns in Excel increases the accuracy and reliability of the data, which further helps with data integrity and consistency.  

Still have any doubts, get all your doubts cleared by visiting our Community Page for data analytics.

FAQs

Is there any way to highlight the differences between the two columns in Excel?

Yes, we can highlight the difference between two columns using conditional formatting. To find the duplicates in the column, go to Home tab > Conditional Formatting > Highlight Cell Rules > Duplicate Values. Repeat the process for the second column, and choose the unique values. It will show the differences between the columns.

How do I compare two columns in Excel to find matching values?

Use the VLOOKUP function. In a new column, input =VLOOKUP(A2, B:B, 1, FALSE), where “A2” is the cell to compare, “B:B” is the range to look into, and “1” specifies the column to retrieve. This function checks if the value in A2 exists in column B. If it does, it returns that value; otherwise, it shows an error.

What is the simplest way to identify differences between two columns in Excel?

Use the IF function. In a new column, type =IF(A2=B2, “Match”, “Different”). This function compares cells A2 and B2. If they match, it displays “Match”; otherwise, it shows “Different”.

Is there a way to compare columns and show unique values from each in Excel?

Yes, you can combine VLOOKUP and IFERROR. In one new column, use =IFERROR(VLOOKUP(A2, B:B, 1, FALSE), “Unique”). Repeat the process for the other column, switching the lookup direction to identify unique values from each column.

How do I compare columns and extract values that are present in one column but not the other?

You can use FILTER or COUNTIF function. For instance, in a new column, input =IF(COUNTIF(B:B, A2)=0, A2, ” “). This formula checks if the value in column A is not in column B and displays it; otherwise, it leaves the cell blank.

Course Schedule

Name Date Details
Data Scientist Course 02 Mar 2024(Sat-Sun) Weekend Batch
View Details
Data Scientist Course 09 Mar 2024(Sat-Sun) Weekend Batch
View Details
Data Scientist Course 16 Mar 2024(Sat-Sun) Weekend Batch
View Details

Leave a Reply

Your email address will not be published. Required fields are marked *

Speak to our course Advisor Now !

Related Articles

Subscribe to our newsletter

Signup for our weekly newsletter to get the latest news, updates and amazing offers delivered directly in your inbox.