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

Get FREE Advanced Excel Exercises with Solutions!

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.

compare 2 csv files in excel

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.

Viewing Side by Side

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.

Working on Arrange Windows Dialog Box to see 2 CSV files side by side

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

Using View Side by Side tool to Compare 2 CSV files in Excel

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.

Using IF Function

  • Secondly, select cell B4 and enter the following formula.
=IF('[File 1.csv]File 1'!B4<>'File 2.csv'!B4,1,0)

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.

Applying formula to Compare 2 CSV files in Excel

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

Using the Fill Handle tool

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

It’ll give all the remaining cells’ results.

Using IF Function to compare 2 csv files in excel

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.

Utilizing AND, IF, and ISBLANK Functions

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.
=IF(OR(ISBLANK('[File 1.csv]File 1'!B4),ISBLANK('File 2.csv'!B4)),"  ",IF('[File 1.csv]File 1'!B4='File 2.csv'!B4,0,1))

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.

Utilizing AND, IF, and ISBLANK Functions to Compare 2 csv files

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

Utilizing AND, IF, and ISBLANK Functions to Compare 2 csv files with blank cells in Excel

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.
=IF(COUNTIF('File 2.csv'!$E$5:$E$14,E5)=0,1,0)

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.

Inserting IF and COUNTIF Functions

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.

Applying Conditional Formatting

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.
=B4<>”File 2”!B4
  • After that, click on the Format button.

Working on New Formatting Rule dialog box to compare 2 csv files in excel

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.

Applying Conditional Formatting to compare 2 csv files in excel


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.

Employing VBA Code

Instantly, the Microsoft Visual Basic for Applications window appears.

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

Inserting Code Module

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

VBA code

  • Therefore, Run the code.

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

Employing VBA Code to compare 2 csv files in excel

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


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shahriar Abrar Rafid
Shahriar Abrar Rafid

Shahriar Abrar Rafid, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, Bangladesh, has worked with the ExcelDemy project for more than 1 year. He has written over 100+ articles for ExcelDemy. He is a professional visual content developer adept at crafting scripts, meticulously editing Excel files, and delivering insightful video tutorials for YouTube channels. His work and learning interests vary from Microsoft Office Suites and Excel to Data Analysis, VBA, and Video recording and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo