How to Create a Progress Tracker in Excel (3 Handy Ways)

A progress tracker is a very handy tool in our life. You may need a progress tracker for tracking progress of employees in a project, to keep track of your to-do list, or for many other cases. In Microsoft Excel, you can easily create a progress tracker which is very efficient. This article demonstrates how to create a progress tracker in Excel.


Download Practice Workbook

You can download the practice workbook from the link below.


3 Methods to Create a Progress Tracker in Excel

Let’s assume you have a dataset with a list of people and the percentage of their Task Completed. Now, you want a progress bar to track their progress. At this point, I will show you two methods of how to create a progress tracker in excel using the dataset below.

How to Create a Progress Tracker in Excel dataset


1. Using Conditional Formatting Feature to Create a Progress Tracker

The most convenient and fastest way to create a progress tracker is using the Excel Conditional Formatting feature. Now, if you want to create a progress tracker using the Conditional Formatting feature, you can follow the steps below.

Steps:

  • First, select the range C5:C12. In this case, cells C5 and C12 are the first and last cells of the Task Completed.
  • Then, go to Conditional Formatting from the Home tab.
  • Next, select New Rule.

using conditional formatting

  • Now, from Select a Rule Type select Format all cells based on their values.
  • After that, select Data Bar from Format Style.

using conditional formatting

  • At this point, for Minimum select Number as Type and insert 0 as the Value.
  • Similarly, for Maximum select Number as Type and insert 1 as the Value.
  • Then, from Color choose the color your progress bar to be.
  • After that, add Solid Border from Border.
  • Next, from Bar Direction select Left to Right.
  • Consequently, click on OK.

using conditional formatting

  • Finally, you will have your progress tracker as in the screenshot below.

using conditional formatting

Read More: Progress Bar in Excel Cells Using Conditional Formatting


Similar Readings


2. Inserting Bar Chart to Create a Progress Tracker in Excel

Another convenient way to create a progress tracker is using the Bar Chart. Now, if you want to create a progress tracker in Excel using a Bar Chart, follow the steps below.

Steps:

  • First, select range B5:C12. In this case, B5 is the first cell of the column Name.
  • Then, go to Insert tab > Insert Column or Bar Chart > Stacked Bar.

Inserting Bar Chart to Create a Progress Tracker in Excel

  • Now, a chart like the following screenshot will appear.
  • Next, Double-Click on the Vertical Axis to go to Format Axis options.

Inserting Bar Chart to Create a Progress Tracker in Excel

  • At this point, from Axis Options check the Categories in Reverse Order box.

Inserting Bar Chart to Create a Progress Tracker in Excel

  • Then, Double-Click on the Data Series to go to the Format Data Series options.
  • After that, from Series Options change the Gap Width to 90%.

Inserting Bar Chart to Create a Progress Tracker in Excel

  • Now, go to the chart and click on Chart Elements.
  • Consequently,  check the Data Labels box.
  • Also, change the color of the bars to your convenience.

Inserting Bar Chart to Create a Progress Tracker in Excel

  • Eventually, you will get your progress tracker like the screenshot below.

Inserting Bar Chart to Create a Progress Tracker in Excel

Read More: How to Make Progress Chart in Excel (2 Simple Methods)


3. Utilizing Check Boxes and Circle Chart to Create a Progress Tracker

Now, suppose you have a dataset with a to-do task list for a week. Also, you want to create a progress tracker for the week using Check Boxes and a Circle Chart. At this point, follow the steps below to do so.

Utilizing Check Boxes and Circle Chart to Create a Progress Tracker

Steps:

  • First, create a new column for Check Box.
  • Next, select cell C5 and go to  Developer tab > Insert.

In this case, cell C5 is the first cell of the Check Box column.

  • Then, select Check Box (Form Control).
  • Consequently, drag the Fill Handle to the remaining cells of the column.

Utilizing Check Boxes and Circle Chart to Create a Progress Tracker

  • Now, add another column to assign the result of the check boxes.
  • Then, Right-Click on the check box in cell C5 and select Format Controls.

Utilizing Check Boxes and Circle Chart to Create a Progress Tracker

  • At this point, go to the Control tab in the Format Control box.
  • Then, insert the cell $D$5 in the Cell link.

In this case, cell D5 will return logic TRUE as the check box is checked. Also, cell D5 is right next to C5 in the same row.

  • Consequently, click on OK.

Utilizing Check Boxes and Circle Chart to Create a Progress Tracker

  • Similarly, assign every other check box to the next cell in the row.
  • After that, select cell F6 and insert the following formula.
=COUNTIF(D5:D12,TRUE)/COUNTIF(B5:B12,"*")

Here, cell F6 is the cell indicating the Task Completed percentage. Also, we use the COUNTIF function to count the number of tasks completed and the total number of tasks.

Utilizing Check Boxes and Circle Chart to Create a Progress Tracker

  • Next, select cell G6 and insert the following formula.
=1-F6

In this case, cell G6 is the cell indicating the Task Remaining percentage.

Utilizing Check Boxes and Circle Chart to Create a Progress Tracker

  • At this point, select range F4:G6.
  • Then, go to Insert tab > Insert Pie or Doughnut Chart > Doughnut.

Utilizing Check Boxes and Circle Chart to Create a Progress Tracker

  • After inserting the chart, Double-Click on the Data Series to go to the Format Data Series option.
  • Then, from Series Options change the Doughnut Hole Size to 50%.

Utilizing Check Boxes and Circle Chart

  • Now, click on the Data Point for the Task Completed and change the color to your choice.
  • Similarly, change the color for the Data Point of Task Remaining.

Utilizing Check Boxes and Circle Chart

  • Next, insert a Text Box inside the Doughnut.
  • Then, click on the Text Box and insert the following formula.
=$F$6

Utilizing Check Boxes and Circle Chart

  • Finally, you will have your progress tracker utilizing Check Boxes and Circle Chart like the screenshot below.

Utilizing Check Boxes and Circle Chart

Read More: Progress Circle Chart in Excel as Never Seen Before


Conclusion

Last but not the least, I hope you found what you were looking for from this article. If you have any queries, please drop a comment below. Also, if you want to read more articles like this, you can visit our website ExcelDemy.


Related Articles

Sowmik Chowdhury
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo