How to compare two text columns in excel for matches

 

We can use a simple formula to do this real quick.

Let’s say we have two columns data like this.

Now, we can write the formula in the C2 Cell as =A1=B1

Then Press enter and then drag the formula down until where you want the result. The columns is filled with TRUE if the value in the first column matches with value in the second column other wise FALSE.

This is the output of the formula.

Compare two excel sheets and highlight the differences

Comparing two excel sheets for differences:

We can compare two excel Sheets for differences without any third party tool, just with the help of well known formulas.

This is achieved with the help of conditional formatting.

Steps:

1.Open the the workbook

2. Select the area of the worksheets in both of the excel files

Let's say Sheet1 looks like this.

And Sheet2 looks like this

There are some differences in both the sheets and we want to highlight those differences.

 For this we are using  conditional formatting formula for the selected ranges.

Click on conditional formatting on Home Tab. Select New Rule

Now Select "Use a formula to determine which cells to format" and Write the formula in the  Edit box as    =A1<>Sheet1!A1

Then click on Format button to select formatting options for highlighting. Here I want to highlight the differences in RED Colour. Select the colour you want and click OK.

Now the selected colour appears under formula like this.

4. Then click OK, the differences are highlighted with the format specified in the previous step.

Now you are able to see the differences highlighted in the red color.