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.
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 a column data comparison of the following sample data.
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

Using the IF Formula

Using the EXACT Formula
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
Step 2
Conditional Formatting
Navigate to the "Home" option and select duplicate values in the toolbar. Next, navigate to Conditional Formatting in Excel Option.
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
Unique Values
With the first type of column comparison understood, you will move to the next type by implementing the equals operator.
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.
Excel will deliver the result as FALSE for every unsuccessful comparison and TRUE for every successful comparison, as shown below.
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.
The final result will be displayed as follows.
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.
You can drag the formula to all the cells to obtain the desired result, as shown below.
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.
Now, you can copypaste or drag the modified formula to all the cells to get the final errorfree result, as shown below.
Unlike the sample data, the realworld 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.
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.
Now, drag the modified formula cell to all the cells, and the resultant data will be shown as follows.
With that, you can conclude this tutorial on Compare columns in Excel.
Compare 2 Columns Using the IF Formula
The IF formula is used to compare 2 columns in Excel when you want to display a desired result for a similarity or a difference.
Consider the above example if you want the result as the following:
 “Different car brands” if the name of the brands do not match
 “Same car brands” if the name of the brands match
Using the IF formula, we will compare two columns in Excel, columns A and B.
We will be using the formula: “=IF(A2=B2, “Same car brands,” “Different car brands”).”
If the values match, this formula will return “Same car brands” for every “true” value. Likewise, return “Different car brands” for every “false” value when the values do not match.
The result is shown in column D in the below image.
Compare Using the EXACT Formula
To use the EXACT formula to compare two columns in Excel, we can use the following formula for the above example:
“=EXACT(A2, B2)”
If the value in one column is exactly the same as the other column, the result will be displayed as “true,” and if the values are not equal, the result will be “false.”
One should keep in mind that the EXACT formula is casesensitive.
If we write “Honda” in two different cases and apply the formula “=EXACT(A12, B12),” we will get the result “false,” whereas if the case is the same in both cells, then the result will be “true.”
Features of the Comparison Methods
The features of the methods to compare two columns in Excel are as follows:
 The IF function's output can be changed or customized depending on the user's needs.
 When two identical values with differing casing are used, the EXACT function outputs "false."
 Using the comparison operator "equal to" is the most fundamental way to compare two Excel columns.
 The type of comparison technique that should be performed is determined by the data format and the specific type of result needed.
Which Method to Use in Each Scenario
We have learned the methods to compare two columns in Excel. Here is a description to help you choose the method for certain scenarios:
Comparing Two Columns in Excel RowbyRow
To compare two columns in Excel rowbyrow, use the following formulas:
 =IF(A2 = B2, “match”, “ ”)
 =IF(A2<>B2, “no match”, “ ”)
 =IF(A2 = B2, “match”, “no match”)
If you need the results to be casesensitive, then use the following formulas:
 =IF(EXACT(A2, B2), "Match", " ")
 =IF(EXACT(A2, B2), "Match," "No match")
Comparing Multiple Columns for Row Matches
If you need to compare and find the differences and similarities between more than two columns, then use the following formulas:
 =IF(AND(A2=B2, A2=C2), "Complete match", " ")
 =IF(COUNTIF($A2:$E2, $A2)=4, "Complete match," "), where 4 is the number of columns you are comparing
If you want to compare columns with any two or more cells with the same values in the same row, then you might use the following formulas:
 =IF(OR(A2=B2, B2=C2, A2=C2), "Match", "")
 =IF(COUNTIF(B2:D2,A2)+COUNTIF(C2:D2,B2)+(C2=D2)=0,"Unique","Match")
Compare Two Columns for Matches and Differences
To compare two datasets, to find the unique values present in column A and not in column B one can use any of the formulas for finding the match and differences:
 =IF(COUNTIF($B:$B, $A2)=0, "Not present in B", "")
 =IF(ISERROR(MATCH($A2,$B$2:$B$10,0)),"No present in B","")
You can use a single formula to get the result for matches and unique values:
 =IF(COUNTIF($B:$B, $A2)=0, "No Present in B", "Present in B")
Compare Two Lists and Pull Matching Data
To compare two lists and find the matching data, you can use the VLOOKUP function. You can also use the INDEX MATCH formula. You can use the following formulas for this scenario:
 =VLOOKUP(D2, $A$2:$B$6, 2, FALSE)
 =INDEX($B$2:$B$6, MATCH($D2, $A$2:$A$6, 0))
 =XLOOKUP(D2, $A$2:$A$6, $B$2:$B$6)
A2, B2 and D2 are the first cells of three columns. 2 is the number of columns compared.
Highlight Row Matches and Differences
You can create a conditional formatting formula that can highlight the rows that include identical values in all the columns. You can use the following formula for the desired result:
=AND($A2=$B2, $A2=$C2)
or
=COUNTIF($A2:$C2, $A2)=3
3 is the number of columns and A2, B2 and C2 are the topmost cells, to compare.
You 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” dropdown, and choose “Go To Special.” Select Row Differences and click OK.
3. The cells having different values than the cells compared in each row will be colored. To change the color click the Fill Color icon on and choose the color of your choice.
FAQs
1. Is it possible to compare two columns in Excel using the IndexMatch function?
Yes, you can compare two columns in Excel using the IndexMatch function by creating the required formula for the data required.
2. How to compare multiple columns in Excel?
To compare multiple columns in Excel, you can use the conditional formatting option on the home and format the setting to “duplicates” or “uniques”, and choose the desired color to highlight the values to compare multiple columns.
3. How do you compare two lists in Excel for matches?
You can compare two lists in Excel using IF function, MATCH function or highlighting row differences.
Become a Data Scientist by learning from the best with Simplilearn’s Caltech Post Graduate Program In Data Science. 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 data science and landing the right job? Simplilearn's Data Science course has got you covered. This Caltech Post Graduate Program in Data Science offered by Simplilearn teaches you the fundamental concepts of data analysis and statistics to help you achieve reliable decisionmaking. 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 professionallevel 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.