How to Make an Assignment Tracker in Google Sheets

Juggling deadlines and assignments can quickly spiral out of control, leaving you feeling buried and disorganized. Creating a dedicated assignment tracker is one of the best ways to get ahead, and you don't need fancy software to do it. This tutorial will walk you step-by-step through building a powerful and automated assignment tracker right in Google Sheets.

Start with a Clean Slate: Initial Setup

First things first, let's get the basic structure of your tracker in place. This foundational step will create a solid grid to build upon.

1. Create a New Google Sheet

Open Google Sheets and start a new, blank spreadsheet. Give it a clear, simple name like "My Assignment Tracker" or "Fall Semester Assignments 2024".

2. Define Your Core Columns

Click on the cells in the first row (A1, B1, C1, and so on) and type in your column headers. A good starting point includes the essential details you'll need for every task. Here are the columns we'll begin with:

Course: The class or subject this assignment is for. Helps with filtering and organization.
Assignment Name: A clear, descriptive name for the task (e.g., "History Essay - Chapter 5" or "Calculus Problem Set 3").
Due Date: The absolute deadline. Having this in a dedicated column is critical for sorting and our countdown formula later on.
Status: The current state of the assignment. Is it not started, in progress, or finished?
Priority: Helps you decide what to work on next when you have multiple deadlines approaching. We'll use a simple High, Medium, Low system.
Notes: A catch-all column for any extra details, links to resources, or reminders.

Your sheet should now look like a clean, simple table, ready for you to start filling it out with your assignments.

Add Automation and Visual Flair

This is where your simple table transforms into a smart, interactive dashboard. By adding color-coding, dropdown menus, and dynamic formulas, you'll make your tracker easier to use and much more powerful.

Level Up with Status Dropdowns

To avoid typos and keep your status column consistent, a dropdown menu is the perfect tool. Instead of manually typing "In Progress" every time, you can just select it from a list.

Click on the letter for the 'Status' column (e.g., Column D) to select the entire column.
Go to the menu and click Data > Data validation.
In the sidebar that appears, click + Add rule.
Under "Criteria," select the "Dropdown" option.
Enter your status options one by one. Good options to start with are:
- Not Started
- In Progress
- Submitted
- Completed
You can even assign colors to each option directly in this menu to make your tracker more visual. Click the gray circle next to each item to pick a color.
Click "Done." Now, your 'Status' column has clean, clickable dropdowns for every cell.

See What Matters with Conditional Formatting

Conditional formatting may sound complex, but it's simply a way to change a cell's color automatically based on its contents. This is perfect for giving you a quick visual summary of your workload.

Let's set up two rules: one to color-code rows when an assignment is completed, and another to highlight high-priority tasks.

1. Turn Completed Assignments Green

This formatting will strike through an entire row and give it a green background once you mark it "Completed," giving you a satisfying sense of accomplishment.

Select your entire data range, starting from cell A2 and dragging down to the bottom of your last column (e.g., A2:F1000). This ensures the rule applies to all your data now and in the future.
Go to the menu and choose Format > Conditional formatting.
In the sidebar, under "Format rules," choose the "Custom formula is" option from the dropdown menu.
In the value box, type the following formula. Be sure to replace $D2 with the correct column letter for your 'Status' column if it's different:
=$D2="Completed"
Below the formula box, pick your formatting style. For a "completed" look, choose a light green background color and click the strikethrough button.
Click 'Done'. Now, whenever you change an assignment's status to "Completed," the whole row will celebrate with you!

2. Flag High-Priority Tasks in Red

Next, let's create a rule that instantly draws your eye to the most urgent tasks.

With your range still selected, click "Add another rule" in the Conditional formatting sidebar.
Once again, set the format rule to "Custom formula is."
Enter this formula, replacing $E2 with the letter for your 'Priority' column:
=$E2="High"
Set the formatting style to a light red or orange background color.
Click "Done." From now on, any task you flag as "High" priority will stand out immediately.

Know Your Deadlines with a Days Left Countdown

Our final automation touch is an automatic countdown that shows you exactly how many days you have left to finish each task. First, add a new column to your sheet named "Days Left."

Click on the first empty cell of that new column (e.g., G2) and type in the following formula:

=IF(D2="Completed", "", MAX(0, C2-TODAY()))

Press Enter, then click the small blue circle in the bottom corner of the cell and drag it down to apply the formula to the rest of the column. Now let's break down what's happening here:

TODAY(): This super-handy function always returns the current date.
C2-TODAY(): This subtracts today's date from your due date (our C column) to calculate the remaining days.
MAX(0, ...): By wrapping our calculation in MAX, we tell Google Sheets to show 0 if the due date is in the past, avoiding ugly negative numbers.
IF(D2="Completed", "", ...): This is an optional bonus. It checks if the task is done and leaves the countdown cell blank, giving you a cleaner look.

Take It Further: A Mini-Dashboard View

To get an at-a-glance overview of your total workload, you can add a small summary section at the top of your sheet. This gives you key numbers without needing to scroll and count them by hand.

Count Your Tasks Automatically

Insert a couple of empty rows above your column headers. In this new space, you can set up cells to track your progress in different statuses. For example, in cell A1, type "In Progress." Then in the cell next to it (B1), enter this formula:

=COUNTIF(D:D, "In Progress")

This formula scans your entire 'Status' column (D:D) and counts the number of cells that contain the exact text "In Progress." You can repeat this pattern to summarize assignments you have not started or have completed!

Tips for Maintaining Your Tracker

Like any tool, your assignment tracker is only useful if you use it consistently. Here are a few tips to make it a seamless part of your workflow:

Make it a Habit: Pick a time each day - whether it's first thing in the morning or just before you wrap up - to review and update your tracker. Adding tasks as soon as you get them keeps nothing from slipping through the cracks.
Keep It Simple: It’s tempting to add dozens of columns and complicated formulas, but a tracker with 20 columns can quickly become more work to maintain than it's worth. Start with the basics and only add new information as you truly need it.
Personalize it: Is there a certain column that would apply directly to a major group project or internship? This tracker is for your work and nobody else's. Go ahead and customize at will - just so long as that work isn't making the tool a pain to work with.
Bookmark It: Keep your sheet one click away by saving it as a bookmark in your browser. Reducing friction makes it more likely that you’ll keep it up to date.

Final Thoughts

Creating a dynamic, visual, and automated assignment tracker in Google Sheets is entirely achievable. By setting up the right columns and leveraging features like data validation, conditional formatting, and a few simple formulas, you can build a system that keeps you organized and focused on what's due next.

While handling personal tasks in a sheet works wonderfully, business reporting across different marketing and sales platforms often requires compiling data manually. That's why we’ve worked at automating the reporting and analysis process. Instead of downloading CSVs to wrestle with in spreadsheets, you can use Graphed to connect directly to all your data sources - from Google Analytics and Shopify to your CRM - and have an AI agent pull out real-time info and create dashboards using simple, conversational language.