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 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.
- 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.
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.
- 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.
🔍 Breakdown of the Formula
👉 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))
- 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.
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:
- 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.
=IF(VLOOKUP($C$4,Tasklist,6)="Done","Completed","Work in Progress")
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.
🔍 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.
- 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.
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.
- How to Create Leave Tracker in Excel (Download Free Template)
- Maintain Store Inventory in Excel (Step by Step Guide)
- How to Track Stocks in Excel (Download Free Template)
- Track Project Progress in Excel (Download Free Template)
- How to Track Multiple Projects in Excel (Download Free Template)
- How to Keep Track of Customer Payments in Excel (With Easy Steps)