Google Sheets Multiple Conditional Formatting Rules

How-to basics · Updated November 21, 2026

Yes. Google Sheets lets you apply multiple conditional formatting rules to the same cells or range: open Format > Conditional formatting, add a rule, then choose Add another rule for each additional condition. Each rule can use a built-in condition or a custom formula, so a range can respond to several different values or situations.

What multiple conditional formatting rules do

Conditional formatting changes how cells look when their contents meet criteria you define. A rule might color overdue dates red, highlight unusually large expenses, or mark a row as complete when its status says “Done.” Multiple rules let one range respond to more than one condition without changing the underlying data.

For example, a task tracker might use a red fill for overdue tasks and a green fill for completed tasks. A single Status or Due date column can be checked by separate rules, each with its own condition and style. You can also apply rules across a wider range so an entire row is highlighted based on one cell in that row.

It helps to distinguish multiple rules from multiple conditions within one rule. Separate rules make sense when each condition needs a different style. A custom formula using AND or OR is useful when one style should appear only when a combination of conditions is true. For example, you could highlight a task only when it is overdue and not complete.

How to add more than one rule

In Google Sheets in a web browser, select the cells you want to format and choose Format > Conditional formatting. The conditional formatting rules sidebar opens. The Apply to range field shows which cells the rule affects; check it before setting a condition, especially if you selected a large area or nonadjacent cells.

  1. In the sidebar, choose a condition under Format cells if, such as Text is exactly, Date is before, or Greater than.
  2. Enter the value or date if the selected condition asks for one, then choose the fill color, text color, or other available style.
  3. Select Done to save the rule.
  4. To add another condition, select Add another rule in the sidebar. Set its range, condition, and style, then select Done.

You can repeat these steps for as many distinct cases as your sheet needs. To edit an existing rule, open the sidebar again and select that rule. To remove it, open the rule and use the remove or trash control. Changes to a rule affect its formatting wherever the rule applies, so confirm the range before saving.

Choosing built-in conditions or custom formulas

Built-in conditions are the quickest choice when the test is straightforward. They cover common comparisons, text matching, dates, blank or nonblank cells, and values within a range. For instance, selecting Text contains and entering “Review” can highlight cells whose text includes that word. These options are often easier to maintain than a formula when the condition is simple.

Choose Custom formula is when you need a test that the built-in menu cannot express, or when a condition in one column should format cells elsewhere. The formula must evaluate to TRUE or FALSE for each cell in the applied range. References should be written for the top-left cell of that range, and dollar signs control which parts of the reference move as the rule is applied.

Suppose task statuses are in column C, with the first task on row 2, and you want to color the status cell when it says “Overdue.” Apply the rule to C2:C100 and use =$C2="Overdue". The column is fixed with $C, while the row number can change, so each row is checked against its own status.

To color a whole task row when its status is overdue, apply the same formula to A2:E100. The formula still checks column C, while the relative row reference changes from row to row. A common mistake is to use =$C$2="Overdue" for the whole range: that locks both the column and row, causing every row to depend on the value in C2.

Combining conditions in one custom formula

Use AND when every condition must be true, and OR when any one of several conditions is enough. For a task list with due dates in B and status in C, apply this formula to A2:E100 to highlight rows whose due date has passed and whose status is not “Done”: =AND($B2<TODAY(),$C2<>"Done",$B2<>""). The final test avoids treating blank due-date cells as overdue.

For duplicate values, suppose names are in A2:A100 and you want every repeated name highlighted. Apply the rule to A2:A100 with =COUNTIF($A$2:$A$100,A2)>1. The fixed search range stays the same for each cell, while A2 shifts to check the current value. If blank cells should not be highlighted as duplicates, use =AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1).

These examples show why careful range selection matters. A formula can be correct but appear wrong if the apply-to range starts on a different row than the formula assumes. If your range begins at row 5, adjust the formula’s relative row reference to 5 as well.

Rule order and overlapping styles

Several rules can be true for the same cell at once. This can happen when rules cover the same range, when a broad condition overlaps a more specific one, or when a formula is true in more than one situation. The order of rules in the conditional formatting sidebar matters when rules compete over the same formatting. Put the rule you want to take priority higher in the list, and check the displayed order after adding or editing rules.

Design rules to avoid unnecessary conflicts. If a cell could be both “high priority” and “overdue,” decide which signal should be most visible. You might give the higher-priority rule a distinctive text color and the overdue rule a fill, rather than assigning two competing fill colors. Alternatively, combine the tests into one formula if only a single outcome should be formatted.

Do not assume that a rule’s position changes the data or prevents other tests from being evaluated. Conditional formatting is a display layer: it changes visual presentation, not cell contents, sorting, or calculations. If a cell’s appearance is confusing, inspect all rules that include it, their order, and their formatting choices.

Useful examples for common sheets

  • Flag late invoices: If due dates are in B2:B200, apply a date-based rule for dates before today. For a more controlled test that ignores blank cells and paid invoices in column C, apply a custom formula to A2:C200: =AND($B2<TODAY(),$B2<>"",$C2<>"Paid").
  • Mark low inventory: If quantities are in D2:D100, use a built-in “Less than” condition with the reorder threshold, or use a custom formula such as =$D2<10 when the threshold is ten units.
  • Highlight missing information: To flag blank email cells in column B, apply a built-in “Is empty” condition to B2:B100. If the entire row should be highlighted when the email is blank, apply a custom formula to A2:D100: =$B2="".
  • Show a numeric gradient: Use a color scale when the goal is to show relative low-to-high values, such as sales or test scores. Use separate rules for distinct categories, such as below target and at or above target. A gradient communicates differences between numbers; it is not the same as a set of discrete conditions.

Common problems and how to fix them

If nothing changes, first verify that the value really satisfies the condition. A date that looks like a date may be stored as text, and a number stored as text may not behave like a numeric value. Check the cell’s actual contents, the chosen condition, and the apply-to range. Also confirm that the rule is enabled and that the chosen format is visually noticeable against the existing cell style.

If the wrong rows are highlighted, check relative and absolute references. A reference such as $C2 locks the column but lets the row vary; C$2 locks the row but lets the column vary; and $C$2 locks both. In a formula rule, the first relative reference should correspond to the first cell in the applied range. This is the most frequent source of off-by-one results.

If the expected color does not appear, another rule may apply to the same cells, or a later edit may have changed the range. Open Format > Conditional formatting and review every listed rule, not just the one you remember creating. Remove obsolete rules, narrow ranges where appropriate, and use distinctive styles to make competing rules easier to identify.

Lastly, remember that a custom formula needs to return a logical result. A comparison such as =$D2<10 produces TRUE or FALSE. If a formula contains a typo, mismatched quotation marks, or references the wrong cells, Sheets may reject it or format cells unexpectedly. Test a simple case first, then expand the apply-to range.

Google Sheets compared with Excel

Google Sheets and Microsoft Excel both support multiple conditional formatting rules, built-in criteria, and formula-based rules. The ideas of applying a range and controlling relative references are similar, but the menus and some rule-management details differ. This article’s steps use Google Sheets in a browser, where the path is Format > Conditional formatting; Excel’s conditional formatting commands are on the Home tab.

Because the programs can interpret or present formatting rules differently, do not assume a workbook imported from Excel will look exactly the same in Sheets, or vice versa. After importing or exporting a file, review the rules and their ranges in the destination program. If a complex rule is essential, confirm that its formula and appearance are preserved before relying on the sheet as a report.

Frequently Asked Questions

Can Google Sheets apply two conditional formatting rules to the same cell?
Yes. Add separate rules whose apply-to ranges include that cell. If both conditions are true, the rules may compete over shared formatting choices, so review their order and styles in the conditional formatting sidebar.

Can one conditional formatting rule have multiple conditions?
Yes. Select Custom formula is and combine tests with functions such as AND or OR. Use one rule when a combined condition should produce one style; use separate rules when different conditions need different styles.

How do I highlight an entire row based on one cell?
Set the apply-to range to the rows and columns you want highlighted, then write a custom formula that locks the condition’s column but leaves the row relative. For example, for statuses in C and a range of A2:E100, use =$C2="Done".

Can conditional formatting change the value in a cell?
No. It changes the cell’s appearance, not its contents. Use a formula, data validation, or another editing method if you need to calculate or change values.

Why does my formula highlight the wrong row?
The formula’s relative row reference may not match the first row of the apply-to range, or a reference may be locked with dollar signs when it should move. Check the range’s starting cell and adjust the formula references accordingly.

Related

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.