You might be in a situation where you needed to compare two Excel Tables and faced many problems to get that done. Today I will let you know how to compare two Excel Tables with a simple formula. This procedure will only show if the two Microsoft Excel Tables are the same or not, but will not highlight the differences. One more important thing to know is that this trick works only if both Excel Tables have the same number of rows and columns.
Compare Two Excel Tables with a simple Formula
Let me take an example with two tables as Table1 and Table2 as shown below.
The formula which we are going to see is very simple and helps us to compare two Excel Tables. As you could see, two Excel tables look similar. But there are small differences in tables, which makes it very hard to manually compare every cell.
There is no need to fill rows and columns with formulas -you just need a simple formula to compare two Excel Tables.
Simple Formula – SUMPRODUCT
SUMPRODUCT is the simple formula which we would be using to compare two tables in Excel and get the job done.
So, go ahead and enter the below formula in Excel sheet which has tables Table2 and Table2.
=SUMPRODUCT((Table1=Table2)-1)=0
The ultimate result would be TRUE if two tables are the same and if there are any differences, the result would be FALSE.
Read: Microsoft Excel time-saving tricks for fast working.
How SUMPRODUCT helps us here?
SUMPRODUCT function performs multiplication on an array of table value comparison and then returns the sum.
=SUMPRODUCT((Table1=Table2)-1)
Result returned by above part of the formula would be checked to if it is zero or not which the other part of the formula is.
To learn more, select part of the formula which is (Table1=Table2) and press F9. This part compares each cell of two tables and returns TRUE or FALSE. So we could see only TRUE or FALSE as a result of this part of the formula.
If you also select -1 and press F9, then results show numbers.
If all comparisons are TRUE (1), then all numbers would be zero (1-1) which would return zero as the sum.
If any comparison is FALSE (0), then one of the numbers would be -1, which in turn the entire result is not equal to zero.
How many differences are there in the Excel Tables?
In order to know how many differences are there in two tables, then we need to make the small change to the formula.
Instead of checking the sum is equal to zero or not, you need to multiply the sum with -1.
=SUMPRODUCT((Table1=Table2)-1)*-1
Using this formula, it says that there are four differences in these two Excel Tables. It never highlights the differences.
Thus you can compare two Excel Tables with simple formula as explained.