How can I check if the values of multiple cells are equal?

Question

Let's say I have 6 different cells (that are not all in a line). I want to check if the values in these cells are equal. How could I do this with a function? I'd want the function simply to display "EQUAL" or "NOT EQUAL" (or maybe change the cell background color?).

Answer 1

One option for 6 cells would be this:

=IF(AND(A1=B2,B2=C3,C3=D4,D4=E5,E5=F6),"EQUAL","NOT EQUAL")

Another option - this way you don't need to reference the same cell twice:

=IF(AND(ARRAYFORMULA(A1={B2,C3,D4,E5,F6})),"EQUAL","NOT EQUAL")

---

If you wanted to color some cells if values in these cells are equal, you would need to create a Conditional Formatting rule with a similar formula:

1. Select the cells you want to color
2. Format > Conditional Formatting
3. Select "Custom formula is"
4. Fill in one of the above formulas without the IF part of formula, e.g.
   =AND(ARRAYFORMULA(A1={B2,C3,D4,E5,F6}))
5. Select the formatting style (color)
6. Done