How to Compare Two Sheets in Google Sheets

PDF download Download Article
An easy-to-follow guide on comparing data in two different Google Sheets
PDF download 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

PDF download Download Article
  1. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/f\/f0\/Compare-Two-Sheets-in-Google-Sheets-Step-1.jpg\/v4-460px-Compare-Two-Sheets-in-Google-Sheets-Step-1.jpg","bigUrl":"\/images\/thumb\/f\/f0\/Compare-Two-Sheets-in-Google-Sheets-Step-1.jpg\/aid10838818-v4-728px-Compare-Two-Sheets-in-Google-Sheets-Step-1.jpg","smallWidth":460,"smallHeight":346,"bigWidth":728,"bigHeight":547,"licensing":"<div class=\"mw-parser-output\"><p>\u00a9 2024 wikiHow, Inc. All rights reserved. wikiHow, Inc. is the copyright holder of this image under U.S. and international copyright laws. This image is <b>not<\/b> licensed under the Creative Commons license applied to text content and some other images posted to the wikiHow website. This image may not be used by other entities without the express written consent of wikiHow, Inc.<br>\n<\/p><p><br \/>\n<\/p><\/div>"}
    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. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/d\/dc\/Compare-Two-Sheets-in-Google-Sheets-Step-2.jpg\/v4-460px-Compare-Two-Sheets-in-Google-Sheets-Step-2.jpg","bigUrl":"\/images\/thumb\/d\/dc\/Compare-Two-Sheets-in-Google-Sheets-Step-2.jpg\/aid10838818-v4-728px-Compare-Two-Sheets-in-Google-Sheets-Step-2.jpg","smallWidth":460,"smallHeight":346,"bigWidth":728,"bigHeight":547,"licensing":"<div class=\"mw-parser-output\"><p>\u00a9 2024 wikiHow, Inc. All rights reserved. wikiHow, Inc. is the copyright holder of this image under U.S. and international copyright laws. This image is <b>not<\/b> licensed under the Creative Commons license applied to text content and some other images posted to the wikiHow website. This image may not be used by other entities without the express written consent of wikiHow, Inc.<br>\n<\/p><p><br \/>\n<\/p><\/div>"}
    Click the "+" sign at the lower left corner to add this sheet.
    Advertisement
  3. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/1\/1a\/Compare-Two-Sheets-in-Google-Sheets-Step-3.jpg\/v4-460px-Compare-Two-Sheets-in-Google-Sheets-Step-3.jpg","bigUrl":"\/images\/thumb\/1\/1a\/Compare-Two-Sheets-in-Google-Sheets-Step-3.jpg\/aid10838818-v4-728px-Compare-Two-Sheets-in-Google-Sheets-Step-3.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>\u00a9 2024 wikiHow, Inc. All rights reserved. wikiHow, Inc. is the copyright holder of this image under U.S. and international copyright laws. This image is <b>not<\/b> licensed under the Creative Commons license applied to text content and some other images posted to the wikiHow website. This image may not be used by other entities without the express written consent of wikiHow, Inc.<br>\n<\/p><p><br \/>\n<\/p><\/div>"}
    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]
  4. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/a\/ac\/Compare-Two-Sheets-in-Google-Sheets-Step-4.jpg\/v4-460px-Compare-Two-Sheets-in-Google-Sheets-Step-4.jpg","bigUrl":"\/images\/thumb\/a\/ac\/Compare-Two-Sheets-in-Google-Sheets-Step-4.jpg\/aid10838818-v4-728px-Compare-Two-Sheets-in-Google-Sheets-Step-4.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>\u00a9 2024 wikiHow, Inc. All rights reserved. wikiHow, Inc. is the copyright holder of this image under U.S. and international copyright laws. This image is <b>not<\/b> licensed under the Creative Commons license applied to text content and some other images posted to the wikiHow website. This image may not be used by other entities without the express written consent of wikiHow, Inc.<br>\n<\/p><p><br \/>\n<\/p><\/div>"}
    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. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/1\/17\/Compare-Two-Sheets-in-Google-Sheets-Step-5-Version-2.jpg\/v4-460px-Compare-Two-Sheets-in-Google-Sheets-Step-5-Version-2.jpg","bigUrl":"\/images\/thumb\/1\/17\/Compare-Two-Sheets-in-Google-Sheets-Step-5-Version-2.jpg\/aid10838818-v4-728px-Compare-Two-Sheets-in-Google-Sheets-Step-5-Version-2.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>\u00a9 2024 wikiHow, Inc. All rights reserved. wikiHow, Inc. is the copyright holder of this image under U.S. and international copyright laws. This image is <b>not<\/b> licensed under the Creative Commons license applied to text content and some other images posted to the wikiHow website. This image may not be used by other entities without the express written consent of wikiHow, Inc.<br>\n<\/p><p><br \/>\n<\/p><\/div>"}
    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.
    6. 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
      Submit
      Thanks for submitting a tip for review!
      Advertisement

      You Might Also Like

      How to
      Sign Out of Your Google Account on All Devices at Once
      How to Delete Google Photos without Deleting Them on Your Phone
      How to Disable Google Safe Search
      How to Sign Up for a New Google Voice Phone Number
      2 Simple Ways to Unsync Google Photos
      How to Restore Google Authenticator if You Lost or Reset Your Phone
      2 Easy Ways to Permanently Delete (or, Hide) Your YouTube Channel
      How to Use Google Translate: Complete Beginner's Guide
      How to
      Fix the "Google Play Store Has Stopped" Error
      How to Use Dark Mode in Google Docs: Easy Steps for Desktop & Mobile
      Reverse Image Search in Google Lens: Android, iPhone, & Desktop
      How to Step Seeing Google Ads on PC or Mobile Devices
      Easy Ways to Find Backed Up Photos on Google: 3 Methods
      How to Make a Free Website with Google Sites: Step-by-Step Guide
      Advertisement

      References

      1. ↑ https://support.google.com/docs/answer/75943?co=GENIE.Platform%3DDesktop&hl=en
      2. https://infoinspired.com/google-docs/spreadsheet/how-to-compare-two-sheets-in-google-sheets-for-mismatch/

      About This Article

      Thanks to all authors for creating a page that has been read 88,806 times.

      Is this article up to date?

      Advertisement