How to compare Two Excel Tables with a simple Formula

You could be in a scenario the place you wanted to compare two Excel Tables and confronted many issues to get that completed. Today I’ll let you know the way to compare two Excel Tables with a simple formulation. This process will solely present if the 2 Microsoft Excel Tables are the identical or not, however is not going to spotlight the variations. One extra essential factor to know is that this trick works provided that each Excel Tables have the identical variety of rows and columns.

Compare Two Excel Tables with a simple Formula

Let me take an instance with two tables as Table1 and Table2 as proven under.

how to compare two excel tables with a simple formula - How to compare Two Excel Tables with a simple Formula

The formulation which we’re going to see could be very simple and helps us to compare two Excel Tables. As you may see, two Excel tables look comparable. But there are small variations in tables, which makes it very laborious to manually compare each cell.

There isn’t any want to fill rows and columns with formulation -you simply want a simple formulation to compare two Excel Tables.

Simple Formula – SUMPRODUCT

SUMPRODUCT is the simple formulation which we might be utilizing to compare two tables in Excel and get the job completed.

So, go forward and enter the under formulation in Excel sheet which has tables Table2 and Table2.

=SUMPRODUCT((Table1=Table2)-1)=zero

1554275858 628 how to compare two excel tables with a simple formula - How to compare Two Excel Tables with a simple Formula

The final consequence can be TRUE if two tables are the identical and if there are any variations, the consequence can be FALSE.

Read: .

How SUMPRODUCT helps us right here?

SUMPRODUCT operate performs multiplication on an array of desk worth comparability after which returns the sum.

=SUMPRODUCT((Table1=Table2)-1)

Result returned by above a part of the formulation can be checked to whether it is zero or not which the opposite a part of the formulation is.

1554275858 128 how to compare two excel tables with a simple formula - How to compare Two Excel Tables with a simple Formula

To be taught extra, choose a part of the formulation which is (Table1=Table2) and press F9. This half compares every cell of two tables and returns TRUE or FALSE. So we might see solely TRUE or FALSE as a results of this a part of the formulation.

If you additionally choose -1 and press F9, then outcomes present numbers.

1554275858 303 how to compare two excel tables with a simple formula - How to compare Two Excel Tables with a simple Formula

If all comparisons are TRUE (1), then all numbers can be zero (1-1) which might return zero because the sum.

If any comparability is FALSE (zero), then one of many numbers can be -1, which in flip the complete consequence isn’t equal to zero.

How many variations are there within the Excel Tables?

In order to know what number of variations are there in two tables, then we’d like to make the small change to the formulation.

Instead of checking the sum is equal to zero or not, you want to multiply the sum with -1.

=SUMPRODUCT((Table1=Table2)-1)*-1

1554275858 146 how to compare two excel tables with a simple formula - How to compare Two Excel Tables with a simple Formula

Using this formulation, it says that there are 4 variations in these two Excel Tables. It by no means highlights the variations.

Thus you may compare two Excel Tables with simple formulation as defined.

Leave a Comment