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

In our everyday life, especially for 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.

Overview

In the following image, you will find an overview of the whole article.

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

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.

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:

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. The formula becomes:

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

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

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:

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 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:

Step 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-

Step 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.

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:

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:

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.

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:

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.

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:

Step 2:

• Now press Enter.

Step 3:

• Pull the Fill Handle icon till last.

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

Download Practice Workbook

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.

<< Go Back To Excel Compare Cells | Compare in Excel | Learn Excel