Conditional formatting is a powerful tool for making data in your spreadsheet easier to understand.
With conditional formatting, you can do things like color-code your spreadsheet based on the values of a cell, or change other formatting of the cell.
One thing many spreadsheet users don’t know about conditional formatting is that you can use it to format a cell based on the values of another cell.
In this tutorial, I will show you how to use conditional formatting based on another cell in Google Sheets.
Highlight Cell Based On Another Cell
The trick to changing the formatting of a cell based on another cell is to use the Custom formula is option in the conditional format rules.
Let’s take a look at an example.
In this example, I have a month in column A and revenue for that month in column B. I am going to set up a custom formula in conditional formatting that will highlight the month in column A, if the revenue for that month in column B is greater than 2000.
So basically what I am doing is creating a formula that will highlight a cell based on the value of another cell.
Here’s how this is done:
1. The first step is to select the cell range that you want to highlight
2. Next in the top menu select Format>Condtional formatting
3. Navigate down to the Format rules area and click on the drop-down box to chang the Format cell is rule
4. Select Custom formula is
5. In the text box, we will be entering our formula. In this example, I want to highlight the cells in column A, if the corresponding cell in column B is greater than 2000. So my formula is “=B2>2000”
6. Next we will need to select the color that our cells that meet this rule will change to. Select the Fill color option under Formatting style
7. Choose your desired color. I have chosen a green color in this example. Select the green Done button when you are finished
8. You will now see that the cells that have met the custom rule you set up will have highlighted with the color you have chosen
Conditional Formatting Based on Text in Another Cell
In the previous example, I showed changing the color of one cell based on the value of another cell, but we can also use the same process to change the formatting of a cell based on text in another cell.
In this example, I will be highlighting the students’ name in Column A, if the text in Column C is equal to “Fail’
To do this, the steps are the same as in the previous example. The key is setting up the appropriate custom formula in the conditional formatting rules.
My formula in this example is:
=C2=”Fail”
After I have set up the formula and applied it to the appropriate range, this is how it will look in my spreadsheet:
Closing Thoughts
When you start to use custom formulas with conditional formatting a lot of possibilities open up for what you can do with the formatting in your spreadsheet.
In this resource, I have covered formatting based on the values of another cell, whether that cell contains a numeric or text value. There are many more ways that you can use it as you get creative with your custom formulas.
More Google Sheets Tutorials:
How to Make Negative Numbers Red
Cells, rows, or columns can be formatted to change text or background color if they meet certain conditions. For example, if they contain a certain word or a number. Example A teacher can highlight test scores to see which students scored less than 80%.
Use advanced conditional formatting
Use custom formulas with conditional formatting
You can use custom formulas to apply formatting to one or more cells based on the contents of other cells.
- On your computer, open a spreadsheet in Google Sheets.
- Select the cells you want to format.
- Click Format Conditional formatting.
- Under the "Format cells if" drop-down menu, click Custom formula is. If there's already a rule, click it or Add new rule Custom formula is.
- Click Value or formula and add the formula and rules.
- Click Done.
Note: Formulas can only reference the same sheet, using standard notation "(='sheetname'!cell)." To reference another sheet in the formula, use the INDIRECT function.
Example 1
To highlight when there's more than one occurrence of the same value in your data:
- On your computer, open a spreadsheet in Google Sheets.
- Select the range you want to format. For example, cells A1 to A100.
- Click Format Conditional formatting.
- Under the "Format cells if" drop-down menu, click Custom formula is. If there's already a rule, click it or Add new rule Custom formula is.
- Write the rule for the first row. In this case the rule would be, "=COUNTIF($A$1:$A$100,A1)>1."
- Choose other formatting properties.
- Click Done.
Example 2
To format an entire row based on the value of one of the cells in that row:
- On your computer, open a spreadsheet in Google Sheets.
- Select the range you want to format, for example, columns A:E.
- Click Format Conditional formatting.
- Under the "Format cells if" drop-down menu, click Custom formula is. If there's already a rule, click it or Add new rule Custom formula is.
- Write the rule for the first row. For example, if you want to make the whole row green if the value in column B is "Yes", write a formula like "=$B1="Yes"."
- Choose other formatting properties.
- Click Done.
Absolute vs. relative references
Often, you will need to add dollar signs ($) in front of letters and numbers in formulas so that the formatting is applied using absolute references as opposed to relative references (A1 to B1, A2 to B2).
Use wildcard characters with conditional formatting
You can use wildcard characters to match multiple expressions. Wildcard characters can be used with the "Text contains" or "Text does not contain" fields while formatting.
- To match any single character, use a question mark (?). For example, a text rule containing "a?c" would format cells with "abc," but not "ac" or "abbc."
- To match zero (0) or more characters, use an asterisk (*) . For example, a text rule containing "a*c" would format cells with "abc," "ac," and "abbc" but not "ab" or "ca."
- To match a question mark or asterisk in text, you can escape the wildcard characters by adding a tilde (~) in front of them. For example, a text rule containing "a~?c" would format cells with "a?c" but not "abc" or "a~?c."
Notes:
- To remove a rule, point to the rule and click Remove .
- Rules are evaluated in the order listed. The first rule found to be true will define the format of the cell or range. To reorder rules, click and drag them.
- If you copy and paste from a cell or range that has formatting rules, these rules will be applied when you paste the copied data.