How to Create a Work in Progress Report in Google Sheets with ChatGPT

Tired of losing track of all your ongoing projects? A simple Work in Progress (WIP) report can give you a crystal-clear overview of everything your team is currently handling. This tutorial will show you how to build a dynamic WIP report in Google Sheets and use ChatGPT to create formulas and automations that save you hours of manual work.

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 is a Work in Progress (WIP) Report?

A Work in Progress report is a snapshot of all the tasks and projects that are currently active - meaning they've started but aren't yet finished. Think of it as your project command center. Instead of relying on scattered notes, overflowing inboxes, and your own memory, a WIP report organizes everything into one central, easy-to-understand view.

For marketing teams, this might track ongoing campaigns, content creation, and website updates. For sales teams, it could be a list of active leads moving through the pipeline. And for founders or project managers, it’s a high-level view of every moving piece in the business.

Why You Absolutely Need One

Maintaining a WIP report might sound like another administrative task, but the payoff is huge. Here’s why it’s so valuable:

• It prevents overload. By visualizing every active task, you can immediately see when you or your team are nearing capacity. This helps you say "no" to new tasks or re-prioritize existing ones before burnout strikes.
• It clarifies priorities. When everything is laid out, it’s easier to see what truly needs attention. You can quickly distinguish between urgent tasks and those that can wait, ensuring your effort is always focused on what matters most.
• It uncovers bottlenecks. Is a certain type of task always getting stuck? Does one person have three times as many projects as everyone else? A WIP report makes these workflow issues obvious, allowing you to fix them before they cause major delays.
• It improves accountability and communication. A shared WIP report acts as a single source of truth. Everyone knows who is responsible for what, what the deadlines are, and the current status of each project. This drastically reduces "just checking in" emails and unnecessary meetings.

Step 1: Build a Simple WIP Template in Google Sheets

Before we bring in ChatGPT, let's set up the foundation of our report in Google Sheets. The key here is simplicity. You can always add more columns later, but starting with the essentials makes the report easier to manage.

Create a new Google Sheet and add the following columns:

• Task ID: A unique identifier for each task (e.g., PROJ-001). This helps with tracking.
• Task/Project Name: A clear, concise description of the task.
• Owner: The person responsible for completing the task.
• Start Date: The day the task was officially started.
• Due Date: The target completion date.
• Status: The current state of the task (e.g., In Progress, Blocked, Needs Review).
• Priority: The urgency of the task (e.g., High, Medium, Low).
• Notes: Any additional context or links relevant to the task.

Pro Tip: Use Data Validation for Statuses

To keep your status column clean and consistent, use data validation to create a dropdown menu. This prevents people from using variations like "In Progress," "WIP," or "Working on it" for the same status.

1. Click on the header of the 'Status' column (the letter F) to select the entire column.
2. Go to Data > Data validation.
3. Next to 'Criteria', select 'List of items'.
4. In the box, enter your statuses separated by commas: Not Started, In Progress, Blocked, Needs Review, Completed
5. Click Save. Now, each cell in that column will have a neat dropdown menu.

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 2: Use ChatGPT to Supercharge Your Report

Now that our structure is in place, we can bring in ChatGPT to act as our personal data analyst and formula wizard. Instead of googling for complex formulas or trying to remember syntax, you can just describe what you want in plain English.

Automating Key Metrics with Formulas

Dashboards thrive on automated metrics that update in real-time. Here's how you can ask ChatGPT to generate the formulas you need.

1. Calculating "Days Overdue"

A crucial metric for project management is understanding which tasks are falling behind. We want a formula that checks a task's due date and status and tells us how many days overdue it is.

Your Prompt to ChatGPT:

"I'm creating a Work in Progress report in Google Sheets. I have a 'Due Date' in column E and a 'Status' in column F. I want a formula for column I, 'Days Overdue', that calculates how many days past the due date a task is. The calculation should only happen if the status in column F is NOT 'Completed'. If the task is completed, or if the due date hasn't passed yet, the 'Days Overdue' cell should be blank."

ChatGPT's Likely Response:

=IF(AND(F2<>"Completed", E2<TODAY()), TODAY()-E2, "")

How it works:

• AND(F2<>"Completed", E2<TODAY()): checks if the status is not "Completed" and the due date is in the past.
• TODAY()-E2: calculates days overdue.
• "": leaves cell blank if conditions are not met.

2. Creating an "At a Glance" Summary

Next, let's build a small summary section at the top of our sheet so you can see key project stats without having to scroll or filter.

Your Prompt to ChatGPT:

*"Using my Google Sheet layout where task statuses are in column F (starting from F2), give me formulas for the following summary metrics:

1. Total tasks that are 'In Progress'.
2. Total tasks that are 'Blocked'.
3. Total tasks that are 'Completed'."*

ChatGPT's Likely Response:

It will provide you with a set of simple, powerful COUNTIF formulas.

• For "In Progress": =COUNTIF(F2:F, "In Progress")
• For "Blocked": =COUNTIF(F2:F, "Blocked")
• For "Completed": =COUNTIF(F2:F, "Completed")

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

Bringing Your Data to Life with Conditional Formatting

Static reports are easy to ignore. Conditional formatting uses color to draw attention to important information, making it impossible to miss issues like overdue tasks.

Let's have ChatGPT generate the rule for us.

Your Prompt to ChatGPT:

"I need to create a conditional formatting rule in Google Sheets. I want to highlight an entire row in light red if that task is overdue. In my sheet layout, the Due Date is in column E and the Status is in column F. A task is considered overdue if its due date has passed AND its status is not 'Completed'. Tell me what 'custom formula' I should use for this rule and what range I should apply it to."

ChatGPT's Likely Response:

1. Select your data range, for example, A2:H1000.
2. Go to Format > Conditional formatting.
3. Under 'Format rules,' select 'Custom formula is' from the dropdown.
4. Enter this formula:

=AND($E2<TODAY(), $F2<>"Completed")

1. Set the formatting style (e.g., a light red background).
2. Click Done.

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 3: Taking It Further with Google Apps Script

When you're ready to get more advanced, you can use ChatGPT to write simple Google Apps Scripts that fully automate parts of your workflow. For example, wouldn't it be great if completed tasks automatically moved themselves off your WIP report and onto an "Archive" sheet?

Your Prompt to ChatGPT:

"Write me a Google Apps Script for Google Sheets. The script should look in a sheet named 'WIP'. When it finds a row where the value in column 6 (the 'Status' column) is 'Completed', it should copy that entire row to a sheet named 'Archive', and then delete the row from the 'WIP' sheet."

ChatGPT's Likely Response (a sample script):

function archiveCompletedTasks() {
  const ss = SpreadsheetApp.getActiveSpreadsheet(),
  const wipSheet = ss.getSheetByName("WIP"),
  const archiveSheet = ss.getSheetByName("Archive"),

  // Get all data from the WIP sheet
  const data = wipSheet.getDataRange().getValues(),
  const rowsToDelete = [],

  // Loop through rows from the bottom up to avoid skipping rows
  for (let i = data.length - 1, i >= 1, i--) {
    const row = data[i],
    const status = row[5], // Column F is index 5

    if (status === "Completed") {
      archiveSheet.appendRow(row),
      rowsToDelete.push(i + 1), // Store row number to delete later
    }
  }

  // Delete rows from the WIP sheet, from bottom up
  for (const rowNum of rowsToDelete.sort((a, b) => b - a)) {
    wipSheet.deleteRow(rowNum),
  }
}

To use this, go to Extensions > Apps Script, paste in the code, and Save. You can then run the script manually or set up a trigger to run it automatically every day. Just like that, you’ve automated your report cleanup process without writing a single line of code yourself.

Final Thoughts

Creating and maintaining a WIP report in Google Sheets is a game-changer for staying organized and on track. By pairing the spreadsheet with ChatGPT, you transform it from a static list into a smart, automated dashboard that works for you, giving you clear insights into your team's workload and project health.

While using ChatGPT to build formulas in Google Sheets is powerful, we know the most time-consuming part of reporting is often just getting all your data in one place. Pulling CSVs from your ad platforms, CRM, and analytics tools can turn into hours of work before you can even begin your analysis. This is exactly why we built Graphed. We connect directly to tools like Google Analytics, Shopify, Facebook Ads, and Salesforce, so your data is always live and in one place. From there, you can just ask questions in plain English - like "create a dashboard showing campaign costs vs. revenue" - and get a real-time report in seconds.