Google Sheets Conditional Formatting Range: How to Set It

In Google Sheets, a conditional formatting range is the cell or group of cells a rule is allowed to format. Set it in the rule’s Apply to range field, such as A2:D100, then choose a condition or custom formula to control which of those cells change appearance.

What the conditional formatting range means

A conditional formatting rule has two separate parts: the range it can affect and the condition that decides when the formatting appears. For example, a rule might apply to C2:C50 and highlight cells whose values are less than 10. The range sets the rule’s reach; the condition tests the values.

The range does not usually decide which cells are checked by the condition. A rule can format an entire row based on a value in one column, provided the range and formula references are set up appropriately. Conversely, selecting a range does not automatically create a rule for every cell in the sheet: cells outside the range are not covered by that rule.

Ranges are written using column letters and row numbers. A2:A20 means cells A2 through A20, while A2:D20 covers four columns across those rows. A full-column range such as A:A refers to column A. In most cases, using a bounded range that includes the data you need makes the rule easier to understand and manage.

How to set or change the range

In Google Sheets in a web browser, select a cell in the relevant data or select the cells you want, then choose Format > Conditional formatting. The conditional formatting rules panel opens at the right side of the sheet. In Apply to range, check or enter the cells the rule should cover. Then set the condition and formatting style, and select Done.

Select the intended cells first if that is convenient. For example, select B2:B100 to format values in that part of column B.
Choose Format > Conditional formatting.
Review the Apply to range field. Replace its contents if the suggested range is too small, too large, or starts on the wrong row.
Choose a condition under Format cells if, or select the custom formula option for a rule based on a formula.
Choose a formatting style and click Done. Reopen the rule later if you need to adjust its range or condition.

You can also open the panel first and then use its range selector to choose cells on the grid. When editing a rule, changing Apply to range changes where that rule can act; it does not create a new rule or change the condition itself. If you want the same appearance under a different condition, add another rule rather than assuming the range field controls the test.

How to choose the right range

Start with the cells that should visibly receive the formatting, not merely the cells that contain the value being tested. If only the status cells should change color, apply the rule to the status column. If an entire record row should be highlighted when its status is overdue, apply the rule across the table’s columns and use a custom formula that checks the status column.

One column of values: use a range such as C2:C200 when only those cells need formatting.
A table with a header: begin on the first data row, for example A2:E200, so the header is not formatted as though it were a record.
Whole rows in a table: select all columns that make up each row, such as A2:E200, then make the formula check a relevant column.
New entries added over time: include a reasonable number of future rows, or use a full-column range if applying the rule to extra blank cells is acceptable.

A range that is too short leaves later records unformatted. A range that is unnecessarily broad can make the rule harder to inspect and may cause blank cells to receive formatting when the condition treats blanks as matching. When choosing between a fixed range and a whole-column range, consider how the sheet is maintained: a fixed range is explicit, while a whole-column range automatically covers later rows.

Using custom formulas with a range

Custom formulas are useful when the appearance depends on a different cell from the one being formatted. In Google Sheets, choose Custom formula is in the conditional formatting panel. A key detail is that the formula is interpreted relative to the top-left cell of the selected range. Use dollar signs to lock a column or row when you need references to stay fixed as the rule evaluates other cells.

For example, with status values in D2:D100 and the table occupying A2:D100, apply the rule to A2:D100. The custom formula =$D2="Overdue" checks column D for each row and formats the corresponding cells across that row. The dollar sign before D keeps the status check in column D, while the row number remains relative so row 3 checks D3, row 4 checks D4, and so on.

For a date-based example, with due dates in B2:B100 and records in A2:E100, apply the rule to A2:E100. The formula =AND($B2<>"",$B2<TODAY()) formats a row when its due date is not blank and falls before today. The blank check matters because otherwise empty cells can sometimes be treated as dates in comparisons. The formula does not change the dates; it only determines whether the rule’s formatting is shown.

Common reference patterns include A2 for a relative column and row, $A2 for a fixed column with a changing row, A$2 for a changing column with a fixed row, and $A$2 for a fully fixed cell. Match the reference pattern to the range’s first cell. If a custom rule seems to shift to the wrong column or row, check its formula against the top-left cell of Apply to range.

Examples for common sheet layouts

For a simple threshold, suppose quantities are in C2:C40 and you want values below 5 highlighted. Set Apply to range to C2:C40, choose the less-than condition, and enter 5. This is simpler than a custom formula because the condition directly tests each cell in the selected range.

For duplicate-looking records or category-based highlighting, apply the rule to just the category cells if the goal is to call attention to those entries. If the whole row should stand out, expand the range across the row and write the custom formula so it tests the category cell in that row. The formatting range and the formula’s test reference can therefore be different, as in the overdue-status example.

For a tracker that receives new rows, a range such as A2:E500 gives room for additions without applying a rule to every row in the sheet. If the sheet regularly grows beyond that limit, update the range or choose a whole-column range such as A2:E, which starts at row 2 and extends downward. Check how blank rows look before adopting an open-ended range, especially when the rule uses comparisons or formulas.

Common range mistakes and how to fix them

If only part of a table changes appearance, the first thing to inspect is the range. Open Format > Conditional formatting, select the relevant rule in the panel, and compare Apply to range with the actual data area. A rule set to A2:A20 will not format cells in column B or rows after 20, even if its condition is otherwise correct.

The header is highlighted: move the range’s first row below the header, or revise the rule if the header should be treated separately.
Only the first row behaves correctly: check whether a custom formula uses a fixed row reference where a relative row reference is needed.
The wrong column controls the formatting: lock the intended test column with a dollar sign, such as $D2, while allowing the row number to adjust.
Blank rows receive a color: add a condition that excludes blanks, or narrow the range to the rows that contain data.
New rows have no formatting: extend the range or use an open-ended range that includes future entries.

Also check for multiple rules covering the same cells. A cell may be within several ranges at once, and overlapping rules can make the visible result confusing when their styles differ. Review the list of rules in the panel, remove obsolete rules, and arrange or adjust overlapping rules so their purpose is clear. Changing the range of one rule will not automatically update every other rule on the sheet.

Google Sheets versus Excel ranges

The basic idea is shared by Google Sheets and Microsoft Excel: a conditional formatting rule applies to a specified set of cells and evaluates a condition. However, the menus and rule editors are different. In Sheets, use Format > Conditional formatting and edit Apply to range. In Excel for Microsoft 365 on Windows, conditional formatting is managed from Home > Conditional Formatting; its dialog and range controls are not the Google Sheets side panel.

Do not assume that instructions for one program’s dialog labels or rule-management controls will match the other. A custom formula can also need reference adjustments if you move the rule between different starting ranges. The safest approach is to check the destination range’s top-left cell, then verify the formula references and test the rule on a few representative rows.

Frequently Asked Questions

How do I apply conditional formatting to an entire column in Google Sheets?
Open Format > Conditional formatting and enter a range such as C:C in Apply to range. If you want to exclude the header, use a range beginning at row 2, such as C2:C.

Can one conditional formatting rule cover multiple columns?
Yes. Enter a rectangular range such as A2:D100 to cover four columns across the selected rows. For a custom formula, make sure its references are appropriate for the range’s top-left cell and for the values you intend to test.

Why is my conditional formatting not applying to new rows?
The new rows may be below the rule’s current Apply to range limit. Edit the rule and extend its range, or choose an open-ended range such as A2:D if the rule should cover future rows and applying it to blank rows will not cause unwanted formatting.

Can I format a whole row based on one cell?
Yes. Apply the rule to the full row area, such as A2:D100, and use a custom formula that checks the relevant column for each row. For example, if column D contains the status, =$D2="Overdue" checks that status while formatting across the row.

Does changing the range change the conditional formatting condition?
No. The range controls which cells the rule can format. The condition or custom formula controls when the formatting appears. Edit both parts if you need to change both where the rule applies and what it responds to.

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.