How to create a task tracker in excel

Being able to track and organise your tasks is critical to running an efficient operation. This is especially important if you have multiple people in your company – you want to be able to see what’s going on and who’s doing what.

That’s where having a master list of tasks that is constantly being updated that everyone has access to is mission critical.

At some point your memory and post-it notes aren’t going to cut it. But building a simple task tracker in excel isn’t a daunting task, we’re going to go through it in a few simple steps and get you up and running in no time.

We’ll also suggest something better than Excel at the end of this, but if you want to skip ahead then click here.

Step 1: Creating the tables sheet

Open Excel and rename “Sheet 1” to “Tables”. Renaming sheets is pretty simple just right click on the sheet and then click ‘rename” and enter the new name.

Here we’re going to create several tables which you’ll use to create drop down menus to input repeated data such as the name of a task assignee when creating a task.

This is to simply save you time.

In this renamed sheet create the following tables:

You can fill in the information in the tables with data more relevant to you – it would be remarkable if you had a team member with the name Oliver Twist but stranger things have happened.

Step 2: Create the tasks sheet

Now add a new sheet, to do so simply click the little plus button next to the “Tables” sheet and rename it “TaskList” or whatever you want.

The “TaskList” is where you’ll add your new tasks to and be able to filter tasks by some attribute such as Assignee or Priority, recall the tables we created back in step one.

So, the first job is to create the following headings on the top row of the sheet:

*Task Type:*: Refer back the table in step one, what kind of task is it? This will be a drop down menu option Task Description: Details about what the task is Assignee: Who is carrying out the task Date Assigned: When was the task created Due Date: When should the task be completed Priority: Refer back to the table in step one, how important is the task? Current Status: What’s happening with the task right now? Completed Date: The date that the task is finally completed Status: Tells you whether the task is over due or not Overdue: Tells you by how many days the task is overdue Comments: Any extra information related to the task

It should end up looking something like this:

Step 3: Creating the drop down menus

Data entry is boring so whenever you can save time but cutting down on it do so.

Certain things you’re going to enter again and again when creating new tasks such as an assignee’s name or the priority of the task, instead of typing it out again and again its better to just have it as a drop down menu you can select every time.

So we’ll run through an example by creating a drop down menu for all the cells in the column “Task Type”.

If you look back at the tables sheet, and at the table Task type you’ll notice the options run from column D, row 5 or D5 all the way down to column D, row 13 or D13. We’ve left extra blank options in case you want more people.

But the important part is that you want the options D5 to D13 in the sheet “Tables” to appear under the ‘Task Type’ column as a drop down menu in each of its cells. See the screenshot below and it becomes clear:

That’s the background logic, now here’s how to do it:

1. Select a bunch of cells under the heading ‘Task Type’ and then click the ribbon at the top called “Data”
2. Now click the “Data Validation” button, a new dialog box will open.
3. Under the “Allow” heading click the drop down menu and select ‘List’ as we want a list of options.
4. Next, we want to enter the following in the source box: =Tables!$D$5:$D$13. What that means is that it’s looking in the sheet called “Tables” and the cells D5 to D13 to populate the list. Another way of entering the source is clicking the arrow on that input box then clicking over to the “Tables” sheet and highlighting the cells you want, you’ll see it in the GIF below but it basically does the same thing; tell excel where to look for the list to populate your newly created drop down menu.
5. Click ‘OK’ and you’re done and then you’ll see a little drop down button appear on the cells under “Task Type” and you can select the relevant option.

Once you’ve created the drop down menu for the “Task Type” column follow the above procedure for the “Assignee”, “Priority” and “Current Status” columns. The only thing different will be the source as obviously the table options will be in different cells, for example look back at the “Tables” screenshot above the “Priority” table options are from H5 to H13 so the source would be =Tables!$H$5:$H$13.

Step 4: How to automatically know if a Task is overdue and by how many days

The next bit of set up work is to make sure that the “TaskList” sheet tells you when a Task is nearing its due date or has gone overdue and by how many days.

For this we’re going to need to enter some IF statements.

So, the first IF statement we’ll write is for the column “Overdue”. This will tell us how many days a Task is overdue by. If the number is negative for example -10, that simply means there are 10 days left until the task is due.

So the IF statement that’ll go into the first empty cell in the Column “Overdue” is: =IF(LEN(E2)=0,"",IF(LEN(H2)=0,TODAY()-E2,""))

Then all you need to do is drag and copy that down to all the cells in the Column “Overdue”.

But here is a brief summary of what the IF statement means in English.

If there’s nothing written in cell E2 “Closure Date” then leave this cell empty. If cell H2 (Completed Date) is empty then display the result of today’s date minus the due date of the task.

So if the result is zero that means the task is due today, if it’s greater than zero for example three it means its three days overdue and if its a negative number that just means how many days are left before it is actually due.

So, the next IF statement we’ll write is for the column “Status”.

The IF statement that’ll go into the first empty cell is =IF(OR(LEN(A2)=0,LEN(H2)<>0),"",IF(J2<0,"Still Time",IF(J2=0,"Due Today",IF(J2>0,"Overdue"))))

Then all you do is drag and copy that down to all the cells in Column “Status”.

But here is a brief summary of what the IF statement means in English.

If the string length of cell A2 (Task Type) is zero i.e. it’s empty and nothing is written in it or cell H2 (Completed Date) has something written in it then leave this cell blank.

If the cell J2 (Overdue) is less than zero then display “Still Time”, if it’s equal to zero then display “Today” and if it’s greater than zero then display “Overdue”.

And that’s it, you’ll now be informed automatically if a task is overdue. When you add a completed date both cells will go blank as it is now no longer due, the task has been completed.

Step 5: Filtering Tasks

Pretty soon you’ll realise you have a lot of tasks and the spreadsheet will end up unwieldy and a real pain in the butt to look at.

This is why being able to filter is important, for example I just want to look at my own tasks and no one else’s.

Fortunately this is really simple to do. Just highlight the first row with all your headings and then click on the ribbon above ‘Data’ and then click the ‘Filter’ button.

That’s it and you’ll notice that drop down buttons appear on your heading cells which will allow you to filter a task by an assignee for example.

The better way of managing tasks

Above is how to manage tasks in a very basic excel spreadsheet but keeping up to date and version control is going to be a pain plus it’s nowhere near as flexible and intuitive as using a dedicated CRM to track your tasks.

This is why we recommend RealtimeCRM, creating and updating tasks is really quick and simple. Plus, let’s say you have multiple opportunities that require the same task, instead of creating the task over and over again in RealtimeCRM you can simply create the task once and apply it to all of the relevant Opportunities.

You can also tag your tasks on the fly and filter by them too which is a less cumbersome than the spreadsheet method above. Plus you can sync your tasks to your calendar in a few easy steps, check out how here