How to Compare Two Sheets in Google Sheets

An easy-to-follow guide on comparing data in two different Google Sheets

Last Updated: August 12, 2022

If you have two entire sheets (or tabs) in a Google Sheets document that you want to compare, there's a fairly simple formula you can use. This wikiHow will tell you how to find differences between two sheets in Google Sheet.

Steps

1
Set up your two tabs in Google Sheets first. This method will help you compare two tabs within the same Google Sheets file, so you'll need both tabs set up and ready.
- In other words, if you have two theoretically identical spreadsheets you want to compare to find any differences, put one in each tab. By default, these tabs will be named "Sheet1" and "Sheet2," but you can customize the names if you'd like.
2
Create a third sheet/tab for your comparison. Click the "+" sign at the lower left corner to add this sheet.

Advertisement
3
Find cell A1 on the new comparison tab. Enter the formula =IF(Sheet1!A1<> Sheet2!A1, Sheet1!A1&" | "&Sheet2!A1, "")
- If you've changed your sheets so they're not called Sheet1 and Sheet2, adjust the names as needed. For example, if your first sheet is called "Original Data" and the second is called "New Data," your formula would be =IF('Original Data'!A1<> 'New Data'!A1, 'Original Data'!A1&" | "&'New Data'!A1, "")^{[1]
  X
  Research source}
4
Paste this formula to every relevant cell of the comparison sheet. Use Ctrl+C to copy the formula, highlight all of the cells of the comparison sheet (to match the number of rows and columns used by your original two sheets), and paste using Ctrl+V.
5
Review the results. The comparison spreadsheet will show you where any mismatches exist between the two sheets. Where the values are different, the comparison sheet will show both versions, separated by the pipe symbol ("|").
- If you don't see any text in the comparison sheet at all, it means the two sheets are identical. Only differences will show up.