GridWorkshop
Guide

How to set up a simple project plan in Excel or Google Sheets

Published by GridWorkshop. About 5 minutes to read.

You do not need project management software to run a small project. A single sheet that lists what has to be done, who is doing it and by when will carry most projects from start to finish. This guide shows how to set one up in Excel or Google Sheets, including a simple timeline bar chart built from ordinary cells.

Step 1: write down the goal and the end date

At the top of the sheet, write one sentence that says what "finished" means, and the date it has to be finished by. For example: "New website live and taking orders by 30 June." Every task you add later should clearly help reach that sentence. If it does not, it belongs to a different project.

Step 2: break the work into tasks

List everything that has to happen. Then tidy the list:

Twenty to forty tasks is plenty for a small project. If you have two hundred, you are planning in too much detail too early.

Step 3: set up the columns

ColumnWhat goes in it
A: IDA simple number, so tasks can refer to each other
B: TaskWhat has to be done
C: OwnerOne named person. Shared tasks tend not to get done
D: StartThe date work begins
E: Working daysHow long it should take
F: EndWorked out by a formula
G: Depends onThe ID of any task that must finish first
H: StatusNot started, In progress, Done or Blocked

Make Status a dropdown list using Data validation, so everyone uses the same four words.

Step 4: let formulas work out the dates

The WORKDAY function adds working days to a date and skips weekends. It exists in both Excel and Google Sheets. For a task that starts on the date in D2 and takes the number of working days in E2:

=WORKDAY(D2,E2-1)

The "minus 1" is there because the start day counts as the first day of work. A one day task that starts on Monday also ends on Monday.

When a task cannot begin until another one finishes, set its start date with a formula instead of typing it. If task 3 depends on task 2, the start of task 3 is the next working day after task 2 ends:

=WORKDAY(F2,1)

Now if task 2 slips, task 3 and everything after it move by themselves. To skip public holidays too, list the dates in a spare range and add it as the third argument: =WORKDAY(D2,E2-1,Holidays!A:A).

Step 5: add a simple timeline

A bar chart of tasks against dates, often called a Gantt chart, can be made with conditional formatting and no chart tools at all.

  1. In row 1, starting in column J, enter a run of dates. Put the first date in J1, then =J1+7 in K1 and fill to the right for a weekly view.
  2. Select the empty grid under those dates, from J2 down to your last task and across to your last date.
  3. Add a conditional formatting rule that uses a custom formula:
    =AND(J$1<=$F2,J$1+6>=$D2)
  4. Choose a fill colour and apply it.

Each cell now colours itself when its week overlaps the task's start and end dates, giving you a bar for every task. The dollar signs matter: J$1 keeps the rule looking at the date row, and $D2 and $F2 keep it looking at the start and end columns. For a daily view, use =J1+1 in the date row and the rule =AND(J$1>=$D2,J$1<=$F2).

Step 6: mark the milestones

A milestone is a moment, not a piece of work: "Design approved", "Stock arrives", "Launch". Add milestones as rows with a single date and zero effort, and make them bold. Three to six is enough. They are what you report on when someone asks how the project is going.

Step 7: show what is late

Add a second conditional formatting rule to the task rows so that overdue tasks stand out:

=AND($F2<TODAY(),$H2<>"Done")

Anything past its end date and not marked Done turns the colour you choose. A quick count of progress is also useful: =COUNTIF(H:H,"Done")/COUNTA(B2:B100) gives the share of tasks completed.

Step 8: keep it alive

A plan is only useful if it matches reality. Pick a fixed time each week, ten minutes is enough, and do three things:

  1. Update the status of every task that was meant to move.
  2. Change the dates of anything that has slipped, and see what that pushes.
  3. Look two weeks ahead and check each task has an owner who knows it is coming.

If the end date moves, say so early. A plan that honestly shows a two week delay is more use to everyone than one that still shows the original date.

Common mistakes

Things that could go wrong deserve their own list. See how to use a risk log for a simple way to keep one alongside the plan.

If you would rather start from a finished workbook, the Project Management Pack has the task list, timeline, dashboard and risk log already built for Excel and Google Sheets. You can buy it on Gumroad.

Keep reading