Compare Google sheets for differences cell by cell

Compare Sheets Cell by Cell compares cells in each row between multiple Google sheets for differences. The add-on highlights all single cells with non-identical contents using a color of your choice, and entire unique rows (where not even a single cell matches other sheets) with another hue. As an extra, you will opt for a status column with an additional description for each difference.

• Video: How to compare multiple Google Sheets for differences
• Before you start
• How to use Compare Sheets Cell by Cell
  • Start the tool
  • Step 1: Select sheets to compare
  • Step 2: Select your main sheet
  • Step 3: Choose what to do with found differences
    • Show differences on the sheets
    • Create a comparison report
  • See the result
• How to work with the comparison report
  • Video: How to work with the comparison report
  • Why comparison report?
  • Use groups
  • Use filters
  • Of equal rows

Before you start

Please bear in mind that Google Sheets limits the total number of possible cells in your file up to 10 million cells.

How to use Compare Sheets Cell by Cell

Start the tool

Go to the Extensions menu, find Compare Sheets and pick Compare sheets cell by cell.

Step 1: Select sheets to compare

Start by picking the sheets that you want to compare:

1. Tick off the Sheets checkbox whenever you decide to compare all sheets from the tree view.
2. Hit Add files to Drive to select more spreadsheets for comparison from your Drive.
   Tip. Use the Search field at the top of the Import files from Drive window to quickly find the required spreadsheet.

   Just type in a word your spreadsheet name may contain and press Find. The add-on will look for exact & partial matches all over your Drive, shared drives (if any), and the files shared with you. All spreadsheets with matching names will appear in the Compare Sheets Cell by Cell tree view.

   To get a hint on where a particular spreadsheet resides, just hover your mouse over it:
   

   Tip. You can add several spreadsheets to compare at once. Click on them while holding the Shift (for adjacent) or Ctrl (for non-adjacent) keys on your keyboard. The files will be highlighted in light blue:
   
   Press Add to include them in the comparison tree-view.
3. If there's a spreadsheet you don't want to use for comparison, select it and hit Exclude.
4. Here you can limit the range for comparison if necessary. Click All data next to the required sheet, and do one of the following:
   • Enter the range from your keyboard.
   • Click the Select range icon and highlight the range with your data directly on the sheet.
   • Click the Select range icon, select any cell within your table, and press Auto select. This will automatically pick the used range — till the first empty column & row:
     

Once you define sheets and ranges, hit Next.

Step 2: Select your main sheet

Pick one of the selected sheets in the drop-down to make it your main one:

This will be your example sheet that will be compared with all the other sheets. As a result, all differences will feature the relation between the main sheet and every other sheet.

Step 3: Choose what to do with found differences

Decide how you want to review the differences: show differences on each sheet respectively or create one comparison report with all differences in one place.

Show differences on the sheets

Show differences on the sheets will mark all distinct records with color or/and in a status column when you tick off the corresponding checkbox(es):

• If you choose to color everything, all Different cells (those that vary in contents between all sheets) will be highlighted in yellow by default, while all Unique rows (those rows that appear only in the sheet you'll look at) will become blue:
  
  Note. Different cells will be marked on each compared sheet but the main one.
  Tip. Of course, you can pick any other hue from the built-in color palette:
  
  Note. If your tables have their own colors, the add-on will overwrite them with the selected colors to show differences. These changes cannot be reversed with the Undo option (or Ctrl+Z shortcut).

  But since Google Sheets keeps all versions of each sheet, you can always restore the original via File > Version history > See version history (or Ctrl+Shift+Alt+H shortcut):
  

  Tip. Learn more about Google Sheets version history in our tutorial.

  Or just duplicate the sheets/spreadsheet before running the add-on.

• If you opt for a 2nd checkbox, two extra columns will be added next to your tables:
  • The Status will tell which row is unique or contains different cells.
  • The Description will list the names of other sheets that have different contents in these cells or don't have these rows.

Click Compare for the add-on to start looking for differences cell by cell. 

Create a comparison report

Create a comparison report will collect all data in one report on a new sheet. The rows from all sheets will be grouped by their serial numbers. All different cells and unique/missing rows will be highlighted accordingly with an additional status in a corresponding column.

Here you can pick your own color to highlight different cells, unique rows & missing rows in the final report.

A couple of extra options will help you get the complete picture:

• To see even those rows that match entirely across all compared sheets, tick off the checkbox Include equal rows to the report.
  Tip. If the row is missing or differs from at least one sheet, it's not considered equal anymore. It will appear in your report only for the sheets with differences: as a unique, missing or different row correspondingly.
• If the formatting of your source tables matters (font settings and colors, fill colors, etc.), select Preserve formatting as well.

Hit Compare and the add-on will start creating a report.

See the result

Once the add-on finishes comparing your sheets, you will see a resulting message. It will contain different info depending on the way you chose to handle differences.

• For differences on the sheets, the summary will say how many sheets have been compared and how many unique rows and different cells have been found:
  
• For the report, it will give its super shortened version: how many rows have been compared in total and how many different cells and missing/unique rows there are on the main sheet.

  You'll also get a link to open the actual report in another spreadsheet right away:
  

  Tip. The add-on will name this new file Comparison report and create it in your Drive right next to the file with your main sheet.

How to work with the comparison report

A comparison report is one of the ways available to review differences. And it's the best way if you'd like to get the whole picture.

Why comparison report?

This report is a collection of all data from all the sheets you compare:

For ease in your analysis, the report is based on your main sheet (since it's the one that is compared with every other sheet). So the table on the right will first look like your main sheet, only colored one.

At the top, you can show/hide the legend (using the plus sign to the left of the 1st row) that hints at the meaning of the statuses & colors used in the report:

Use groups

Each row from the main sheet is grouped with its counterparts from other sheets. Open the groups to see the relations (a.k.a. differences) between the rows:

1. Use these buttons to expand/collapse groups and see the rows that were compared.
2. This first column contains the names of the sheets — e.g. A (2022) — and the names of the files (in parentheses) so you know where this or that row belongs.
3. The next column tells the relation between the main sheet and every other sheet.

   In the screenshot above, the first group features the row that appears on sheet A but is missing from sheets B and C. In the second group, the row appears on all 3 sheets but with slight differences in a couple of cells.

4. These row numbers tell where each row appears exactly in its original table. This way you can swiftly find it in the source file.

Use filters

As you can see, the Sheet and Status columns also contain filters:

• Filter the 1st column to direct your attention to the required sheets only:
  
  Note. Since the add-on doesn't expand the groups for you, some sheets may remain hidden from the view after filtering. Make sure to expand the groups beforehand so the filtered results display all required rows.
• Filter the 2nd column to see the rows with a particular type of difference (hide irrelevant differences from the view):
  
  Tip. If your filter returns the rows from the main sheet and you're unable to expand their groups, it means no other row from this group fits the filtering conditions and it's simply filtered out from the view.

Of equal rows

When you use the Include equal rows to the report option, you may notice that some matching rows are not there. The thing is, the add-on treats the row as equal if these both are true:

1. The row is present across all compared sheets.
2. The row is completely matching across all compared sheets.

A great example is a header row. It's the same in all our sheets so it is mentioned in the report:

However, if the row is missing or differs from at least one sheet, it's not considered equal anymore. It will appear in your report only for the sheets with differences: as a unique, missing or different row correspondingly.

With that in mind, equal rows will be useful at first so you get used to the report and its parts. Once you do, you can opt out of equal rows. Your report will then be quicker to create and easier to review.