Best Ways to Compare Two Columns in Excel

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

• Why Do We Compare Two Columns in Excel?
• How Do We Compare Two Columns in Excel?
  • IF Condition
  • Using Equal Operator
  • Conditional Formatting
  • VLOOKUP Function
  • EXACT Function
• Conclusion
• FAQs

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 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:

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:

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.

Step 2: Go to Home tab > Conditional Formatting > Highlight Cell Rules > 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.

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.

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.

• 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.

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