How to Create a Workflow Tracker in Excel (with Easy Steps)

Step 1 – Creating a Dataset for the Workflow Tracker

We have listed the tasks in the Task column.

Input the starting dates in the Starting Date column.

Insert the due dates in the Due Date column.

Step 2 – Calculating the Required Days

We add a Days Spent column to our dataset.

To calculate the Days Required, use the following formula in cell F5.

=D5-C5

This formula simply subtracts the Due Date from the Starting Date.

Hit Enter.
Drag down the formula with the Fill Handle tool.

You can see the complete Days Required column.

Step 3 – Determining the Progress

Use the following formula in cell G5.

=E5/F5

This divides Days Spent with Days Required.

Hit Enter.
Select cell G5 and go to the Home tab.
From the Number group, select Percentage.

Drag down the formula with the Fill Handle tool.

You can see the complete Progress column.

Step 4 – Determining the Status of the Tasks

Use the following formula in cell H5.

=IF(G5=100%, “Complete”, “In Progress”)

Formula Breakdown

IF(G5=100%, “Complete”, “In Progress”) → the IF function does a logical test between a given value and the value we expect.
IF(G5=100%, “Complete”, “In Progress”) → becomes
- Output: In Progress
Explanation: Since the logical test is False, the IF function returns In Progress.

Hit Enter.
Drag down the formula with the Fill Handle tool.

You can see the complete Status column.

Step 5 – Determining the Residual Percentage of Work

Use the following formula in cell F5.

=1-E5

Hit Enter.
Drag down the formula with the Fill Handle tool.

You can see the complete Remaining column.

Step 6 – Finding the Overall Project Progress

Merge cells F15 and F16.
Use the following formula in cell F15.

=AVERAGE(E5:E12)

Press Enter.

Step 7 – Creating a Project Workflow Report

Calculate the Total Days Required with the following formula in cell C20.

=SUM(D5:D12)

Hit Enter.

Determine the Total days spent with the following formula in cell C21.

=SUM(C5:C12)

Hit Enter.

To find the Overall Progress, use the following formula in cell C22.

=C21/C20

Press Enter.

Step 8 – Determining the Task Status

Use the following formula in cell F20 to get the number of Completed tasks.

=COUNTIF(G5:G12, “Complete”)

Formula Breakdown

COUNTIF(G5:G16, “Complete”) → the COUNTIF function determines the number of cells based on criteria.
G5:G16 → is the array
Complete → is the criteria
COUNTIF(G5:G16, “Complete”) → becomes
- Output: 2
Explanation: 2 is the number of cells that are Complete.

Hit Enter.

Use the following formula in cell F21 to get the number of In Progress tasks.

=COUNTIF(G5:G12,”In Progress”)

Hit Enter.

To get the percentage of tasks that are Completed, use the following formula in cell G20.

=F20/(F20+F21)

Hit Enter.

To determine the percentage of tasks that are In Progress, use the following formula in cell G21.

=F21/(F21+F20)

Hit Enter.

Step 9 – Inserting a Column Chart

We will insert a column chart to show each Task for their corresponding Progress and Remaining percentage.

Select the Task column.
Press and hold the Ctrl key.
Select the Progress and Remaining columns.
Go to the Insert tab.
From the Insert Column or Bar Chart group, select 2D Stacked Column chart.

You can see the Column chart.
Edit the Chart Title.

Here’s the result.

Step 10 – Using a Pie Chart for Percentages of Total Tasks Completed+

Select cells E20:E21 and cells G20:G21 (hold Ctrl while selecting).
Go to the Insert tab.
From the Insert Pie or Doughnut Chart group, select 3D Pie chart.

You can see the 3D Pie chart.
Edit the Chart Title.

Here’s the Pie chart for Task Status.
Click on Chart Elements and check Data Labels.

Here’s the pie chart.

Here’s the entire tracker.

Download the Template

Workflow Tracker.xlsx

<< Go Back to Workflow in Excel | SmartArt in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!