Have you ever wondered how to highlight cells in one sheet based on the values in another sheet without mirroring the data? In this Google Sheets tutorial, we will show you how to accomplish this using conditional formatting.
Non-Blanks Cells Highlighting
In a Google Sheets workbook, you may have two sheets, “Sheet 1” and “Sheet 2”. Let’s say you have entered values in different columns in “Sheet 1”, and you want to highlight the non-blank cells in “Sheet 2” based on the values in “Sheet 1”.
To achieve this, we do not need to mirror the values because we want to keep the second sheet entirely different. Here’s how you can do it:
Step 1: Sample Data
Consider the values in the range B2:G10 in “Sheet 1” for highlighting in the same range in “Sheet 2”.
Step 2: Desired Result
Cells in the range B2:G10 in “Sheet 2” will be highlighted based on the values in the same range in “Sheet 1”.
Mirroring sheets means sharing the same values between sheets. However, in this case, we want to highlight cells in “Sheet 2” without mirroring the data. Here’s how you can accomplish this:
How To Highlight Cells in One Sheet if the Same Cells in Another Sheet Have Values
Now, let’s get to the formula that will allow you to color cells in “Sheet 2” based on the values in “Sheet 1”.
=NOT(ISBLANK(INDIRECT("Sheet 1!"&Address(Row(),Column(),))))
This formula will check if the corresponding cell in “Sheet 1” is not blank and will apply conditional formatting to color the cell in “Sheet 2” accordingly.
Entering a Custom Formula in Conditional Formatting – Steps
To apply this custom formula rule in Google Sheets conditional formatting, follow these steps:
- Go to “Sheet 2” since we want to color cells in this sheet.
- Click on the “Format” menu and choose “Conditional formatting…”.
- Enter the range B2:G10 in the “Apply to range” field. Please note that the same range in “Sheet 1” will be considered for highlighting in “Sheet 2”. You can control the range to highlight here. If you want to highlight an entire sheet, simply enter the range A1:1000 (1000 rows) in the field.
- Use the formula provided above in the “Custom formula is” field.
By following these steps, you will be able to highlight cells in “Sheet 2” if the same cells in “Sheet 1” have values.
More Conditional Formatting Tips
Conditional formatting is a powerful feature in Google Sheets. Here are some additional tips to help you make the most out of it:
- How to Conditional Format a Chessboard in Google Sheets.
- Find All the Cells Having Conditional Formatting in Google Sheets.
- Compare Two Google Sheets Cell by Cell and Highlight.
- Highlight Matches or Differences in Two Lists in Google Sheets.
- How to Conditional Format Duplicates Across Sheet Tabs in Google Sheets.
We hope this tutorial has provided you with valuable insights on highlighting cells in one sheet based on the values in another sheet without mirroring the data. If you have any questions or need further assistance, feel free to visit our website Crawlan.com.
Happy highlighting, folks!
