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

We’ll input a group of words in some random cells in different columns and 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

In the data set, we will take a new column named Status to show the result of the applied methods.

Method 1 – COUNTIF Function to Check Multiple Cells Are Equal

Steps:

• Go to Cell E6. We will compare 3 cells of Test1, Test2 & Test3 Columns, in 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 can be any cell of the selected range.
• The formula should look like the following:

• Press Enter to get a return value.

• The result for the sample is 3. That means Excel found 3 identical 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.
• Edit the formula and put Equal(=) sign and 3 at the end of the last formula. We used 3 as we are checking 3 Cells. The formula becomes:

• Press Enter to get a result with a Boolean value TRUE.

• Drag down the Fill Handle icon from Cell E5 to get results for the rest of the cells.

The COUNTIF function is case-insensitive.

Method 2 – Apply AND Function to Check Multiple Cells Are Equal

Steps:

• Select cell E5.
• Write the AND function.
• As the 1st argument, select B5.
• Put an equals (=) sign.
• For the 2nd argument, select Range D5:E5. The formula becomes:

• Press Enter.

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

Case 2 – AND Function to Check for Ranges

Steps:

• In Cell E5, write the AND function again.
• As the 1st argument, select the range of cells, B5 to B8.
• For the 2nd argument, select the range of cells, C5 to C8.
• The formula becomes:

• Press Enter.

Now, we want to check Column C and Column D.

• Copy the formula to check the range C5 to C8 and D5 to D8. The new formula becomes:

• Press Enter and get the result.

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

The EXACT function is case-sensitive.

Steps:

• Go to Cell E5.
• Write the EXACT function.
• Select the B5, C5, D5 Cells to check.
• In the last argument, select B5 as the reference value. The formula is:

• Press Enter and you’ll get a return value TRUE for the example.

• Results are shown in 3 cells as we selected 3 cells.
• Pull down the Fill Handle icon to fill in the column.

However, we want to get one result for all the 3 cells in each row, since the current ones are confusing.

• Edit the formula and add AND before it. This will show results only in one cell instead of different cells.
• The new formula becomes:

• Press Enter.

• Pull down the Fill Handle icon from Cell E5 to fill the column.

Case 2 – Using EXACT Function to Check One Range with Another

We can modify the formula we reached in the previous case.

Steps:

• Edit the formula on Cell E5.
• In the 1st argument of the EXACT function, change the range into B5:B8.
• Put a Comma( , ).
• For the 2nd argument, change the range to C5:C8. The formula becomes:

• Press Enter.

Here, we’ll get the return value FALSE since cells B6 and C6 don’t contain exactly the same values.

Method 4 – Check If Multiple Cells Are Equal by Using IF Function

We will check the values of Test1 and Test3 Columns.

Steps:

• In Cell E5, type the IF function.
• Select Cells B5 and D5 and put the Equal(=) sign between them.
• Then put the result text “TRUE” if the condition is true and “FALSE” otherwise. So, the formula becomes:

• Press Enter.

• Pull the Fill Handle icon to the last cell in the column.

In Cell E8 we will get FALSE since the values are not the same. This method is case-insensitive.

Download Practice Workbook

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