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

`=EXACT(B5,C5)`

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

`=B5<>C5`

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

`=IF(B5<>C5,"Unique","Similar")`

⏩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 U**se 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-

`=$B5<>$C5`

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

`=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:**

**➥**** 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**–

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

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