Here’s my job tracking tool. Grab a free copy.

March 11, 2016 • Glenn Murray

I’m a little obsessive. It manifests in a lot of ways, but religiously tracking stuff is one of the big ones. The distances I walk, and how long it takes me. My weight. The aisles things in my shopping list are in. How long since my son broke his leg. How long since I last snapped at the kids. The duration of the ad break vs program content on My Kitchen Rules…

All this data has a purpose. Even if, in some cases, it’s simply to make me feel in control when I’m not.

But there’s one tracking obsession that I can honestly say is absolutely critical to my life and work: job tracking.

As a freelance copywriter, I usually have around 20 jobs going at any one time. Without good job tracking, I forget where each is up to, and it’s almost impossible to estimate turnaround times and schedule new work. Things slip through the cracks and I miss deadlines.

So a good job tracking system is a must. Not just for me, but for any freelance copywriter.

Job tracking tools I’ve tried (and would like to create)

I’ve tried just about every project management / job tracking system out there. The popular and the obscure. Big and small. Paid and free. Local and cloud. I stuck with a few of them for a while. Toggl for instance. And Liquid Planner. And Slack. And Asana. And clunky old Access and ACT! But they all seemed a square peg in a round hole. They did either too much or too little, or they were just more work than they were worth.

I even wireframed my own (lemme know if you’d like to build it!):

A couple of screens from the awesome job tracking tool I wireframed

The job tracking tool I use now

But in the absence of a working version of my dream tool above, I keep coming back to the trusty old spreadsheet. (Not Excel, though; Microsoft is pretty much dead to me. I use Google Sheets, these days.)

Of course, spreadsheets aren’t perfect. Not by a long shot. In fact, they have so many failings I’m not even going to start on them here. But they’re REALLY good at what they ARE good at. And for me, that’s capturing, displaying and manipulating a small amount of predictable data in one bite-sized chunk. Something that gives me high level info at a glance (e.g. job status), but also lower level detail if I want it, without drill down through multiple windows (e.g. comments).

Now before you look at it, don’t get too excited. I’m no spreadsheet expert, I’m not doing anything particularly tricky, and I’m not even close to pushing the limits of what a spreadsheet can do. There’s nothing here you couldn’t do yourself, and you may well already be doing more, or doing it in a more sophisticated way. I’m sharing, not because it’s a ground-breaking tool, but because it’s the ONLY tool that’s ever worked for me.

So here it is. As I said, it’s a Google Sheets spreadsheet. You don’t need a Google account to access it, but I’m pretty sure you’ll need one to make a copy of it. And you WILL need to make a copy of it to use it, because I have the permissions set so that you can only view it (not comment or change).

Screenshot of my copywriting jobs spreadsheet. Click to see the real thing and make a free copy.

If you like the look of it, and you’d like to try it out to track your jobs, you’ll need to make a copy of it (File > Make a copy), and save it to your Google Drive (File > Organise).

If you don’t have a Google account or don’t use Google Drive, you can save it as an Excel Spreadsheet (File > Download as > Microsoft Excel (.xlsx)). Note, however, that if you do that, you’ll lose one of the big benefits – being able to view and update the spreadsheet on any device. I find this portability incredibly helpful, because, no matter where I am, I can stay on top of my projects.

Some comments and explanations

• Obviously the spreadsheet is full of dummy data. (Except the bits about Kate and Belinda. Those ladies are out of control.)
• I’ve set the Deposit and Balance columns to auto-calculate based on the assumption that you charge a 50% first instalment. (That’s what I charge.) To change this, just change the formula in the cells.
• You don’t need to enter a dollar sign or commas in any of the value columns; they auto-format. However, obviously if you use a currency other than dollars, you’ll need to change the format (Format > Number).
• I’ve set up conditional formatting to automatically colour the first two cells if the job requires attention, depending on what I enter in the Status column:
  • If you enter “Proposal” (without the quotation marks), the first two cells of the row will be filled turquoise. I use this to highlight jobs that I need to create a proposal for.
  • “Invoice” and “Questionnaire” both turn the first two cells turquoise too. The former to remind me I need to invoice the client, the latter to highlight the jobs I need to create a questionnaire for.
  • “Me” turns the first two cells yellow, to tell me at a glance that the ball’s in my court, and I need to get the job done. The client’s waiting!
  • “Freelancer” turns the first two cells a salmon sort of colour, to tell me the job is with a freelancer (e.g. a designer). So I can’t completely forget about it, but I don’t need to actually do any work on it, other than keep tabs.
  • “Deposit” and “Pay” both turn the first two cells red, to tell me the client owes me money.
  • “Review” and “Info” don’t change the colour at all. “Review” tells me the job is in review with the client. “Info” tells me I’m waiting on information from the client. Neither changes the colour because I don’t have to pay close attention to these jobs. The ball’s in the client’s court.
• Once you’ve saved a copy of the spreadsheet, you can change the auto-formatting to suit whatever milestones/labels/formatting you prefer. Just select the first two columns, then click Format > Conditional formatting.
• I’ve added a total dollars row at the bottom. It totals the Value column to tell me how much all my current jobs are worth, and also the Balance column to tell me how much money is still to come in.
• After a job is complete and the client pays their balance, I just delete the row.

Lemme know your thoughts

That’s it. As I said, it’s pretty simple, but it does the job, and does it efficiently and without fuss. Which means I can do the same.

Enjoy. And lemme know if you have any questions.