How can I compare two Excel spreadsheets for differences

You can watch a video tutorial here.

Comparing two Excel sheets for differences is an operation you will frequently need to do when working with datasets. This happens especially when you are consolidating data from multiple sources. You could compare the sheets by arranging them side-by-side and then manually identifying the differences but this method is highly prone to error. Excel provides ways in which this comparison can be automated. The method that you use depends on the type of comparison that you need to do. 

In this example, we will look at two scenarios.

  1. Compare missing data. We will compare two lists of customers received from two sales executives and identify the missing values in each sheet. This can be done using the VLOOKUP() function.
    • VLOOKUP() function: this searches for a value from Table 1 in the left-most column of a range from Table 2, and returns the value in the column specified from the row of the match in the range of Table 2.
      • Syntax: VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
        1. lookup_value: the cell reference of the first value in the key column from Table 1
        2. table_array: the range of Table 2 which is made constant with the addition of dollar signs ($)
        3. col_index_num: the number of the column in Table 2 that has to be retrieved
        4. range_lookup: TRUE will return an approximate match, FALSE will return an exact match
  2. Compare for incorrect data. We will compare two identical customer lists and identify order values that do not match. This can be done using the IF() function.
    • IF(): this evaluates a condition and returns a value depending on whether the condition is true or false
      • Syntax: IF(condition, true, false)
        1. condition: the condition or statement to be evaluated
        2. true: the value to be returned if the condition is true
        3. false: the value to be returned if the condition is false

Option 1 – Compare for missing data

Step 1 – Create a column for missing values on the first sheet

  • On the first sheet i.e. ‘Mary’, create a column called ‘Names from George’
  • Select the first cell in the column

Step 2 – Create the formula

  • Type the formula using cell references:

=VLOOKUP(Customer code from ‘Mary’, $Table from ‘George’&,2,FALSE)

  • Customer code from ‘Mary’ is the Lookup value
  • Table from ‘George’ is the Table-array, made constant by the addition of dollar signs
  • 2 is the Col_index_num as the value in the second column is to be returned
  • FALSE is the Range_lookup as we need an exact match
  • Press Enter

Note: A few things to keep in mind when using Vlookup:

  • The key that is common between the tables must always be in the left-most column of Table 2
  • Table 2, or the table that is being referred to must always be sorted on the key
  • It is better Table 2 has unique values or else this function may not return the desired result

Step 3 – Copy the formula

  • Using the fill handle from the first cell, drag the formula to the remaining cells

OR

  1. Select the cell with the formula and press Ctrl+C or choose Copy from the context menu (right-click)
  2. Select the rest of the cells in the column and press Ctrl+V or choose Paste from the context menu (right-click)

Step 4 – Identify the missing values

  • The customers that are present in the ‘Mary’ sheet and not in the ‘George’ sheet are indicated by the error values i.e. #N/A

Step 5 – Create a column for missing values on the second sheet

  • On the second sheet i.e. ‘George, create a column called ‘Names from Mary’
  • Select the first cell in the column

Step 6 – Create the formula

  • Type the formula using cell references:

=VLOOKUP(Customer code from ‘George’, $Table from ‘Mary’&,2,FALSE)

  • Customer code from ‘George’ is the Lookup value
  • Table from ‘Mary’ is the Table-array, made constant by the addition of dollar signs
  • 2 is the Col_index_num as the value in the second column is to be returned
  • FALSE is the Range_lookup as we need an exact match
  • Press Enter

Step 7 – Copy the formula

  • Using the fill handle from the first cell, drag the formula to the remaining cells

OR

  1. Select the cell with the formula and press Ctrl+C or choose Copy from the context menu (right-click)
  2. Select the rest of the cells in the column and press Ctrl+V or choose Paste from the context menu (right-click)

Step 8 – Identify the missing values

  • The customers that are present in the ‘George’ sheet and not in the ‘Mary’ sheet are indicated by the error values i.e. #N/A

Option 2 – Compare for incorrect data

Step 1 – Create a column for comparison on the first sheet

  • On the first sheet i.e. ‘21Jan’, create a column called ‘Compare’
  • Select the first cell in the column

Step 2 – Create the formula

  • Type the formula using cell references:

=IF(Order Value from 21Jan = Order Value from 22Jan, TRUE,FALSE)

  • Press Enter

Step 3 – Copy the formula

  • Using the fill handle from the first cell, drag the formula to the remaining cells

OR

  1. Select the cell with the formula and press Ctrl+C or choose Copy from the context menu (right-click)
  2. Select the rest of the cells in the column and press Ctrl+V or choose Paste from the context menu (right-click)

Step 4 – Identify the incorrect values

  • ‘TRUE’ is displayed for the values that match and ‘FALSE’ for those that do not