Google Sheets Highlight Row by Date: Easy Steps

Updated 6 November 2026 · Standard formula, shown below

To highlight an entire row in Google Sheets based on a date, you use a custom formula in the conditional formatting rules. This formula checks the date in a specific column and, when the condition is true, applies the formatting to every cell in that row. The process takes about a minute once you know the formula pattern.

Why Use Conditional Formatting for Dates

Conditional formatting is the only built-in Google Sheets feature that changes cell or row appearance based on a value. Unlike manually coloring cells, it updates automatically when dates change or new rows are added. This makes it ideal for tracking deadlines, project milestones, birthdays, or any schedule where you want a visual cue.

The key to highlighting a whole row, rather than just one cell, is the use of an absolute column reference in the formula. For example, if your dates are in column B, you write =$B2>=TODAY() instead of =B2>=TODAY(). The dollar sign locks the column, so the rule evaluates the date in column B for each row, but the formatting applies across all columns in that row.

Step-by-Step: Highlight a Row by Date

Select the range of rows you want to format, for example A2:F100. Start at row 2 if you have a header in row 1.
Click Format in the top menu, then Conditional formatting.
Under “Apply to range,� confirm your selected range appears.
Under “Format rules,� change the dropdown from “Format cells if…� to Custom formula is.
Type your formula into the box. Use the cell reference of the first row in your selected range.
Click the paint roller icon to choose a fill color, then click Done.

For example, to highlight rows where the date in column B is today or later, use:

=$B2>=TODAY()

This highlights any row whose date is today or in the future. To highlight only today’s rows, use =$B2=TODAY(). To highlight past dates, use =$B2<TODAY().

Choosing the Right Date Formula

The formula you choose depends on what “based on date� means for your use case. Here are the most common patterns:

Exact date match: =$B2=DATE(2025,6,15) – highlights rows with June 15, 2025.
Today only: =$B2=TODAY() – highlights rows where the date equals the current date.
Upcoming within 7 days: =AND($B2>=TODAY(), $B2<=TODAY()+7) – highlights rows with dates from today through a week from now.
Past due: =AND($B2<TODAY(), $B2<>"") – highlights rows with a date before today, ignoring blanks.
Specific month: =MONTH($B2)=6 – highlights all rows in June, regardless of year.
Specific year: =YEAR($B2)=2025 – highlights all rows in 2025.

For date ranges, the AND function is essential. It lets you combine two conditions, such as a start and end date.

Common Mistakes and How to Fix Them

The most frequent error is forgetting the dollar sign before the column letter. Without it, the rule still works, but it may highlight incorrect rows when you copy the formatting to other cells. Always use $B2 (absolute column, relative row) in the formula.

Another issue is applying the rule to the wrong range. If your data starts in row 2, your formula must reference row 2. If you select A2:F100 but type =$B1>TODAY(), the rule will be offset by one row and highlight the wrong rows.

Blank cells can also cause problems. If a cell is empty, =$B2<TODAY() treats the blank as 0, which is less than today, so it highlights empty rows. Add AND($B2<>"", ...) to exclude blanks.

Finally, dates stored as text won’t work with these formulas. If your dates are left-aligned in the cell, they are likely text. Use Data > Split text to columns or the DATEVALUE function to convert them, or re-enter them as proper dates.

Highlighting Rows Based on a Date in Another Sheet

Sometimes the date you need to check is in a different sheet. You can reference it directly in the formula. For example, if your dates are in a sheet named “Data� in column B, use:

=Data!$B2=TODAY()

Make sure the sheet name has no spaces, or wrap it in single quotes: ='Data Sheet'!$B2. The row number must match the row in your selected range. If you select A2:F100 on the current sheet, the formula still uses row 2 from the other sheet.

Performance and Limitations

Conditional formatting with custom formulas works well on sheets up to a few thousand rows. Beyond that, the sheet may slow down because each row recalculates the formula. To improve performance, limit the “Apply to range� to only the rows you actually need, rather than the entire column (e.g., A2:F1000 instead of A:F).

Also note that conditional formatting is purely visual. It does not filter, sort, or move data. If you need to actually isolate rows by date, use a filter view or a QUERY function instead.

Frequently Asked Questions

How do I highlight a row if the date is within the current week?

Use =AND($B2>=TODAY()-WEEKDAY(TODAY(),2)+1, $B2<=TODAY()-WEEKDAY(TODAY(),2)+7) to get Monday-to-Sunday of the current week. If your week starts on Sunday, change the second argument of WEEKDAY to 1.

Can I highlight a row based on a date that is in a dropdown list?

Yes. The formula works the same way. The cell contains a date value, and the conditional formatting checks it. Just ensure the dropdown options are actual dates, not text.

Why is my formula not highlighting any rows?

Check that your dates are real dates (right-aligned) and not text. Also verify the range in “Apply to range� matches the row numbers in your formula. If you selected A2:F100, your formula must reference row 2, not row 1.

How do I highlight an entire row when a checkbox is checked?

Use =$A2=TRUE if the checkbox is in column A. The principle is the same as a date – any true/false condition works.

Can I use multiple date conditions on the same row?

Yes. Add multiple conditional formatting rules. For example, one rule for “past due� in red and another for “due today� in yellow. The first rule in the list takes priority if both apply, so order them accordingly.

Does this work on the mobile app?

Conditional formatting rules are applied on the web, but the highlighting displays on the mobile app. You cannot create or edit rules in the Android or iOS apps, only view them.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.