How to compare if multiple cells are equal in Excel?
As we all know, to compare if two cells are equal, we can use the formula A1=B1. But, if you want to check if multiple cells have the same value, this formula will not work. Today, I will talk about some formulas to compare if multiple cells are equal in Excel.
Compare two ranges and find if the cells are equal or not:
With Kutools for Excel’s Compare Cells utility, you can quickly find the same or different values between two cells.
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!
Supposing I have the following data range, now I need to know if the values in A1:D1 are equal, to solve this task, the following formulas will help you.
1. In a blank cell besides your data, please enter this formula: =AND(EXACT(A1:D1,A1)), (A1:D1 indicates the cells that you want to compare, and A1 is the first value in your data range)see screenshot:
2. Then press Ctrl + Shift + Enter keys together to get the result, if the cell values are equal, it will display TRUE, otherwise, it will display FALSE, see screenshot:
3. And select the cell then drag the fill handle to the range that you want to apply this formula, you will get the result as follows:
1. The above formula is case sensitive.
2. If you need to compare the values without case sensitive, you can apply this formula: =COUNTIF(A1:D1,A1)=4, (A1:D1 indicates the cells that you want to compare, A1 is the first value in your data range, and the number 4 refers to the number of cells that you want to check if), then press Enter key, and you will get the following result:
Excel Productivity Tools
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 4 months ago=COUNTIF(A1:D1,A1)=4 ---> mind = blown
To post as a guest, your comment is unpublished.· 6 months agohow can i find the matching numbers in a column
To post as a guest, your comment is unpublished.· 7 months agohi, i want to display the value / text of a table if a particular value is below 100. is there any option
To post as a guest, your comment is unpublished.· 7 months agoHi
I need to highlight multiple numbers that appear three times or more in one column.
Can anyone assist?
To post as a guest, your comment is unpublished.· 7 months agoHello, Dal,
To highlight the cell values that appear three times or more, the Conditional Formatting can help you. Please enter this formula into the Conditional Formatting: =COUNTIF($F$2:$F$500,F2)>2 (Note: Change the cell references to your need),and then choose one color for highlighting the cells.
To post as a guest, your comment is unpublished.· 7 months agoThe "And(Exact...." works great if you have a set range. In my case, It is multiple cells within a range of columns.
Basically need the comparison to evaluate A7, B7, M7, AD7, AE7, AX7, BC7, and BQ7, and return "TRUE" if they are identical text.