Google Sheets Conditional Formatting for Full Rows

Yes. In Google Sheets, you can use conditional formatting to highlight an entire row when a cell in that row meets a condition. Select the rows and columns to format, open Format > Conditional formatting, choose Custom formula is, and enter a formula such as =$C2="Overdue" when column C contains each row’s status and the selected range begins on row 2.

What full-row conditional formatting means

Conditional formatting changes how cells look when their contents satisfy a rule. A rule can affect just the cell being checked, or it can format a larger range based on the value of one or more cells in each row. For a full-row effect, the range you apply the rule to must include all the columns you want colored, while the custom formula checks the relevant cell in each row.

For example, a task tracker might have task names in column A, owners in B, status in C, and due dates in D. You could make every cell from A through D turn light red when that row’s status is Overdue. The status cell determines whether the rule is true; the formatting range determines which cells receive the color. These are separate choices, and confusing them is a common reason only one cell changes color.

Formatting an entire row does not mean selecting the sheet’s entire grid. Usually, it is better to apply the rule to the actual table, such as A2:D200, rather than every column and row. A defined range is easier to manage and avoids coloring blank areas of the sheet.

Set up a rule in Google Sheets

In Google Sheets in a web browser, use these steps. The example assumes row 1 contains headings and the data starts in row 2.

Select the table cells you want to format, for example A2:D200. Include all columns that should receive the row color.
Choose Format > Conditional formatting. The conditional-formatting rules panel opens.
Check the Apply to range field. It should contain the full table range, such as A2:D200.
Under Format cells if, choose Custom formula is.
For a table with statuses in column C, where the first data row is row 2, enter =$C2="Overdue".
Choose a fill color or other formatting style, then select Done.

The dollar sign before C keeps the check in the status column as the rule evaluates across the selected range. The row number has no dollar sign, so it adjusts for each row: the second row checks C2, the third checks C3, and so on. If the condition is met, Sheets formats the cells in the apply-to range for that row.

How the custom formula works

A custom formula is evaluated relative to the top-left cell of the apply-to range. When the range begins at A2, a reference to C2 points to the status cell in that same row. As Sheets evaluates the rule on subsequent rows, the row reference changes. This is why the example uses a mixed reference, $C2: the column is fixed, but the row is allowed to move.

References with dollar signs behave differently. $C$2 fixes both the column and row, so every row would be judged by the value in C2. C2 allows both the column and row to shift as the rule is evaluated across the range, which may cause the test to look at different columns. For a rule based on one particular field, fixing the column and leaving the row relative is usually the useful pattern.

The formula’s row number should match the first row in the selected range. If the apply-to range is A5:D100, write the condition using row 5, not row 2. A mismatch can make the formatting appear one or more rows above or below the matching record.

Useful full-row rule examples

Different tables call for different conditions. In each example, the range should cover the columns you want to shade, and the formula should use the first row of that range. Adjust the column letters and row numbers to match your sheet.

Match a status: With data starting in row 2, status in C, and the range A2:F200, use =$C2="Complete" to highlight rows marked Complete.
Flag an overdue date: With due dates in D, the range A2:F200, and blank due dates meant to stay unformatted, use =AND($D2<>"",$D2<TODAY()). The date must be a real date value, not text that only looks like a date.
Highlight high values: With amounts in E and range A2:F200, use =$E2>1000 to format rows where the amount exceeds 1000.
Match either of two labels: With a category in B and range A2:F200, use =OR($B2="Urgent",$B2="High") to format rows in either category.

For the overdue-date example, TODAY returns the current date, so which rows qualify can change as days pass. If you want a fixed cutoff instead, use a date stored in a cell and refer to that cell in the condition. This makes the cutoff easier to update without editing the rule itself.

Choose the right range and reference style

The apply-to range should include the cells that ought to change appearance, not just the cell used to decide whether the rule is true. If column C contains the status but A through F make up the visible record, use A2:F200 as the range and check column C in the formula. If you apply the rule only to C2:C200, only the status cells can be formatted.

Decide whether a rule should cover a fixed number of rows or grow with the table. A range such as A2:F200 is easy to inspect, but data added below row 200 will not be covered unless you extend the range. You can instead use an open-ended range such as A2:F, which covers later rows in those columns. For a very large sheet, a practical bounded range may be easier to maintain and can limit unnecessary formatting work.

For a row-level rule based on one column, keep that column fixed with a dollar sign and leave the row relative. For a condition that deliberately checks a different column in each formatted cell, a different reference pattern may be appropriate. Before filling a table with several rules, test one rule on a few rows and confirm that both the condition and the affected columns match your intent.

Common problems and how to fix them

Only one cell is colored: Expand Apply to range to include every column you want formatted. The formula can still check a single cell.
The wrong row is highlighted: Make the formula’s row number match the first row of the apply-to range. For A2:F100, reference row 2; for A5:F100, reference row 5.
Every row reacts to one status cell: Check that the formula uses a relative row reference such as $C2, not an absolute reference such as $C$2.
The rule never matches: Check spelling, spaces, and data types. A status with a trailing space is not necessarily equal to the same word without it; a date stored as text may not compare as a date.
Blank rows get colored: Add a check that the tested cell is not blank, or narrow the apply-to range to the rows containing data. For example, a status-based rule can also require that column A contains a record identifier.
One rule seems to override another: Review the conditional-formatting rules for overlapping ranges and conditions. Simplify overlapping rules where possible, and inspect their order and formatting choices in the panel.

When troubleshooting, open Format > Conditional formatting with a cell in the affected range selected. Confirm that you are editing the intended rule, then check its range and formula separately. Changing the fill color cannot correct a reference error, and changing the formula cannot make cells outside the apply-to range respond.

Full-row formatting compared with other options

Conditional formatting is useful when the highlight should update automatically as data changes. Typing a new status, changing a date, or updating a number can cause a row to gain or lose its formatting without manually recoloring cells. This makes it a good fit for trackers, review queues, inventory lists, and other tables where the condition is part of the data.

Manual fill color is simpler for a one-time annotation, but it does not automatically follow changing values. Filter views and filters help you display records that meet criteria, but they do not by themselves apply a row color. Alternating colors can make tables easier to scan, though that feature colors rows by position rather than by a value such as status. These tools can be combined when each has a distinct purpose.

In Excel, conditional formatting also supports formula-based rules, but the steps and interface are different. This guide uses Google Sheets’ browser interface and its Format > Conditional formatting panel; Excel ribbon instructions should not be substituted for those steps.

Good practices for maintainable rules

Keep the rule aligned with the structure of the table. Use one clear rule for each meaningful condition, choose colors with enough contrast to read the text, and avoid using color as the only way to communicate an important status. A text label such as Overdue remains understandable when someone prints the sheet or views it with different color settings.

When a sheet has many rules, give the conditions distinct purposes and avoid overlapping rules that assign contradictory formatting. Test the rule against examples you know should match and examples that should not. If your data uses dropdowns, use the exact labels shown in the dropdown options. If the table is shared, make sure collaborators understand what the colors mean and who can edit the rule.

It is also worth checking how new records enter the range. If people routinely append rows, use an apply-to range that includes those rows or agree on a process for extending the rule. A correct formula cannot format records that are outside the rule’s range.

Frequently Asked Questions

Can Google Sheets highlight an entire row based on one cell?
Yes. Apply the rule to all the columns in the row that should be formatted, then use a custom formula that checks the relevant cell in that row. Fix the tested column with a dollar sign and leave the row number relative.

Why does my formula start with a dollar sign?
In a reference such as $C2, the dollar sign fixes the column so the rule always checks column C. The row number remains relative, allowing the rule to check the corresponding status cell on each row.

Can I highlight a row when a cell contains a word?
Yes. For an exact match, compare the cell with the label you expect, such as =$C2="Overdue". For partial text matches, choose a custom formula suited to the data and test it against values that should and should not match.

Can I highlight a row when a date is past due?
Yes. Use a custom formula that compares the row’s due-date cell with TODAY, and add a nonblank check if empty dates should not be highlighted. Confirm that the values are stored as dates rather than plain text.

Does full-row formatting include columns outside my selected range?
No. The rule only formats cells in its apply-to range. If your table spans A through F, include those columns in the range; columns beyond F will not change unless you extend 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.