Last Updated on November 2, 2023 by Jake Sheridan
In this tutorial, you will learn how to use conditional formatting based on another cell in Google Sheets.
How To Use Conditional Formatting Based On Another Cell in Google Sheets
Conditional formatting is a powerful tool in Google Sheets that allows you to highlight cells that meet certain criteria.
We can use conditional formatting to highlight cells based on the value of another cell. This can be helpful if you want to visualize the relationship between two cells, or if you want to quickly see which cells need to be updated based on the value of another cell.
For example, you may want to see if any row in your table contains a value that exceeds some maximum value. You can apply a custom formula that compares a column with a cell that holds that maximum limit.
In this guide, we will show you how to use conditional formatting based on another cell in Google Sheets.
How To Apply Conditional Formatting Based On Another Cell’s Value in Google Sheets
Here’s how to apply conditional formatting based on another cell’s value in Google Sheets.
First, identify the range you want to apply conditional formatting on and the cell that will be used for comparison. In this example, we’ll be adding conditional formatting to columns A to E by comparing Column C with the maximum quantity stated in cell H1.
Select the range you want to add conditional formatting to.
Click on the Conditional formatting option found under the Format menu.
In the conditional format rules panel, click on the dropdown list to reveal additional options for format rules.
Select the Custom formula is option from the dropdown menu.
Write the custom formula in the provided textbox. In this example, we only want to highlight rows that have a quantity greater than the limit provided in cell H1.
Next, you may now specify the type of formatting to add to cells that follow the stated criteria. Click on Done to proceed with adding the format rules to the specified range.
Rows that fit your rule’s criteria should now be highlighted.
Modifying the comparison cell will automatically update which rows are highlighted.
In the example above, raising the max quantity lowers the number of rows in our table that are highlighted.
This guide should be everything you need to use conditional formatting based on another cell in Google Sheets.
You may make a copy of this example spreadsheet to test it out on your own.
More ways to do conditional formatting Google Sheets:
- Remove Conditional Formatting
- How To Do Conditional Formatting
- Copy Conditional Formatting
- Conditional Formatting for Duplicates
- Conditional Formatting with a Checkbox
- Conditional Formatting with Multiple Conditions
- Conditional Formatting Based On Another Sheet
- Apply Conditional Formatting Across An Entire Row
- Conditional Formatting to Compare Two Columns
- Conditional Data Validation
- Color Scale Based On Another Cell
- Relative Reference
- Conditional Formulas
- Conditional Formatting Based On Another Cell Not Empty
- Conditional Formatting Based On Another Cell Date
- If Cell Contains Text in
- Highlight Cells
- Highlight Duplicate Cells
- Make Negative Numbers Red