Compare two Columns in Excel
For example, for a specific month, a table displays the following information:
Since the data pertains to the same organization, there can be a similarity or a difference within the given set of values. The same is explained as follows:
It is difficult to scan through the data to track the matches and the differences manually. Hence, there are techniques which can be followed to compare 2 columns of an excel worksheet. How to Compare two Columns in Excel? (Top 4 Methods)The top four methods to compare 2 columns are listed as follows:
Letusunderstandthesemethods with the help of examples. You can download this Compare Two Columns Excel Template here Compare Two Columns Excel Template #1 Compare Using Simple FormulaeThe following table shows the names of the employees of an organization in column A. Columns B and C display their login and the logout times respectively. We want to compare two excel columns B and C to find out the employees who forgot to logout from the office. The official timings are 10:30 a.m.7:30 p.m. Use the formula method for comparison. For the given data, if the login time is equal to the logout time, we assume that the employee has forgotten to logout. The steps to compare two columns in excel with the help of the comparison operator equal to (=) are listed as follows:
#2 Compare 2 Columns Using the IF FormulaWorking on the data of example #1, we want the following results:
Use the IF functionIF FunctionIF function in Excel evaluates whether a given condition is met and returns a value depending on whether the result is true or false. It is a conditional function of Excel, which returns the result based on the fulfillment or nonfulfillment of the given criteria. read more to compare the columns B and C. We apply the following formula. =IF(B2=C2,Forgot to Punch Out,No Problem) If the value in column B is equal to that of column C, the result is true, otherwise false. For every true value, the formula returns forgot to punch out. Likewise, it returns no problem for every false value. The output is shown in column E of the following image. Hence, Roshan and Uday are the only two employees who have forgotten to logout from the office. #3 Compare Using the EXACT FormulaWorking on the data of example #1, we want to compare the two excel columns B and C with the help of the EXACT functionEXACT FunctionThe exact function is a logical function in excel used to compare two strings or data with each other, and it gives us the result whether the both data are an exact match or not. This function is a logical function, so it provides true or false as a result.read more. We apply the following formula. =EXACT(B2,C2) If the value in column B is equal to that of column C, the formula returns true, otherwise false. The output is shown in column F of the following image. Hence, the entries of columns B and C are identical for Roshan and Uday. Note: The EXACT function is casesensitive. We have written the name Raman in columns A and B with different casing. We apply the following formula. =EXACT(A2,B2) The formula returns false when the casing of cell A2 is different from that of cell B2. Likewise, it returns true if the casing in columns A and B is the same. The output is shown in column C of the following image. #4 Compare 2 Excel Columns Using Conditional FormattingWorking on the data of example #1, we want to highlight those entries that are identical in columns B and C. Use the conditional formattingConditional FormattingConditional formatting is a technique in Excel that allows us to format cells in a worksheet based on certain conditions. It can be found in the styles section of the Home tab.read more feature. Step 1: Select the entire data. In the Home tab, click the conditional formatting dropdown under the styles section. Select new rule. Step 2: The new formatting rule window appears. Under select a rule type, choose the option use a formula to determine which cells to format. Step 3: Enter the formula $B2=$C2 under edit the rule description. Step 4: Click format. In the format cells window, select the color to highlight the identical entries. Click Ok. Click Ok again in the new formatting rule window. Step 5: The matches of columns B and C are highlighted, as shown in the following image. Hence, the entries of columns B and C are identical for Roshan and Uday. The Properties of the Comparison MethodsThe features of the comparison methods discussed are listed as follows:
Note: The comparison method to be used depends on the data structure and the kind of output required. Frequently Asked Questions1. What does it mean to compare two columns and how is it done in Excel? The comparison of two data columns helps find the similarities and the differences. In case of similarity, a value exists in the same row of both the columns. In contrast, a difference is a deviation of one value from the other. 2. How to highlight the different values of two columns without using the formula in Excel? Let us compare the following 2 columns consisting of names in the mentioned sequence: 3. How to compare multiple columns in Excel? Let us compare the following columns consisting of numbers in the mentioned sequence: Recommended ArticlesThis has been a guide to compare two columns in Excel. Here we discussed the top 4 methods to compare 2 columns in Excel, 1) the operator equal to, 2) IF function, 3) EXACT formula, and 4) conditional formatting. We also went through some practical examples. You can download the Excel template from the website. For more on Excel, take a look at the following articles
All in One Excel VBA Bundle (35 Courses with Projects)
