Google Sheets Custom Conditional Formatting Guide

Yes. Google Sheets lets you create custom conditional formatting rules, including formula-based rules that automatically change cell colors or styles when your conditions are met. In a browser, select the cells, choose Format > Conditional formatting, then select Custom formula is to enter a rule.

What custom conditional formatting does

Conditional formatting changes how a cell looks when its value meets a condition; it does not change the underlying value. You can use it to spot overdue dates, mark repeated entries, flag unusual numbers, or highlight an entire row based on the status in one column. When the cell values change, Sheets reevaluates the rule and updates the formatting.

Google Sheets offers built-in conditions such as text contains, date is before, and greater than. A custom formula is useful when those options cannot express the relationship you need. For example, a built-in condition can identify a cell containing a word, while a formula can use the word in one column to decide whether to format several other columns in the same row.

The result is visual only. A highlighted task is not automatically sorted, moved, or marked complete, and a color by itself is not reliable data for calculations. Keep the actual status or value in cells, and treat the formatting as a signal for readers.

Create a custom rule in Google Sheets

Select the cells you want the rule to format. Selecting the intended range first helps Sheets set the correct starting row.
Choose Format > Conditional formatting. The conditional-formatting panel opens on the right.
Check the Apply to range field and correct it if needed. A range such as A2:D100 covers four columns and 99 rows.
Under Format rules, open the condition menu and select Custom formula is.
Enter a formula beginning with an equals sign, choose the formatting style, and select Done.

You can choose a fill color, text color, bold, italics, or other available styling options. To add another condition, select Add another rule in the panel. The same menu also offers Color scale, which is useful for showing a gradient across numeric values rather than applying one formula-driven style.

If a rule already exists, select a cell in its range and reopen Format > Conditional formatting to review it. The panel shows the rule, its formatting, and the range it applies to. This is also where you can edit or remove a rule.

How custom formulas use cell references

A custom formula is evaluated for each cell or row in the selected range. Its references should usually be written as if the formula were being checked for the top-left cell of that range. Sheets adjusts references as it evaluates the other cells. This is why the starting row in the formula matters.

The dollar sign locks part of a reference. In a reference such as $D2, the column D stays fixed while the row number can change. That is useful when a value in column D controls formatting across several columns. By contrast, D2 can shift in both directions as the rule applies. A reference such as $D$2 locks both the column and row, so every evaluated cell checks that same cell.

For example, to format the row when its status is Late, assume the range A2:D100 contains task details and column D contains status values. Set Apply to range to A2:D100. The formula is =$D2="Late". If D2 contains Late, the formatting applies to A2:D2; on the next row, Sheets checks D3 instead. The column is fixed so every cell in the row checks D, while the row changes as expected.

Formula conditions should return a true-or-false result. Comparisons such as equal to, less than, and greater than can provide that result directly. Functions such as AND and COUNTIF can combine checks when one comparison is not enough.

Practical formula examples

Highlight overdue, unfinished tasks. Suppose A2:D100 contains a task table, column C contains due dates, and column D contains task status. Apply the rule to A2:D100. Use =AND($C2<TODAY(),$D2<>"Complete",$C2<>""). If C2 is yesterday and D2 says Open, the formula returns TRUE and formats that row. A future due date, a Complete status, or a blank due-date cell prevents the rule from highlighting it. The date comparison is dynamic: TODAY changes as the current date changes.

Find duplicate names or codes. Suppose A2:A100 contains the values to check, with a header in A1. Apply the rule to A2:A100 and use =COUNTIF($A$2:$A$100,A2)>1. If the value in A2 appears twice or more in that range, COUNTIF returns at least 2, the comparison is true, and the cell is highlighted. Each occurrence of a duplicated value is marked. This identifies repeated values, not necessarily incorrect records, so check whether duplicates are valid in your data.

Format a number only when another column meets a threshold. Suppose A2:B50 has product names in column A and quantities in column B. To format the product names when quantity is below 5, apply the rule to A2:A50 and use =$B2<5. When B2 contains 3, A2 is formatted; when it contains 7, it is not. The formula’s row number matches the top row of the selected range, even though the range includes only column A.

When to use a built-in rule instead

Use a built-in condition when it states your requirement clearly. Text contains, is empty, date is before, and greater than are easier to inspect than a formula. They are also convenient for a quick one-column highlight that does not depend on other cells.

Choose a custom formula when the rule needs to combine conditions, look at another column, or format a wider range based on one cell. For instance, a formula can require that a due date has passed and the task is not complete. A color scale is different: it maps values across a range to colors, making it useful for comparing low-to-high values, but it is not a substitute for a true-or-false rule such as flagging a specific status.

For a shared sheet, favor rules that another person can understand and maintain. A short rule tied to a clearly named status is often more useful than a complicated formula whose logic is not documented.

Rule order, ranges, and maintenance

A sheet can have multiple conditional-formatting rules, and more than one may match the same cell. When the appearance is unexpected, inspect all rules that cover that cell and their order in the sidebar. Reorder or simplify overlapping rules so the intended formatting is clear. Avoid assuming that a cell can communicate several independent conditions through competing fill colors.

Keep Apply to range aligned with the table. If your data starts on row 2, a formula referring to row 1 may check the header instead of the first record. When extending a table, confirm that the range includes new rows; a correct formula does not format cells outside its applied range. You can edit the range in the conditional-formatting panel.

For custom formulas that refer to cells on another sheet, direct cross-sheet references are restricted in conditional-format rules. A common workaround uses INDIRECT, but it adds complexity and should be checked carefully when sheet names or ranges change. If possible, bring the relevant value into the same sheet or use a helper column to make the condition easier to maintain.

Common problems and how to fix them

The whole row or wrong column is highlighted: Check which reference parts have dollar signs. To base a multi-column row format on column D, lock the column but allow the row to change.
Nothing is highlighted: Confirm that the rule begins with an equals sign, returns TRUE for a sample row, and uses the row number matching the top of Apply to range. Check that the values are the expected type; a date stored as text may not behave like a real date.
Only the first row works: Make sure the row number is not locked unnecessarily. A reference such as $D$2 always checks D2; it will not automatically check each record’s status.
Blank cells are marked unexpectedly: Add a blank check to the condition when empty values should be excluded. This matters especially for date and number comparisons.
Existing formatting seems to disappear: Review overlapping rules and their order, and check the selected formatting style. Also distinguish conditional formatting from ordinary formatting applied through the toolbar.

A helpful troubleshooting method is to test the logic in a normal cell temporarily, where TRUE or FALSE is visible, then compare the references with the selected range. Remove the test formula afterward if it is not part of the sheet.

Limits and practical considerations

Conditional formatting is useful for visual scanning, but it can make a large or densely formatted sheet harder to read. Use a small set of meaningful colors and keep the same meaning consistent—for example, red for overdue and green for complete. Do not communicate important status by color alone; include readable text such as Open or Complete for people who cannot distinguish the colors or view the sheet in a different display.

Custom formatting is available in Google Sheets through the browser interface. Excel has its own conditional-formatting controls and rule handling, so Excel instructions may give a different menu path even when a formula looks familiar. If a sheet is downloaded or opened in another spreadsheet program, review the rules afterward rather than assuming every detail will transfer identically.

Frequently Asked Questions

Can Google Sheets conditional formatting highlight an entire row?

Yes. Set Apply to range to the full row range you want formatted, such as A2:D100, and write a custom formula that checks the appropriate column while allowing the row reference to change. For example, the Late-status rule above checks column D for every row.

Can I use a formula in conditional formatting?

Yes. Select Custom formula is in the Format rules menu and enter a formula that evaluates to TRUE or FALSE. Use references suited to the top-left cell of the applied range, and test the result against a sample row if it does not behave as expected.

Does conditional formatting change the cell value?

No. It changes the appearance only. The text, number, or date remains in the cell, so formatting cannot replace a status value or serve as the data source for a calculation.

Can conditional formatting identify duplicates?

Yes. Apply a custom formula using COUNTIF to the range being checked. A rule that counts each value in the full range and tests whether its count is greater than one highlights every occurrence of a repeated value.

Why does a custom rule work in one row but not another?

Check the applied range and reference anchors. A locked row keeps checking one row everywhere, while a relative row reference advances through the range. Also confirm that the formula’s starting row matches the first row in Apply to range.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.