Google Sheets Conditional Formatting for Entire Rows

To highlight an entire row in Google Sheets, select the rows’ full data range, open Format > Conditional formatting, choose Custom formula is, and enter a formula that checks a cell in each row—for example, =$C2="Overdue". Set the formatting style and click Done; the dollar sign keeps the check in column C while the row number adjusts for each row.

What row-based conditional formatting does

Conditional formatting changes a cell’s appearance when its contents meet a rule. A typical rule highlights one cell—for example, a status cell containing “Overdue.” A row-based rule uses that status cell to format several cells in the same record, such as the customer name, due date, and amount alongside it. The values do not move or change; only the display changes.

The key is to apply the rule to every cell you want colored, then write a formula that evaluates the relevant row. If your table occupies columns A through F and the status is in C, apply the rule to A2:F100 and make the formula refer to C2. Sheets checks that formula for each row in the selected range. It effectively tests C2 for the first row, C3 for the next, and so on.

This approach is useful for task trackers, inventory lists, attendance sheets, project plans, and sales records. It also helps readers scan a table: a complete row can signal a late task or a particular category without requiring someone to inspect a single status cell at a time.

Set up a rule that highlights a full row

Select the data cells you want formatted. For a table in A2:F100, select that range; leave row 1 out if it contains headings.
Choose Format > Conditional formatting. The conditional-formatting rules panel opens.
Under “Apply to range,” confirm or edit the range, such as A2:F100.
Open “Format cells if” and choose Custom formula is.
Enter the formula for the condition, choose a fill color or other formatting, and click Done.

For a table whose status is in C, with records beginning on row 2 and the formatting range A2:F100, use =$C2="Overdue". The formula checks whether the status in column C is exactly “Overdue.” If true, Sheets formats the corresponding cells from A to F in that row. Text comparisons are not case-sensitive in the usual way Sheets evaluates equality, but spelling and extra spaces can still prevent a match.

To add another condition, use Add another rule in the panel. Each rule has its own range, formula or built-in condition, and appearance. Choose a fill color with enough contrast to keep the text readable.

Why the dollar signs and row number matter

Custom formulas in conditional formatting use ordinary spreadsheet references, interpreted relative to the top-left cell of the apply range. In =$C2="Overdue", $C locks the test to column C, while the unanchored row number can change as Sheets evaluates each row. The equals sign at the start tells Sheets to evaluate a formula. The quoted word is text to match, not a cell reference.

If the apply range starts at A2, the formula’s row reference should normally start at 2. If the apply range starts at A5, write the row reference as 5 instead. A mismatch can make the rule appear shifted: for instance, formatting row 3 based on the status in row 2. The test column also needs to match the actual location of the value. If the status is in D, use $D2 for a range beginning on row 2.

Locking the column is especially important when formatting multiple columns. Without the dollar sign, a formula such as =C2="Overdue" can shift its column reference as Sheets evaluates cells across the range. The result may be that some cells in a row check different columns. Locking the column tells each cell in the formatted row to use the same status field.

Useful row-highlighting examples

The same pattern works for many kinds of rules. In each example, first select the range to format and adjust the referenced column and starting row to match your sheet.

Highlight rows with a particular status: With status values in C2:C100 and a formatting range of A2:F100, use =$C2="Complete". This colors the full record when its status is Complete.
Highlight rows above a numeric threshold: With inventory quantities in D2:D100 and a formatting range of A2:E100, use =$D2<10. It highlights quantities below 10. Use a different number if your reorder threshold differs.
Highlight overdue, unfinished work: With due dates in D, status in E, and a formatting range of A2:E100, use =AND($D2<>"",$D2<TODAY(),$E2<>"Complete"). It excludes blank dates and completed tasks, and flags dates earlier than today.
Highlight rows with a checkbox selected: With checkboxes in column F and a formatting range of A2:F100, use =$F2=TRUE. A checked Google Sheets checkbox evaluates as TRUE by default. If the checkbox uses custom checked or unchecked values, adjust the test to match those values.

For the overdue example, dates must be stored as dates rather than text that merely looks like a date. TODAY() updates as the date changes, so a row can become overdue without anyone editing it. A blank check matters because Sheets may treat an empty cell differently from a real date comparison.

Choose a built-in rule or a custom formula

Built-in conditions are convenient when the condition applies directly to the selected cells. For example, a number rule can format cells greater than a threshold, and a text rule can find cells containing a word. They are a good choice when you only need to color the value cell itself.

Use Custom formula is when one cell controls the formatting of other cells in the row, or when the condition combines multiple fields. A custom formula can also test whether a date is before today or whether a status and date meet conditions together. It returns a logical result—true or false—for each row. You do not need to put the formula into the worksheet itself.

Color scale rules serve a different purpose: they visualize relative values with a color gradient. They are not a substitute for a rule that colors a whole record based on one status field. If the goal is to make a complete row stand out, use a custom formula and apply it to the row’s full range.

Keep the rule aligned as your data changes

Choose an apply range that matches the table’s real structure. If you set a rule to A2:F100, new records below row 100 will not inherit it automatically. For a table expected to grow, you can use a larger range such as A2:F, provided the formula and layout suit the extra rows. An open-ended range can be convenient, but applying formatting to thousands of unused cells may make a sheet harder to manage.

When you insert or move columns, verify that the formula still checks the intended field. For example, if the status column moves from C to D, the formula must be updated to reference D. Sorting the table generally moves row contents together, but it is still wise to inspect the rule’s apply range and formula after major layout changes. Avoid including heading rows unless you specifically want the rule to evaluate them.

For a second table on the same sheet, create a separate rule with its own range and starting row. A formula written for a range beginning on row 2 may not line up with a different table beginning on row 20 unless its row reference is changed accordingly.

Common problems and how to fix them

Only one cell changes color: The apply range may contain only the status column. Edit “Apply to range” so it includes every column in the row you want formatted.
The wrong row is highlighted: Check that the formula’s row number matches the top row of the apply range. For A2:F100, the row reference should start at 2.
Some columns behave differently: Lock the condition column with a dollar sign, as in $C2. A reference without a locked column can shift across the formatted range.
No rows match: Confirm that the formula uses the correct column, the text is spelled as it appears in the data, and the rule type is Custom formula is. Check for leading or trailing spaces and confirm that dates are genuine date values.
More than one rule affects a row: Review the rules in the conditional-formatting panel. Overlapping rules can make the final appearance confusing, especially when they apply different fills or text styles. Simplify the conditions or adjust the ranges so the intended formatting is clear.
Colors seem missing after copying data: Pasting cells can bring formatting along or replace formatting, depending on the paste action. Reopen the rules panel and check that the rule still exists and covers the pasted rows.

For debugging, temporarily choose an obvious fill color and test one known row. Check the condition cell and the rule’s range before changing the formula. This separates a range problem from a formula problem and is usually faster than rebuilding the entire rule.

Google Sheets versus Excel

The basic method is similar in Google Sheets and Microsoft Excel: select a range, create a conditional-formatting rule, and use a formula that refers to the relevant row. The interface and some details differ. In Google Sheets in a web browser, use Format > Conditional formatting and select Custom formula is. In Excel for Microsoft 365 on Windows, the route is Home > Conditional Formatting > New Rule, followed by the option to use a formula to determine which cells to format.

Do not assume that a rule copied between spreadsheet programs will behave identically in every case. Check its range, formula references, and supported condition types after importing or exporting a workbook. For a straightforward row test such as a status in one column, the relative-row and fixed-column reference pattern is broadly familiar in both programs.

Frequently Asked Questions

Can Google Sheets highlight an entire row based on one cell?
Yes. Apply the rule to the full row range, choose Custom formula is, and write a formula that checks the condition cell in that row. Lock the condition column if the rule spans several columns.

How do I highlight a row when a cell contains a word?
For an exact status such as “Review,” use an equality test like =$C2="Review", adjusting the column and row to match your table. If you need to match part of a cell’s text rather than its full value, create a custom formula using a text-search approach that fits your data.

Can I highlight a row based on a date?
Yes. A custom formula can compare the date column with TODAY(), for example to identify past due dates. Make sure the cells contain date values, decide how blank dates should be handled, and exclude completed items if appropriate.

Why does my conditional format start on the wrong row?
The formula’s row number should correspond to the first row of the apply range. If the range begins at row 2 but the formula starts with row 1, the test may be offset. Check both settings in the conditional-formatting panel.

Will new rows receive the formatting automatically?
Only if they fall within the rule’s apply range. A range ending at row 100 does not cover row 101. Extend the range or choose a suitably open-ended range if the table grows regularly, then confirm that the formula’s starting row still matches.

Practical takeaway

For most row-highlighting tasks, the essential setup is simple: select the full record range, use Format > Conditional formatting, choose Custom formula is, and write a row-relative test with the condition column fixed. For a status in C and records beginning on row 2, =$C2="Overdue" is the basic pattern. Match the range, formula, and real layout of your sheet, then verify the result on a known row before relying on it across a large table.

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.