How to differentiate data in Two Excel Spreadsheets

Have you wanted to compare two different spreadsheets in MS Excel to figure whether or not the data in them is different, or alternatively, whether there are any duplicate rows?

Manually going through the entire list of data is a herculean task. Fortunately, MS Excel has a function known as Countif () which makes this task easy. Let’s have a look at how this function works.

1. Let’s say your data is saved in Sheet1 and Sheet2 of the Excel workbook.
2. Enter the following formula in cell B1 of Sheet1 =COUNTIF(Sheet1!A:A,Sheet2!A) and Press Enter. If the record is unique the result will be 0, else the count will tell you how many rows in Sheet2 match column A of Sheet1.
3. Copy the formula from cell B1 through as many rows as you have in the table on Sheet1.

Using Vlookup

This function is commonly used to search and compare data in an Excel worksheet quickly. “V” in Vlookup stands for “Vertical.” This function has four parts – the lookup value, table range containing the value, the column to display once the match is found and the type of match. You can play around with this in various ways to compare or find differences in your data. Here’s one example.

1. Assume that you have a three column table which contains the Employee ID, Department and Name of the Employee. Let’s say want to return the name of the employee when you know his/her Employee ID. Follow the steps carefully.
2. Sort the values either in the ascending or descending order. Then click the first cell of the fourth column. Click on the “Formulas” tab and select the “Lookup & Reference” button.
3. From the drop-down list choose Vlookup(). The dialogue box opens.
4. In the “”Lookup value” field, enter the value you have to search.
5. Click the “Table Array” field, to select range of cells or the entire table which contains the data you are searching for.
6. In the “Column Index Number” field, enter “3,” that is it has to search in all three columns.
7. In the “Range Lookup” field, enter “False.” False indicates the exact match, while true will find a close enough match.  In the fx bar, the function will look like this – =VLOOKUP(38,A2:C10,3,FALSE)
8. Now click Ok Button. Excel will compare the “lookup value” against the three columns and display the cell value in the fourth column, if the match is found.

If you need to go deeper or are looking at a really large data set, there are a bunch of special tools that help you compare not only xls files, but also files in csv and other popular formats. One such tool is ExcelDiff by Suntrap systems. It detects difference in cell content, type, formatting and presents it in a easy-to-understand visual format. They have a free 30 day trial in case you’d like to experiment.  Another such popular tool is the DiffEngineX which is a bit more powerful in that it lets you compare “groups” of differences and even highlights the exact difference, even in formulas.

Of course, it’s always better to try things out for yourself. We’re always there to help you out like with our advanced Excel course, that can show you other better ways to achieve your goals.