Tracker in Excel (Task Tracker, Progress Tracker and so on)

A tracker in Excel can be used for many purposes including project management, inventory management, event planning, data organization, employee tracking, and more. The versatility and flexibility of Excel allows you to create many types of tracker.

Download Practice Workbook

You can download this practice workbook while going through the article.

How to Create a Tracker in Excel

In our example we have information about specific tasks. We have the category, importance, and status of those tasks.

• Put the tasks in range C5:C14.

• Select range D5:D14 >> go to the Data tab >> choose Data Validation from the Data Tools group.

• Set the Data Validation dialog box as shown below. Put this formula into the source bar.

• You will see a drop-down box in all the cells of the Category column.

• Use the drop-down box and fill up all the cells of the Category column.

• Repeat these steps and perform Data Validation for the Importance and Status columns. The source bar in the Data Validation dialog box will have different formulas.

Source formula of Data Validation for Importance Column:

Source formula of Data Validation for Status Column:

• Now fill up range E5:F14 with the help of the drop-down boxes and complete the task tracker.

• You can get information about a particular task. Put the Serial of the task in cell C4.

• Put these formulas based on the VLOOKUP function in range C5:C8 to see the information of the task.

Formula in cell C5:

Formula in cell C6:

Formula in cell C7:

Formula in cell C8:

• You can also create a task summary. To do this, go to cell C5 and put enter the following formula based on the COUNTIFS function:

• Use Fill Handle to AutoFill data in range C6:C8.

This formula will only count the number of cells if the value in cell B5 (Work) and cell C4 (Completed) of the Summary worksheet matches any value in range D5:D14 and range F5:F14 in the Task Tracker worksheet respectively.

• Go to cell D5 and enter this formula based on the COUNTIFS function:

• Select cell D5 and use Fill Handle to AutoFill data in range D6:D8.

• Go to cell E5 and enter this formula based on the SUM function:

• Select cell E5 and use Fill Handle to AutoFill data in range E6:E8.

• Now select range B4:E8 >> go to the Insert tab >> select Column Chart option >> choose an appropriate Column Chart.
• You will see a Column Chart with the summary for each category of your tasks.

How to Create Different Trackers in Excel

1. Create a Real Time Tracker in Excel

Design the dataset in the following way.

• Enter the value of hourly payment in cell C4.
• Fill up the Employee ID and Name columns.

• Select range D7:E16 >> go to the Home tab >> select Time format from the Number group.

• Fill up the Start Time and End Time columns for each employee.

• Go to cell F7 and put this formula based on the TEXT function.

• Select cell F7 and use Fill Handle to AutoFill data in range F8:F16.

• Go to cell G7 and put this formula based on the combination of HOUR and MINUTE functions.

• Select cell G7 and use Fill Handle to AutoFill data in range G8:G16.

2. Create Workflow Tracker in Excel

We can use the following format to create a workflow tracker.

• Go to cell E5 and enter this formula.

• Select cell E5 and use Fill Handle to AutoFill data in range E6:E14.

• Put the actual number of days spent for each task in range F5:F14.

• Select range G5:G14 >> go to the Home tab >> change the number format into Percentage.

• Go to cell G5 and enter this formula.

• Select cell G5 and use Fill Handle to AutoFill data in range G6:G14.

• Go to cell H5 and enter the following formula based on the IF function:

• Select cell H5 and use Fill Handle to AutoFill data in range H6:H14.

3. Create a Progress Tracker in Excel

3.1. Use Conditional Formatting

• Follow Method 2 step-by-step to create a progress tracker.

• Select range G5:G14 >> go to the Home tab >> Conditional Formatting >> New Rule.

• Set the New Formatting Rule dialog box as shown below.

• You will see the progress tracker with data bars in each cell of range G5:G14.

3.2. Use Bar Chart

We will use the same progress tracker as shown in the previous method.

• Now select range B4:B14, press the Ctrl button, and select range G4:G14.
• Go to the Insert tab >> select Bar Chart option >> choose an appropriate Clustered Bar Chart.

• You will see the bar chart of the progress tracker in your worksheet. You can give it a suitable title.

3.3. Apply Check Boxes and Circle Chart

We will use the following format to create a progress tracker with check boxes and a circle chart.

• Select cell D5 >> go to the Developer tab >> click on Insert >> choose Check Box (Form Control).

• Create a check box in cell D5.
• Select cell D5 and use Fill Handle to AutoFill to create checkboxes in range D6:D14.

• Right-click on the check box in cell D5 >> select Format Control.

• Set the Format Control dialog box as shown below.
• Insert $E$5 in the Cell Link bar and click OK.

• Now do the same for the rest of the check boxes in range D6:D14 and enter the corresponding cell of column E in the Cell Link bar of Format Control dialog box.
• Tick the check boxes of the completed tasks and you will see TRUE (if checked) or FALSE (if unchecked) in the corresponding cells of the Task Completion column.

• Now select range G5:H5 and change the number format into Percentage.

• Go to cell G5 and enter the following formula which combines COUNTIF and COUNT functions:

• Go to cell H5 and enter this formula into the cell.

• Select range G4:H5 >> go to the Insert tab >> select the Doughnut chart.

• You will see a circle chart in your worksheet. Give the chart an appropriate title.

Templates of Tracker in Excel

1. Inventory Tracker in Excel

You can also create an inventory tracker in Excel. An inventory tracker can be used to monitor and manage in example a company’s inventory. You can use the inventory tracker below:

2. Project Progress Tracker in Excel

A project progress tracker is a very handy tool in our day-to-day life. You may need a progress tracker to monitor different activities of your employees who are working on a project and to keep track of their progress. This type of tracker enables companies to have more control and a higher efficiency in managing their employees.

3. Fully Functional To Do List

Excel comes with powerful features and functions to create a fully functional to do list. The list below is very flexible, easy to customize, and you can use it to keep track of your day-to-day activities.

Things to Remember

• Select the source for Data Validation carefully.
• Select the desired range of cells before setting the Conditional Formatting dialog box.
• Keep your tracker updated.

Frequently Asked Questions

1. What are the best practices for organizing data in an Excel tracker?

You should use consistent headings and group interconnected information together. It is better to use separate sheets or tabs for different sections or categories. You should keep an organized data structure that is easy to understand and navigate.

2. How do I set up data validation to ensure accurate data entry in my tracker?

To set up data validation in Excel follow these steps:

• Select the cells where you want to apply validation.
• Go to the Data tab.
• Click on Data Validation, and specify the validation criteria such as whole numbers, decimal numbers, dates, or values from a specific list. You can also set custom validation rules to meet your specific requirements.

3. How can I protect my tracker from accidental modifications or unauthorized access?

To protect your tracker,

• Go to the Review tab.
• Click on Protect Sheet or Protect Workbook and set a password to prevent unauthorized modifications.

You can also restrict editing permissions and specify who can make changes to the workbook.

Tracker in Excel: Knowledge Hub

• Create a Tracker in Excel
• To-Do List in Excel
• Excel Expense Tracker
• Excel Weight Los Tracker

<< Go Back to Excel Templates