When you want to find the later changes in two copies of the same sheet, you can use conditional formatting. Here you can learn how to compare two Google Sheets cell by cell and highlight the differences or matches.

Just want to compare two spreadsheets cell by cell and no highlighting? Then I have a different approach here – How to Compare Two Sheets in Google Sheets for Mismatch.

When I take you back to the topic, I think I should clear one thing immediately about this Google Docs Spreadsheet tutorial. What’s that?

When I say compare two Google Sheets cell by cell, I mean to say two things. The ‘two Google Sheets’ can be two Sheet tabs in one file or in two separate files. Didn’t get?

In concise;

1. Compare and highlight two entire Sheet tabs that in one Google Sheets file.

2. Compare/highlight two entire Sheet tabs in two different Google Sheets. That means one sheet tab in file # 1 and the second sheet tab in file #2.

My formula would work in both the above scenarios.

Compare Two Google Sheets Cell by Cell and Highlight The Differences/Matches

First, let us start with the comparison of two sheets in the same file and highlight the differences.

Two Tabs in Same Google Sheets File

Compare Two Google Sheets Cell by Cell and Highlight The Differences

I have two tabs in my Google Sheets file. Tab 1 is named as “Sheet1” and Tab 2 is “Sheet2”

In both the Sheets, I am just limiting the number of rows to 10 and columns to 5. I have deleted all the other columns and rows to speed up the highlighting.

You can select the entire sheet but recommended only the ranges with data. It can improve the performance of your Sheet.

Again I am reminding you this is a cell by cell comparison. That means cell A1 in Sheet1 will be compared to cell A1 in Sheet2. Similarly, cell D6 in Sheet1 will be compared to cell D6 in Sheet2.

If the values are not matching, the custom formula that I am going to provide you will highlight the corresponding cell in Sheet1. If you wish, you can highlight Sheet2 also. I’ll touch that part later.

Here are the contents in my two Sheet Tabs. In the first Sheet tab (Sheet1) I have highlighted the differences.

Table 1 in Sheet1:

Table 1 in Sheet2:

Here is the formula that highlights the differences in two entire Sheets in Google Doc Spreadsheets:

=A1<>(Indirect("Sheet2!"&Address(Row(),Column(),)))

How to apply this conditional formatting formula that compares and highlights differences in two sheet tabs?

Must Check: Google Sheets Functions Guide by Info Inspired.

My entire sheet range is A1: G10. So here I am applying this formula in this range in Sheet1. This is a custom rule formula.

Steps:

Click the menu “Format”. Under the drop-down choose “Conditional format”. Apply the above formula in the custom formula field as below and voila!

Compare Two Google Sheets Cell by Cell and Highlight The Matches

Of course, I can tweak the above formula to highlight the matches in Google Sheets. But I prefer a different approach which is tricky. What’s that?

Just use the same above custom formula rule. No changes in the formula! Then how?

In the above example, I have used the color Red to highlight the matches. Here instead of the color Red, apply White. Yes! you heard me right. That’s your conditional formatting rule #1.

Then add a new rule as below and choose the color Red or any other color of your choice. But don’t use the color White this time. Here I am choosing the light Green color.

This time, in the second conditional formatting rule,  instead of custom formula, select “Cell is not empty” under the “Format cells if…”. That’s all.

Additional Tips: If you want the highlighting to appear in both the tabs, you can do as follows.

You have already highlighted Sheet1. In Sheet2 you can use the below formula.

=A1<>(Indirect("Sheet1!"&Address(Row(),Column(),)))

Yes! Just change the “Sheet2” to “Sheet1” in the formula as above. That’s the one and only changes that you may want to make in Sheet2.

That’s all about the cell by cell comparison of two tabs in same Google Sheets file for differences or matches.

One Tab Each in Two Different Google Sheets Files

Now let’s see how to compare two different Google Spreadsheet files cell by cell. Before starting this section I request you to properly test the above formulas and learn it.

Make sure that you could properly understand how to compare two Google Sheets cell by cell and highlight the differences or matches (two Sheet tabs in the same file). That’s a must to understand this section.

Hope you have already done that part. Now let me begin this new section.

File Names Involved:

The name of my first Google Sheets file is “attendance1” and sheet name in that file is “A1”.

My second file name is “attendance2” and the sheet name in that file is “B2”.

I am going to highlight the differences in the file “attendace1”, sheet “A1”.

To apply conditional formatting between two Google Spreadsheets first you should link the files. How?

Insert a new tab in the file “Attendace1” and name it as “A2”.

Cell A1 in that new sheet tab use the below IMPORTRANGE formula.

=IMPORTRANGE("URL","B2!A1:G10")

Replace URL in this formula with the URL of the file “Attendance2” that you can copy from the address bar of your browser.

If the formula returns #REF! error type, do click on it and allow access.

=A1<>(Indirect("A2!"&Address(Row(),Column(),)))

Go to the Sheet “A1” and apply the above conditional formatting formula. Select the color that you want to highlight the differences.

To compare cell by cell and highlight the matches, you can follow my example under the title above “Compare Two Google Sheets Cell by Cell and Highlight The Matches”.

Cell by Cell Comparison [Spreadsheet Copy]

More Resources:

1. How to Conditional Format Duplicates Across Sheet Tabs in Google Sheets.

2. Role of Indirect Function in Conditional Formatting in Google Sheets.