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
Layout
The layout and structure of the sheets you will compare should be identical. This includes grouping, filtering, and sorting. Since the add-on considers the row & column order for comparison, sheets with different sorting order will most likely be perceived as completely different.
Number of columns
The number of columns also affects the results. For instance, there are 10 columns in Sheet1 and 11 columns in Sheet2. That extra column is somewhere inside the 2nd table. This will affect the comparison order, making your sheets most likely to be perceived as different.
Hiding that extra column won't help (see the next paragraph). So to minimize its impact on comparison, either move it to the end of your table and limit the range in the add-on up to that column, or remove that column from your table completely.
Filtered & hidden data
The add-on processes hidden & filtered data as well. Make sure that any hidden or filtered rows or columns are intended to be included in your comparison to avoid unexpected results.
Merged cells
Merged cells also impact the comparison result. When it's merged cells in one table and multiple unmerged cells in another on corresponding rows, they will all be treated as different cells.
For accurate comparisons, consider unmerging cells or make cell merging consistent across all sheets being compared.
The same values = complete matches
Only complete matches are treated as the same values. Partial matches, e.g. "view" vs. "view access", will be considered as different cells.
And keep an eye out for excess spaces: they can interfere with results as well. Our Remove tool will help you get rid of them.
Text case
The add-on is case-sensitive. Such values as "view" and "View" will be treated as different cells. Our Change Case tool will help you standardize the text case for consistent comparison results.
Google Sheets cell limit
Please bear in mind that Google Sheets limits the total number of possible cells in your file up to 10 million cells.
Explore other deduplication tools
- Compare Sheets for Duplicates — compare two or more columns, tables or sheets for duplicates or uniques.
- Compare Two Columns — scans two columns within one sheet for duplicate or unique values.
- Remove Duplicate Rows — find identical or unique rows within a table.
- Quick Dedupe — 1-step version of Remove Duplicate Rows.
- Remove Duplicate Cells — will look for dupes or uniques in all single cells cells within one sheet.
- Combine Duplicate Rows — merges or consolidates unique records that refer to multiple duplicate values on different rows.
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:
- Tick off the Sheets checkbox whenever you decide to compare all sheets from the tree view.
- 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. - If there's a spreadsheet you don't want to use for comparison, select it and hit Exclude.
- 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.
Tip. All sheets are grouped by the spreadsheets they come from. This is especially useful when you have several sheets with the same name.
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.
Note. The more sheets you compare and the more differences there are between them, the more time the add-on requires to create the report.
Video: How to work with the comparison report
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:
- Use these buttons to expand/collapse groups and see the rows that were compared.
- 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.
- 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.
- 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:
- The row is present across all compared sheets.
- 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.
Post a comment
Seen by everyone, do not publish license keys and sensitive personal info!
If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.
