How to Compare Two Columns in Excel

Comparing Values in Two columns can be done in many different ways, and the method to use will depend on the data structure and what the user wants from it.

What Do You Mean by Comparing Columns in Excel?

While using Excel, certain situations arise when using columns becomes necessary. It is an easy task when the comparison is between two small tables. But it gets more accessible when the comparison is between two large columns. Without proper knowledge of how to compare two columns in Excel, one has to compare them manually. 

It is essential to learn the tricks and tactics to match two Excel columns. It can help streamline data analysis workflow. These steps include everything from using built-in functions to conditional formatting. There are several methods of doing it. This article will shed light on all the crucial aspects and ways to compare two columns in Excel. upGrad has come up with a comprehensive course to utilize these skills in real world applications. You can opt for a Master of Science in Data Science from LJMU which will help you reap the benefits of data management. 

Why Is It Useful to Compare Two Columns in Excel? 

There are several reasons which make it necessary to compare two columns in Excel. It includes; data storage, manipulation, and decision-making. Excel works efficiently in presenting data to the user. The data analyst then compiles the data. It can be beneficial for sales and marketing.

Excel spreadsheets are pretty large and contain several sheets. Data analysts can compare two columns in Excel for match, either in the same or different sheets. Doing so manually can be highly time-consuming. The comparison of two columns in Excel can highlight if a cell contains data. Excel presents this information as; TRUE/FALSE, Match/Not Match, etc. Another course that you can choose to become a data analyst is the Executive PG Programme in Data Science from IIIT Bangalore. 

How to Compare Two Columns in Excel 

It is essential to compare two Excel columns to check the missing or present data. There are various ways of corresponding data. The method you choose will also depend upon what you want to achieve from the data. The forms of comparing data are as follows:

• Make use of functions to highlight unique values in each column. It can also highlight duplicates.
• Use conditional formatting to show unique or duplicates.
• Data comparison is also possible through row-by-row analysis.
• Make use of vlookup to compare two columns. 

Microsoft Excel offers plenty of ways to compare data in a single column. Although one may opt for multiple comparisons occasionally, it is essential to know about them. 

Example 

Comparing two columns in the same row: First, write an average IF formula. It will compare the first two cells. In the same row, input the formula but in a different column. Now drag the fill handle to copy it to the other columns. 

Comparing Two Columns in Excel with Equals Operator

It is possible to compare two columns in Excel row-by-row. You can find the matching data with the Match or Not match results. In excel, match two columns using the formula =A2=B2. As the name suggests, the use “=” sign reveals the difference between two values.  

After inserting formulas in two different columns, users have to insert the procedure in another cell. After pressing enter, they must drag it to the very end. If the values in both toes are equal, it will return as TRUE. If the values in both rows are unequal, the result will be FALSE. It is a quick and easy step to compare two columns in Excel for a match. 

Example

For example, take an entity’s shipping and billing address in a sheet. In excel, compare two cells by inputting the data into each. You need to use the formula to see if they match each other. If the value in Column B March the value in column C, the result will be TRUE. Any little difference will give the result as FALSE. 

Compare Two Columns in Excel Using the IF Condition

You can even opt for a comparison of two columns using the IF condition. This method verified whether a specific condition was met. It then gives back a particular value for TRUE and FALSE. After using the equals operator method, you can use the IF condition to get the exact value of each. The formula for doing so is: =IF(A2=B2,”Match”,” “).  But if the condition comes out as FALSE, then you can use the formula: =IF(A2=B2, “Match,” “Not a Match “). If you need to compare 2 columns in Excel to find the difference, you need to change the sign. In other words, you can change the equal sign in the formula with the (<>) non-equality symbol. 

Example

Let’s further add to the example above of shipping and billing addresses. You can input the IF condition to return as SAME for TRUE and check to bill for FALSE. In any case, the result will come back as the set terms. It will make splitting the difference easier. 

Compare Two Columns in Excel Using the EXACT() Function 

You can use the Exact function to compare columns in Excel when it is case-sensitive. It also gives out the result as TRUE or FALSE. This function does not take into consideration the differences in formatting. The syntax for it is: =EXACT( text1, text2). In this, both are necessary arguments. 

It would help if you first used the IF condition to decide whether a comparison is a match or not match. If it shows a match, then it is case-sensitive. Then you can implement the formula: =IF(EXACT(A2, B2), “Match,” “Mismatch”). If the if condition is true, then the first argument returns. If it is not so, then the second argument returns. 

Example 

For example, take two lists of names. You can customize the IF condition with your choice of terms. If there are any changes in both the surnames in the list, then the Exact function will give out FALSE. If there are no such changes, then the result will be Match. 

Compare Two Columns in Excel using Conditional Formatting 

To use conditional formatting, you must first click on Home. Then select “Styles” and proceed. You can then view the conditional formatting option. After you select it, you can view the option”highlight cell rules .”After you click on it, select “Duplicate Values .”You will then see a dialogue box. You can then select the values from the drop-down box. 

After following all the steps, you can apply conditional formatting to the cells. You can either choose a duplicate or unique condition. It would help if you formatted those cells that consist of values with options. 

Example 

There are two sets of data; Data 1 and Data 2. Each data table contains different names. Some of the names in one table do not appear in the other. You can use conditional formatting to find the missing data and highlight it. 

Using Lookup Function to Compare Two Columns

Using the LOOKUP function, you can look for values in a single row or column. It will analyze the values and then give the corresponding value from another row or column. There are several lookup functions that you can use. This includes; HLOOKUP, VLOOKUP, and XLOOKUP.

Here, the H and V stand for horizontal and vertical. It is also important to note here that the XLOOKUP function is a maxi of both the LOOKUP and VLOOKUP. You can implement any of the functions to analyze the values of each column or row. You can better understand it by looking at the example below. 

Example

Table A consists of exams taken by a student. Table B contains the passing subjects. In the result sheet, all the subjects must appear. Here, you can use VLOOKUP() in cell C and drag it down. You can then easily find all the passing and failed subjects in one sheet.

Formula-free way to compare two columns/lists in Excel

• Use conditional formatting. This is a quick and easy way to highlight cells that contain matching or non-matching values. To do this, follow these steps:

1. Select the cells that you want to compare.
2. Click the Home tab.
3. In the Styles group, click Conditional formatting.
4. Click New Rule.
5. In the Select a Rule Type dialog box, select Use a formula to determine which cells to format.
6. In the Format values where this formula is the true box, enter a formula that compares the values in the two columns. For example, to highlight cells that contain matching values, you would enter the procedure “=A2=B2`.
7. Click Format to choose a formatting style.
8. Click OK twice to create the rule.

• Use the Find and Replace commands. This method is more powerful than conditional formatting, but it can be more time-consuming. To use this method, follow these steps:

1. Select the cells that you want to compare.
2. Click the Home tab.
3. In the Editing group, click Find.
4. In the Find what box, enter the value that you are looking for.
5. Click Find all.
6. To highlight all of the matching values, click Select All.
7. To replace all of the matching values with another value, enter the replacement value in the Replace with box and click Replace all.

• Use the Advanced Filter command. This method is the most powerful, but it can also be the most complex. To use this method, follow these steps:

1. Select the cells that you want to compare.
2. Click the Data tab.
3. In the Data Tools group, click Advanced Filter.
4. In the Advanced Filter dialog box, select the Filter by criteria option.
5. In the Criteria range box, enter the cells range containing the values you want to compare.
6. In the Criteria box, enter a formula that compares the values in the two columns. For example, you would enter the formula “=A2=B2` to filter for matching values.
7. Click OK to filter the data.

Conclusion

It is important to know how to match two columns in Excel. There are several options that facilitate comparing data in a single or multiple spreadsheet with ease. But comparing multiple sheets can demand a lot of time. It is inconvenient to fill out each column manually with the right results. So, it is important to implement the above functions to get accurate results. It will help streamline the process and save a lot of time. You can opt for Graduate Certificate Programme in Data Science from upGrad which will help you climb the ladder of success as a Data Analyst.