loading...
Cover image for Project management in Google Sheets

Project management in Google Sheets

rick_viscomi profile image Rick Viscomi Updated on ・3 min read

Google Sheets can be a handy tool for project management. I wanted to create this post to share a few tricks to build a Gantt chart in Sheets.
Note: This is based on a template I found in Sheets that I modified to be more dynamic.

1. The timeline

timeline row in Sheets

You probably already know that like most spreadsheet tools, Sheets has native date support. So you can enter text like "3/4/2019" and Sheets will understand it to mean March 4, 2019 (sorry non-Americans).

What you might not know is that you can apply arithmetic operations on a date, like adding days to a date. So for our timeline, we can start with a fixed date like March 4 and dynamically create the next week's date by adding 7 to it. In the example above, March 4 is in cell E3. So the next week's date will be in F3 and we can generate it by using the formula =E3+7. Copy F3, select the rest of the row, and select Edit > Paste special > Paste formula only to generate dates for rest of the timeline.

custom date format

To get the dates to appear only as the day of the month, you can use custom date formatting. Select the timeline cells, go to Format > Number > More formats > More date and time formats..., and put only the "Day" chip in the form field.

2. The current week

current week highlighted

You'll notice in the previous screenshot that April 22 is highlighted in yellow. Today's date is April 25, so this is indicating that we're currently in the week of the 22nd.

This is done thanks to conditional formatting. The format rules include three date-related values: date is, date is before, and date is after. So Sheets can dynamically change the background color of the cell when its date value is in the past 7 days.

To create this rule, select all cells in the timeline row, go to Format > Conditional formatting..., and set the rule to "Date is in the past week". In my case, I applied bold weight and yellow background color formatting.

So every time you open the sheet, it should be obvious what the deadlines are for the current week.

3. Project grid

grid of filled cells

This is more of a quick tip, but getting the project grid to consist of squares might be tedious if you're doing each row and column manually.

To set the rows and columns to exactly the same height/width, first select the all of the columns, right click, select Resize columns..., and enter the column width in pixels. I'm using 26px. Then repeat for all of the rows. They don't need to be perfect squares, as long as all rows are the same height and all columns are the same width.

The rest of the magic is to apply border and background styles to make the cells appear like blocks in the waterfall chart. This can be tedious, but copying cells and selecting Edit > Paste special... > Paste format only can be a helpful way of repeating the same styles across many cells.

If you want to make a copy of my sheet as a starting point, you can find it here. Hope you found this useful!

Posted on by:

rick_viscomi profile

Rick Viscomi

@rick_viscomi

I work on web transparency projects like the HTTP Archive and Chrome UX Report. I work towards a faster, better web for everyone.

Discussion

pic
Editor guide
 

Why would you want to do this?
I have never seen Gantt charts work, but that might also be due to the various PMs I had to deal with. But spreadsheets abuse is already a major thorn in my eye.

"Your scientists were so preoccupied with whether they could, they didn't stop to think if they should." -- Dr Ian Malcolm

 

Gantts can be lovely, not so perfectly suited for perhaps day-by-day planning (as things can move too quickly), but for a general snapshot of a plan, especially for initial kickoffs and for presentations to a client or team. And this is a lot more legible and visually clean than a deeply detailed gantt such as gantter, smartsheet, or good ol' ms project.

 

I think they can be very useful to visually where task A ends and task B picks up. This is especially useful for visualizing which tasks can be tackled in parallel and which ones cannot. For large projects this is a great tool to refine a time estimate.

 

Use dates in ISO 8601 format see w3.org/TR/NOTE-datetime and those from the U.S. and anywhere else will understand.

 
Sloan, the sloth mascot Comment marked as low quality/non-constructive by the community View code of conduct

Wow! This is a terrible idea. Coda.io is SUCH a better solution to this "problem".

 

True, but the article as about how to maximize gsheets, not an article about the best tool for visual project planning. Lane, thanks for bringing up Coda.io! Cool box of tools!

 

Looking back I should really have been much more gracious in my initial post. So sorry for coming off like I did. "Terrible idea" isn't accurate at all for projects that are smaller in scope. Thank you for the thoughtful approach

 

I don't disagree. But, I've tried to use Sheets for project management and using a spreadsheet in a way it wasn't designed to be used has gotten me into trouble numerous times. I'm certainly all for creative use cases for things but it's valuable to know a given tool's limitations before building a business critical workflow on said tool.