In this article, I am going to teach you what the conditional formatting feature is, how it works to your advantage and, how you can apply conditional formatting based on the value of another cell in Google Sheets.
So, let me get stared for you!
What is Conditional Formatting in Google Sheets?
Conditional formatting is tool used in Google Sheets which lets you format your cells if a logical condition of yours is met. It especially helpful for analytical and comparison purposes and it is fairly easy to apply as well.
For instance, you would want to format a particular range of cells within your set of values, if your condition is true; so after applying this tool, you can easily identify from your wide range of values, which of those values meets your condition. Therefore, letting you draw analytical and effective conclusions.
Conditional Formatting: Based on the Value of Another Cell
Now, conditional formatting based on another cell, works, by using the same logic. However, the only difference is, that with only conditional formatting you could make changes to your cell/s (format it/them) if the value within that very cell met your given conditions.
But with conditional formatting based on the value of another cell simply means that you can apply the formatting on a cell – if the value/s in another cell meets your condition/s.
Makes your life easier, doesn’t it?
In this article, I can hopefully show you two situations when you can apply the conditional formatting based on another cell tool.
How to Use the Conditional Formatting Based on another Cell in Google Sheets
See the set of values I have presented below for my example for you. It is a list of people and their respective incomes.
For taxation purposes, I need to extract those names of the people from this list, with the income stated below $3,000. Moreover, the names of those said people should be highlighted.
So, I need my spreadsheet program to first, analyze the values in the “Income” column and, format the cells in the “Names” column accordingly.
I need to format the cell A2, based on the value in cell B2 meeting the condition I give.
Conditional Formatting: Steps
1. Select the entire cell range for “Names” – (A2 to A9).
2. Find the “Format” option from the top menu bar of your spreadsheet.
3. From the drop-down menu, find the “Conditional Formatting” option and click on it. By clicking on it, a dialogue box should pop-up on the right side of your screen.
4. From the very first two options of the conditional formatting side-bar, ensure that the “Single Color” is chosen.
5. From the second drop-down menu labeled “Format rules”, you need to click on the “Custom formula” option. This means that you will give your own input for the condition. It’s the very last option from the drop-down menu.
6. If you select the “Custom formula is” option, an empty field named “Value of formula” will automatically open immediately below it.
7. It is in this field that you are required to actually give your logical condition. In the case of this example, that will be:
8. In the last menu called “Formatting style” you can customize the color you prefer to format (highlight) your cells with. Let me go with the light yellow color.
(You can either choose to color the cells or the text in them).
9. You can now select “Done” right at the bottom right.
As you can see, Google Sheets has recognized whether the values in the cells B2 to B9 meet my logical condition of them being less than 3,000. Then, it has formatted the cells A2 to A9 based on the condition being true or false.
For formatting cell A2, Google Sheets has analyzed the value in cell B2; for formatting cell A3, it has in the same sense, analyzed the value in cell B3. And, so on.
Thus, conditional formatting in Google Sheets based on the value in another cell.
Now, I will proceed on to formatting a particular range of cell based on the values of multiple other cells.
Doing so might be necessary, for when you need to identify certain values which stand for more than a single attribute and, you need to analyze each of those attributes.
Following my first example in this tutorial, suppose I am analyzing incomes of people, but this time, I am analyzing income categorically.
Meaning, I have broken up the total income of each person into three categories; and, I want to judge each of those categories and format my names in the same way.
Condition Formatting: Based on Multiple Other Cells
I want to find out the names of those people whose income is less than $500 for each type of income.
1. Select the entire range of cells from A2 to A9.
2. Similar to the prior example, Select the “Format” option from the top menu bar and click on “Conditional formatting” from the drop-down menu.
3. Again, check that the “Single color” option has been selected.
4. In this “Format rules” menu, click on the “Custom format” option down at the very end of the menu.
5. In the “Value or Formula” menu, where you are supposed to give your logical condition, type in
This formula makes sure to check each value in the cell ranges B2:B9, C2:C9 and D2:D9 and, format the cell range A2:A9, if the said values match my given condition.
6. For the style of the formatting, I will go with the color red.
7. Lastly, I click on “Done”.
Google Sheets has analyzed each value in the given ranges of cells. Then, it has formatted (highlighted) the values in the A2:A9 cell range. If one, or, more than one value in the mentioned three cell ranges are less than the value 500, my spreadsheet will color the cells in the “Names” column red.
FAQs for Conditional Formatting in Google Sheets Based on Another Cell
Is there any difference between the IF Formula and Conditional Formatting in Google Sheets?
The conditional formatting is basically an extension of the IF formula in Google Sheets. You can apply the IF formula on your values and get results, without altering the format of the cell. The IF function is applied on values and it will not do anything to the format of your cell unless you want to.
Is it possible to apply an IF formula in Conditional formatting in Google spreadsheets?
Yes of course. All you have to do is, when you give the input for the “Custom formula” from “Format rules”, type in your IF formula in the given empty field. Afterwards, select “Done” as usual.
What if I need to format my cell/s based on the condition of another cell’s text and not numerical figures?
Did you notice the other options your spreadsheets has under the “Format rules” drop-down menu? It contains a quite number of choices for when you need to conditionally format a cell based on another cell’s text value.
For instance, if a text has a certain beginning or end, if that text is exactly something you have given as input or, if a text is not something.
I hope my article, on using conditional formatting based on the value in another cell, will help you effectively in the future. Best of luck!
For relevant articles on using the IF formula from TeqTog, you may like: How to Search IF Cell Contains in Google Sheets and Multiple IF Statements in Google Sheets and Google Sheets Formulas Not Working (Refresh Formulas)