Compare Two Google Sheets Cell by Cell and Highlight

Published on

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:

sheet1: highlight differences after comparing cell by cell

Table 1 in Sheet2:

sheet2: highlight differences after comparing cell by cell

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!

formula rule to highlight cells after comparing two sheets

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.

cell by cell comparison and conditional format matches

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.

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

1 COMMENT

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.