Compare Two Columns in Excel

Two columns in excel are compared when their entries are studied for similarities and differences. The similarities are the data values that exist in the same row of both the compared columns. In contrast, the differences are those values that exist in only one of these columns.

For example, for a specific month, a table displays the following information:

• Column A lists the names of the employees who took three consecutive leaves.
• Column B lists the names of the employees who took two consecutive leaves.

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:

• A similarity or a match (column A is equal to column B) implies employees who took both three and two continuous leaves.
• A difference (column A is not equal to column B) implies employees who took either three or two continuous leaves.

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.

The top four methods to compare 2 columns are listed as follows:

• Method #1–Compare using simple formulae
• Method #2–Compare using the IF formula
• Method #3–Compare using the EXACT formula
• Method #4–Compare using conditional formatting

Let us understand these methods with the help of examples.

#1 - Compare Using Simple Formulae

The following table shows the names of the employees of an organization in column A. Columns B and C display their log-in and the log-out 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 log-in time is equal to the log-out time, we assume that the employee has forgotten to logout.

#2 - Compare 2 Columns Using the IF Formula

Working on the data of example #1, we want the following results:

• “Forgot to punch out,” if the employee forgets to logout
• “No problem,” if the employee logs out

Use the IF function 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 Formula

Working on the data of example #1, we want to compare the two excel columns B and C with the help of the EXACT function.

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 case-sensitive.

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 Formatting

Working on the data of example #1, we want to highlight those entries that are identical in columns B and C. Use the conditional formatting feature.

Step 1: Select the entire data. In the Home tab, click the “conditional formatting” drop-down 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 features of the comparison methods discussed are listed as follows:

• The outcomes of the IF function can be modified according to the requirement of the user.
• The EXACT function returns “false” for two same values having different casing.
• The simplest technique to compare two excel columns is by using the comparison operator “equal to” (method #1).

Note: The comparison method to be used depends on the data structure and the kind of output required.