Compare cell values in excel

Índice Show

Case Study 1: Case-sensitive formula to compare values in Excel.
Learn compare values in Excel by following steps:-
Case Study 2: Compare values in Excel by string length.
How can we check text strings in each row contain an equal number of characters?

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 if multiple cells are equal with formulas

Compare if multiple cells are equal with formulas

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:

Notes:

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:

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. Click to download Kutools for Excel!

Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. Download and free trial Now!

Related article:

How to check if the number is integer in Excel?

Comparing values in Excel is something that we all do once in a while. Microsoft Excel offers a number of options to compare and match data, but most of them focus on searching in one column. When using Excel for data analysis, accuracy is the most vital concern. Incorrect information may lead to missed deadlines, misjudged trends, wrong decisions, and lost revenues. While Excel formulas are always perfectly true, their results may be wrong because some flawed data penetrated into the system.

In this case, the only remedy is to check data for accuracy. It’s no big deal to compare two cells manually, but it’s next to impossible to spot the differences between hundreds and thousands of text strings. In this tutorial, we will learn how to compare values in Excel and find matches and differences between them.

Now, let’s go through the following steps to compare the values of two cells.

Case Study 1: Case-sensitive formula to compare values in Excel.

Imagine that sometimes it may be important for you not only to compare text in two cells but also to compare the character case. Case-sensitive text comparison can be done using the Excel EXACT function

Learn compare values in Excel by following steps:-

So here are the steps that you have to follow to compare values in excel from 2 columns:

Step 1: First Select the Entire dataset where you compare the values
Step 2: Click on Home Tab in Excel
Step 3: Go to Style Group, click on “Conditional Formatting” Option
Step 4: Move your cursor on the Highlight Cell Rules Option
Step 5: Click On Duplicate Values in Excel Cell
Step 6: In Duplicate Values Dialog Box, select ‘Duplicate’
Step 7: Click the format button & specify the format, where you want to compare the values in excel
Step 8: Click OK & you will get compared Values in Excel

Now let’s see each step in detail.

Step 1: Create a table the same as the above picture. This table is showing the list of Two departments of an office. We will compare two cells between each row with a case-sensitive formula. Column C will show the exact matching of each cell. If the exact match found, then the result will be TRUE, if not then the result will be FALSE.

Step 2: Now input this formula in cell C2 to compare cell A2 and B2:

=EXACT(A2, B2)
Now press ENTER.

Now, cell C2 is showing False because John and Johm do not match exactly.

Step 3: Copy the formula into another cell of column C. Now we will get the above result like the picture. Here we can see only row 4 has the TRUE result. Means cell A4 and B4 contain the exact value Vijay, this is the reason we have got the result TRUE.

Tip 1: If you want to see Exact instead of TRUE as result and Not Exact instead of FALSE, you can use this formula instead of our previous formula and see the magic:

=IF(EXACT(A2 ,B2), “Exactly equal”, “Not equal”)

So, in this way, we can compare two cells value with Case-sensitive formula!

Read More: Learn Excel Vlookup Easy Tips & Tricks

Case Study 2: Compare values in Excel by string length.

Sometimes we want to check if the text strings in each row contain an equal number of characters.

How can we check text strings in each row contain an equal number of characters?

Easy, first, you get the string length of two cells using the LEN function and then compare the numbers.

Step 1: In our previous table-use column D as length match. We will type the formula in each cell of column D for length comparison of each cell between a particular row.

Supposing the strings to be compared are in cells A2 and B2, use the following formulas in cell D2:

=LEN(A2)=LEN(B2)
Now press ENTER and see the Magic.

Step 2: Now we can see that the result is showing True for cell A2 and B2. A2 contains John and B2 contains Johm. Here both names contain 4 characters, for this, the result is showing TRUE. Now copy the formula into another cell of column D and see the result.

Step 3: After using the formula in another cell we can see that 3 rows are showing the FALSE result in the above picture. Because their length of characters is not the same. Those rows are row 6, row 8 and row 9.

Read More: Pivot Table Tutorial On Grouping Numbers, Dates and Texts

Tip 2: If you want to see Equal instead of TRUE as result and Not Equal instead of FALSE, you can use this formula instead of our previous formula and see the magic: