Compare a Range 1

In Excel, you can compare the cells in two ranges with an array formula.

For example, to see if all of the values in A1:A100 are identical to those in B1:B100, type this array formula:


Note: This is an array formula and it must be entered using Ctrl-Shift-Enter.

The formula will return the number of corresponding cells that are different. If the formula returns 0, it means that the two ranges are identical.