Excel Diff: Easy Ways to Find (and Highlight) Data Set Differences

Excel is extremely versatile and this leads to it being the most popular spreadsheet software in the market today. This widespread use is more often that not likely to lead to situations where you want to find the difference between certain tables, columns or entire spreadsheets. In this article, we walk you through just how to do that.

We assume you have a basic working knowledge of Excel.If not, we do recommend you try out this Excel course that’s been made really easy or alternatively this Excel crash course for beginners.  Once you’re done, read on to learn about some of the most commonly used functions to compare and retrieve data in Excel.

Use Conditional Formatting to Differentiate and Highlight Values

Say you have two lists of data (List 1 and List2) in your excel worksheet. You want to compare them to find out whether they have some common data, or highlight the different data sets. How would you do it? Here’s one way you can use to compare two lists and identify the different or duplicate data.

1. Select the two columns that you want to compare
2. In the Home tab, click “Conditional Formatting”. You will get a drop-down menu.
3. Click on “Highlight Cell Rules,” and then go straight to “More Rules” and click on it.
4. Here you will find many options. Depending upon your preference choose one of them. For instance, say you want to highlight all the data that’s different in the both the columns. First select “Format only unique or duplicate values.“, then, choose “Format only unique”. Alternatively, if you want to format only duplicate values, select “Duplicate values”.
5. Now Click the “Format” button and set the font, border and fill options to highlight the cells you want.
6. Click “OK” to confirm the changes.

Using IF function to differentiate between values of two lists

If you have two columns containing numerical values, you can use the If() function to find whether the value of one column is greater than the other,  less, equal to or not equal to. Just follow these simple steps.

1. Lets say you have the list values in A1 and B1. Now place your cursor in C1.
2. Enter the function, “=if()”. Figure how  you want to compare the values in A1 to B1 – equal, greater, lesser etc. IF () has three parts. The first part is the conditional part, the second is the cell value if the condition is met and the third part is the cell value if the condition is not met. Here are the formulas that you can use.

• =IF(A1>B1,”True”,”False”)
• =IF(A1<B1,”True”,”False”)
• =IF(A1=B1,”True,”False”)
• =IF(A3<>B3,”true”,”false”) (not equal to)

You can use one of the functions at a time.

1. Enter the formula and press the “Enter” Key.
2. Now you can either copy and paste the formula into C2, C3  and so on. Or you can hold the handler of your cursor in C1 and drag it down.

How to Compare data in Two Excel Spreadsheets

Say you want to compare two different spreadsheets 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.