How to Use Google Sheets Conditional Formatting with Custom Formulas

Conditional formatting is an essential tool in Google Sheets for highlighting data. Most users know how to use the basic rules, such as turning a cell red if it contains the word “Overdue” or turning it green if a number is greater than 100.

But what if you want to highlight an entire row based on the value of a single cell? What if you want to highlight a cell only if it matches a value on a completely different sheet? The standard dropdown rules cannot do this. You must use Custom Formulas.

In this guide, you will learn how to write custom formulas within the conditional formatting engine to build complex, intelligent dashboards.

Accessing the Custom Formula Menu

The setup for a custom formula begins exactly like a standard conditional formatting rule.

1. Highlight the entire range of cells you want the formatting applied to (e.g., A2:E50).
2. Click Format in the top menu, then select Conditional formatting.
3. In the sidebar that appears, click the Format rules drop-down menu.
4. Scroll to the very bottom and select Custom formula is.
5. A blank text box will appear beneath it. This is where you write your logic.

Use Case 1: Highlighting an Entire Row

Imagine you have a project tracker in columns A through E. Column E contains the status (“Done”, “In Progress”, “Stuck”). You want the entire row to turn grey when the status in Column E is marked “Done”.

If your range is A2:E50, type this custom formula:

=$E2="Done"

The Magic of the Dollar Sign: The dollar sign ($) is crucial. It tells Google Sheets to “lock” the condition to Column E. As the conditional formatting engine scans across the row (checking cell A2, then B2, then C2), the $ forces it to continually look back at E2. If E2 says “Done”, it applies the formatting to every cell in that row.

Use Case 2: Comparing Two Columns

Suppose you are tracking inventory. Column C is your current stock level, and Column D is your minimum required stock level. You want Column C to turn red only if the current stock drops below the minimum required stock.

Select the range for Column C (e.g., C2:C100) and use this custom formula:

=C2<D2

Because there are no dollar signs, the formula is relative. For cell C2, it checks if it is less than D2. For cell C3, it checks if it is less than D3, and so on down the entire column.

Use Case 3: Highlighting Alternating Rows (Zebra Striping)

While Google Sheets has a built-in “Alternating colours” feature, it overwrites other formatting. If you want a subtle zebra-stripe effect that plays nicely with other rules, you can use a custom formula with the ISEVEN and ROW functions.

Select your entire table range and enter:

=ISEVEN(ROW())

This formula checks the physical row number of every cell. If the row number is even (2, 4, 6), the condition is TRUE, and the background colour is applied. This creates perfectly alternating stripes that automatically adjust if you insert or delete rows.

By mastering custom formulas in conditional formatting, you break free from the limitations of the standard menus and can design spreadsheets that react dynamically to complex logic.