Tutorial Playlist

Excel Tutorial

Overview

Your One-Stop Solution to Know About Excel Worksheets

Lesson - 1

Your One-Stop Solution For Excel UserForms

Lesson - 2

The Best Guide on How to Send an Email in Excel

Lesson - 3

Your One-Stop Solution That Will Help You Learn How to Convert PDF to Excel

Lesson - 4

A One-Stop Solution for Acing the Excel MIS Report

Lesson - 5

All You Need to Know About Barcode in Excel

Lesson - 6

Your One-Stop Solution to Learn Mail-Merge in Excel and Its Implementation

Lesson - 7

A Comprehensive Guide on Excel Flash Fill

Lesson - 8

Slicers in Excel: The Ultimate Guide to Help You Design and Develop Excel Slicers

Lesson - 9

One-Stop Solution to Learn Everything About Excel Budget Template

Lesson - 10

All You Need to Learn About Match Function in Excel and Its Real-Time Implementation

Lesson - 11

Everything You Need to Learn on How to Create a Pivot Table From Multiple Sheets

Lesson - 12

The Major Fundamentals About XLookUp in Excel Along With Practical Examples

Lesson - 13

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

Lesson - 14

The Best Guide and Your One-Stop Solution to Master the Fill Series in Excel

Lesson - 15

What Is the DATE Function in Excel and How to Implement It?

Lesson - 16

Your One-Stop Solution to Designing and Implementing Hyperlinks in Excel

Lesson - 17

One-Stop Solution to Master Everything You Need to Know About Auto-Sum in Excel

Lesson - 18

DAX in Excel: The Complete Guide for DAX Functions and Formulas in Excel

Lesson - 19

A Perfect Guide for All You Need to Know About Data Formatting in Excel

Lesson - 20

The Perfect Guide to Learn How to Add Columns in Excel

Lesson - 21

The Perfect Guide That Will Explain to You How to Remove Page Break in Excel

Lesson - 22

The Perfect Guide to Explain to You How to Remove Blank Rows in Excel

Lesson - 23

The Perfect Guide to Learning How to Highlight Duplicates in Excel

Lesson - 24

One-Stop Solution Understand the Fundamentals of Indirect Function in Excel

Lesson - 25
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.

PCP in Business Analysis

In partnership with Purdue UniversityVIEW COURSE
PCP in Business Analysis

How to Compare Two Columns in Excel?

  • Make sure the entire dataset is selected.
  • Click on the Home tab.
  • Select 'Conditional Formatting' from the Styles group.
  • Select Highlight Cell Rules by hovering your cursor over it.
  • In the Duplicate Values section, click the button.
  • Make sure that 'Duplicate' is selected in the Duplicate Values dialogue box.

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.