{"id":29426,"date":"2023-06-16T16:47:10","date_gmt":"2023-06-16T14:47:10","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=29426"},"modified":"2024-06-10T21:38:57","modified_gmt":"2024-06-10T19:38:57","slug":"compare-two-columns-in-excel","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/compare-two-columns-in-excel\/","title":{"rendered":"6 Different Ways to Compare Two Columns in Excel – A Detailed Tutorial"},"content":{"rendered":"

\"6<\/a><\/p>\n

Comparing data by analyzing and identifying similarities and differences<\/strong> is a task that is frequently encountered in various contexts. This task can be performed manually<\/strong> by visually looking at data one by one but it can be both time-consuming and prone to errors<\/strong>. Fortunately, Excel provides a solution<\/strong> for this issue!<\/p>\n

There are several techniques<\/strong> that allow us to easily compare two columns<\/a> and streamline your data in Excel. Here are a few of those approaches<\/strong> –<\/p>\n

Let\u2019s look at these methods thoroughly!<\/p>\n

Download the Excel Workbook below to follow along and understand how to compare two columns in Excel –
\n<\/strong>Download excel workbook<\/span>Compare-Two-Columns-in-Excel.xlsx<\/span><\/a><\/h4>\n
\n
\n

Method 1: Equal Operator<\/span><\/strong><\/p>\n<\/div>\n<\/div>\n

This is a simple yet effective<\/strong> approach that allows users to compare two<\/a> columns and check if the cell values are equal or not<\/strong>. To use this operator in Excel, simply add the equal sign (=) in between the two values<\/strong> that you want to compare.<\/p>\n

For example, if you want to compare the value mentioned in cells A2 and B2, you can enter the following formula<\/a> –<\/p>\n

=A2=B2<\/strong><\/p>\n

If the values in the specified cells are equal<\/strong> then the function returns TRUE<\/strong> otherwise it returns FALSE.<\/p>\n

In this example<\/strong>, we are trying to individually compare two columns using the equal operator –<\/p>\n

STEP 1:<\/span><\/strong> Create a new column.<\/p>\n

\"6<\/a><\/p>\n

STEP 2:<\/span> Input the two cells<\/strong> that you are trying to compare separated by an equal sign operator<\/strong>.<\/p>\n

=A2=B2<\/strong><\/p>\n

\"6<\/a><\/p>\n

STEP 3:<\/span><\/strong> Copy the formula below to populate the formula for the remaining cells in the column.<\/p>\n

\"6<\/a><\/p>\n

You can see that the rows with matching values will return the value as TRUE otherwise it will return the value as FALSE.<\/p>\n

\"6<\/a><\/p>\n

 <\/p>\n

Method 2: EXACT Function<\/span><\/strong><\/p>\n

The Equal<\/strong> operator is used to compare cell values but it does not consider case sensitivity<\/strong>. The EXACT<\/strong> function is used specifically to compare text<\/strong> keeping in mind the\u00a0distinction between uppercase and lowercase letters<\/strong>. It will return TRUE only if the values are identical in a case-sensitive manner as well.<\/p>\n

Let’s compare two columns in this example using the EXACT function –<\/p>\n

STEP 1:<\/span><\/strong> Enter the EXACT<\/strong> function.<\/p>\n

=EXACT(<\/strong><\/p>\n

\"6<\/a><\/p>\n

STEP 2:<\/span><\/strong> Enter the first argument<\/strong> i.e. the first text that you need to compare. Here, it is in cell A2.<\/p>\n

=EXACT(A2,<\/strong><\/p>\n

\"6<\/a><\/p>\n

STEP 3:<\/span><\/strong> Enter the second argument<\/strong> i.e. the second text that you need to compare. Here, it is in cell B2.<\/p>\n

=EXACT(A2,B2)<\/strong><\/p>\n

\"6<\/a><\/p>\n

STEP 4:<\/span><\/strong> Copy the formula below<\/strong> to populate the formula for the remaining cells in the column.<\/p>\n

\"6<\/a><\/p>\n

You can see when the values in columns A and B are exactly the same in case sensitive manner<\/strong>, the function is returning the value as TRUE<\/strong> or else FALSE<\/strong>.<\/p>\n

\"6<\/a><\/p>\n

 <\/p>\n

Method 3: IF Function<\/span><\/strong><\/p>\n

You can use the IF function in Excel to compare two columns<\/strong><\/a> and specify the desired output<\/strong> if the comparison is TRUE or FALSE. As compared to the equal operator, it helps you in providing more flexibility<\/strong> as opposed to an equal operator where you can only display boolean results – TRUE or FALSE.<\/p>\n

Here’s an example –<\/p>\n

STEP 1:<\/span><\/strong> Enter the IF<\/strong> function.<\/p>\n

=IF(<\/strong><\/p>\n

\"6<\/a><\/p>\n

STEP 2:<\/span><\/strong> Enter the first argument<\/strong> i.e. the logical test.<\/p>\n

=IF(A2=B2,<\/strong><\/p>\n

\"6<\/a><\/p>\n

STEP 3:<\/span><\/strong> Enter the second argument<\/strong> i.e. the value if the condition is true.<\/p>\n

=IF(A2=B2,”Match”,<\/strong><\/p>\n

\"6<\/a><\/p>\n

STEP 4:<\/span><\/strong> Enter the third argument<\/strong> i.e. the value if the condition is false.<\/p>\n

=IF(A2=B2,”Match”,”No Match”)<\/strong><\/p>\n

\"6<\/a><\/p>\n

STEP 5:<\/span><\/strong> Copy the formula below<\/strong> to populate the formula for the remaining cells in the column.<\/p>\n

\"6<\/a><\/p>\n

You can see when the values in columns A and B are exactly the same<\/strong>, the function returns the value as TRUE<\/strong> or else FALSE<\/strong>.<\/p>\n

\"6<\/a><\/p>\n

 <\/p>\n

Method 4: Conditional Formatting<\/span><\/strong><\/p>\n

To use conditional formatting to compare two columns in Excel<\/a>, follow the steps below –<\/p>\n

STEP 1:<\/span><\/strong>\u00a0<\/b>S<\/strong><\/a>elect the cell range<\/strong> where you want the formatting to be shown.<\/p>\n

\"6<\/a><\/p>\n

STEP 2:<\/span><\/strong> <\/b><\/a>Go to\u00a0Home\u00a0>\u00a0Condition Formatting\u00a0>\u00a0New Rule.<\/strong><\/p>\n

\"6<\/a><\/p>\n

STEP 3: <\/span><\/strong><\/a>In the New Formatting Rule dialog box, select Use a Formula to determine which cells to format<\/strong>.<\/p>\n

\"6<\/a><\/p>\n

STEP 4: <\/span><\/strong><\/a> Type the formula<\/strong> stated below \u2013<\/p>\n

=$A2<>$B2<\/strong><\/p>\n

\"6<\/a><\/p>\n

STEP 5:<\/span><\/a> <\/strong>Click on the\u00a0Format<\/strong>\u00a0button.<\/p>\n

\"6<\/a><\/p>\n

STEP 6:<\/span><\/a> <\/strong>Under the Fill<\/strong> tab, select the light red color. Click OK.<\/p>\n

\"6<\/a><\/p>\n

STEP 7:<\/span><\/a><\/strong> Double-check the result under Preview and then click OK<\/strong>.<\/p>\n

\"6<\/a><\/p>\n

And Voila, you will see that the desired result has been achieved. The cell values in column A that do not match Column B will be highlighted in light red fill.<\/p>\n

\"6<\/a><\/p>\n

 <\/p>\n

Method 5: MATCH Function<\/span><\/strong><\/p>\n

The MATCH function is typically used to return the position of an item in a range. But, you can also use it to compare two columns and check if a specific item in List1\u00a0<\/em>exists in\u00a0List2<\/em><\/strong>. The function will\u00a0return the row position of that item in\u00a0List2\u00a0<\/em>hence confirming that it exists<\/strong>.\u00a0 If you get a\u00a0#N\/A it means that the cell\u00b4s item does not exist<\/strong>\u00a0in\u00a0List2<\/em>.<\/p>\n

Let’s understand it better with an example –<\/p>\n

STEP 1:<\/span><\/a><\/strong> Enter the MATCH<\/strong> function.<\/p>\n

=MATCH(<\/strong><\/p>\n

\"6<\/a><\/p>\n

STEP 2:<\/span><\/a><\/strong> Enter the first argument – lookup_value<\/strong>. Here, it is mentioned in cell A2.<\/p>\n

=MATCH(A2,<\/strong><\/p>\n

\"6<\/a><\/p>\n

STEP 3:<\/span><\/a><\/strong> Enter the second argument – lookup_array<\/strong>. Here, it is the range B2:B30.<\/p>\n

=MATCH(A2,$B$2:$B$27,<\/strong><\/p>\n

\"6<\/a><\/p>\n

STEP 4:<\/span><\/a><\/strong> Enter the third argument – match_type<\/strong>. Here, it is 0 for an exact match.<\/p>\n

=MATCH(A2,$B$2:$B$27,0)<\/strong><\/p>\n

\"6<\/a><\/p>\n

STEP 5:<\/span><\/strong> Copy the formula below<\/strong> to populate the formula for the remaining cells in the column.<\/p>\n

\"6<\/a><\/p>\n

You can see when the values in columns A and B are the same<\/strong>, the function returns the value as a row number<\/strong>\u00a0or else #N\/A<\/strong>.<\/p>\n

\"6<\/a><\/p>\n

 <\/p>\n

Method 6: Highlight Row Difference<\/span><\/strong><\/p>\n

You can easily\u00a0highlight differences in value in each row<\/strong> using an in-built feature in Excel. It will provide you with an idea of how many lines in the columns differ in values.<\/p>\n

In the data below, you have two lists in Column A and Column B respectively.<\/p>\n

Follow the\u00a0steps below<\/strong> to compare two columsn and highlight row difference<\/a> :<\/p>\n

STEP 1:<\/span><\/strong> Select both columns.<\/p>\n

\"6<\/a><\/p>\n

STEP 2:<\/span><\/strong>\u00a0Go to\u00a0Home<\/strong>\u00a0>\u00a0Find & Select<\/strong>\u00a0>\u00a0Go To Special<\/strong>\u00a0or simply press keys\u00a0Ctrl + G<\/strong>\u00a0and Select\u00a0Special<\/strong>\u00a0to open the Go To Special dialog box.<\/p>\n

\"6<\/a><\/p>\n

STEP 3:<\/span><\/strong>\u00a0Select\u00a0Row Difference<\/strong>\u00a0and Click\u00a0OK<\/strong>.<\/p>\n

\"6<\/a><\/p>\n

And, Voila!<\/p>\n

\"6<\/a><\/p>\n

All the values in List 2 that do not match the corresponding value in List 1 have been highlighted.<\/p>\n

STEP 4:<\/span><\/strong>\u00a0You can mark these cells with color as well. Go to\u00a0Home<\/strong>\u00a0>\u00a0Font Color<\/strong>\u00a0> Select\u00a0Red<\/strong>.<\/p>\n

\"6<\/a><\/p>\n

This will permanently\u00a0highlight the cells in red font color<\/strong>\u00a0for future reference.<\/p>\n

\"6<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

Comparing data by analyzing and identifying similarities and differences is a task that is frequently encountered in various contexts. This task can be performed manually by visually looking at data one by one but it can be both time-consuming and prone to errors. Fortunately, Excel provides a solution for this issue! There are several techniques […]<\/p>\n","protected":false},"author":1,"featured_media":29566,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"cos_headline_score":0,"cos_seo_score":0,"cos_headline_text":"6 Different Ways to Compare Two Columns in Excel - A Detailed Tutorial","cos_headline_has_been_analyzed":false,"cos_last_analyzed_headline":[],"om_disable_all_campaigns":false},"categories":[35,479,283,276],"tags":[1259,1258,1257,1260],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/29426"}],"collection":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/comments?post=29426"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/29426\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/29566"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=29426"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=29426"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=29426"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}