How to Compare 2 CSV Files in Excel (6 Easy Ways)

Microsoft Excel is a powerful software. We can perform numerous operations on our datasets using the tools and features of Excel. Sometimes, we have to make comparisons between multiple CSV files. The reason could be about finding the matches or the differences. There are some methods to carry out these operations. In this article, we’ll demonstrate 6 easy and quick methods to compare 2 CSV files in Excel. So, let’s explore them one by one.

How to Compare 2 CSV Files in Excel: 6 Easy Methods

For ease of understanding, we’re going to use 2 different CSV files. We put them in the same folder. One is File 1 and the other one is File 2.

Now, we’ll use these two CSV files to compare themselves using various methods in Excel. So, let’s explore them one by one.
Here, we have used Microsoft Excel 365 version, you may use any other version according to your convenience.

1. Viewing Side by Side

We can compare two different files by watching them together in side by side view. Excel has arranged this for us. Excel has its own command to view different workbooks side by side at a time. It’s simple and easy. So, let’s see it in action.

📌 Steps:

• At the very beginning, open the two CSV files.
• Then, go to any of these.
• After that, move to the View tab.
• Later, click on View Side by Side command on the Window group.
• Next, select Arrange All on the same group.

Note: The View Side by Side command will be only visible while you open two or more workbooks. Otherwise, it remains gray and inactive on the ribbon.

Your previous action will open the Arrange Windows dialog box.

• In the dialog box, select Vertical as Arrange type.
• Then, click OK.

Consequently, we can see the two files side by side in our display. Now, we can easily compare them and scroll them simultaneously.

Note: Here, we have done a little bit of formatting like fixing Column Width, and increasing the Font Size for better visual presentation. In actuality, CSV files show plain text without any kind of formatting.

Read More: How to Convert CSV to Excel with Columns

2. Using IF Function

In this second method, we’ll use the most popular IF function to compare 2 CSV files. So, without further delay, let’s dive in!

📌 Steps:

• First of all, create a new Excel workbook.
• In cell B2, write down the Heading as the CSV files.

• Secondly, select cell B4 and enter the following formula.

Here, the IF function inserts a logical_test that the value in cell B4 of File 1 shouldn’t be equal to the value of the corresponding cell B4 of File 2. And if the statement becomes true, the formula puts a 1 in cell B4 in the new workbook. Otherwise, it will put a 0.

• After that, press ENTER.

• Now, bring the cursor to the right-bottom corner of cell B4 and it’ll look like a plus (+) sign. Actually, it’s the Fill Handle tool.
• Thus, drag this to cell E4.

• Again, select cells in the B4:E4 range and drag the Fill Handle to cell E14.

It’ll give all the remaining cells’ results.

Here, 1 means there is a change in the values of these positions in File 1 and File 2. On the other hand, 0 indicates unchanged values.

Read More: Difference Between CSV and Excel Files

3. Utilizing AND, IF, and ISBLANK Functions

In the previous method, we did how we can compare 2 CSV files. But what should we do when we have blank cells in any of the files like the following?

In File 1, we got blank cells B10 and D8.

Moreover, we got cell C13 as blank in File 2.

In our previous method, these cells will get the result 1, but here we want to keep them blank in the new workbook. So, allow us to demonstrate the process below.

📌 Steps:

• In the new workbook, go to cell B4 and insert the following formula.

Here, we used the ISBLANK function to test if the cell is blank or not. Then, we assigned the OR function to combine two ISBLANK functions operating in two different CSV files. Now, it works as the logical_test of the first IF function. If it’s true, then the formula will return blank (“ ”). Otherwise, it’ll return the result of the previous method.

In the case of cell B4, both CSV files have values and they are the same. So, in the new workbook, cell B4 will get the value 0.

• As usual, hit the ENTER key.

We can notice that cells B10, C13, and D8 get blank as they were in the CSV files.

Read More: How to Convert CSV to XLSX Command Line

4. Inserting IF and COUNTIF Functions

In this section, we’ll use the combination of IF and COUNTIF functions. Follow the simple steps.

📌 Steps:

• At first, create a new column with the heading Compare under Column F.
• Then, proceed to cell F5 and write down the formula below.

This formula compares the values in the Unit columns of the 2 different CSV files. If they have the same values in the corresponding cell, then it returns 0. Otherwise, it gives 1 in the cell.

• Following this, press ENTER.

Read More: Convert CSV to Excel Automatically

5. Applying Conditional Formatting

In this method, we’ll apply the Conditional Formatting feature to compare 2 CSV files in Excel. So, let’s begin.

📌 Steps:

• Firstly, copy the worksheet from File 2 and paste it as a new worksheet in File 1.

Note: We are doing this because Conditional Formatting cannot be applied between two different workbooks.

• Secondly, select cells in the B4:E14 range.
• After that, go to the Home tab.
• Then, click on the Conditional Formatting drop-down on the Styles group.
• From the drop-down list, select New Rule.

Immediately, the New Formatting Rule dialog box appears before us.

• Here, choose Use a formula to determine which cells to format under the Select a Rule Type section.
• In the Format values where this formula is true box, write down the following formula.

• After that, click on the Format button.

Suddenly, the Format Cells wizard pops up.

• Later, advance to the Fill tab.
• Following this, choose Red from the available colors.
• Then, click OK.

• Presently, in the New Formatting Rule dialog box, click OK.

Here, we can clearly see that the unmatched cells in File 1 get highlighted with Red color. Now, the difference is clearly visible to us. That’s the beauty of Conditional Formatting.

6. Employing VBA Code

Have you ever thought of automating the same boring and repetitive steps in Excel? Think no more, because VBA has you covered. In fact, you can automate the prior method entirely with the help of VBA. So, let’s follow the steps below.

📌 Steps:

• Initially, get the two files in one worksheet like before.
• Then, go to the Developer tab.
• After that, click on Visual Basic on the Code group.

Instantly, the Microsoft Visual Basic for Applications window appears.

• Currently, jump to the Insert tab.
• After that, select Module from the options.

It inserts a code module where we can write down our VBA code to compare 2 CSV files.

• Now, paste the following code into the module.

Sub Compare_2_CSV()
Dim dRange As Range, Select_Cell As Range
Sheets(1).Activate
Set dRange = ActiveCell.CurrentRegion
For Each Select_Cell In dRange
If Select_Cell.Value <> Sheets(2).Range(Select_Cell.Address).Value Then
Sheets(2).Range(Select_Cell.Address).Interior.Color = vbRed
End If
Next Select_Cell
End Sub

• Therefore, Run the code.

Note: Make sure you select cell B4 in the File 1 worksheet before executing the code.
In the File 2 worksheet, you can notice the cells with different values from the File 1 worksheet get highlighted with red color.

Read More: How to Import Text File to Excel Automatically

Download Practice Workbook

You may download the following CSV files for better understanding and practice yourself.

Conclusion

This article explains how to compare 2 CSV files in Excel in a simple and concise manner. Don’t forget to download the Practice file. Thank you for reading this article. We hope this was helpful. Please let us know in the comment section if you have any queries or suggestions.

Related Articles

• How to Convert CSV to XLSX without Opening
• How to Open Notepad or Text File in Excel with Columns
• How to Convert CSV to XLSX
• How to Import CSV into Existing Sheet in Excel
• How to Import CSV Data into Excel

<< Go Back to Import CSV to Excel | Importing Data in Excel | Learn Excel