If you're working on an Excel project with a client, or other employees in your company, how do you keep track of everything that needs to be done? If emails are flying back and forth, with questions, and comments, and specification changes, it's easy for things to slip through the cracks. Then, 5 minutes before the deadline, someone realizes that a key piece is missing!
Nobody wants that to happen, so it helps to have a system for keeping track of everything that has to be done.
Task List in Excel
For projects that require more than a couple of simple changes, I usually create a task list in Excel. It's nothing too fancy – just a list of what needs to be done, broken into steps.
- if the project requires changes to several files, there are columns to record the file and sheet names.
- task types can be entered, so the time estimates can be grouped by those categories
- task description – as detailed as necessary – break large tasks into separate steps
- time estimate, in minutes, to get a rough idea of how long the project will take
You can see a few more columns, in the next screen shot.
- person the task is assigned to
- date the task was completed
- actual time to complete the task
- notes or questions about the task
- person who should answer the question
- replies to the questions
For other projects, you might need additional information, such as:
- task due date
- reference number
What else would you need to record for each task?
Drop Down Lists
To make it easier to enter the tasks, I use data validation to create drop down lists in some columns.
The source lists are stored on a separate sheet, in named Excel Tables.
Total the Task Times
At the top of the task sheet, there are cells that summarize the estimated and actual task times. That gives you a quick overview, while you enter the tasks, or update them with actual times.
The times are entered as minutes, so the formula sums all the times, then divides by 60
=SUM(tblTasks[Time Est Minutes])/60
Project Task Summaries
After the tasks are entered, you can create a pivot table or two, to show the estimated and actual times. The times can be summarized per person, or per task category, or any other grouping that you'd like.
Share the Task List
I don't like setting up shared Excel workbooks, but I've found that it works well to upload a file to Microsoft's OneDrive site, and everyone can edit the task list there, using Excel Online. The only strange thing that I've noticed is that after you select from a drop down list, the cell below is selected, instead of staying in the same cell.
After you upload the file, use the Share options, to send a link to all the interested parties. I save the link as a bookmark in my browser, so it's easy to get back to the file again.
You can download a copy of your task file, after you make changes, to store as a local backup. Or, go back to look at previous versions of the file.
Download the Project Task Tracker Sample
You can download a copy of my project task tracker file, from my Contextures website. On the Sample Files page, go to the Data Validation section, and look for DV0069 – Excel Project Task Tracker.
The zipped file is in xlsx format, and does not contain macros.