How to Highlight Overdue Tasks Automatically in Google Sheets

Spot overdue tasks automatically in Google Sheets using conditional formatting. In this step-by-step tutorial, you’ll learn how to highlight an entire task row when its due date has passed—and remove the highlight when the task is marked Done. We’ll build on a simple team task tracker so you can quickly see which unfinished tasks need attention, without checking every deadline manually. In this video, you’ll learn how to: • Highlight overdue task rows automatically • Exclude completed tasks from the highlight • Ignore blank due dates • Test your rule by changing dates and task statuses FOLLOW ALONG Set up your tracker with these columns: A: Task B: Owner C: Due Date D: Status E: Notes 1. Make sure the Due Date column contains actual dates, rather than text. 2. Select your task rows—for example, A2:E100. 3. Go to Format → Conditional formatting. 4. Under “Format cells if,” choose “Custom formula is.” 5. Copy the formula from the pinned comment below. 6. Choose a light red fill and click Done. 7. Test the rule: an unfinished task with a past due date should highlight. Change its status to Done, and the highlight should disappear. The rule checks that a task exists, the due date is valid, the deadline has passed, and the status is not Done. Tasks due today are not considered overdue. IMPORTANT The formula in the pinned comment assumes your selected range starts on row 2, due dates are in column C, and statuses are in column D. Adjust the references if your tracker uses a different layout. Subscribe to Work Smarter with Andrew for practical workflows using Google Workspace, Microsoft 365, and the tools you use every day at work. #GoogleSheets #ConditionalFormatting #TaskManagement