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.
Please bear in mind that Google Sheets limits the total number of possible cells in your file up to 10 million cells.
If you need to compare two sheets or columns for duplicates or uniques, try Compare Columns or Sheets instead.
If you are to find identical or unique rows in a table, take advantage of the Remove Duplicate Rows add-on or its one-step version Quick Dedupe.
To look for dupes or uniques in all single cells within one sheet, Find duplicate or unique cells will do better.
To merge or consolidate unique records that refer to multiple duplicate values on different rows, turn to Combine Duplicate Rows.
Go to the Extensions menu, find Compare Sheets and pick Compare sheets cell by cell.
Start by picking the sheets that you want to compare:
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:
Once you define sheets and ranges, hit Next.
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.
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 will mark all distinct records with color or/and in a status column when you tick off the corresponding checkbox(es):
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):
Or just duplicate the sheets/spreadsheet before running the add-on.
Click Compare for the add-on to start looking for differences cell by cell.
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:
Hit Compare and the add-on will start creating a report.
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.
You'll also get a link to open the actual report in another spreadsheet right away:
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.
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:
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:
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.
As you can see, the Sheet and Status columns also contain filters:
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:
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.