How to Create a Work in Progress Report in Google Sheets

A Work in Progress (WIP) report sounds boring, but it's one of the most powerful tools a team can use to see exactly what’s happening, who’s overworked, and where a project is about to go off the rails. This guide will walk you through building a dynamic and genuinely useful WIP report from scratch using only Google Sheets. We’ll cover everything from structuring your data to adding automatic color-coding and a summary dashboard that actually gives you insight.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

What Exactly is a Work in Progress (WIP) Report?

A WIP report is a real-time snapshot of all the active tasks and projects your team is currently handling. It’s not a high-level project plan, it’s the on-the-ground view of what’s getting done right now. Think of it as your team’s mission control center, showing every moving piece in one organized place.

Why bother? A well-maintained WIP report does three critical things:

• It Creates Total Visibility: Everyone on the team, from senior leaders to individual contributors, can see the status of every task. This ends the constant "Hey, what's the status of X?" interruptions and endless follow-up emails.
• It Helps You Spot Bottlenecks Early: Is one person assigned way too much work? Is a particular stage of your workflow consistently getting stuck? A WIP report makes these problems impossible to ignore before they cause major delays.
• It Enables Better Decision-Making: When you know your team's current capacity, it's easier to decide if you can take on a new project or if you need to re-prioritize. It replaces gut feelings with actual data about what your team can handle.

While dedicated project management tools are great, sometimes all you need is a flexible, collaborative, and free solution - and that's where Google Sheets shines.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 1: Setting Up Your Google Sheet

Let's start with a clean slate. Create a new Google Sheet and give it a clear name like "Marketing Team WIP Report - Q3 2024". A clean structure is the foundation of a useful report. You’ll create a single tab, which we can call "Tasks" or "To-Do List," with the following columns as a starting point. Feel free to add or remove columns based on what your team needs to track.

Here are the essential columns we'll use:

• Task ID: A unique identifier for each task (e.g., MKTG-001, MKTG-002). This is helpful for referencing specific tasks in discussions.
• Task Description: A clear, concise description of the work to be done. Not "Blog," but "Draft blog post on AI marketing trends."
• Project: The larger initiative or goal this task belongs to (e.g., "Summer '24 Campaign," "Website Redesign"). This helps with filtering and sorting.
• Assignee: The person responsible for completing the task.
• Start Date: The date the task was started or is planned to start.
• Due Date: The date the task is expected to be completed.
• Status: The current state of the task (e.g., Not Started, In Progress, Blocked, Completed). This is the most important column for at-a-glance updates.
• Notes: An optional field for extra context, links to documents, or questions.

Your blank sheet should look professional and ready for data. Format the column headers by making them bold and freezing the top row (View > Freeze > 1 row). This keeps your headers visible as you scroll through your task list.

Step 2: Add Functionality to Bring It to Life

A static list is fine, but we can make our sheet intelligent. We’ll add features that standardize data entry, provide visual cues, and automatically calculate helpful metrics. This is what separates a basic list from a dynamic report.

Use Data Validation for Consistent Statuses

Mistyping statuses ("In progess" vs. "In Progress") can break your formulas and charts. We can prevent this by creating a dropdown menu for the 'Status' column.

1. Select the entire 'Status' column (click the letter 'G' at the top, or whatever letter your status column is).
2. Go to Data > Data validation.
3. A sidebar or pop-up will appear. Click + Add rule.
4. Under "Criteria," select "Dropdown."
5. Enter your status options: Not Started, In Progress, Blocked, Review, Completed. You can assign default colors to each option here, which is a fantastic visual aid.
6. Ensure "Show a warning" is selected for invalid data, and click "Done."

Now, a tidy dropdown arrow will appear in each cell of your status column, ensuring everyone uses the same terminology.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Implement Conditional Formatting for Instant Visual Insights

Conditional formatting turns your spreadsheet from a wall of text into a visual scanner. We’ll set up two key rules to start.

Rule 1: Highlight Overdue Tasks

This rule will automatically highlight any task that is past its due date but is not yet marked as 'Completed'.

1. Select your entire data range, starting from the first data row (e.g., A2:H1000).
2. Go to Format > Conditional formatting.
3. The formatting rules sidebar will open. Under "Format cells if...," choose "Custom formula is."
4. Enter the following formula:
5. Choose a formatting style, like a light red or orange background, to make overdue tasks pop.
6. Click "Done."

Now, any overdue task will immediately grab your attention without you having to manually check every date.

Rule 2: Creating a Progress or "Risk" Column with a Formula

Let's create a dynamic "Risk" column that tells us what to worry about. Add a new column to your sheet named Task Health. In the first cell of that column (e.g., I2), enter this IF formula:

=IF($G2="Completed", "Done", IF($G2="Blocked", "Blocked", IF($F2<TODAY(), "Overdue", IF($F2-TODAY()<3, "At Risk", "On Track"))))

Let's break that down:

• If the Status in G2 is "Completed," it shows "Done."
• If it's not completed, it checks if the Status is "Blocked," and if so, it shows "Blocked."
• If it's not blocked, it checks if the due date is in the past, showing "Overdue."
• If it's not overdue, it checks if the due date is less than 3 days away, showing "At Risk."
• And if none of those are true, it's "On Track."

Drag this formula down the entire column. Now you can apply another conditional formatting rule to this 'Task Health' column, making "Overdue" and "Blocked" cells red, "At Risk" yellow, and "On Track" green. This provides an even clearer view of project health.

Step 3: Build a Summary Dashboard

A long list of tasks is useful, but managers and stakeholders often need a 10,000-foot view. We can build a simple dashboard right at the top of our sheet or on a new tab.

Create a Summary Statistics Box

On a new tab called "Dashboard," create a small area to summarize key numbers. You'll use the COUNTIF and COUNTIFS functions for this.

Set up two columns: Metric and Value. Here are some examples:

• Total Tasks: Use the formula =COUNTIF('Tasks'!B:B, "<>") to count all non-empty cells in the task description column.
• Tasks In Progress: Use =COUNTIF('Tasks'!G:G, "In Progress") to count tasks with that specific status.
• Tasks Blocked: Use =COUNTIF('Tasks'!G:G, "Blocked").
• Tasks Overdue: Use =COUNTIF('Tasks'!I:I, "Overdue") which counts tasks based on our custom formula's output.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Visualize the Data with Charts

Charts transform numbers into narrative. Two simple charts will dramatically improve your report’s impact.

Chart 1: Pie Chart of Tasks by Status

1. On your Dashboard tab, list out your statuses ('Not Started', 'In Progress', 'Blocked', etc.) in one column.
2. In the column next to it, use COUNTIF to count how many tasks correspond to each status.
3. Select this small table of data.
4. Go to Insert > Chart. Google Sheets will likely default to a pie chart, which is perfect for showing the distribution of task statuses.

Chart 2: Bar Chart of Tasks per Assignee

1. Following the same process, create another small table on your Dashboard tab. List each team member's name in one column.
2. In the next column, use COUNTIFS to count the number of incomplete tasks for each person. The formula would look like this:
3. Select this data.
4. Go to Insert > Chart and select a Bar Chart. This instantly visualizes team workload and helps you ensure tasks are distributed fairly.

Step 4: Maintain Your Report for Maximum Value

A WIP report is only useful if it's up-to-date. This isn’t a one-and-done setup, it's a living document that should become part of your team's routine.

• Make it a Team Responsibility: Everyone is responsible for keeping their own tasks updated. Make it a rule to update tasks before the end of each day or before weekly team check-ins.
• Use it in Your Meetings: Make this report the centerpiece of your weekly team meetings or daily stand-ups. Filter the sheet by assignee to go person-by-person, or by project to discuss progress on key initiatives.
• Keep it Simple: The temptation is to add more and more columns and complex formulas. Resist this. A powerful WIP report is simple, clear, and easy to update. If it takes more than a minute to update, your team won't use it.

Final Thoughts

Building a work in progress report in Google Sheets is a simple, no-cost way to inject powerful clarity and accountability into your team’s workflow. By setting up a clear data structure and layering on automated features like data validation, conditional formatting, and dashboard charts, you can transform a basic to-do list into a dynamic project management tool.

Once you’ve mastered Sheets, you might find the process of manually downloading CSVs and pasting data into them is the new time-sink. For truly automated reporting, we built Graphed to be the next step. You connect your data sources - like project management boards, CRMs, and ad platforms - once, and we handle the rest. You can build that same WIP report simply by asking, "Show me all incomplete tasks by assignee and project, colored by status," and get a live, interactive dashboard that updates itself in real-time. It's the perfect way to move from managing spreadsheets to focusing on insights.