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?
Here are several effective methods to compare two columns in Excel:
-
Conditional Formatting in Excel
-
Using Equals Operator
-
Using VLOOKUp 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 VLOOKUp Function
To compare two columns in Excel, you can use the VLOOKUP formula:
Follow the detailed steps mentioned 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.
Future-Proof Your AI/ML Career: Top Dos and Don'ts
Free Webinar | 5 Dec, Tuesday | 7 PM ISTRegister Now
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 copy-paste or drag the modified formula to all the cells to get the final error-free result, as shown below.
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.
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.
IF Formula: =IF(A2=B2,”Match”,” ”)
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 case-sensitive.
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.”
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:
Scenario 1: Comparing Two Columns in Excel Row-by-Row
To compare two columns in Excel row-by-row, 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 case-sensitive, then use the following formulas:
- =IF(EXACT(A2, B2), "Match", " ")
- =IF(EXACT(A2, B2), "Match," "No match")
Scenario 2: 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")
Scenario 3: 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")
Scenario 4: 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.
Scenario 5: 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 top-most 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” drop-down, 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. How to compare two columns in Excel?
One popular method for comparing two columns in Excel is to follow these steps: select both columns of data → go to the Home tab → click on Find & Select → choose Go To Special → select Row Differences → click OK.
2. Is it possible to compare two columns in Excel using the Index-Match function?
Yes, you can compare two columns in Excel using the Index-Match function by creating the required formula for the data required.
3. 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.
4. 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.
5. How do I compare two columns in Excel and highlight the duplicates?
To compare two columns in Excel and highlight the duplicates, follow these steps:
- Select the two columns you want to compare.
- Go to the Home tab and click on Conditional Formatting.
- Choose "Highlight Cells Rules" and select "Duplicate Values" from the dropdown menu.
- In the Duplicate Values dialog box, make sure "Duplicate" is selected.
- Choose a formatting style or leave the default style.
- Click OK.
Excel will then highlight the duplicate values in the selected columns, making them easy to identify.
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 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.
