Download Article
An easy-to-follow guide on comparing data in two different Google Sheets
Download Article
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
Download Article
-
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.
-
Create a third sheet/tab for your comparison. Click the "+" sign at the lower left corner to add this sheet.Advertisement -
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
- 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
-
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 . -
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.
Advertisement
Expert Q&A
Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Submit
Advertisement
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
- 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
Submit a Tip
All tip submissions are carefully reviewed before being published
Name
Please provide your name and last initial
Thanks for submitting a tip for review!
Advertisement
References
About This Article
Thanks to all authors for creating a page that has been read 93,743 times.
Advertisement
