Highlight Cells if Same Cells in Another Sheet Have Values – Docs Sheets

Without mirroring values you can highlight cells in one Sheet if the same cells in another Sheet have values. This is a Google Sheets tutorial and let’s see how to do this type of cell coloring using conditional formatting in it.

In a Google Sheets file (workbook) I have two sheets named “Sheet 1” and “Sheet 2”. In “Sheet 1” I have entered values in different columns.

I want to highlight these non-blank cells (cells with value), not in the same Sheet, but in another Sheet, i.e. in “Sheet 2”.

For this, I don’t want to mirror values because I want to keep the second Sheet entirely different. I may or may not add values in that.

Sample Data in “Sheet 1”: Consider the Values in B2:G10 for highlighting in “Sheet 2” in the same range.

Sample Job Sch to Test Cell Coloring Without Mirroring Data

Result in “Sheet 2”: Cells in the Range B2:G10 Highlighted Based on the Values in the Same Range in “Sheet 1”.

Example to Highlight Cells if the Same Cells in Another Sheet Have Values

Mirroring Sheets means to share the same values between Sheets. For example, if cell A1 in “Sheet 1” has the value 100, you can mirror that value in another Sheet using the formula = 'Sheet 1'!A1.

Let’s see how to highlight cells in “Sheet 2” based on the values (non-blank cells) in another sheet (“Sheet 1” here) in Google Sheets.

How To Highlight Cells in One Sheet if the Same Cells in Another Sheet Have Values

I have already shown you the sample data and my expected output. Now what you want is the formula to color cells based on values in another Sheet.

In the above example, I have considered the values in a particular range. But if you wish, you can include the entire Sheet.

For this, you do not need to make any changes in my formula. I’ll explain that below.

Here is the formula to highlight cells in “Sheet 2” if the same cells in “Sheet 1” have values.

=NOT(ISBLANK(INDIRECT("Sheet 1!"&Address(Row(),Column(),))))

Entering a Custom Formula in Conditional Formatting – Steps

As a side note, the above is one custom rule formula for conditional formatting. Similarly, you can use custom formula rules in Data Validation and Filter. Want to see examples to custom formula rule in the Filter Menu command? Here you go!

  1. How to Filter by Month Using the Filter Menu in Google Sheets.
  2. Filter Unique Values Using the Filter Menu in Google Sheets.

To apply custom formula rule in Google Sheets conditional formatting, follow the below few steps.

  1. Go to “Sheet 2” since we want to color cells in this Sheet.
  2. Click the menu “Format” and choose Conditional formatting...
  3. Enter B2:G10 in the field under Apply to range. Please note that the same range in “Sheet 1” will only be considered in “Sheet 2” for highlighting. That means here you can control the range to highlight. If you want to highlight an entire Sheet, just enter the range A1:1000 (1000 rows) in the said field.
  4. Use the above-provided formula below the Custom formula is field.

If the above settings confuse you, see the below screenshot.

Conditional format rule to color a different Sheet

Follow the above tips to highlight cells if the same cells have values in another Sheet in Google Sheets.

More Conditional Formatting Tips:

  1. How to Conditional Format a Chessboard in Google Sheets.
  2. Find All the Cells Having Conditional Formatting in Google Sheets.
  3. Compare Two Google Sheets Cell by Cell and Highlight.
  4. Highlight Matches or Differences in Two Lists in Google Sheets.
  5. How to Conditional Format Duplicates Across Sheet Tabs 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.

Lookup Previous Values Dynamically in Excel and Google Sheets

Sometimes, you may want to look up the previous values relative to the current...

Sort Data but Keep Blank Rows in Excel and Google Sheets

Using a dynamic array formula or the FILTER command, we can sort data and...

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

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...

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.