In some cases, we may only need to compare texts of strings and highlight their similarities or differences in Excel. This article will guide you with eight easy methods to compare text in Excel and highlight differences.
How to Compare Text in Excel and Highlight Differences: 8 Ways
Let’s get introduced to our dataset first. I have placed some best-selling books in an online shop for two consecutive months. Now I’ll compare them and highlight differences using some easy techniques. First, I’ll show how to do it for the same rows.
Method 1: The EXACT Function
The EXACT function is used to compare two strings or data with each other and it gives us the result whether both data are an exact match or not. Let’s use it for our very first method. To show the output I have added a new column named ‘Remark’.
⏩Activate Cell D5
⏩Then hit the Enter button.
⏩After that double click the Fill Handle icon to copy the formula for the other cells.
Now take a look at the output that shows FALSE for different values and TRUE for matched values in the same row.
Method 2: The Boolean Logic
We can perform the same operation using simple Boolean logic. For the different values, it will show TRUE and FALSE for the matched values in the same row.
⏩Write the given formula in Cell D5–
⏩Press the Enter button and apply the Fill Handle icon to copy the formula.
Here’s our output-
Method 3: The IF Function
If you use the IF function with the Boolean logic then we can get the output with our specified text. I have set that it will show ‘Unique’ if it gets a different text and ‘Similar’ if gets the same text.
⏩In Cell D5 type the formula-
⏩Then just click the Enter button and use the Fill Handle tool.
Now you will get the output with specified text.
Read More: Return YES If 2 Cells Match in Excel
Method 4: Conditional Formatting with Formula
Conditional Formatting is a very convenient option to compare text and highlight differences in Excel. Here we can use pre-selected colors to highlight differences.
⏩Select the data range B5:C12
⏩Then click as follows: Home > Conditional Formatting > New Rule
A formatting dialog box will open up.
⏩ Press Use a formula to determine which cells to format from the Select a Rule Type box.
⏩Later, type the formula given below in Format values where this formula is true box-
Then ‘Format Cells’ dialog box will appear.
⏩ Choose your desired color from the Fill option. I have chosen lite green color.
⏩Press Ok and it will go back to the previous dialog box.
⏩ At this moment, just press OK.
Now you see that all the different values in the same row are highlighted with a picked color.
Method 5: Excel VBA Macros
Instead of using built-in functions, we can code in Excel to perform any desired operation. In this method, I will highlight differences in the same row using VBA codes.
⏩Right-click your mouse to the sheet title to open the VBA window.
⏩Write the codes given below-
Sub highlight() Dim yRange1 As Range Dim yRange2 As Range Dim yText As String Dim yCell1 As Range Dim yCell2 As Range Dim I As Long Dim J As Integer Dim yLen As Integer Dim yDiffs As Boolean On Error Resume Next If ActiveWindow.RangeSelection.Count > 1 Then yText = ActiveWindow.RangeSelection.AddressLocal Else yText = ActiveSheet.UsedRange.AddressLocal End If lOne: Set yRange1 = Application.InputBox("Range A:", "Compare Text", yText, , , , , 8) If yRange1 Is Nothing Then Exit Sub If yRange1.Columns.Count > 1 Or yRange1.Areas.Count > 1 Then MsgBox "Multiple ranges or columns have been selected ", vbInformation, "Compare Text" GoTo lOne End If lTwo: Set yRange2 = Application.InputBox("Range B:", "Compare Text", "", , , , , 8) If yRange2 Is Nothing Then Exit Sub If yRange2.Columns.Count > 1 Or yRange2.Areas.Count > 1 Then MsgBox "Multiple ranges or columns have been selected ", vbInformation, "Compare Text" GoTo lTwo End If If yRange1.CountLarge <> yRange2.CountLarge Then MsgBox "Two selected ranges must have the same numbers of cells ", vbInformation, "Compare Text" GoTo lTwo End If yDiffs = (MsgBox("Click Yes to highlight similarities, click No to highlight differences ", vbYesNo + vbQuestion, "Compare Text") = vbNo) Application.ScreenUpdating = False yRange2.Font.ColorIndex = xlAutomatic For I = 1 To yRange1.Count Set yCell1 = yRange1.Cells(I) Set yCell2 = yRange2.Cells(I) If yCell1.Value2 = yCell2.Value2 Then If Not yDiffs Then xCell2.Font.Color = vbRed Else yLen = Len(yCell1.Value2) For J = 1 To yLen If Not yCell1.Characters(J, 1).Text = yCell2.Characters(J, 1).Text Then Exit For Next J If Not yDiffs Then If J <= Len(yCell2.Value2) And J > 1 Then yCell2.Characters(1, J - 1).Font.Color = vbRed End If Else If J <= Len(yCell2.Value2) Then yCell2.Characters(J, Len(yCell2.Value2) - J + 1).Font.Color = vbRed End If End If End If Next Application.ScreenUpdating = True End Sub
⏩Later, press the Run icon to run the codes.
Now a dialog box will open up to select the first data range.
⏩Select the range B5:C12
⏩Press OK then another dialog box will open up to select the second data range.
⏩Set the data range C5:C12
⏩Press OK again.
⏩Now to highlight differences just press the No button.
Now, different text in the same rows is highlighted with red color.
3 Quick Ways to Compare Text in Excel and Highlight Differences for All Rows
Method 1: Conditional Formatting
We can use the Conditional Formatting tool to compare text in Excel and highlight differences for all the rows without any formula.
⏩ Select the data range B5:C12
⏩Then click as follows: Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
A dialog box will open up.
⏩ Select the Unique option and desired color from the Format cells that contain box.
⏩Finally, just press OK.
All the different texts are now highlighted with our picked color.
Method 2: IF+COUNTIF Functions
To compare text in Excel and highlight differences we’ll combine two functions which are- the IF function and the COUNTIF function. Here, we’ll check the text of Column B whether they are common in Column C or not in any row. The IF function checks whether a condition is met and returns one value if true and another value if false. The COUNTIF is used to count cells in a range that meets a single condition.
⏩Type the formula in Cell D5–
=IF(COUNTIF($C$5:$C$12,$B5)=0,"No match in C","Match in C")
⏩Hit the Enter button.
⏩Finally, use the Fill Handle icon to copy the combined formula.
Now we can easily find out the differences from the image below-
⏬ Formula Breakdown:
The COUNTIF function will check the value of Cell B5 through the range C5:C12 whether it is equal or not. If equal then it will return 1, otherwise 0. So the output will return as-
➥ IF(COUNTIF($C$5:$C$12,$B5)=0,”No match in C”,”Match in C”)
Finally, the IF function will show ‘No match in C’ for FALSE and ‘Match in C’ for TRUE. It will return as-
No Match in C
Method 3: ISERROR+VLOOKUP Functions
Lastly, let’s use another combination of functions to do the previous operation. We’ll apply the ISERROR and VLOOKUP functions. It will check the text of Column B through Column C, if it gets an uncommon text then it will show TRUE if not then it will show FALSE. The ISERROR function in Excel checks whether a value is an error and returns TRUE or FALSE. The VLOOKUP function is used to look up a value in the leftmost column of a table and returns the corresponding value from a column to the right.
⏩Type the given formula in Cell D5–
⏩Then just hit the Enter button and use the Fill Handle tool to copy the formula.
Now see the output in the image below-
⏬ Formula Breakdown:
The VLOOKUP function will check Cell B5 through the range C5:C12. If it finds a common value then it will show that value otherwise will show #N/A. So it returns for Cell B5–
Then the ISERROR function will show “TRUE” for #N/A and “FALSE” for other outputs. For Cell B5 it will return as-
Download Practice Book
I hope all of the methods described above will be good enough to compare text in Excel and highlight differences. Feel free to ask any questions in the comment section and please give me feedback.