Ever tried comparing the contents of two different Google Sheets to find discrepancies? If they’re small worksheets with a few rows and columns of data, it might not be that tough. With larger amounts of data, however, it’s a whole other story!
Fortunately, data processing software like Google Sheets are better programmed than the human eye, in picking up small differences and similarities between sheets.
Although Google Sheets does not really provide any direct tools to compare two sheets, it is possible to do so using some of the tools that it does provide, along with a little bit of creativity.
In this tutorial we will show you how to use Google Sheets to compare two sheets.
To demonstrate these topics, we will be using the following data samples, consisting of 5 records (rows) each:
We will assume that Sample1 is in a sheet named ‘Sheet1’ and Sample2 is in a sheet named ‘Sheet2’.
How to Access Cells from a Different Sheet in Google Sheets
If you want to access cells from another sheet, you can use the cell references of the cells, prefixed by the name of the sheet and an exclamation mark (‘!’).
So if you want to access cell A2 of Sheet1, then you can use the cell reference “Sheet1!A2”.
Similarly, for an absolute cell reference, you can use the reference “Sheet1!$A$2”.
How to Compare Two Sheets in Google Sheets to Find Mismatches in Cells
If you have two sheets of data and you want to find out if the data or contents in both are exactly the same or not, then you will need to use just a single formula and paste it throughout a third sheet.
The formula that you can use is as follows:
=IF(Sheet1!A1<>Sheet2!A1,Sheet1!A1&" | "&Sheet2!A1,"")
The IF function requires three parameters:
- a condition
- a text or formula that should be returned if the condition is TRUE
- a text or formula that should be returned if the condition is FALSE
Here, we specified the condition as: Sheet1!A1<>Sheet2!A1, which means ‘cell A1 from Sheet1 not equal to cell A1 from Sheet2’.
If the condition is TRUE, we specified that the function should return the value in Sheet1, cell A1, followed by a pipe symbol (‘|’), followed by the value in Sheet2, cell A1, or “Sheet1!A1&” | “&Sheet2!A1”.
If the condition is FALSE, the function should simply display a blank cell (“”).
When you paste the above formula in all the cells of the third sheet (let’s call it ‘Sheet3’) it will display exactly which cells are different (or mismatched) as well as how they are different, as shown below:
Let’s take a look at the steps you need to follow if you want to apply this to our sample data:
- Create a third Sheet (by clicking on the ‘+’ icon at the bottom of the window. You should see a new tab with the name ‘Sheet3’.
- In the first cell of this sheet (cell A1), type the formula: =IF(Sheet1!A1<>Sheet2!A1,Sheet1!A1&” | “&Sheet2!A1,””)
- Copy the formula by pressing CTRL+C on the keyboard.
- Select all the cells of Sheet3 by pressing CTRL+A on the keyboard or clicking on the ‘Select All’ button at the top left corner of your sheet.
- Press CTRL+V on the keyboard to paste the formula to all the selected cells.
You should now find all cells that are the same in Sheet1 and Sheet2 to be blank. The cells where the contents are different, however, should display the values of that cell from both sheets, separated by a ‘|’ character.
How to Compare Two Sheets for Exact Row Matches
A very common application of Google Sheets is in comparing sheets by row to find which rows (or records) match and which ones don’t.
In our example, let’s try to compare Sheet1and Sheet2 row-by-row. We will display the results in a third sheet, named ‘Sheet3’. If a row matches in both sheets, we should see the word ‘Matching’ displayed in the corresponding row of Sheet3. If not, then we should see the word ‘Not Matching’ in the corresponding row.
For this, we will need to use a formula that includes the IF function with a nested AND. The AND function takes two or more conditions as parameters. It returns a TRUE value if all conditions are TRUE and FALSE otherwise.
If both your Google Sheets have 2 columns each to compare, then here are the steps you need to follow:
- Create a third Sheet (by clicking on the ‘+’ icon at the bottom of the window.
- In cell A2 of this sheet type the formula: =IF(AND(Sheet1!A2=Sheet2!A2,Sheet1!B2=Sheet2!B2),”Matching”,”Not Matching”)
- Copy the formula down to the rest of the column by dragging the cell’s fill handle.
You should now see the word ‘Matching’ wherever the corresponding rows of the two sheets match, and the word ‘Not Matching’ otherwise.
Notice that even though the names in the last row of our samples match, since the corresponding items in the same row don’t match, the function returned the result “Not Matching”.
For this method to work, both your sheets need to have the same number of columns. If your sheets have more than two columns, you can expand on the formula by adding more parameters comparing each cell of a single row. So if you have 3 columns, your formula would be:
=IF(AND(Sheet1!A2=Sheet2!A2,Sheet1!B2=Sheet2!B2, Sheet1!C2=Sheet2!C2),"Matching","Not Matching")
And so on.
How to Compare Two Sheets and Highlight Matching Rows
If you prefer highlighting the matching rows within one of the two sheets instead of displaying the results in a separate sheet, then you can use the Conditional Formatting feature of Google Sheets.
Conditional formatting is a technique that lets you format cells based on a condition. We could use the same formula as the one discussed in the previous section.
However, conditional formatting in Google Sheets does not allow references to cells from another sheet.
One way around this is to use the INDIRECT function to access the other sheet indirectly. This will get clearer as we demonstrate the method in steps.
Here’s how you can use Conditional formatting to compare two sheets and highlight the matching rows in one of them:
- Click the Format menu from the menu bar.
- Select ‘Conditional Formatting’.
- This will open the ‘Conditional format rules’ sidebar on the right of the window.
- In the input box under “Apply to range”, type in the range of cells you want to apply the formatting to. In our example, if you want to apply the formatting to Sheet2, then type Sheet2!A2:A10
- In the Format rules section, under “Format cells if”, click on the dropdown arrow.
- Select “Custom formula is” from the dropdown menu.
- You will see an input box below the dropdown list. Type your custom formula there: “=AND(A2=INDIRECT(“Sheet1!A2:A”),B2=INDIRECT(“Sheet1!B2:B”))”.
- Under “Formatting style”, click on the Fill Color button.
- Select the color you want to use, for highlighting the matching rows. We selected “light yellow 2”.
- Click the Done button, to let Conditional formatting do its work.
You should now see the matching rows highlighted in your selected color.
The formula we used here compares two columns of each row in Sheet1 and Sheet2. It uses the INDIRECT function to extract a reference to the cells in columns A and B of Sheet1. The formula then checks if cells corresponding to both columns in each row match. If they do, then it highlights the row.
Note: If your sheets have more than two columns, you can expand on the formula by adding more parameters comparing each column. So if you have 3 columns, your formula would be:
And so on.
Note: To highlight only rows that don’t match, you can replace the ‘=’ symbols in the formula with ‘<>’.
How to Compare Two Sheets to Find Duplicate Data
The first three methods mainly found rows in both sheets that matched exactly and in the same order. However, in practical situations, the rows are not always ordered in the same way in both sheets. So let us take a look at a situation where we want to compare two sheets and find out which rows of Sheet1 are repeating in Sheet2, irrespective of order.
In other words, let us use Conditional formatting to find and highlight duplicate data in Sheet2. Here are the steps:
- Select a blank column right after the rightmost column of Sheet2. Let’s say we used column C in our example.
- In the second row of this column, insert the formula: =IFERROR(CELL(“address”,INDEX(Sheet1!$A$1:$A$9,MATCH(A2,Sheet1!$A$1:$A$9,0),1)),FALSE). This formula will return the address of a cell in Sheet1, column A, that matches the contents of cell A2 in the current sheet. If a matching cell does not exist in Sheet1, then the formula will return FALSE.
- Copy this formula to the rest of the rows in the column by dragging the fill handle. Here’s what you should see:
- Now we can start applying conditional formatting. Repeat the steps for conditional formatting discussed in the previous section (Steps 1 to 10), but replace the formula in Step 7 with: “=IF(NOT(C2=FALSE),IF(OFFSET(INDIRECT(C2),0,1)=B2,TRUE,FALSE),FALSE)”
You should now see all the duplicate rows of data in Sheet2 highlighted in your selected color.
Note: If you want to hide the column containing the matching addresses, you can simply right-click on the column and select ‘Hide Columns’ from the context menu that appears.
Explanation of the Formula
The formula in Step 4 looks a little complex, but you might be able to understand if we break it down for you. Let’s analyze the formula layer by layer, starting from the inner layer, and moving outwards:
This function returns the value in Column B of Sheet1, corresponding to the address we obtain in cell C2. Remember, C2 contains the cell address of the name in Sheet1 that matches the name in cell A2. So this formula will simply return the Item value corresponding to the matching name.
The IF function now checks if this value of Sheet1, Column B matches the corresponding value in Sheet2, Column B. If it does, then the formula returns a TRUE, otherwise it returns a FALSE.
This formula now checks if C2 contains a cell address or a FALSE value. If it contains a cell address, it means that a matching name has been found in Sheet1. If that happens, the formula returns a TRUE or FALSE value depending upon the result of the internally nested IF function. If C2 contains a FALSE value, then the formula also returns a FALSE.
In other words, the entire formula first checks if there’s a duplicate Name value in Sheet1. If not, it returns FALSE. If a duplicate Name does exist, it checks if the corresponding Item value also matches. If it does, it returns TRUE, otherwise, it returns FALSE.
Note:If your sheets have more than two columns, you can expand on the formula by incorporating an AND function into your nested IF function. For example, if you have 3 columns in each Sheet, you can change your formula to:
Here, we are assuming that your matching cell addresses are in column D of Sheet2 instead of column C.
How to Compare Two Sheets to Find Missing Data
Finally, what if you want to highlight the missing rows, instead of the duplicate ones?
In that case, you basically need just the opposite rows highlighted. So you will simply need to add a NOT function outside the conditional formatting formula discussed in the previous section.
In other words, your formula should now be:
Here’s the result you would get:
The Bottom Line
In this tutorial we discussed different ways in which you can compare two Sheets for differences. The formulas we used might seem a little complex, but we encourage you to try it out yourself to get a better understanding of how they work.