Have you ever wondered how to apply conditional formatting in Google Sheets based on another cell? Well, you’re in luck! In this article, I will show you exactly how to do that and more. Get ready to learn how to use conditional formatting in Google Sheets to highlight cells based on the value of another cell.
What is Conditional Formatting?
Before we dive into the advanced topics, let’s cover the basics. Conditional formatting is a quick and easy way to highlight important data in Google Sheets. It allows you to use data to tell a story, and it’s really fast. Conditional formatting also includes simple visual enhancements like alternating row colors. But it gets even more exciting when you can format based on the value of another cell.
How to Use Conditional Formatting Based on Another Cell
To use conditional formatting in Google Sheets based on another cell, you just need to use the “Custom formula is” option in the conditional formatting menu. But it’s also important to know what kind of custom formula you need to build.
Using “Custom formula is” for Conditional Formatting Based on Another Cell
Let’s walk through an example to understand the syntax of the function. Suppose you want to highlight a specific set of data. Follow these steps to complete conditional formatting in Google Sheets based on another cell:
- Select the cells that contain the names (A2:A11).
- Go to the “Format” tab.
- Click on “Conditional formatting”.
- In the conditional formatting rules pane, select “Single color”.
- In the “Format cells if” dropdown menu, select “Custom formula is”.
- In the formula bar, enter the formula:
=B2<35. - Specify the format by clicking on the “Formatting style” dropdown menu.
- Click on “Done”.
That’s it! You’ll notice that we’ve formatted the cells in column A based on a simple evaluation in column B. The names John and Elvis are highlighted in red because their corresponding test scores are less than the chosen number, 35. In other words, we’ve formatted the cells based on the adjacent ones. We could just as easily use custom formatting based on another column or row of cells. The key is to modify the custom formula.
How Does the “Custom formula is” Function Work?
The reason we can use conditional formatting based on another cell in Google Sheets is due to data validation. We evaluate whether a condition is true. If it’s not, the formatting does not apply. If it’s true, we can format our target cells.
In the example above, even though we selected cells that contain names (A2:A11), the formula used the values of adjacent cells. For example, for cell A2, the conditional formatting evaluated the formula B2<35. Since the value of B2 is 12 and the formula evaluates to true, it was highlighted.
Similarly, for cell B3, the value of A3 is evaluated, and so on. Note that you can use a similar method to highlight duplicates. This is the simplest version of conditional formatting, but it’s not the only one. Let’s talk about another way to do this.
How to Use Conditional Formatting in Google Sheets Based on Multiple Cells
In the example above, you saw conditional formatting in Google Sheets based on another cell. But you can also do it with multiple cells. The method is slightly different. Let’s discuss how to evaluate multiple cells and then highlight cells based on the result. (This includes evaluating an entire row!)
For example, suppose you have a dataset like the one shown below:
Now, assume you’re the teacher and you want to highlight the students who failed in one or more subjects. At the same time, you also want to highlight the names of students who scored above 80 in all three subjects.
Here are the steps to follow:
- Select the range of data containing the names (A2:A11).
- Go to the “Format” tab.
- Click on “Conditional formatting”.
- In the conditional formatting rules pane, select “Single color”.
- In the “Format cells if” dropdown menu, select “Custom formula is”.
- In the formula bar, enter the formula:
=OR(B2<35,C2<35,D2<35). - Specify the format by clicking on the “Formatting style” dropdown menu. Here, you can choose a background color from the color scale. Since we are highlighting the students who failed, I chose the color red.
- Click on “Done”.
- In the conditional formatting pane, click on the “Add new rule” option.
- In the conditional formatting rules pane, from the “Format cells if” dropdown menu, select “Custom formula is”.
- In the formula bar, enter the formula to create new formatting rules:
=AND(B2>80,C2>80,D2>80). - Specify the color from the color scale in the formatting style.
- Click on “Done”.
This instantly highlights the names as shown above. Note that the conditional formatting, in this case, is based on multiple cells. The formatting rule takes into account a more complex formula, which evaluates multiple cells before formatting our target data.
How to Apply Conditional Formatting Based on Text in Google Sheets
We’ve talked about how to use conditional formatting based on another cell’s data. But can you do the same with text? Absolutely!
Here’s how to apply conditional formatting rules in Google Sheets if another cell contains text. In short: you simply need to enter the text string in quotes in the custom formula.
Here is a concrete example: Let’s say you work at a co-ed primary school and you need to suggest the best female students for a scholarship program at an all-girls private school. You could use conditional formatting to highlight all the female students. We’ll use a small sample of fake data to show how this would work.
To highlight all the students who identify as female, we can apply conditional formatting to the column containing their names in green using a text rule. To do this, simply:
- Select the cells you want to apply the formatting rules to (A2:A9).
- Go to “Format” and select “Conditional formatting”.
- Change the “Conditional” dropdown to “Custom formula is”.
- Enter the formula
=B2="F". - Choose green as the color in the formatting style color scale.
Suppose we also wanted to include non-binary students. We could use the OR function for this, like so:
=OR(B2="F",B2="NB")
How to Apply Conditional Formatting Based on Another Range of Cells
Instead of just one cell, you can also use a range of cells as an argument for your conditional formatting rule. This is slightly different than simply basing our formatting on another cell. In this case, you evaluate an entire range of cells and then use conditional formatting in Google Sheets based on the overall evaluation.
Looking at our example sheet, we can highlight the names of students who scored less than 50 in Mathematics.
Here’s how you do conditional formatting in Google Sheets based on another column:
- Select the range of cells you want to apply the formatting rules to.
- Go to “Format” and select “Conditional formatting”.
- Change the “Conditional” dropdown to “Custom formula is”.
- Enter the formula
=B2:B<50. - Choose the color you want to use from the formatting style color scale.
- Click “Done”.
The conditional formatting applies the formatting rules to highlight all the cells in column A that correspond to a cell in Column B that is less than 50. In this case, it’s John, Elvis, and Sheldon.
Frequently Asked Questions
Here are some of the most frequently asked questions about conditional formatting based on another cell in Google Sheets. I’ve done my best to provide concise answers. If you would like a more detailed explanation, check out the guide above.
Can I use conditional formatting based on another cell in Google Sheets?
Yes, you can apply conditional formatting to a cell based on another cell. Just follow the easy steps outlined in the FAQ above and use the custom formula that fits your needs.
Can I use conditional formatting in Google Sheets based on the color of another cell?
Yes, you can use conditional formatting in Google Sheets based on the color of another cell. This is a bit more complex than the methods I described above. You will need to use Google Apps Script. This means a little extra coding, but the result is worth it. First, you will filter by color. Then, you will apply conditional formatting based on that filter. Don’t want to code? There is another workaround solution for formatting based on cell color. Simply highlight a cell if a Google Sheets checkbox is checked or not.
How can I use conditional formatting to display green if the answer is correct and red if the answer is wrong?
You can set up your conditional formatting rule to display green when the correct answer is entered and red when the wrong answer is entered.
Let’s take an example. We want cell B2 to display green if the correct answer is entered and red if the wrong answer is entered.
To do this, you need to set up three formatting rules:
- One for if the cell is equal to (the correct answer).
- Another one for if the cell is not equal to (the wrong answer).
- The last one for if the cell is empty.
I have divided this into three sections. I will start with the first section “is equal to”.
Is Equal To:
- Select the range of cells you want to apply the formatting rules to.
- Go to “Format” and select “Conditional formatting”.
- Choose “Is equal to” from the formula dropdown.
- Type
=A17(the cell with the correct answer). - Choose green from the formatting style color scale.
Is Not Equal To:
- Click on “Add a rule”.
- Choose “Is not equal to” from the formula dropdown.
- Type
=A17(the cell with the correct answer). - Choose red from the formatting style color scale.
Is Empty:
You will need to add another rule for if the cell is empty and choose white as the background color. Make sure this cell is above the “is not equal to” rule.
If a value corresponding to cell A4 is entered, the cell will be highlighted in green, and if another value is entered, it will be highlighted in red, as shown below.
How can I use “greater than” and “less than” operators together for conditional formatting?
If you want to conditionally format a range of cells using both the “greater than” and “less than” operators simultaneously, you can use the IF function in your formatting rule instead of an inherent IF function.
For example, if we wanted to highlight our spreadsheet so that students who scored above 40 but below 70 are highlighted, we would use the formula:
=IF(B2:B11>40, B2:B11<70)
Go to the conditional formatting window and paste this as your custom formula.
You can see from our example that only the students who scored above 40 but below 70 are highlighted.
How can I color a cell based on the value of another cell?
To color a cell based on the value of another cell, simply follow these steps:
- Select the range of cells you want to apply the formatting rules to.
- Go to “Format” and select “Conditional formatting”.
- Change the “Conditional” dropdown to “Custom formula is”.
- Enter a formula to define the formatting rule.
- Select the color you want to use for formatting.
- Click “Done”.
How can I apply conditional formatting if one cell is greater than another?
To apply conditional formatting if one cell is greater than another, simply follow these steps:
- Select the range of cells you want to apply the formatting rules to.
- Go to “Format” and select “Conditional formatting”.
- Change the “Conditional” dropdown to “Custom formula is”.
- Use the “greater than” operator to indicate which cell should have the larger value, for example:
B2>C3. - Click “Done”.
Can I use an IF formula in conditional formatting in Google Sheets?
The definition of any conditional formatting rule is essentially an IF function. For example, you could set a “Custom formula is” rule of B2>C3. This statement means “IF” B2 is greater than C3, highlight the cell. You can also use IFS statements as custom formulas, such as =IFS(B2:B11>40, B2:B11<70).
How do I apply conditional formatting based on another column?
To apply conditional formatting based on another column, simply use the column address as an argument in a custom formula. For example, you could use =B2:B<50 to check if values in column B (excluding the header in B1) are greater than 50.
Conclusion
To format cells based on another cell in Google Sheets, simply follow the easy steps listed in the FAQ above and use the custom formula that fits your needs. If you found my article helpful, you might also want to check out our guide on searching in Google Sheets.
Do you have any other questions about conditional formatting? Is there anything else you would like to see included in my guide? Let me know in the comments!
If you found this article on conditional formatting based on another cell in Google Sheets helpful, you might also enjoy the following tutorials on Google Sheets:
- Creating a Heatmap in Google Sheets (Step-by-Step Tutorial)
- Formatting Phone Numbers in Google Sheets
- How to Create a Dependent Dropdown List
- How to Color Alternate Rows in Google Sheets
- How to Zoom In and Out in Google Sheets
- How to Insert an Image into a Google Sheets Cell
- How to Compare Two Columns in Google Sheets
- Format Painter in Google Sheets (Easily Copy Formatting)
- How to Lock Cells in Google Sheets: Step-by-Step Guide
Remember, if you need any further assistance with Google Sheets or any other topic, Crawlan.com is here to help. Don’t hesitate to reach out!
