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.


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.


Step-by-Step Procedure to Create a Task Tracker in Excel

Follow these 5 steps below to create a task tracker of your own in Excel. Each step is described in detail in its own sub-section.


Step 1: Make 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.

how to create a task tracker in excel

  • 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.

how to create a task tracker in excel

  • 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.

how to create a task tracker in excel

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

how to create a task tracker in excel

  • 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.

how to create a task tracker in excel

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

how to create a task tracker in excel

  • 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.

how to create a task tracker in excel

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.

how to create a task tracker in excel

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


Step 2: Create 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.

=FILTER(Tasklist,(AllTaskList!B:B<>0)*(AllTaskList!G:G="Not Done"))

how to create a task tracker in excel

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

how to create a task tracker in excel

  • 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.

=SORT(FILTER(Tasklist,(AllTaskList!B:B<>0)*(AllTaskList!G:G="Not Done")),3)

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

how to create a task tracker in excel

🔍 Breakdown of the Formula

=FILTER(Tasklist,(AllTaskList!B:B<>0)*(AllTaskList!G:G=”Not Done”))

👉 AllTaskList!B:B<>0 is a formula to express a condition and returns a boolean value.

👉 Similarly, AllTaskList!G:G=”Not Done” is a condition and returns a boolean value.

👉 FILTER(Tasklist,(AllTaskList!B:B<>0)*(AllTaskList!G:G=”Not Done”)) returns all the row values where both the above conditions were TRUE.


Step 3: Create 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.

=FILTER(Tasklist,(AllTaskList!G:G="Not Done")*(AllTaskList!D:D='Pending Tasks by Categroy'!D4))

how to create a task tracker in excel

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

how to create a task tracker in excel

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

how to create a task tracker in excel

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


Step 4: Build 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:
=C4

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

=VLOOKUP($C$4,Tasklist,2)

how to create a task tracker in excel

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

=VLOOKUP($C$4,Tasklist,3)

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

=VLOOKUP($C$4,Tasklist,4)

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

=VLOOKUP($C$4,Tasklist,5)

how to create a task tracker in excel

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

=IF(VLOOKUP($C$4,Tasklist,6)="Done","Completed","Work in Progress")

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

how to create a task tracker in excel

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

how to create a task tracker in excel

🔍 Breakdown of the Formula

=IF(VLOOKUP($C$4,Tasklist,6)=”Done”,”Completed”,”Work in Progress”)

👉 VLOOKUP($C$4,Tasklist,6) looks for the value in cell C4 in the 6th column of the original dataset named “Tasklist”.

👉 =IF(VLOOKUP($C$4,Tasklist,6)=”Done”,”Completed”,”Work in Progress”) checks if the value that came up is “Done”. If it is the value, then it prints “Completed”, otherwise goes for the value “Work in Progress”.


Step 5: Generate 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.

=COUNTIFS(AllTaskList!$D:$D,Summary!$B5,AllTaskList!$G:$G,C$4)

how to create a task tracker in excel

  • 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.

how to create a task tracker in excel

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

=SUM(C5:D5)

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

how to create a task tracker in excel

  • 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.

how to create a task tracker in excel

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

how to create a task tracker in excel

  • 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.

how to create a task tracker in excel

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

how to create a task tracker in excel

  • 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.

how to create a task tracker in excel

  • 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.

how to create a task tracker in excel

  • 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.

how to create a task tracker in excel

  • 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.

how to create a task tracker in excel

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.

Read More: Create Fully Functional To Do List in Excel (4 Handy Methods)


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. For more guides like this, visit Exceldemy.com.


Related Articles

Abrar Niloy

Abrar Niloy

Hi! my name is Abrar-ur-Rahman Niloy. I have completed B.Sc. in Naval Architecture and Marine Engineering. I have found my calling, if you like, in Data Science and Machine Learning and in pursuing so, I have realized the importance of Data Analysis. And Excel is one excel-lent tool do so. I am always trying to learn everyday, and trying to share here what I am learning.

2 Comments
  1. [Require your URGENT help.]

    When I open this Task tracker template after downloading, it shows an _xlfn. prefix, which is displayed in front of certain formulas (‘SORT’ and ‘FILTER’) in every sheet where they were used.

    I am using MS Office Home and student 2016 licensed version. I found that this error occurs when these formulas are not available, in my Office version and also in many other versions.

    Therefore, I kindly request the team to create and send me the new task tracker template which resolves this formula issue in my MS Office version. I would be earnestly waiting for your assistance.

    Regards,
    Ashish

  2. Greetings ASHISH PANT,
    I appreciate you asking this question. We use the Microsoft Office 365 version here. In order to resolve your problem, you need to open the Excel workbook in Microsoft Office 365 version.

Leave a reply

ExcelDemy
Logo