Hassan Shuvo
If you have a large data set and you want to check whether your dataset contains any duplicate values, this article is just right for you. In this article, we will demonstrate how you can check if multiple cells are equal in Excel. I hope the journey will be pleasant enough for you.
📁 Download Excel File
You can download this file to practice with us.
Learn to Check If Multiple Cells Are Equal in Excel with These 7 Methods
In this article, we have accumulated 7 simple yet beautiful methods to check if multiple cells are equal in Excel. We’ll use functions like AND, EXACT, COUNTIF, IF, OR, and DELTA functions to accomplish this. Besides, we will use a list of groceries for 3 days and another column to check whether our groceries are a match. Let’s start with the main part of this article.
1. Using Conventional Formula
We chose the easiest formula of them all. In this section, we will see whether the contents of two or more cells are equal or not just by placing the Equal (=) sign in between them. If we find any matches among the cells, the formula will show TRUE, and if not, then FALSE. Let’s see, step by step.
⬇️⬇️ STEPS ⬇️⬇️
- First, we will compare the contents of Columns B and C.So, selecting cell E6, we will write this formula in Formula Bar.
=(B6=C6)
- Then, hit Enter and you will see the cell showing TRUE as the formula did find cells B6 and C6.
- Finally, to copy the formula down the column use Fill Handle.
Now, to check if multiple cells are equal, we can use a formula like this.
- First, select, cell E6 and enter this formula.
=(B6=C6:D6)
- Next, get the result by hitting Enter.
- Again, use Fill Handle to copy the formula in other rows.
As you can see, we compared B6 with C6 and D6, and because of the formula, Excel compared each one of the contents individually and published the result in a similar fashion.
Read More: How to Select Multiple Cells in Excel (10 Quick Methods)
2. Applying AND Function
The problem with the previous method is that for every set of data the formula compared, it published an individual result. But we want to compare a particular cell with a range of cells. Also published one distinct result. So how can we do that? We will use the AND function in this case. AND is a logical function in Excel that is used to need more than one condition at the same time. It can either return TRUE or FALSE.
Syntax of the AND function:
AND(logical1, [logical2], …)
The arguments:
logical1 – the first criterion or logical value to be evaluated.
logical2 – the second criterion or logical value to be evaluated. This one is optional.
You can add more conditions if you want.
Let’s use the formula in our example step by step.
⬇️⬇️ STEPS ⬇️⬇️
- First, select cell E6 and type this formula in the formula bar.
=AND(B6=C6:D6)
- Then, hit Enter and see the result.
- Finally, use Fill Handle to copy the formula down the column.
Now the formula generated only one result, which means it compared cell B6 with C6 and D6 and found no similarities with both of them. That’s why it published FALSE.
Read More: 10 Ways to Select Multiple Cells Without Mouse in Excel
3. Employing AND and EXACT Functions
If your contents in the cells are case-sensitive you can use the EXACT function in conjunction with the AND function. The EXACT function examines two text strings, taking upper and lower case characters into consideration, and returns TRUE if they are the same and FALSE otherwise.
Syntax of the EXACT function:
EXACT(text1, text2)
text1 – The initial text string to be compared.
text2 – second text string to be compared.
Let’s use the formula in our example.
⬇️⬇️ STEPS ⬇️⬇️
- To begin, select cell E6 and type this formula in the formula bar.
=AND(EXACT(B6:D6,B6))
- Next hit Enter button and see the result.
- Finally, use the Fiil Handle tool to copy the formula.
As you can see, despite the fact that the rows contain similar text but in different cases, the formula returns false because we used the exact function in E8 cell.
🔨 Formula Breakdown
👉 In the formula, the EXACT function first searches for texts that have similar cases.
👉 If the EXACT function finds a match, the AND function returns TRUE, if not then FALSE.
Read More: 14 Examples to Select Cells with Keyboard Shortcut in Excel
4. Utilizing COUNTIF Function
The COUNTIF function is also helpful in checking if multiple cells are equal or not. This formula does not generate TRUE or FALSE results but rather generates the number of duplicate results as an integer number. However, we can modify the formula to show the true or false results. Let’s learn about this function.
Syntax of the function:
COUNTIF(range, criteria)
The arguments:
range – the number of cells to be counted.
criteria – the criterion that governs which cells are counted.
COUNTIF counts the number of cells in a range that satisfy a single condition. It may count cells including dates, numbers, or text. COUNTIF‘s criteria support logical operators (>,>,=) and wildcards (*,?) for partial matching.
⬇️⬇️ STEPS ⬇️⬇️
- First, select E6 where we want to show our result, and type this formula in the formula bar.
=COUNTIF(B6:D6,B6)
- Then, press Enter to see the number of duplicates. As you can see within cells B6 to E6 the formula found two duplicates.
- As usual, use the Fill Handle tool to autofill the formula in the other rows.
- If you don’t want to see the number of duplicates but rather just TRUE or FALSE to make sure there are duplicates, use this formula instead.
=COUNTIF(B6:D6,B6)>1
- Then, press Enter and subsequently Fill Handle tool to repeat the process.
5. Applying IF Function
The IF function is one of the most popular functions used in Excel. You can also use this function to check if multiple cells are equal in Excel. Moreover, there is an extra advantage to using this function. If the function funds anything duplicate in your specified range, you will receive a custom message. Let’s learn about the IF function.
Syntax of the function:
IF(logical_test, [value_if_true], [value_if_false])
The arguments:
logical_test – a logical phrase or value that may be evaluated as TRUE or FALSE.
value_if_true – when the logical test evaluates to TRUE, this value is returned.
value_if_false – when the logical test returns FALSE, this value is returned.
Let’s use the function in our example.
⬇️⬇️ STEPS ⬇️⬇️
- First, select your output sell as we selected cell E6 and copy this formula in the Formula Bar.
=IF(B6=C6,TRUE,FALSE)
- Next hit Enter and see the magic.
- Like always, apply Fill Handle to copy the formula to the entire column.
5.1. Applying IF with COUNTIF Function
We can also use the IF function with the COUNTIF function to get the same result.
⬇️⬇️ STEPS ⬇️⬇️
- First, select the output cell E6 and type this formula.
=IF(COUNTIF(B6:D6,B6)>1,TRUE,FALSE)
- Next hit Enter.
- Finally, use the Fill Handle to cover all the rows.
🔨 Formula Breakdown
👉 The COUNTIF function first counts the number of times the duplicates appear in the designated cells.
👉 Then the IF function returns true if the duplicates appear more than once.
5.2. Applying IF with AND and EXACT Functions
In the case of case-sensitive contents, we can use the IF function with the AND and EXACT functions.
⬇️⬇️ STEPS ⬇️⬇️
- First, select the output cell E6 and type this formula.
=IF(AND(EXACT(B6:D6,B6)),”Duplicate”,”Not Found”)
- Next hit Enter.
- Finally, use the Fill Handle to cover all the rows.
🔨 Formula Breakdown
👉 First, AND(EXACT(B6:D6,B6)) first searches for case-sensitive duplicates.
👉 If the AND function returns true, the IF function prints ‘Duplicate’ otherwise ‘Not Found’.
6. Using OR Function
There is another function we can use in this context. The OR function This function provides TRUE if any of the specified parameters is TRUE and FALSE if all of the supplied arguments are FALSE. You might be wondering if this function is comparable to AND function. But there is a distinct difference between these two. While the AND function returns true for finding match in every cell in the specified range, the OR function requires only one match to return true.
Syntax of OR function:
OR(logical1, [logical2], …)
The arguments:
logical1 – the first criterion or logical value to be evaluated.
logical2 – the second criterion or logical value to be evaluated. This one is optional.
⬇️⬇️ STEPS ⬇️⬇️
- First, select the output cell E6 and type this formula.
=OR(B6=B6:D6)
- Next hit Enter.
- Finally, use the Fill Handle to cover all the rows.
7. Applying DELTA Function
This is the final method and function we will be explaining. In the above methods, we used text examples, but in this case, we will use numbers, so if you want to find matches or duplicates within number data, you can use the DELTA function, which only works for numbers.
Syntax of DELTA function:
DELTA(number1, [number2])
The DELTA function determines whether two numeric values are equal. It yields 1 when the numbers are equal; otherwise, it returns 0.
⬇️⬇️ STEPS ⬇️⬇️
- First, select the output cell E6 and type this formula.
=DELTA(B6,C6)
- Next hit Enter.
- Finally, use the Fill Handle to cover all the rows.
📄 Important Notes
🖊️ IF function can be used to show customized text or value when finding a duplicate.
🖊️ Exact function is case-sensitive.
🖊️ DELTA function only works for numbers.
📝 Takeaways from This Article
📌 We discussed 6 functions in this article to check if multiple cells are equal in Excel.
📌 First, we demonstrated a basic formula you all know.
📌 Next, the AND function alone and combined with the EXACT function was shown.
📌 We then showed how the COUNTIF function can be helpful here.
📌 Then, the IF function came into action.
📌 Finally, we showed OR function and DELTA function in the case of numbers to finish the article.
📌 Lastly. the advantages and limitations of these functions are discussed.
Conclusion
So that’s it, folks. We have come to the conclusion of our article. I hope you understood the procedures with ease, and in the future, you will have no trouble checking if multiple cells are equal in Excel. If you like this article, don’t be shy to comment, and Moreover, if you know any other methods or functions that can achieve the same results, share them with us. Again, for more tutorials for Excel, please visit Excelden.com. Thank you.
Related Articles
- 3 Ways to Divide a Group of Cells by a Number in Excel
- 7 Ways to Assign Value If a Cell Contains Word in Excel
- 3 Ways to Clear Contents Without Deleting Formulas in Excel
