What Is Conditional Formatting Based On Another Cell In Google Sheets?
Conditional Formatting Based on Another Cell In Google Sheets is a powerful tool that allows users to highlight specific cells or ranges based on the contents of other cells in the same spreadsheet. We can format an entire row based on a single column’s Cell. We can compare values like greater than, less than, or equal to, from another Cell to highlight the existing dataset.
The Google Sheets Conditional formatting based on another cell feature helps to draw attention to important information quickly and increases data analysis efficiency when we display different colors or font styles when certain conditions are met. For example, we have the data table below with Pass or Fail results of five students. We will first apply formatting to a selected cell range and then use a formula to apply Conditional formatting to other cells using the already formatted cells.
Select cells A2:B6 and apply the conditional formatting rule by entering the custom formula =$B2=“Pass”. We will get the below output.
We can observe that the cells A3, A5 and A6 have the cell value as “Pass” and because of the set formula the selected cell range is formatted.
Table of contents
Key Takeaways
- Conditional Formatting based on another Cell in Google Sheets helps us to highlight a cell considering the cell value of another cell if it’s a number, a cell range, compared data regardless of the selected cell’s value, that must be formatted.
- We create one rule, choose the cell to be formatted and insert a formula or a rule linking to another cell. Remember, if the dataset is modified, the formatted cells get updated too.
- Conditional Formatting helps us highlight errors, empty or blank cells, specific words, or any symbols, alphabets, or characters in the selected cells.
- As we also saw, we can clear the set Conditional Formatting Rules for the selected cell, dataset or the entire sheet.
How To Use Conditional Formatting Based On Another Cell In Google Sheets?
We can use Conditional Formatting Based On Another Cell as follows:
In the worksheet, select the “Format” tab à click the “Conditional formatting” option, as shown below.
The “Conditional format rules” pane appears on the right side. Here, click the “Add another rule” option, as shown below.
We see the “Single Color” and the “Color scale” tabs. Here, click the “Single color” tab,
- First, select the required cell or cell range in the “Apply to range” field that must be formatted.
- Next, select the “Custom formula is” option from the “Format cells if…” drop-down and enter the formula accordingly in the “Value or formula” field that appears linking it to the applies range, then select the required highlight color from the “Formatting style”, as shown below.
- Finally, click the “Done” option. Then, we will have the cellshighlighted as the output.
In this article, we will see how we can use the Conditional Formatting Based on Another Cell in Google Sheets for the following scenarios, namely,
- If Another Cell Contains Number.
- Based on Another Cell Range.
- Based on Another Cell Color.
- If One Cell Is Greater Than Another.
Examples
We will consider specific examples for the above-mentioned scenarios using the Google Sheets Conditional Formatting based on another cell criteria.
Example #1 – Conditional Formatting If Another Cell Contains Number
The following data consists of a list of smartphones and their quarterly sales. We will format cell A5 based on the cell value of D2, if it is a number.
The steps to format a cell based on another cell value if it is a number are as follows:
Step 1: Choose cell A5 – select the “Format” tab – click the “Conditional formatting” option. The “Conditional format rules” pane appears on the right side. Here, click the “Add another rule” option, as shown below.
Step 2: We see the “Single Color” and the “Color scale” tabs. Select the “Single color” tab, then,
select the “Custom formula is” option from the “Format cells if…” drop-down, enter the formula =ISNUMBER(D2) in the “Value or formula” field, and select the color, here yellow, from the “Formatting style”, as shown below.
Step 3: Finally, click the “Done” option to get the below output.Cell A5 is formatted because the other cell value, i.e., cell D2, is a number.
Example #2 – Conditional Formatting Based on Another Cell range
The following data consists of three employee sales for three days. We will format cell B3 based on the cell range C7:C9.
The steps to format a cell based on another cell range values are as follows:
Step 1: Choose cell B3 – select the “Format” tab – click the “Conditional formatting” option. The “Conditional format rules” pane appears on the right side. Here, click the “Add another rule” option, as shown below.
Step 2: We see the “Single Color” and the “Color scale” tabs. Select the “Single color” tab, then,
select the “Custom formula is” option from the “Format cells if…” drop-down, enter the formula = =C7-C9<50000 in the “Value or formula” field, and select the color, here green, from the “Formatting style”, as shown below.
Step 3: Finally, click the “Done” option to get the below output.Cell B3 is formatted because the cell range C7:C9 is 60,000, which is greater than 50,000.
Example #3 – Conditional Formatting Based on Another Cell Color.
Unlike Excel, in Google Sheets we cannot format based on the color of another cell but only for some options such as,
- Greater than, less than, values in-between or equal to values.
- Text that has some strings, starts with, ends with, etc.
- Cell is empty, not empty, etc.
However, to apply Formatting Based on Another Cell Color we can use the condition of the cell to format the other. Let us see with an example.
The following data consists of products order details such as, order date, customer names who ordered the products and the delivery status. We will format cell A8 based on the E4 cell value.
The steps to format a cell based on another cell color or value are as follows:
Step 1: Choose cell A8 – select the “Format” tab – click the “Conditional formatting” option. The “Conditional format rules” pane appears on the right side. Here, click the “Add another rule” option, as shown below.
Step 2: We see the “Single Color” and the “Color scale” tabs. Select the “Single color” tab, then,
select the “Custom formula is” option from the “Format cells if…” drop-down, enter the formula = =$E4=”Pending” in the “Value or formula” field, and select the color, here yellow, from the “Formatting style”, as shown below.
Step 3: Finally, click the “Done” option to get the below output.Cell A8 is formatted because the E4 cells delivery status is Pending, regardless of A8 cells order that is delivered.
Example #4 – Conditional Formatting if One Cell Is Greater Than Another.
Let us consider the data of Example 2 again, where the table consists of three employee sales for three days. We will format cell B5, if One Cell Is Greater Than Another, i.e., if cell C3>C7.
The steps to format a cell based on another cell range values are as follows:
Step 1: Choose cell B5 – select the “Format” tab – click the “Conditional formatting” option. The “Conditional format rules” pane appears on the right side. Here, click the “Add another rule” option, as shown below.
Step 2: We see the “Single Color” and the “Color scale” tabs. Select the “Single color” tab, then,
select the “Custom formula is” option from the “Format cells if…” drop-down, enter the formula = =C3>C7 in the “Value or formula” field, and select the color, here blue, from the “Formatting style”, as shown below.
Step 3: Finally, click the “Done” option to get the below output.Cell B5 is formatted because the cell range C3 is 30,000, which is greater than cell C7 which is 15,000.
Important Things To Note
- We must remember that we cannot format a cell based on another cell’s color. In such scenarios, we can use the cell’s value.
- Depending on our requirements, we can also set multiple rules.
- Once the rules are set, if the cell values from where the formatting is taken changes, then, automatically the formatted cell gets updated too.
Frequently Asked Questions (FAQs)
We can clear the Conditional Formatting in Google Sheets in a couple of ways, namely,
Delete the set rule –
Let us consider example 1’s output image, shown below.
Hover around the set rule in the “Conditional format rules” pane, we will get the “Remove rule” option. Click it, as shown below.
Then, the dataset becomes as how it was at first, as shown below.
However, if we have multiple rules set, we can just clear the rules that are not required. In the same way as learnt, hover around the set rules and delete the rule that is not required, as shown in the images below.
We can use the above learnt options to clear single or multiple rules at once.
At times, we must clear all the rules set for a dataset, such as formatting styles or conditional formatting, then, we can proceed with the following steps,
Select the entire worksheet or just the dataset – go to the “Format” tab – click the “Clear formatting” option, as shown below.
The Google Sheets conditional formatting based on another cell may not work for the following reasons, namely:
a) The formatted dataset has been modified or updated and did not get refreshed to display the updated data.
b) We have not set the conditional formatting rules or the set rules are deleted.
c) Also, since Google Sheets are online, it might not work due to network issues.
We have another alternate way to apply the “Conditional formatting” as follows:
First, select the dataset – right-click on the selected dataset – scroll to the end of the list and click the “View more cell actions” right arrow – select the “Conditional formatting” option, as shown below. 
Download Template
This article must help understand Conditional Formatting based on another Cell in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What is Conditional Formatting Based On Another Cell In Google Sheets. We learn how to use Formatting Based On Another Cell, example. You can learn more from the following articles –
EVEN Function In Google Sheets
Leave a Reply