How to quickly check if two columns in Excel are equivalent in value?

I am interested in taking two columns and getting a quick answer on whether they are equivalent in value or not. Let me show you what I mean:

So its trivial to make another column (EQUAL) that does a simple compare for each pair of cells in the two columns. It's also trivial to use conditional formatting on one of the two, checking its value against the other.

The problem is both of these methods require scanning the third column or the color of one of the columns. Often I am doing this for columns that are very, very long, and visual verification would take too long and neither do I trust my eyes.

I could use a pivot table to summarize the EQUAL column and see if any FALSE entries occur. I could also enable filtering and click on the filter on EQUAL and see what entries are shown. Again, all of these methods are time consuming for what seems to be such a simple computational task.

What I'm interested in finding out is if there is a single cell formula that answers the question. I attempted one above in the screenshot, but clearly it doesn't do what I expected, since A10 does not equal B10.

Anyone know of one that works or some other method that accomplishes this?

This is the sort of thing array formulas were designed for. You just need one cell, one formula to give you a comparison:

``````=AND(A2:A11=B2:B11)
``````

Enter the formula by pressing Ctrl+Shift+Enter. If done correctly it will appear as the following:

``````{=AND(A2:A11=B2:B11)}
``````

EDIT:

For a case-sensitive comparison, the equals operator will not work. Instead, the EXACT() function should be used. Also enter this as an array formula.

``````{=AND(EXACT(A2:A11,B2:B11))}
``````
• That works perfectly and was exactly what I was looking for, thanks. – mindless.panda Jun 24 '11 at 17:49
• Only caveat I'll add after using this method is it ignores casing on text values. Not a problem for what I'm doing, but something to be aware of. – mindless.panda Jun 26 '11 at 19:27
• Right you are. I've edited my answer to include a case-sensitive solution. – Excellll Jun 27 '11 at 13:58

You can use the =AND() function on the third column. This function will return TRUE only if all the entries in the third column are true, otherwise it will return FALSE.

• I like this method because the third column is usually my goto method. Using =AND() makes identifying if there is a difference much faster. However it still requires that pesky third column. I'm really interested if this can also be avoided. I get the feeling it requires an array formula, but I woefully inexperienced in those. – mindless.panda Jun 24 '11 at 15:45
• The only other solution that I could think of would be a macro – EBGreen Jun 24 '11 at 15:47

I know this is an old question but here is my answer. If the two columns contain numbers only, then you can just find the difference between the numbers. If all the results are zero, then the two columns are equal. If not, then you'll have a non-zero number at the location.

• How is `=A2-B2` any different from `=A2=B2`?  The user still must (manually / visually?) scan a potentially very long column to find the row(s) where the values differ. – Scott Dec 12 '19 at 21:05

As long as you have the “EQUAL” column in Column `C`, you can use `=MATCH(FALSE, C:C, 0)` to find the first row that contains a `FALSE`; i.e., the first mismatch.

You can also (assuming data1 is in column A and data2 is in column B) enter a very simple formula below for a true/false if the 2 cells are equal in Column C. Column C: "=A1=A2"

• That's what s/he already has. – Ben N Aug 17 '15 at 17:04

I do this by making the equal column a 0 if equal and 1 if not. Then you can sum the third column.

• This is effectively the same as Greg’s answer, from two years ago. – Scott Jul 26 '13 at 2:05

Paste the code at the bottom into the column you want to display the equality result.

The comparison operator for `not equal to` is `<>`. It is used like this:

`=IF(A1<>B1, "not equal", "equal")`