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

Get FREE Advanced Excel Exercises with Solutions!

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.


Watch Video – Create a Task Tracker 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.

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.

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.

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

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.

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

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:
=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: 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.

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


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


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

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.
Abrar-ur-Rahman Niloy
Abrar-ur-Rahman Niloy

Abrar-ur-Rahman Niloy, holding a B.Sc. in Naval Architecture and Marine Engineering, has contributed to Exceldemy for nearly 1.5 years. As a leader in Excel, VBA, and Content Development teams, he authored 114+ articles and assisted the Exceldemy forum. Presently, as a project writer, he prioritizes stepping out of his comfort zone, aiming for constant technical improvement. Niloy's interests encompass Excel & VBA, Pivot Table, Power Query, Python, Data Analysis, and Machine Learning libraries, showcasing his commitment to diverse... Read Full Bio

10 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

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

      Regards
      ExcelDemy

  2. This is a great tutorial! I’ve been wanting to create a task tracker for a while, and this is the perfect guide. Thanks!

    • Hello, Instast!

      Thanks for your appreciation. To get more helpful content stay in touch with ExcelDemy.

      Regards
      ExcelDemy

  3. This is really amazing! Why not add a timestamp at a new column, at the very end of every table. This will help tracking the latest updates.

    Thank you very much!

    • Reply Abrar-ur-Rahman Niloy
      Abrar-ur-Rahman Niloy May 18, 2023 at 11:40 AM

      Hi Nawaf, thanks for the appreciation!

      We focused more on the checklist side of the task tracker- whether or not the task is done or yet to be done (or not) was our main focus here. Also, we wanted to rearrange and prioritize our tasks based on a criteria. So we skipped the timestamps in this article. If that is your priority, you can easily do so too by adding an extra column and including it within the dataset too.
      You can also add an assigned time and current time and compare them to find the status of the task in the main dataset. If you face any problem regarding the timestamps or want to do something particular with them, let us know. We are always here to assist.

  4. how great is the content you have delivered

  5. This is a great tutorial! I’ve been wanting to create a task tracker for a while, and this is the perfect guide. Thanks!

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo