How to Create a Work in Progress Report in Excel

A Work in Progress (WIP) report provides you with a real-time snapshot of where your projects stand, who is working on what, and what is at risk of falling behind. This article will guide you through building a clear, effective, and visually appealing WIP report using Microsoft Excel, transforming a simple spreadsheet into a powerful project management tool.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

What is a Work in Progress (WIP) Report?

At its core, a Work in Progress report is a centralized document that tracks the status of all active tasks and projects within a team or an entire company. It goes beyond a basic to-do list by providing critical context like deadlines, ownership, and progress, making it an essential tool for project managers, team leads, and any stakeholder needing a bird's-eye view of operations.

Think of it as the command center for your projects. Are things running on schedule? Is a team member overloaded with tasks? Is that critical campaign launch being held up by one delayed asset? A properly maintained WIP report answers these questions at a glance, helping you identify bottlenecks before they derail your timeline and budget.

WIP reports aren’t just for manufacturing or accounting. They’re incredibly valuable for:

• Marketing Agencies: Tracking deliverables across multiple clients, from social media content and ad campaigns to website redesigns.
• Software Development Teams: Monitoring the progress of features, bug fixes, and development sprints.
• Content Creators: Managing an editorial calendar, from initial drafts and reviews to final publication and promotion.
• Service Businesses: Keeping track of client onboarding, service delivery, and ongoing support tickets.

The goal isn’t to micromanage but to create transparency and enable proactive decision-making. By making progress visible, you empower your team, manage stakeholder expectations, and keep everyone aligned and moving forward.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Gathering the Right Data for Your Report

Before you open Excel, you need a clear idea of what information you want to track. A cluttered report is just as useless as one with too little information. Start with a foundational set of data points and only add more if they provide genuine value.

Here are the essential columns we recommend for a robust WIP report:

• Project ID / Task ID: A unique identifier that helps you reference specific tasks easily. It can be as simple as "P01-T01."
• Project Name: The name of the overarching project the task belongs to (e.g., "Q3 Marketing Campaign").
• Task Description: A clear and concise description of the work to be done (e.g., "Design Facebook Ad Creative").
• Assigned To: The name of the person responsible for completing the task. This ensures absolute clarity on ownership.
• Start Date: The date the task is scheduled to begin.
• End Date (or Due Date): The deadline for task completion. This is critical for monitoring timelines.
• Status: The current state of the task. Keep this simple and standardized with options like: Not Started, In Progress, Blocked, In Review, and Completed.
• % Complete: A numerical value (0-100) that provides a more granular view of progress, especially for longer tasks.
• Notes / Blockers: A column for important context. Is the task waiting on input from another team? Was a key detail changed? This is the place to note it.

Step-by-Step Guide to Creating a WIP Report in Excel

Now that you know what data you need, let's build the report. Follow these steps to transform a blank spreadsheet into a dynamic project tracking dashboard.

Step 1: Set Up Your Spreadsheet as an Excel Table

Don't just type your headers into cells. Using Excel's "Table" feature is one of the most powerful things you can do. It automatically adds filter buttons, applies formatting that is easy to read, and makes formulas much easier to manage.

1. Open a new Excel workbook.
2. In the first row, enter the headers you decided on in the previous section (Project ID, Task Description, Assigned To, etc.).
3. Select any cell within your headers.
4. Go to the Insert tab on the Ribbon and click Table.
5. A dialog box will appear. Ensure the range is correct and check the box for "My table has headers." Click OK.

Your data range now has new formatting and helpful filter dropdowns on each header. This simple step makes sorting tasks by deadline or filtering for a specific team member incredibly easy.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Step 2: Populate Your Project Data

Start entering your tasks into the table. Fill out each column for every task on your plate. Consistency is important here. For example, make sure team member names are spelled the same way every time to make filtering reliable. To make the "Status" column even better, use Data Validation to create a dropdown list. This forces standardization and prevents typos.

1. Select the entire "Status" column within your table.
2. Go to the Data tab and click Data Validation.
3. Under the "Allow" dropdown, select List.
4. In the "Source" box, type your status options separated by commas: Not Started,In Progress,Blocked,In Review,Completed.
5. Click OK. Now, each cell in that column will have a dropdown menu. Easy!

Step 3: Add Conditional Formatting for Status

Make your report instantly scannable by adding colors that change based on task status. This allows you to spot "Blocked" or overdue tasks immediately.

1. Select all the data in your table (but not the headers).
2. Go to the Home tab, click Conditional Formatting > New Rule.
3. Select "Use a formula to determine which cells to format."
4. Let's create a rule for "Completed" tasks. In the formula box, type: =$G2="Completed" (assuming "Status" is in column G, adjust the column letter accordingly. The $ before G is important - it locks the reference to the Status column).
5. Click the Format button. Go to the "Fill" tab, choose a light green color, and possibly go to the "Font" tab to apply a strikethrough effect. Click OK.
6. Repeat this process for other statuses. For "Blocked," use a red or orange fill. For "In Progress," use a light yellow or blue. This visual system transforms your datasheet into an intuitive dashboard.

Step 4: Create Calculated Columns for Deeper Insights

Now, let's add some automation with formulas. One of the most useful additions is a column that tells you if a task is overdue.

1. To the right of your last column, add a new header called "Days Overdue."
2. Because you're using an Excel Table, the new column should be automatically included. In the first cell under the new header, enter the following formula:

=IF(AND([@[End Date]]<TODAY(), [@[Status]]<>"Completed"), TODAY()-[@[End Date]], 0)

Let's break this down:

• [@[End Date]] refers to the End Date in the current row.
• The IF function checks if two conditions are true (AND): the task's End Date is before today's date, AND its Status is not "Completed."
• If both are true, it calculates the number of days between today and the end date. If not, it returns 0.

The table will automatically fill this formula down the entire column. You can now sort by this column to bring the most overdue tasks to the top.

Step 5: Visualize Progress with a Simple Bar Chart

While tables are great, visual progress bars can offer an at-a-glance reading of your report. You can use conditional formatting's "Data Bars" feature for this.

1. Make sure you have a "% Complete" column with numerical values (e.g., enter 75 for 75%).
2. Select the entire "% Complete" column.
3. On the Home tab, go to Conditional Formatting > Data Bars and choose a color.
4. Excel will automatically add a progress bar inside each cell, giving you a quick visual representation of how far along each task is.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Step 6: Build a High-Level Summary with a PivotTable

A PivotTable can summarize your detailed WIP report on a separate sheet, giving you and other stakeholders a high-level overview. Let's create a summary that shows how many tasks each team member has, broken down by status.

1. Click anywhere inside your WIP table.
2. Go to the Insert tab and click PivotTable. Excel will automatically confirm your table range and suggest placing it in a new worksheet. Click OK.
3. A new worksheet will open with a PivotTable Fields pane on the right-hand side.
4. Drag and drop the fields as follows:

• Drag "Assigned To" into the Rows area.
• Drag "Status" into the Columns area.
• Drag "Task ID" into the Values area. It should default to "Count of Task ID," which is exactly what we want.

In seconds, you'll have a summary table showing every team member and a count of their tasks categorized as Blocked, Completed, In Progress, and so on. This dashboard view is perfect for weekly status meetings.

Final Thoughts

Building a WIP report in Excel involves structuring your data properly in a table, enhancing it with visual cues like conditional formatting, and summarizing it effectively with tools like PivotTables. By automating calculations and highlighting key information, you can transform a static list of tasks into a dynamic and genuinely useful project management tool.

Of course, the manual upkeep of even the best Excel sheet can become a chore, especially when your data lives across platforms like Shopify, Salesforce, or Google Analytics. At Graphed, we built a tool to eliminate this entirely. We connect seamlessly to a huge number of apps, so instead of downloading CSVs and wrangling formulas, you can simply ask, "create a dashboard showing all active marketing tasks by owner and status" and get a real-time, shareable report instantly. It’s like having a data analyst ready to pull any report you need in seconds, freeing you up to focus on the work itself.