Learn how to compare values 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 values of two cells.

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

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

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

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

  • =EXACT(A2, B2)
  • Now press ENTER.

compare-values3Now, cell C2 is showing False because John and Johm do not match exactly.

compare-values4Step 3: Copy the formula into another cell of column C. Now we will get above result like the picture. In 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.

compare values in Excel Step 5Sometimes 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 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.

compare values in Excel Step 6Step 2: Now we can see that the result is showing True for cell A2 and B2. A2 contain John and B2 contain Johm. In 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.

compare-values7Step 3: After using the formula in another cell we can see that 3 rows are showing the FALSE result in above picture. Because their length of characters is not 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:

  • =IF(LEN(A2)=LEN(B2), “Equal”, “Not equal”)

So, now we have learned successfully learned two ways to compare two cells value!

Pre-Registration Open

Power BI Dax

Related Tutorials

Text To Column in Excel | Split Cells
January 4, 2019
Pivot Tables (Complete Guidelines)
December 27, 2018
Power Bi Dax Deduplication Based On Column
December 17, 2018
Special Character Symbol List with Shortcodes in Excel
December 15, 2018
December 10, 2018
December 10, 2018
How to use ABS Function in Excel
December 10, 2018
How to use AND Function in Excel
December 10, 2018
Delete Duplicate in Excel or Remove Duplicate in Excel
November 9, 2018
Excel Formulas PDF
September 6, 2018
How To Lock Cells in Excel | Unprotect Excel
August 13, 2018
4x Faster at Excel
August 6, 2018
Separate Content of One Excel Cells into Separate Columns
August 3, 2018
How to Transpose Excel Columns to Rows | Paste Special Method
July 26, 2018
How to create sparklines in Excel
July 19, 2018
AutoSum in Excel with Shortcut
July 17, 2018
OFFSET Function in Excel
July 6, 2018
Strikethrough Shortcut in Excel & Word
July 4, 2018
INDIRECT Function with SUM, MAX, MIN & Independent Cell Value
June 29, 2018
Pivot Table Slicers In Excel
June 12, 2018
How to Wrap Text in Excel Automatically and Manually
June 6, 2018
How to Hide/Unhide Column in Excel
June 5, 2018
Highlight row based on cell value
June 4, 2018
Learn how to remove blank cells in Excel
June 3, 2018
How to Group Numbers, Dates & Text in Pivot table in Excel
June 1, 2018