Check Cells for Equality – Follow Up [Quick Tip]

Posted on October 16th, 2012 in Huis , Posts by Hui , Quick Tip - 19 comments

Last week in Write a formula to check few cells have same value [Homework],

Chandoo posed the question: Can you write a formula to check if a few cells are equal?


A lot of people answered with answers like:







Although these are all correct, the formulas can be simplified by using a built in function of Excel

The function is



In Excel the = sign does two things

1. When the = sign is the first character of a Cell, Named Formula or Reference it tells the Cell, Named Formula or Reference that it contains a formula

2. It acts as an equality function when it has a value or formula on either side of it


What does the = function do?

What does the = function do?

The = function returns a Boolean or TRUE / FALSE value depending on the equality of the values/formula on either side of it


In a blank cell enter =2=1

Excel will display FALSE to tell us that the values aren’t equal

Now change the formula to =2=2

Excel will display TRUE to tell us that the values are equal


Either side of the = sign can contain a Formula, Cell Reference etc

=(200/100)=2 will return TRUE

=SIN(PI()/2)=1 will return TRUE

=SIN(PI()/2)=15 will return FALSE

=-1=COS(PI()) will Return TRUE


=SIN(PI()/2)=A1 will return TRUE if cell A1 contains either a 1 or a formula that returns the value 1.

It will return False for all other values.


So the above, Homework, formulas can be simplified as :




Original: =IF(SUM(A1:A4)/COUNT(A1:A4)=A1,TRUE,FALSE)

Simplified: =SUM(A1:A4)/COUNT(A1:A4)=A1


Original: =IF(PRODUCT($A:$A)=A1^(COUNTIF($A:$A,A1)),”Yes”,”No”)

Simplified: =PRODUCT($A:$A)=A1^(COUNTIF($A:$A,A1))

and all will still, correctly, answer the question



If your formula started with an If(), see if you can rewrite it based on the above.

Written by Hui...
Tags: , , , ,
Home: Main Page
? Doubt: Ask an Excel Question

19 Responses to “Check Cells for Equality – Follow Up [Quick Tip]”

  1. Mawdo81 says:

    Sorry but =SUM(A1:A4)/COUNT(A1:A4)=A1 will fail in the case of: {2,1,3,2}
    Sum = 8, Count = 4 Sum/Count = 2 = A1 = True but 2<>1<>3<>2 although 2 =2!

  2. Renjith M Das says:

    Why not something even more simple?

    If you insist on the "If..", then

    =If (And(G2:G5=H2:H5),"True",False")

  3. Renjith M Das says:

    Please ignore above...I misread the question

    Please use the below

    ={If (And(G2:G5=G2),"True",False")}
    Ctrl+Shift+ Enter



  4. Renjith M Das says:

    Went back and saw that the formula I suggested is very similar to the obvious here is another


  5. Jorrie says:

    Dan u good ! Learned a new thing today.

    Here's my new formula

    Old Formula: {=IF(SUM(IF(A1=$A:$A,1,0))=COUNTA($A:$A),TRUE,FALSE)}

    New Formula: {=SUM(IF(A1=$A:$A,1,0))=COUNTA($A:$A)}

  6. Valentin says:

    your formula with the equal sign to check ceels for equality works correctly with numbers but not with letters.

    • Mike Coffey says:

      Should you want to compare cellular equality with letters or text, use the =EXACT(D4,D) The result will be either TRUE or FALSE.


  7. shairal says:

    Quick question, what does the ^ in
    =PRODUCT($A:$A)=A1^(COUNTIF($A:$A,A1)) do?


  8. ^ raises to the power of.
    But that formula fails for {2,2,1,1} Product is 4, countif is 2, 2^2 is 4 but whilst 2=2 & 1=1, 1<>2.

  9. Leonid K. says:

    My solution is: COUNTDIFF(A1:A4,1,)=1
    Excel function COUNTDIFF counts the number of unique values in a range or an array.
    SYNTAX :
    - Array (range or array) : can contain any type of data (numbers, text, empty cells...).
    - Blanks (boolean, optional) : specifies if empty cells must be taken into account (default : FALSE).
    - Exclude (value, range or array, optional) : value(s) which must be excluded from the count.
    Unlike many posted solutions, this function allows handling blank cells.

  10. Andrew says:


    Thank you! this is a great function. Did not know about it. 

  11. jeje says:


    is this correct? not sure if I understood the question correctly. 

  12. Quotenjunkie says:

    At the moment I am trying to learn array formulas,

    is this also correct ?


  13. Ankit says:

    Thanks man. You are a live saver 😀

Leave a Reply