How to Check If Multiple Cells Are Equal in Excel (4 Methods)

In our everyday life, especially in our official and business purposes, we use Excel. So, it has become a part of our corporate work. When we work with a large amount of data, sometimes we need to check if multiple cells are equal in Excel. In this article, we are going to discuss this topic. We’ll see how to check if multiple cells are equal or not in Excel.

To explain this, we’ll input a group of words in some random cells in different columns and will check if they are equal or not.

Data set to check if multiple cells are equal


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


4 Methods to Check If Multiple Cells Are Equal in Excel

Here we will discuss four methods to solve this problem. In the data set, we will take a new column named Status to show the result of the applied methods.

Add a column to show the results


1. COUNTIF Function to Check Multiple Cells Are Equal

In this segment, we will use the COUNTIF function to solve this problem. This function counts the number of cells within a range with a given condition. To make the result easily understandable we used cell numbers. All the process is shown in the below steps.

Step 1:

  • First, go to Cell E6. Here we will compare 3 cells of Test1, Test2 & Test3 Columns. The cells are B5, C5, D5.
  • Write the COUNTIF function.
  • Select range B5 to D5 as we will check these cells.
  • In the last argument select B5. It may be any cell of our selected range. It’s used as a reference to check. So, our final formula becomes:
=COUNTIF(B5:D5,B5)

COUNTIF formula to check multiple cells

Step 2:

  • Now, press Enter, and we’ll get a return value.

Step 3:

  • The result is shown 3. That means it found 3 similar values in this range. But we want to show if all the values are the same or not. It will be easier for the presentation.
  • So, edit the formula and put Equal(=) sign and 3 at the end of the last formula. Here we used 3 as we are checking 3 Cells. And the formula becomes:
=COUNTIF(B5:D5,B5)=3

Modify the COUNTIF formula to check the multiple cells

Step 4:

  • Now, press Enter and we’ll get a result with a Boolean value TRUE.

Step 5:

  • Now, pull down the Fill Handle icon from Cell E5. Get results for the rest of the cells. We will get TRUE if all values are the same and FALSE

COUNTIF formula final result after checking multiple cells

Here, the COUNTIF function is case-insensitive. If we want to check exact matching with case sensitivity, this method will not work.

Read More: How to Select Multiple Cells in Excel (7 Quick Ways)


2. Apply AND Function to Check Multiple Cells Are Equal

We can check if multiple cells are equal by using AND function. This function checks all the values one by one and gives a result. If all the values are the same it will show TRUE, otherwise FALSE. Here we show two criteria. One is to check cells with cells and another one is to range with range.

Step 1:

  • Go to edit mood in cell E5.
  • Write AND function.
  • In the 1st argument, select B5.
  • Put an Equal(=) sign.
  • In the 2nd argument, select Range D5:E5. So, the formula becomes:
=AND(B5=C5:D5)

Write formula applying AND function to check multiple cells

Step 2:

  • Then press Enter.

Step 3:

  • As all values are the same in that range, the result is TRUE. Pull down the Fill Handle icon till Cell E8.

AND function result after checking multiple cellsAnd you’ll get the result of the whole range. Cell E8 shows FALSE, as all the values of the cells are not the same.


AND Function to Check for Ranges

Using the AND function we can also check if a range of cells is equal to another range.

Step 1:

  • In Cell E5, write the AND function again.
  • Now, in the 1st argument, select the range of cells, B5 to B8.
  • For the 2nd argument, select the range of cells, C5 to C8. So the formula becomes:
=AND(B5:B8=C5:C8)

Modify the AND functionStep 2:

  • Now press Enter.

Step 3:

Now, we want to check Column C and Column D. Again, type the formula to check the range C5 to C8 and D5 to D8. And the new formula becomes:

=AND(C5:C8=D5:D8)

Apply modified AND function in cellStep 4:

  • Finally, press Enter and get the result.

In Cell E5 we get TRUE and in Cell E6 get FALSE. Because range B5 to B8 is the same as range C5 to C8, but range C5 to C8 is not the same as range D5 to D8.

Read More: How to Select a Range of Cells in Excel (9 Methods)


Similar Readings


3. Use Excel EXACT Function to Check If Multiple Cells Are Equal

The EXACT function is used to check if comparing cell values are the same or not. If same, it will return TRUE, otherwise FALSE. One advantage of using this function is that it is case-sensitive. Other methods are case-insensitive. Like AND function this function is also used for two criteria. One is to check cells with cells and another one is to range with range.

Step 1:

  • First, go to Cell E5.
  • Write the EXACT function.
  • We select the B5, C5, D5 Cells to check.
  • In the last argument select B5 as the reference value. The formula is:
=EXACT(B5:D5, B5)

Write EXACT function to check if multiple cells are equal

Step 2:

  • Now, press Enter and you’ll get a return value, TRUE.

Step 3:

  • Results are shown in 3 cells as we selected 3 cells.
  • Pull down the Fill Handle icon from Cell.

Step 4:

  • But we want to get one result for all the 3 cells in each row. Cell F6 and Cell G8 are showing FALSE, and the rest are TRUE. Cell F6 is showing the status of Cell C6. In Cell C6, the text string starts with a block letter. In the case of G8, it shows the status of Cell D8.
  • Now, edit the formula and add AND It will show results only in one cell instead of different cells. So, the new formula becomes:
=AND(EXACT(B5:D5,B5))
Insert AND function with EXACT function to check if multiple cells are equal

Step 5:

  • Then press Enter.

Step 6:

  • In the status section, we’ll get TRUE as the return value.
  • Now pull down the Fill Handle icon from Cell E5.

AND and EXCAT function result after checking multiple cells

Finally, we’ll obtain the result for the whole range.


Using EXACT Function to Check One Range with Another

Using the EXACT function, we can also check if a range is equal to another range. Here we’ll just modify the previous formula only.

Step 1:

  • Now, edit the formula on Cell E5.
  • In the 1st argument of the EXACT function, change the range into B5:B8.
  • Then put a Comma( , ).
  • And in the 2nd argument, change the range to C5:C8. So, the formula becomes:
=AND(EXACT(B5:B8,C5:C8))

Apply EXACT function to check multiple ranges

Step 2:

  • Now press Enter.

Here, we’ll get the return value FALSE. Since in the EXACT function, we see that it can detect the case-sensitive values as well. So, we can use it for case-sensitive options.

Read More: Multiple Excel Cells Are Selected with One Click (4 Causes+Solutions)


4. Check If Multiple Cells Are Equal by Using IF Function

We can check if only 2 cells are equal using the IF Function. More than 2 cells checking other methods will need. This function is case-insensitive. So, in all cases, we cannot use it. If both cells are the same the result will be TRUE, otherwise, it will show FALSE.

Step 1:

  • Here we will check the values of Test1 and Test3 Columns. In Cell E5, type the IF function.
  • Select Cells B5 and D5 and put the Equal(=) sign between them.
  • Then set TRUE if the condition is true and otherwise So, the formula becomes:
=IF(B5=D5,"TRUE", "FALSE")

Apply IF function to check if multiple cells are equal

Step 2:

  • Now press Enter.

Step 3:

  • Pull the Fill Handle icon till last.

Result of IF function after checking multiple cells

This is our result. In Cell E8 we will get FALSE, as the values are not the same. This method is case-insensitive.

Read More: Excel if One Cell Equals another then Return another Cell


Conclusion

Here we’ve discussed 4 methods on how to check if multiple cells are equal in Excel. We’ve explained with COUNTIF, AND, EXACT & IF functions. Hope you will get your desired solution from here. If you have any suggestions regarding this topic, feel free to comment with us.


Related Articles

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

2 Comments
  1. Perfect, this is exactly what I was looking for! Easy, flexible formula.
    Thank you very much.

    • You are most welcome, MANUEL!
      We provide the best and easy solutions to Excel-related problems. You are invited to visit our blog for more such articles.

Leave a reply

ExcelDemy
Logo