How to Compare Two Sheets in Google Sheets

Show data side-by-side in Google Sheets

Author Info

Last Updated: February 7, 2025

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.

Comparing Data in Google Sheets

To compare two sheets in Google Sheets, open the two sheets in the same file. Create a third sheet to use as a comparison tab. Use the formula for every relevant cell: =IF(Sheet1!A1<> Sheet2!A1, Sheet1!A1&" | "&Sheet2!A1, "").

Steps

Download Article

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.
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}
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.

Expert Q&A

Search

Add New Question

Ask a Question

200 characters left

Include your email address to get a message when this question is answered.

Submit

Tips

You might see some differences highlighted if the whitespace doesn’t match in any of the fields of the original spreadsheets. To eliminate this false positive, make sure you don't have any extra spaces before or after your data in either sheet you're comparing.

Thanks
Helpful 0 Not Helpful 0
You might see some differences highlighted if the text formatting differs between the sheets (for example, if one sheet has the data designated as "Plain Text" and the other as "Number"). Set all fields to the same format type to ensure an accurate comparison.

Thanks
Helpful 0 Not Helpful 0