Once your freelance career has been going for a while, you may find you’re working on several different projects at the same time. You don’t even have to be that busy – projects drift just because you’re waiting for briefing, source materials or feedback, leaving you with several on the go at once. So it’s useful to have a way of keeping track of everything.

There are lots of fancy solutions for managing projects and customer relationships, and I’ve tried a couple, but in the end I’ve settled on a simple Excel spreadsheet that does the job for me. It sits on my desktop (or, latterly, my Dropbox folder) and I open, consult and update it several times a day.

Since I’ve found it so useful, I thought I’d share it. (Don’t worry if you don’t use Excel that much – it’s pretty simple.)

Here’s how it looks:

And here are the download links:

• Old-fashioned Excel 97–2004 workbook (.xls file extension)
• Modern Excel workbook (.xlsx file extension).

Each line in the sheet represents a different project. As you can see, I use blank lines between each one for legibility.

Here’s a brief explanation of the different fields for each project, column by column. (I’ve entered some ficititious content for illustration.)

Column A
Client

The name of the client.

Column B
End client

The name of the end client if the main client is an agency.

If you’re working for several different end clients through the same agency, you can group them together and differentiate between them using this column.

Column C
Project

The name of the project. Give it a name that will make sense to you (not necessarily the one the client uses).

Column D
Status

This indicates where you are with the project.

I have certain stock entries that I use over and over again, such as:

• ‘Start now’
• ‘Waiting to hear re price’
• ‘First/second draft in for comments’.

I also use colour-coded highlighting so I can see at a glance what is going on, as follows.

Blue indicates a ‘cold’ project that is yet to start – most likely because I don’t have enough information to quote, my price/proposal is being considered or I need more information from the client. In the example, I need to set up an interview with someone at CoffeeCo before I can start writing their site, and I’m also waiting to hear about the price for the InterPlan corporate profile.

Yellow denotes a task that requires immediate action – for me, that’s usually either writing or amending the text. If it’s obvious to me what needs doing, I just put ‘Do now’ or ‘Start now’, but you can also enter more detailed content here if there are special instructions you need to remember. In the example content entered, I need to continue working on the ‘Birds of Britain book’ project this week.

However, I don’t actually use this as my ‘to do’ list. When something turns yellow here, I enter it as a ‘to do’ in BusyCal, which I strongly recommend as a way to track jobs and appointments if you’re on Mac. It’s basically a turbocharged iCal. Once a task is ticked off in BusyCal, it usually turns orange on the spreadsheet (see below).

Orange indicates a live project that is stalled or waiting for some reason – for me, that’s probably because I’ve submitted copy that the client is considering. In the example, I’ve submitted a draft for the FabFrame press release (for WindowCo via the Direct Digital agency) and I’m waiting for feedback on it.

Finally, green indicates a project that is complete, as far as I know. This usually says ‘Invoice?’, which means I need to confirm that I can indeed invoice, or something like ‘Asked to invoice 10/7’ which reminds me that I’ve asked the client to confirm that the project is complete on a particular day.

Once I’ve invoiced a project, I delete it from this sheet. Alternatively, you could have another colour for ‘invoiced’ and only delete the job once the invoice had been paid. Personally, I keep all those financial things in a different mental ‘box’ from my day-to-day work, so it makes sense to use a separate bookkeeping spreadsheet to manage them.

Instead of just deleting the project, you could also transfer it to another spreadsheet of completed projects. This would create a ‘diary’ of your work and allow you to get into next-level wonkery like calculating lifetime value for individual clients, but personally I don’t bother.

Column E
PO No

If your client uses purchase-order numbers, enter the number for the project here when you receive it, so you have it to hand when preparing the invoice at the end of the job. You could have a column for your own job number too, if you have them.

Column F
Deadline

Most of my work is just ‘as soon as possible’ for everything, but if you work to specific deadlines, you can enter them here.

If you’ve added a date for all the projects on the sheet, you can sort them by this column, using ‘oldest to newest’, to rank the projects in order of priority.

Columns G–J
Rates and prices

Use these columns to enter the prices you’ve quoted or agreed for your work.

I enter prices I’ve quoted or proposed in column I, ‘Quoted price’, and move them into Column J, ‘Confirmed price’, once the client has given me the go-ahead. This makes it crystal clear whether the price has been agreed or not.

If you’re working with flat fees, simply enter the amount straight into column I or J.

If you’re using a rate per day, hour or item, you can put the number of units in Column G and the rate in Column H, then enter a simple formula in Column I or J to get the result. In the example, I’m charging £10 per hour to edit Birds of Britain, and I’ve quoted for 12 hours. By entering ‘=G8*H8’ (without the quotes) in cell J8, I get the resulting total, which will update if I change the number of hours or the rate.

You can also enter automatically calculated fomulae to give totals for columns I or J, as I’ve done in cell J14.

A total on column J will tell you how much money you’ve got coming in, but to be honest, it’s only marginally useful. In my experience, project timescales are so unpredictable that all you’re really seeing is a theoretical amount that you should receive at some point over the next few months, rather than a solid, timed cash-flow prediction. Still, it can still be reassuring to see a decent amount here, even if there’s a long way to go until you actually receive it.

A total on column I shows how much you could get if all your quoted projects go ahead, but this number is so nebulous that it’s hardly worth calculating.

Column L
Notes

Obviously, use this column to record any special information that doesn’t fit anywhere else. I use this most frequently when I’ve agreed to part-invoice a job, and I need to record how much has been invoiced and how much is left.

So there you have it – a simple, flexible spreadsheet for keeping track of your freelance projects. I hope you find it as useful as I have.

Tags: Excel, freelancers, project management, spreadsheet, xls, xlsx