Credit: Dreamstime
Most people think of Google Sheets as a web app for creating and viewing spreadsheets for data analysis, but it can also be used for basic project management. While Sheets lacks the advanced features of full-blown task-management software, you can create a spreadsheet to track the progress you and your co-workers make on a team project.
This guide goes over the elements in Google Sheets that you can use to help with project tracking. This includes using templates designed for project management, adding dropdown menus with preset status options that you and your co-workers can update as tasks are completed; and tagging co-workers to assign tasks to them.
1. Start with a project management template
Google Sheets includes several templates for project management. You don’t have to use a template, of course, but it can give you a head start on designing your spreadsheet. Then you can customize it according to your project.
From Google Sheets: At the top of your Google Sheets home page, you’ll see a “Start a new spreadsheet” header with a row of thumbnails underneath. These thumbnails are templates. Click Template gallery to the upper right of the thumbnails, and you’ll be taken to a full page with template thumbnails.
From Google Drive: On your Drive home page, click the New button at upper left. On the menu that appears, hold your pointer over the arrow to the right of Google Sheets and select From a template. You’ll see a page with template thumbnails.
On the “Template gallery” page, scroll down until you see the “Project management” header. There are four templates to choose from: Gantt chart, Project timeline, Project tracking, and Event marketing timeline. Click a thumbnail to start a new spreadsheet using that template.
The “Project tracking” template, for example, includes areas where you can track one or more stages of a project. Within each project or stage, you can enter specific tasks necessary to complete the project, along with a status, priority, start and end date, assignee, estimated hours, and more for each task.
Howard Wen / IDG
The Project tracking template gives your team a quick leg up on keeping track of all the tasks to complete a project.
Click anywhere in the spreadsheet to start adjusting it for your own project.
To change any text field: Simply select it and start typing.
To change a date field: Type in a new date or double-click to bring up a mini-calendar where you can select a date.
To select a different option from a dropdown list: Dropdown lists have a small down arrow to the right of the cell. Double-click the cell (or click its down arrow) to see other options to choose from. Select one of the options and it will appear in the cell. (More on dropdowns in a moment.)
To delete any columns or rows you don’t need: Select the column or row, right-click, and select Delete column or Delete row.
To add a column or row: Do the same thing and select Insert 1 column left, Insert 1 column right, Insert 1 row above, or Insert 1 row below.
In this way, you can tweak the template to suit your project perfectly.
2. Create or customise dropdown lists
You can create a dropdown that lists specific numbers or words that you’ve entered as preset choices. Or, if you’re working from a template that already includes dropdown lists, you can add, edit, or remove options in any dropdown.
In a project management spreadsheet, it’s useful to have a status dropdown for each task, with options such as On Hold, Not Yet Started, In Progress, and Complete, as in the “Project Tracking” template we looked at earlier. You can enhance these dropdowns with background colours that denote status at a glance.
We won’t go into all the details of creating a dropdown list from scratch here. See our tutorial on using dropdown lists in Google Sheets for full instructions. Instead, we’ll focus on editing an existing dropdown list, including adding background colours.
To edit a dropdown list: Click the small down arrow at the right edge of the cell that contains the list, then click the pencil icon at the bottom of the menu that appears. The “Data validation rules” sidebar opens to the right of your spreadsheet, showing the rules for this dropdown.
Each option that appears in the dropdown is in a separate text box in the sidebar. You can edit any item by clicking it and typing in new text or numbers. To change the colour of an item in the dropdown, click the circle icon to its left and select a colour.
If the dropdown is one you created yourself, you can further edit it by adding more options or dragging and dropping the options to reorder them. (See our dropdowns tutorial for details.) If you’re working with a dropdown that’s part of a template, you can’t add or rearrange options.
To remove a dropdown list from a cell: Click the small down arrow at the right edge of the cell that contains the list, then click the pencil icon in the menu. At the bottom of the “Data validation rules” sidebar, click the Remove rule button.
There’s lots more you can do with dropdowns that could be useful for project management, such as applying conditional formatting based on values. For example, you could assign a colour scale that ranges from red for a project that’s 10% completed to green when it’s 100% completed. Our dropdown tutorial has full instructions.
3. Tag people you’re working with
Typing the @ symbol inside a cell in Google Sheets will open a panel that lists a few suggested people in your contacts — or you can type the person’s name or email address to pull them up. Click the person’s name, and it’ll be inserted into the cell as a smart chip.
When you hover the pointer over this cell, a mini profile card for them will pop up. On it, you and others you’ve shared the spreadsheet with can click icons to email this person, start a text message or video chat with them, or add them to an event on your calendar.
This is particularly handy in a project management scenario because you can insert a co-worker’s smart chip to assign a task to them. If other collaborators need to find out more information about that task, they’ll be able to contact the assignee directly within the project-tracking spreadsheet.
4. Share and collaborate on a project management spreadsheet
You can use a spreadsheet to track your own personal projects, but the real value comes when multiple people are able to view and collaborate on a spreadsheet to collectively track a group project. To do that, you first need to share the spreadsheet with them.
When you’re viewing your spreadsheet, click the Share button at the upper right. Or, from your Google Drive homepage, click to highlight the spreadsheet that you want to share. Then, toward the upper right, click the Share icon (a head-and-shoulders silhouette with a +).
Through either method, a Share panel will open.
In the entry box, enter the email addresses (or names in your Google Contacts) of the people with whom you want to share. By default, the people you invite to your spreadsheet can edit it and reshare it with others.
To change access permissions for your invitees: Click Editor to the right of the entry box and choose either Viewer or Commenter from the dropdown menu. Viewers can see all the data in the spreadsheet but can’t edit or comment on it. Commenters can attach comments to specific cells, but their comments won’t interfere with your spreadsheet’s data.
For a project management spreadsheet, you mostly like do want some co-workers to be able to edit the spreadsheet — for instance, to change the status dropdowns as tasks are completed and update other fields with additional information. However allowing anyone who views the spreadsheet to change all aspects of it could lead to chaos, so it’s wise to limit Editor permissions to a small group of core collaborators and then assign anyone else either Viewer or Commenter permissions.
The easiest way to assign different permission levels to different groups of people is to send different invitations. First, invite the people you want to have Editor status: add their names or email addresses in the entry box, type a message to them, and click Send. Next, click the Share button again, add a different group of names or email addresses in the entry box, change Editor to Viewer or Commenter, and type a message. and click Send.
To manage access permissions at any time: When you’re viewing your spreadsheet, click the Share button at the upper right. Or, from your Google Drive homepage, click to highlight your spreadsheet and click the silhouette headshot icon. the panel that opens lists all the people who have access to the spreadsheet. Click the down arrow to the right of any person’s name, and on the dropdown menu that opens, change their access to Viewer, Commenter, Editor, or Remove access.
For more information about sharing a spreadsheet, including how to prevent it from being downloaded, printed, or reshared, see our Google Sheets cheat sheet. And for details about collaborating on a spreadsheet, including what it’s like to collaborate in real-time, see “How to collaborate on a document” in our Google Drive cheat sheet.
