Conditional Formatting based on Another Cell in Google Sheets

conditional formatting based on another cell google sheets 10

In this article we will learn about How to use Conditional Formatting based on Another Cell in Google Sheets.

What is Conditional Formatting based on Another Cell in Google Sheets

Sometimes a user may need to perform certain formatting to a cell based on value on another cell in the sheet. This can be achieved by using Conditional Formatting based on Another Cell in Google Sheets. Google Sheets have made it easy to achieve it providing the conditional formatting rules. All we need to do is to write down the rule or condition that is required to activate the formatting. In this article, we will discuss easy and step-by-step method to achieve Conditional Formatting based on Another Cell in Google Sheets.

Why we use Conditional Formatting based on Another Cell in Google Sheets

We might need to use Conditional Formatting based on Another Cell in Google Sheets for some specific problems such as we might need to highlight the product names whose sales are below average of all product sales. It is pretty difficult to work based on simple observation and analysis so it is always better to analyze while having highlighters pointed towards to cell(s) of focus. In this example, we have certain oils in stock and their per liter as well as average sale in liter for this month and also Average sales for the previous month as:

See also  How to Make Negative Numbers Red in Google Sheets [2 Methods]

conditional formatting based on another cell google sheets 1

We have the average calculated through the use of formula and also previous month’s Average Sales (liter) is also available. Our task is to apply Conditional Formatting such that:

  • Average Sales of the current month is highlighted Blue if it is greater than Previous Month’s Average Sales.
  • Average Sales of the current month is highlighted Red if it is less than Previous Month’s Average Sales.

Download/Copy Practice Workbook

How to Do Conditional Formatting based on Another Cell in Google Sheets

Step 1:

Select the cell which needs to be formatted by clicking over it.

Here, select the cell showing current month’s Average Sales (Liter) as:

conditional formatting based on another cell google sheets 2

It will appear with a blue outline showing selection.

Step 2:

Apply Conditional Formatting either by

Right Click -> View More Cell Actions -> Conditional Formatting

Displayed as:

conditional formatting based on another cell google sheets 3

OR

You can also select Conditional Formatting from “Format” toolbar as:

conditional formatting based on another cell google sheets 4

Conditional Formatting Rules Box will appear showing default conditional formatting as:

See also  How to Apply Conditional Formatting Strikethrough in Google Sheets

conditional formatting based on another cell google sheets 5

Step 3:

Apply Conditional Formatting Rules according to the requirements of your problem.

Here, we need to apply 2 rules as:

  1. Average Sales of the current month is highlighted Blue if it is greater than Previous Month’s Average Sales.
  2. Average Sales of the current month is highlighted Red if it is less than Previous Month’s Average Sales.

Rule 1 describes D10 must be greater than (or equals) D11 so we can write it as “=D10>=D11” and apply formatting as Green Highlight.

Select from Format Rules, Format cells if -> “Custom Formula Is” -> Write the formula “=D10>=D11” -> Apply Formatting Required (Highlight in green) as:

conditional formatting based on another cell google sheets 6

Also apply the second formatting rule for highlighting in red by choosing “Add another rule” as:

conditional formatting based on another cell google sheets 7

Now enter the rule details for next rule as:

conditional formatting based on another cell google sheets 8

Step 4:

Click “Done” using the button Done as:

conditional formatting based on another cell google sheets 9

You can see the required Conditional Formatting is applied to the cell.

conditional formatting based on another cell google sheets 10

Notes

You can use Conditional Formatting based on Another Cell Google Sheets method for many other problems using other cell’s values or comparison between them to Conditionally Format another cell.

Make sure to use accurate value of cell number by checking in the cell name box for correct results. Also “=” sign describes the start of formula, if omitted the Conditional Formatting would not work properly. So using “=” sign is a must for using any formula. Here the formula is used to compare the values between current month’s average sale and previous month’s average sales. One similar problem can be to highlight the product names of the products whose sales are less than 20% of stock available. For product number #6788, formula will become “=D2<20/100*C2”. After applying this to whole column we have the result as:

See also  How to Apply a Formula to an Entire Column or Row in Google Sheets

conditional formatting based on another cell google sheets 11

To learn more about how to copy conditional formatting from one cell to others as used above, please refer to the article:

How to Copy Conditional Formatting in Google Sheets (2 Methods)

When you copy formatting from one cell to others (like above), relative addresses used in the formula are automatically calculated. It means that as the reference provided in the formula for B2 is B4 and B3, Google Sheets will automatically refer to the values of C4 and C3 for C2 cell conditional formatting and so on. User need not to do it manually and save a lot of time.

Conclusion

The method for Conditional Formatting based on Another Cell Google Sheets is made easy and step-by-step elaborated procedure is given above. If there is any question, we would love to respond to it. Feel free to ask in the comment section below.

Thanks for reading!

Content Protection by DMCA.com

Widad Khan

Hi, I am Widad Khan. I was born and raised in Pakistan. I have always loved the field of Computer Sciences and since I was a kid, I would say I wanna grow up to be a scientist, now this term is mostly related to "Computer Scientist" and not scientist alone. I am pursuing my Bachelor Degree in Computer Sciences from GCU, Lahore. I have always loved keeping records of different types of data. Earlier, I would use MS Excel for that. But with the advancements in Google Sheets, my work has become much easier. And also it's very easy to share and coordinate data with fellows and coordinates.

OfficeDemy.com
Logo
Enable registration in settings - general