How to Create a Task Tracker in Excel (Download Free Template)

Although there is no direct tool to track tasks in Excel, we can use a combination of different functions and formulas to create a dynamic task tracker of our own. This tutorial will show you the quick and easy steps to set up your task tracker with reports on its current status of it in Excel.

Step 1: Making Dataset for Task Tracker in Excel

At first, we will be making a dataset, that should be automatically updatable. We will make a task list with their entry and name, following the category, urgency, importance and the completion status Follow these steps to make the dataset dynamic so that when you add a new task it automatically updates to the table.

• First, we select the following headers in the dataset.

• Then select cell B5 and go to the Home Now select Conditional Formatting from the Styles tab and select New Rule from the drop-drop down list.

• Next, in the New Formatting Rule box, select Format only cells that contain in the Select a Rule Type Then select cell value as not equal to and select a blank cell for the value. Once you are done here, click on Format.

• Now, go to the Border tab on the Format Cells box and select Outline border as shown in the figure.

• After that click on OK in both the boxes.
• Now go to the Home tab with the formatted cell selected and select Format Painter.

• After that, select an area where you want to keep the dataset.
• Let’s name the spreadsheet AllTaskList. And then create a new one with the name Tables.
• Create different values for categories, urgencies, importances, and the status here.

• Now go back to the AllTaskList sheet and select cell D5.
• Then go to the Data tab and select Data Validation from the Data Tools group.

• As a result, the Data Validation box will pop up. Now go to the Settings tab and select List under Allow.

• Then select the Source field and then select the different values from the Tables

• After that click on OK in the Data Validation
• Consequently, a list box will be added with the categories in the selected cell. Select the appropriate category for it.

• Similarly, add list boxes under the other columns with the help of the other sheet. And then select their values.

• Now fill out all the tasks in the dataset. At this point, the formats will be automatically added and copy the lists created by Data Validation for the next rows. The completed list would look something like this.

You can add more tasks later on too.

• Finally, select columns B:G, and in the name box, write the name of the dataset, Let’s say, Tasklist.

Move on to the next steps now to get more organized data from this dataset.

Read More: How to Create a Daily Task Sheet in Excel

Step 2: Creating New Sheet to Track Pending Tasks from Dataset

Now we need to organize the incomplete tasks, maybe based on the categories or other parameters you want. We will need the usage of the FILTER function and depending on how you want it the SORT function.

• First, create a new spreadsheet for this purpose. And let’s name it Pending Tasks. Put down the headers like the original dataset here.

• Now select cell B5 and write down the following formula.

• Now press Enter on your keyboard. As a result, you will have all the pending tasks here in this spreadsheet.

• Generally, it will be sorted as a serial number. If you want it to be sorted by other parameters first you need the SORT function. For a sort by category use the following formula in cell B5 instead.

• And then press Enter on your keyboard. You will have the pending tasks sorted by category.

Read More: How to Create a Progress Tracker in Excel

Step 3: Creating Pending Tasks Using Different Parameters

If you want all the pending tasks of a specific category or all the urgent pending tasks. Or Important/Not important tasks maybe. To get those in a new spreadsheet, follow these steps.

• First, create a new sheet and create all the headers in it. Let’s name the sheet Pending Tasks by Category.

• Now, select cell D4 and go to the Data
• Under the Data Tools group, select Data Validation.

• Next, in the Data Validation box select List under Allow in the Settings tab.

• Now select different category values from the Tables sheet and press Enter. Then click on OK.

• Select a category from it.
• After that, select cell B7 and write down the following formula.

• Now press Enter. You will see all the values with the category you have selected.

Similarly, you can change the category in cell D4 to see the pending tasks of other categories. Henceforth, the list will update automatically.

Similarly, you can create pending tasks by urgency and importance of those tasks.

Read More: How to Create Real Time Tracker in Excel

Step 4: Building Task Details List

Now we will create a spreadsheet to know the details of one particular task we want. Follow these steps to see how to create such spreadsheets. We will accomplish that with the help of the VLOOKUP function and in the final case the IF function.

• First of all, create a new spreadsheet, Let’s name it Task Details.
• Then select the details and the orientation of the chart containing your details.

• Now enter a serial no in cell C4.
• Then go to cell C6 and type:

• After that, go to cell C7 and write down the following formula. Now press Enter.

• Similarly, go to cell C8 and write down the following formula. Then press Enter.

• In a similar fashion, go to cell C9 and write down the following formula. And then press Enter.

• Then go to cell C10 and write down the following formula. And then press Enter.

• Finally, go to cell C11, write down the following formula and then press Enter.

As a result, you can see the task details from the spreadsheet easily.

If you change the serial no in cell C4, you can see the particulars of that specific task.

Step 5: Generating Dynamic Report on Task Tracker

Now on to the next part to create a summary of the original dataset and visualizing it with graphs for the complete and incomplete tasks. We are going to use a formula for counting that and add some graphs using the Excel tools here. The COUNTIFS and SUM functions will come in handy in this step.

• First, create a new spreadsheet. Let’s name it Summary.
• Then create a dataset to separate complete and incomplete tasks for different categories.

• Now go the cell C5 in the spreadsheet and write down the following formula and then press Enter.

• Now select the cell again. And then click and drag the fill handle icon down to fill up the formula for the rest of the categories.

• After that, click and drag the fill handle icon to the right to fill up the cells of the next column with the same formula.

• Now write the following formula in cell E5. And then press Enter.

• Again, select the cell and click and drag the fill handle icon down.

• Similarly, select the cell again and click and drag the fill handle icon down to the end of the column.

• In a similar fashion and the same formula, find the total tasks done and not done in cells C9 and D9.

• And after some formatting, the final summary will look like this.

• Next, select the range B5:E9 and go to the Insert In the tab, select Recommended Chart.

• As a result, the Insert Chart box will open up. Now, go to the All Charts tab in it.
• Then select your preferred chart. We are using a column chart for the demonstration. To do that, select Column from the left and choose the chart from the right as shown in the picture below.

• After that, click on OK. As a result, the column chart will pop up.

• Finally, the chart will look something like this after some modifications.

• To insert a pie chart for the completed tasks select the category column and the “Done” column in the dataset.

• Now go to the Insert tab and select Recommended Chart from the Charts

• After that, in the Insert Chart box that popped up, go to the All Charts tab and select Pie from the left side of the box. Select the pie chart you want from the right.

• Finally, click on OK and you will have the pie chart emerge on top of the spreadsheet. If you change the chart style, it will look something like this.

• Then again, you can follow the same procedure after selecting the “Category” column and the “Not Done” column and get a pie chart for the incomplete tasks.

• Finally, rearrange the charts with the dataset so that it looks pleasing and you can easily get the idea with the dataset about your task tracker.

If you change anything in the original dataset created in the first step, all of the data in other sheets and the charts will update automatically now. With these simple ways, you can create a task tracker in Excel.

Download Template

Download the task tracker created in this example from the link below. You can already use it as your task tracker or use it as a reference for the steps described below.

Conclusion

This was a step-by-step process to create a task tracker in Excel. Hope you found this guide helpful and informative. If you have any questions or suggestions, let us know below.

Related Articles

• How to Keep Track of Customer Payments in Excel
• How to Keep Track of Customer Orders in Excel
• How to Keep Track of Invoices and Payments in Excel
• How to Keep Track of Clients in Excel
• How to Make a Sales Tracker in Excel

<< Go Back to Create a Tracker in Excel | Tracker in Excel | Excel Templates