Google Sheets Conditional Formatting: How to Add Rules

To add conditional formatting in Google Sheets, select the cells, choose Format > Conditional formatting, set a condition in the side panel, choose the formatting style, and click Done. The rule then changes how matching cells look, without changing their values.

What conditional formatting does

Conditional formatting applies a visual style when a cell meets a condition. Depending on the rule, Sheets can change a cell’s text color, fill color, or text style, or display a color scale across a range. The formatting updates as the underlying values change, so it can make a live spreadsheet easier to scan.

For example, a task list could highlight overdue dates in red, mark completed items in green, or color high sales figures. The values remain ordinary dates, text, or numbers: formatting does not replace data, sort rows, or calculate a new result. It is a visual cue, not a data-cleaning or validation tool.

Rules can apply to one cell, a continuous range, or selected ranges. They may use a built-in condition, such as “Text contains” or “Date is before,” or a custom formula for logic that the standard choices do not cover. A sheet can have multiple rules, which is useful when a single column or table needs several kinds of highlighting.

Add a basic rule in the browser

These steps are for Google Sheets in a web browser. First select the cells you want the rule to format. You can select a column, a few cells, or a table range; check the range carefully because it determines which cells the rule can affect.

Open the sheet and select the target cells, such as B2:B50.
Choose Format > Conditional formatting. Sheets opens a Conditional format rules panel on the right.
Under Apply to range, confirm or edit the cell range. You can enter a range directly if the selection was not quite right.
Under Format rules, choose a condition, such as Greater than, Text contains, or Date is before. Enter the value or text the condition needs.
Under Formatting style, choose a fill color, text color, bold, italic, or other available style.
Click Done. Change a value in the range to check that the rule behaves as intended.

For instance, to flag expenses above 500 in cells C2:C100, select that range, add a rule using Greater than, enter 500, choose a visible style, and click Done. A value of 501 qualifies; 500 does not, because the condition is strictly greater than. If 500 should also qualify, choose an inclusive condition such as “Greater than or equal to,” where available.

Choose the right rule type

For straightforward checks, a built-in rule is usually easiest to maintain. The available condition choices depend on the type of data and include comparisons for numbers, dates, and text, as well as options such as whether a cell is empty. Color scale rules work differently: they show relative values across a range by blending colors between a minimum, midpoint, and maximum. They are useful for spotting patterns, but they do not mark a value as passing or failing a threshold.

Use a custom formula when the rule needs to test another cell, combine conditions, or format a whole row based on one column. As a practical guide:

Use a built-in condition for a simple threshold, such as numbers above a target.
Use a text condition to call attention to labels such as “Needs review.”
Use a date condition for deadlines or dates in a particular period.
Use a color scale to compare many numeric values at a glance, rather than to communicate a strict cutoff.
Use a custom formula when the condition depends on multiple cells or when a matching row needs highlighting.

Color choices matter too. A subtle fill can keep a large table readable, while a bright color is better reserved for important exceptions. Do not rely on color alone to communicate essential meaning: add a status word or another clear indicator where appropriate, especially if the sheet will be printed or shared with people who may not distinguish the colors easily.

Use custom formulas for rows and dates

Custom formulas let a rule evaluate data beyond the individual cell being formatted. The key is to set the apply-to range and references so they line up. In a custom formula, a relative row reference changes as the rule evaluates each row, while a dollar sign can lock a column or row. This is what lets one formula check a status column while formatting several columns in the same row.

Suppose task statuses are in B2:B100 and the full task table is A2:D100. To highlight each row whose status is Overdue, apply the rule to A2:D100, choose Custom formula is, and enter =$B2="Overdue". The dollar sign fixes the status column at B, while the row number remains relative. As Sheets checks row 3, for example, it evaluates B3. If you apply the same formula to only B2:B100, only the status cells—not the whole row—will be highlighted.

For a date example, assume due dates are in C2:C100 and you want to flag past dates while leaving blank cells alone. Apply the rule to C2:C100 and use =AND($C2<>"",$C2<TODAY()). The blank check prevents empty cells from being treated as overdue. TODAY() returns the current date, so the condition moves forward as time passes. If the sheet uses a different column or starting row, adjust the references to match it.

To find repeated entries, suppose names occupy A2:A100. Apply a custom formula to A2:A100: =COUNTIF($A$2:$A,$A2)>1. It highlights a name when it appears more than once in the checked column. If every repeated instance should be marked, this formula does that; it does not identify which occurrence came first. A header should stay outside the apply-to range so it is not counted or highlighted as a record.

Manage rules and overlapping conditions

To edit a rule, open Format > Conditional formatting and select its entry in the rules panel. Change the range, condition, or style, then click Done. To remove a rule, open the same panel, point to the rule, and use the remove or trash control shown for it. Removing conditional formatting does not delete cell contents.

When several rules cover the same cells, they can compete to set visual properties. Review the rules in the panel and arrange or simplify them if the final appearance is confusing. For example, one rule may set a red fill for overdue items while another sets a green fill for completed rows. Decide which condition should take precedence, or make the conditions mutually exclusive—for example, ensure the overdue rule excludes completed tasks. This makes the intended result easier to understand than relying on layered styles.

Use ranges that reflect how the sheet will grow. A rule applied only to A2:D20 will not automatically cover new rows added below row 20 in every situation. If appropriate, apply it to a larger range such as A2:D500, or expand the range when adding records. Larger ranges can be convenient, but avoid entire-column formulas or unnecessarily broad ranges when a smaller range is sufficient; simpler rules are easier to inspect and maintain.

Common problems and how to fix them

If a rule does not appear to work, start by checking the apply-to range and the exact condition. A rule can be correct but attached to the wrong cells, or a text check can fail because the value has an unexpected space or spelling. For a numeric comparison, confirm that the values are numbers rather than text that only looks like numbers. For date rules, confirm that the cells contain dates Sheets recognizes, not date-looking text.

The wrong row is highlighted: Check the custom formula’s row number against the first row in the apply-to range. If the range begins at row 2, a row-based reference will usually begin with 2 as well.
The whole range uses one cell’s result: Check whether a dollar sign has locked a reference that should change by row. For row highlighting based on column B, $B2 locks the column but lets the row change.
Nothing matches: Recheck the comparison operator, spelling, spaces, and data type. A “Greater than 500” rule will not match a cell containing the text “500.”
Blank dates turn red: Add a blank check to the custom formula, or choose a built-in date condition that fits the data.
The color looks inconsistent: Look for another rule on the same cells and inspect its condition and ordering. A color scale on a range can also make individual values look different from a simple pass/fail rule.

A useful troubleshooting method is to test one known matching value and one known nonmatching value. For a threshold of 500, try 501 and 499 in a spare cell within the applied range. For a text rule, enter the exact target word. Testing both sides of a condition helps reveal whether the issue is the rule, the range, or the source data.

Google Sheets versus Excel

Google Sheets and Microsoft Excel both offer conditional formatting for values, text, dates, and formula-driven conditions, but their interfaces and some rule details differ. In Google Sheets in a browser, use Format > Conditional formatting and configure the rule in the side panel. In Excel for Microsoft 365 on Windows, the usual path is Home > Conditional Formatting, with rule choices displayed in ribbon menus and dialog boxes.

Do not assume that every rule or formula will transfer identically between the products. A straightforward threshold is generally easy to recreate, but custom formulas, range references, color scales, and rule priority should be reviewed after copying or importing a workbook. If a file will be used in both products, test representative rows in each one and confirm that the intended cells receive the intended formatting. Conditional formatting is not a substitute for checking the underlying data after conversion.

Frequently Asked Questions

Can I highlight an entire row based on one cell?

Yes. Select the full table range, such as A2:D100, and use a custom formula that locks the column containing the condition but leaves the row relative. If statuses are in B and the target text is Overdue, use =$B2="Overdue". Make sure the formula’s row number matches the first row of the selected range.

Can conditional formatting change the value in a cell?

No. Conditional formatting changes how a cell appears; it does not replace its contents or write a result elsewhere. To calculate or change values, use a spreadsheet formula, data-cleaning step, or another appropriate feature. A visual rule can still help you notice which values need attention.

How do I remove conditional formatting without deleting data?

Open Format > Conditional formatting, select the rule in the panel, and remove it using the control provided for that rule. This removes the formatting rule, not the values in the cells. If only part of the range should stop using a rule, edit the apply-to range instead.

Why does my conditional formatting formula use dollar signs?

Dollar signs create absolute references. In $B2, the column stays fixed at B while the row changes as the rule is evaluated down the range. In B$2, the row is fixed while the column can change. Choose the reference pattern according to how the apply-to range is laid out.

Do conditional formatting rules update automatically?

Yes, rules are reevaluated when relevant cell values change, and a rule using TODAY() depends on the current date. The appearance updates without manually applying the formatting again. If a result seems stale or unexpected, inspect the rule’s range, formula, and source data before recreating it.

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.