Intro

Hey, how is it going? I'm an engineer in a tech company. We've recently finished a project involving 4 engineers for 4 months. To track our progress we were using a custom project tracker in Google Spreadsheet. It worked great for us, so I want to share how the tracker works and what problems it helped us solve. Maybe you can use a similar approach in your next project.

What problem we were solving

We were looking for a project tracking tool which would

1. Let us plan work for a few engineers over multiple weeks, taking multiple external events into consideration (days off, oncall, code freeze, other projects) and allow balancing work load between engineers.

2. Let us see project status at a glance. It should be easy to answer questions like ‘who is working on workstream X?’, ‘are we on track?’, ‘what have we completed in last few weeks?’, ‘when will we deliver feature X?’, ‘when do we plan to be code complete/in QA/launch?’.

3. Be flexible enough to support ad-hoc manipulations, e.g. sort tasks, assign priorities, hide tasks or dates, highlight a date, highlight risky tasks, optional tasks etc.

4. Be efficient in bulk operations. It should be easy to reassign multiple tasks, adjust estimation or start/end date, split task into subtasks.

Flexibility is probably the requirement which is hardest to achieve. Ideally we wanted a tool which was closer to a whiteboard, rather than to a traditional project tracker. That's why we didn't go with Asana or MS Project or similar tools.

A spreadsheet sits nicely in the middle on this axis, so we took it and have built a custom solution on top of it.

Our tracker

The tracker fits one spreadsheet:

It has four areas:

Let’s take a closer look at them.

Tasks

Each row represents a task. A task has a name and a description, an estimation in weeks, and an owner. Owner is indicated by the initials (for example NP for Nikita Popov). Tasks are grouped into workstreams, an arbitrary way to group tasks which helps collocate similar tasks together.

Timeline

One cell in the timeline represents one week. Vertical red line indicates the current week. If an engineer is working on a task during a week, we put their initials into the corresponding cell. For example, in the first row an engineer named B.O. is working on a task estimated at 4 weeks, and he is 3 weeks into it:

Workload

The numbers in green show how many tasks an engineer is working on during each week. These values are calculated automatically: the formula basically counts how many times each engineer initials appear within current week’s column. It can be 0 if an engineer is working on a different project or is on holidays. Ideally the workload should be 0.5 or 1. If you plan 2 full-week tasks for the same engineer, the cell turns red:

Progress

For every week we can calculate project completion progress. We assume that all tasks in the past are complete, otherwise we’d extend or move move them to a time slot in the future. Thus, to estimate progress percentage we can just divide the number of eng weeks before the current moment (to the left on the timeline) by total eng weeks:

This is better than just counting number of complete tasks because the formula takes task size (in weeks) into consideration.

In this section we also indicate holidays, milestones, release phases (QA, launch), team events (e.g. offsites).

How we used the tracker

On Tuesdays we were having engineering syncs where we’d look at the timeline together and check if we’re following the plan. If a task took longer than planned, we would extend it by 1/2 week or 1 week on the timeline, and then check if the workload for the engineer hasn’t exceeded 1. If it was above 1, the we had to push other tasks or pass to a different engineer, or deprioritise a task, or communicate delay for the launch date.

On Thursdays we were having syncs with a broader group of stakeholders. In those meetings we were sharing project status: whether we are on track or not, and project completion %.

Pros and cons

Using a custom tracker task tracker like this one comes with a few downsides compared to a specialised tool. But we believe the benefits are worth it. Let’s take a look at the pros and cons.

🔴 No integration with existing tools (Tasks, Diffs)

A task is just a line in a spreadsheet, it doesn’t have a task number, it cannot be linked to a diff. For some projects it may be a problem. We normally use tasks tool, but typically a task only has a name and brief description. There’s no discussion in the comments, it usually happens offline. If you actually don’t use tasks for collaboration, our tracker might work for you as well.

🔴 Manually updating task status

If you want to mark task as completed, you should color the cell. If you want to pass task to a new owner, or reschedule tasks, you have to edit the cells in the spreadsheet. There’s a possibility that you make a mistake. I made mistakes a few times, e.g. I moved eng weeks on the timeline to the wrong row. It turned out that these errors are easy to spot and fix once we got to task implementation. Spreadsheet editing history can be helpful.

🔴 Need to learn the conventions

Such as how we indicate task status, or how to read completion %. In our team we had one PoC who was keeping the tracker updated (me). It wasn’t hard, and it took ~10mins a week during Eng syncs.

🟢 Everybody already knows how to use it

Because everybody knows Excel. The tracker is just a google spreadsheet, it doesn’t use any scripts and the formulas are basic. Everybody already knows how to use it, how to color a cell and change the contents. It’s only a matter of learning the conventions.

🟢 Customisation possibilities

You can tailor the tracker to your project needs. You want to add task priority? No problem, just add a column:

We have actually added this column right in the middle of a meeting with stakeholders where we decided to prioritise a few features over others.

It only takes a minute to add a field to your tasks. Custom columns work well with conditional formatting (spreadsheet can set cell color based on the contents, e.g. if value = ‘High’ then set color to red) and filtering.

Or let’s say you want to hide completed tasks or weeks that passed already. No problem, just hide those rows or columns.

You want to highlight risky tasks, or tasks with dependencies on other teams? No worries, spreadsheet’s color palette is at your disposal:

🟢 Productivity

If you need to set a field for many tasks (e.g. PoC or priority), just use the magic corner. It’s fast and everybody knows how to do this. Same for timeline: if you need to extend your project by a few weeks, just extend calendar to the right.

Outcome

The tracker helped us greatly, it was easy to track project status both within engineering team and with a wider group of XFNs. It also helped us take action early: when we realised we won't be able to complete the project with current scope before code freeze, so we had to re-prioritise a few parts.
Please share your thoughts
Do you think this can work for your project? Do you know a better way? What tracker are you using? Please share in the comments!

Link to tracker template:
https://docs.google.com/spreadsheets/d/1wvjITX06ceToX1dFNpx0iYasUEiQd4mqXJ3f_RerzDI/edit?usp=sharing

P.S.: More automation with Apps Script

In our tracker we had to update current week indication (red vertical line) manually. It can be automated, a script will be just a few lines. Here’s an example of a similar script which I used for my personal project, can be an inspiration:

Automating Google Spreadsheets with a script turned out to be straightforward, script are written in JavaScript (not Visual Basic fortunately). It opens possibilities for automating more flows.