Conditional formatting in Google Sheets allows you to automatically change the color of a cell based on its contents. While the built-in rules (like “Text contains” or “Greater than”) are useful for basic tasks, they are extremely limited. They only allow a cell to format itself based on its own value.
If you want to highlight an entire row based on the value of a single dropdown menu, or format a cell based on a complex date calculation, you must use Custom Formulas.
Understanding the Custom Formula Syntax
When you write a custom formula for conditional formatting, Google Sheets evaluates your formula as a True/False question for every single cell in the range you selected. If the formula outputs TRUE, the formatting is applied. If it outputs FALSE, the formatting is ignored.
Example 1: Highlighting an Entire Row Based on One Cell
Imagine you have a project tracker in columns A through E. Column D contains a “Status” dropdown. You want the entire row to turn green when the status is changed to “Complete”.
- Highlight your entire dataset, starting from the top-left cell (for example,
A2:E100). - Click Format in the top menu and select Conditional formatting.
- The sidebar will open on the right. Under the “Format rules” section, click the dropdown menu and scroll all the way to the bottom. Select Custom formula is.
- In the formula box, type the following:
=$D2="Complete"
- Choose your formatting style (e.g., a green background color) and click Done.
Why does this work? The dollar sign ($) before the D is the crucial element. It locks the formula to column D. As Google Sheets evaluates the rule across the row (checking A2, B2, C2), it always looks back at D2 to see if the word is “Complete”. If D2 is “Complete”, every cell in that row receives a TRUE result and turns green.
Example 2: Highlighting Overdue Dates
Custom formulas also allow you to use built-in Sheets functions like TODAY().
If you have a column of deadlines (e.g., Column C) and want to highlight any date that is in the past, follow the same steps to open the Conditional Formatting sidebar, select your range, choose “Custom formula is”, and enter:
=C2<TODAY()
If you only want to highlight dates that are exactly 7 days away from today, you can use:
=C2=TODAY()+7
By mastering custom formulas, you can automate almost any visual indicator in your spreadsheets without relying on manual color coding.