How to Compare Text in Excel and Highlight Differences (8 Quick Ways)

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’.

Step 1:

⏩Activate Cell D5

⏩Then hit the Enter button.

Step 2:

⏩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.

Step 1:

⏩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.

Steps:

⏩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.

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.

Step 1:

⏩Select the data range B5:C12

⏩Then click as follows: Home > Conditional Formatting > New Rule

A formatting dialog box will open up.

Step 2:

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

⏩Click Format.

Then ‘Format Cells’ dialog box will appear.

Step 3:

⏩ 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.

Step 4:

⏩ 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.

Step 1:

⏩Right-click your mouse to the sheet title to open the VBA window.

Step 2:

⏩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.

Step 3:

⏩Select the range B5:C12

⏩Press OK then another dialog box will open up to select the second data range.

Step 4:

⏩Set the data range C5:C12

⏩Press OK again.

Step 5:

⏩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.

Step 1:

⏩ Select the data range B5:C12

⏩Then click as follows: Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.

A dialog box will open up.

Step 2:

⏩ 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.

Steps:

⏩Type the formula in Cell D5–

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

➥ COUNTIF($C$5:$C$12,$B5)=0

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-

FALSE

➥ 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.

Steps:

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

➥ VLOOKUP(B5,$C$5:$C$12,1,0)

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–

#N/A

➥ ISERROR(VLOOKUP(B5,$C$5:$C$12,1,0))

Then the ISERROR function will show “TRUE” for #N/A and “FALSE” for other outputs. For Cell B5 it will return as-

“TRUE”

Download Practice Book

Conclusion

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.

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