The Ultimate Guide to Learn How to Compare Two Columns in Excel

Often in Data analytics, there arises a necessity of comparing multiple columns to find the differences that could be decisive for reporting. Doing this manually could take hours and days based on the dataset you're working with. What if there is a way to fix this in a fraction of seconds?

What Do You Mean by Comparing Columns in Excel?  

Comparing columns in Excel is a simple act of checking each cell in Excel against the target cells to identify a match, and in case the match is missing, then display a relevant message.

With the basics discussed, you can move ahead with the practical part.

Post Graduate Program in Business Analysis

In partnership with Purdue UniversityVIEW COURSE
Post Graduate Program in Business Analysis

How to Compare Columns in Excel?

You will execute column data comparison of the following sample data. 

Compare_columns_in_Excel_1

You must try the three different ways to compare columns in Excel, as mentioned below.

  • Conditional Formatting in Excel
  • Using Equals Operator
  • Using LookUp Function

Conditional Formatting in Excel

Conditional Formatting in Excel is one of the simplest ways to compare columns in Excel. You can execute it in simple steps, as shown below.

Step 1

Select all cells in the spreadsheet

Compare_columns_in_Excel_2

Step 2

Conditional Formatting

Navigate to the "Home" option and select duplicate values in the toolbar. Next, navigate to Conditional Formatting in Excel Option. 

Compare_columns_in_Excel_3.

A new window will appear on the screen with options to select "Duplicate" and "Unique" values. You can compare the two columns with matching values or unique values.

Duplicate Values

Compare_columns_in_Excel_4.

Unique Values

Compare_columns_in_Excel_5

With the first type of column comparison understood, you will move to the next type by implementing the equals operator.

FREE Business Analytics With Excel Course

Start your Business Analytics Learning for FREEStart Learning
FREE Business Analytics With Excel Course

Using Equals Operator

Another simple way to compare columns in Excel is by using the equals operator. You can execute it with the following steps.

Create a new result column and add the formula for individual cell comparison, as shown below.

Compare_columns_in_Excel_6

Excel will deliver the result as FALSE for every unsuccessful comparison and TRUE for every successful comparison, as shown below.

Compare_columns_in_Excel_7.

You can make minor tweaks to the formula to deliver customized messages using the "IF" clause with custom messages for "TRUE" and "FALSE" values, as shown below.

Compare_columns_in_Excel_8

The final result will be displayed as follows.

Compare_columns_in_Excel_9.

Moving ahead, you will try using the VLookUp Function in Excel to compare columns in Excel.

Using LookUp Function

Follow the detailed steps discussed below to use the VLookUp Function to compare individual cells.

Create a new result column and add the VLookUp Formula to compare individual cells, as shown below.

Compare_columns_in_Excel_10

Business Analyst Master's Program

Gain expertise in Business analytics toolsExplore Program
Business Analyst Master's Program

You can drag the formula to all the cells to obtain the desired result, as shown below.

Compare_columns_in_Excel_11

You can see traces of failed comparisons displayed as errors. You can modify your formula using the "IFERROR" clause to avoid such errors, as shown below.

Compare_columns_in_Excel_12.

Now, you can copy-paste or drag the modified formula to all the cells to get the final error-free result, as shown below.

Compare_columns_in_Excel_13.

Unlike the sample data, the real-world scenarios might vary a little. For example, the comparison between the two individual cells might end up "FALSE" even if both the cells represent the same data. Now, take a better look at it with the help of the image below.

Compare_columns_in_Excel_14.

The data in columns 1 and column 3 are the same. But, there is an additional extension to a few cells. 

Example: Ford India in the first cell of column 1 and Ford in the first cell of column 3.

The standard VLookUp might end up delivering "FASLE" as a result. You can use wildcards as a minor tweak to the data to avoid this, as shown below.

Compare_columns_in_Excel_15

Now, drag the modified formula cell to all the cells, and the resultant data will be shown as follows.

Compare_columns_in_Excel_16.

With that, you can conclude this tutorial on Compare columns in Excel. 

Gain expertise in the latest Business analytics tools and techniques with the Business Analyst Master's Program. Enroll now!

Next Steps

You have learned how to compare columns in Excel. Pivot Charts in Excel can be your next learning endeavor as they are building blocks of data analytics with interactive dashboards in excel.

You can also check out the beginner's guide to Microsoft Excel to get more insights.

Curious about learning business analytics and landing the right job? Simplilearn's business analytics course has got you covered. This Business Analytics certification course offered by Simplilearn teaches you the fundamental concepts of data analysis and statistics to help you achieve reliable decision-making. This training guides you with Power BI and delves into the statistical topics, which will help you devise data from available data to present your reports using professional-level dashboards.

Should you have any doubts, do let us know in the comments below, and our Excel experts will help you as soon as possible.

About the Author

Ravikiran A SRavikiran A S

Ravikiran A S works with Simplilearn as a Research Analyst. He an enthusiastic geek always in the hunt to learn the latest technologies. He is proficient with Java Programming Language, Big Data, and powerful Big Data Frameworks like Apache Hadoop and Apache Spark.

View More
  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.