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

Get FREE Advanced Excel Exercises with Solutions!

If you want to create a workflow tracker in Excel, you have come to the right place. Here, we will walk you through 10 easy and suitable steps to do the task smoothly.


Workflow Tracker in Excel: Step-by-Step Procedure to Create

In the following article, we will describe some easy steps to create a workflow tracker in Excel.
Here, we used Excel 365. You can use any available Excel version.


Step-1: Creating Dataset for Workflow Tracker

Here, to create a workflow tracker in Excel, first, we have to create a dataset.

  • First of all, we have taken the tasks in the Task column.

Creating Dataset for Workflow Tracker in Excel

  • Then, we input the starting dates in the Starting Date column.

  • Along with that, we insert the due dates in the Due Date column.
  • Therefore, the dataset is ready.
  • Next, we will create a workflow tracker using this dataset.


Step 2: Calculating Required Days

In this step, we will calculate the required days to complete the task so that we can create the workflow tracker.

  • In the first place, we need the spent days on the task, therefore, we add a Days Spent column to our dataset.

Including Day Spent Column for Workflow Tracker in Excel

  • After that, to calculate the Days Required, we type the following formula in cell F5.
=D5-C5

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

  • At this moment, press ENTER.
  • Therefore, you can see the result in cell F5.
  • Moreover, we will drag down the formula with the Fill Handle tool.

Adding Days Required Column for Workflow Tracker in Excel

  • Therefore, you can see the complete Days Required column.


Step 3: Finding Progress of Our Created Workflow Tracker

In this step, we will find out the progress of the workflow tracker.

  • To do so, in the beginning, we will type the following formula in cell G5.
=E5/F5
  • This simply divides Days Spent with Days Required.

Calculating Progress for Workflow Tracker in Excel

  • Afterward, press ENTER.
  • Hence, you can see the result in cell G5.
  • Here, we want the Progress in Percentage.
  • Therefore, we selected cell G5 >> go to the Home tab.
  • After that, from the Number group >> select Percentage.

Inserting Percentage in Progress for Workflow Tracker in Excel

  • Therefore, you can see the percentage of the Progress of Task 1 in cell G5.
  • Furthermore, we will drag down the formula with the Fill Handle tool.

  • Hence, you can see the complete Progress column.


Step 4: Discovering the Status of the Workflow Tracker

In this step, we will discover the Status of the workflow tracker.

  • In the first place, we will type the following formula in cell H5.
=IF(G5=100%, “Complete”, “In Progress”)

Finding Status Column for Workflow Tracker in Excel

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.

  • At this point, press ENTER.
  • As a result, you can see the result in cell H5.
  • In addition, we will drag down the formula with the Fill Handle tool.

Using Fill Handle Tool for Workflow Tracker in Excel

  • Therefore, you can see the complete Status column.


Step 5: Determining Residual Percentage of Work

In this step, we will determine the percentage of the remaining tasks for the workflow tracker.

  • First of all, we will type the following formula in cell F5.
=1-E5
  • This will simply subtract the Progress percentage from 1.

Comuting Remaining for Workflow Tracker in Excel

  • At the moment, press ENTER.
  • Therefore, you can see the result in cell F5.
  • Furthermore, we will drag down the formula with the Fill Handle tool.

  • Therefore, you can see the complete Remaining column.
  • Along with that, you can see the complete layout of the progress tracker.


Step-6: Finding Overall Project Progress for Forming Workflow Tracker

In this step, we will use the AVERAGE function to find out the Overall Project Completion.

  • Firstly, we will type the following formula in cell F15:F16.
=AVERAGE(E5:E12)
  • The AVERAGE function finds the average of a range of cells.

Calculating Overall Project Completion for Workflow Tracker in Excel

  • Afterward, press ENTER.
  • Hence, you can see the result in a range of cells F15:F16.


Step 7: Calculating Project Workflow Report

In this step, we will find out the Project Workflow Report for the workflow tracker.

  • In the first place, we will calculate the Total day Required.
  • To do so, we will type the following formula in cell C20.
=SUM(D5:D12)

Using SUM Function for Workflow Tracker in Excel

  • Afterward, press ENTER.
  • As a result, you can see the outcome in cell C20.

  • In addition, we will find out the Total day spent.
  • Therefore, we will type the following formula in cell C21.
=SUM(C5:C12)

Calculating Total Day Spent for Workflow Tracker in Excel

  • Then, press ENTER.
  • Hence, you can see the result in cell C21.

  • Moreover, to find out the Overall Progress, we will type the following formula in cell C22.
=C21/C20

  • After that, press ENTER.
  • Therefore, you can see the result in cell C22.
  • Moreover, you can see the complete Project Workflow Report.

Finding Overall Progress for Workflow Tracker in Excel


Step 8: Determining Task Status for Workflow Tracker

In this step, we will determine the Task Status for the workflow tracker.

  • First of all, we will type the following formula in cell F20 to find out 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.

  • At this point, press ENTER.
  • Hence, you can see the result in cell F20.

  • Afterward, we will type the following formula in cell F21 to find out the number of In Progress tasks.
=COUNTIF(G5:G12,”In Progress”)
  • Next, press ENTER.
  • Therefore, you can see the result in cell F21.

  • Now, it is time to find out the percentage of tasks that are Completed.
  • Therefore, we type the following formula in cell G20.
=F20/(F20+F21)

  • At this time, press ENTER.
  • As a result, you can see the outcome in cell G20.

  • Next, to find out the percentage of tasks that are In Progress, we type the following formula in cell G21.
=F21/(F21+F20)

  • At this moment, press ENTER.
  • Hence, you can see the outcome in cell G21.
  • Moreover, you can see the complete Task Status.


Step-9: Inserting Column Chart

In this step, we will insert a Column chart so that we can visualize the workflow tracker for better understanding.

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

  • First of all, we will select the Task column.
  • Then, we will press and hold the CTRL key.
  • Moreover, we will select the Progress and Remaining columns.
  • After that, go to the Insert tab.
  • Then, from the Insert Column or Bar Chart group >> select 2D Stacked Column chart.

Inserting Column Chart for Workflow Tracker in Excel

  • Therefore, you can see the Column chart.
  • Furthermore, we will edit the Chart Title.

  • As a result, you can see the Column chart for workflow progress.

Read More: How to Create Workflow Chart in Excel


Step-10: Employing Pie Chart

In this step, we will employ a Pie chart so that we can visualize and compare the percentage of Completed tasks with the percentage of In Progress tasks.

  • In the first place, we will select cells E20:E21 >> select cells G20:G21.
  • After that, we will go to the Insert tab.
  • Then, from the Insert Pie or Doughnut Chart group >> select 3D Pie chart.

  • Therefore, you can see the 3D Pie chart.
  • Next, we will edit the Chart Title.

  • Therefore, you can see the Pie chart for Task Status.
  • Furthermore, we will add Data Labels to the chart.
  • Therefore, we will click on Chart Elements >> mark Data Labels.

  • Therefore, you can see the Pie chart is looking more presentable.
  • Hence, we can understand the progress of the workflow tracker through this chart.

  • Therefore, you can see the complete workflow tracker.


Download Workbook

You can download the following Excel file to get a free template for the workflow tracker.


Conclusion

Here, we show you 10 easy steps to create a workflow tracker in Excel. Thank you for reading this article. We hope it was helpful. If you have any queries, please let us know in the comment section.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Afia Kona
Afia Kona

Afia Aziz Kona, a graduate of Civil Engineering from Khulna University of Engineering & Technology, Bangladesh, serves as a technical content creator in the ExcelDemy project. Possessing a passion for innovation and critical thinking, she actively embraces challenges. Beyond her engineering background, Afia exhibits a keen interest in Excel, having authored numerous articles on Excel & VBA-related issues to simplify the experience for users facing obstacles in Excel. Apart from creating Excel tutorials, she is also... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo