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

Step 1 – Making a Dataset for a Task Tracker in Excel

• Insert the following headers in the dataset.

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

• In the New Formatting Rule box, select Format only cells that contain in the Select a Rule Type.
• Select Cell value as not equal to and select a blank cell for the value.
• Click on Format.

• Go to the Border tab on the Format Cells box and select the Outline border.

• Click on OK in both boxes.
• Go to the Home tab with the formatted cell selected and select Format Painter.

• Select an area where you want to keep the dataset.
• Let’s name the spreadsheet AllTaskList. Create a new one with the name Tables.
• Create different values for categories, urgencies, importance, and the status here.

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

• The Data Validation box will pop up. Go to the Settings tab and select List under Allow.

• Select the Source field and then select the different values from the Tables.

• Click on OK in the Data Validation
• A list box will be added with the categories in the selected cell. Select the appropriate category for it.

• Add list boxes under the other columns with the help of the other sheet.
• Insert values.

• Fill out all the tasks in the dataset. The formats will be automatically added if you copy the lists created by Data Validation for the next rows.

• Select columns B:G, and in the name box, write the name of the dataset, We put Tasklist.

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

Step 2 – Creating a New Sheet to Track Pending Tasks from the Dataset

• Create a new spreadsheet and name it Pending Tasks.
• Copy over the headers from the first sheet.

• Select cell B5 and insert the following formula.

• Press Enter on your keyboard.

• Generally, the list will be sorted as a serial number.
• For a sort by category, use the following formula in cell B5 instead.

Read More: How to Create a Progress Tracker in Excel

Step 3 – Creating Pending Tasks Using Different Parameters

• Create a new sheet and create all the headers in it. Name the sheet Pending Tasks by Category.

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

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

• Select different category values from the Tables sheet and press Enter.
• Click on OK.

• Select a category from it.
• Select cell B7 and insert the following formula.

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

• You can change the category in cell D4 to see the pending tasks of other categories.

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

Read More: How to Create Real Time Tracker in Excel

Step 4 – Building the Task Details List

• Create a new spreadsheet and name it Task Details.
• Insert the fields of a task in a column and format them.

• Enter a serial number in cell C4.
• Go to cell C6 and use:

• Go to cell C7 and insert the following formula, then press Enter.

• Go to cell C8 and apply the following formula:

• Go to cell C9 and apply the following formula:

• Go to cell C10 and apply the following formula:

• Go to cell C11, then apply the following formula:

• You can see the task details from the spreadsheet.

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

Step 5 – Generating a Dynamic Report on the Task Tracker

• Create a new spreadsheet and name it Summary.
• Create a dataset to separate complete and incomplete tasks for different categories.

• Go the cell C5 in the spreadsheet and insert the following formula, then press Enter.

• Select the cell again.
• Click and drag the fill handle icon down to fill up the formula for the rest of the categories.

• Click and drag the fill handle icon to the right to fill up the cells of the next column with the same formula.

• Use the following formula in cell E5.

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

• Find the total tasks done and not done in cells C9 and D9.

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

• Select the range B5:E9 and go to Insert.
• Select Recommended Chart.

• The Insert Chart box will open. Go to the All Charts tab in it.
• Select your preferred chart. We are using a column chart for the demonstration, so select Column from the left and choose the chart from the right as shown in the picture below.

• Click on OK. The column chart will pop up.

• Click on the Chart and you’ll get the Design tab on the ribbon, where you can apply formatting and modifications.

• Select the category column and the “Done” column in the dataset.

• Go to the Insert tab and select Recommended Chart.

• Go to the All Charts tab and select Pie from the left side of the box.
• Select the type of the pie chart you want from the right.

• Click on OK and you will get a pie chart on top of the spreadsheet. If you change the chart style, it will look something like this.

• Follow the same procedure after selecting the “Category” column and the “Not Done” column and get a pie chart for the incomplete tasks.

• Rearrange the charts inside the dataset.

Download the Template

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