How to compare two columns and find differences in Excel?

Question

I have two columns in Excel that I want to compare and find the differences between them.Suppose: - Col A has 50 numbers, i.e. 0511234567 - Col B has 100 numbers in the same format

Answer 1

There’s a couple ways you can go about this. You can use a linear and logical method. This linear method will give you the basic answer of a difference while the logical method will give you flexibility to ask questions within the possible difference.

Below are a few ways to display the difference between 2 columns of data using:

A1 = 57

B1 = 22

The answer could show up in Cell C1 if you’d like so you can see it side by side.

Linear way:

Subtraction:

A1-B1 = 35

Logical:

Equal to:

A1=B1 = False

If statements:

if(A1>B1, “A is greater”, “B is greater or equal to”)

the above evaluates if A is greater than B then gives some outputs.

or

if(A1=B1, “this is equal”, “this is unequal”)

Whereas this function uses two logical statements to validate if this situation is equal or not)

Hope this helps!

Answer 2

If you're trying to see what is missing from either dataset, you can also use the vlookup function with an exact match and any #N/A's that appear will signal that the line is missing from the other dataset.

If you're trying to see the "difference" in math terms, you can take the SUM of both datasets.

=SUM(Dataset A) - SUM(Dataset B)

Answer 3

You can use Conditional Formatting to find the duplicates and highlight them. The un-highlighted cells are unique to each column.

Go to the Home tab, click on Conditional Formatting, Highlight Cell Rules, Duplicate Values. Make sure you selected the columns you are comparing first.

You can also remove duplicates in the Data tab.