Essential Sheets features for project management
Collaborate with your team
Google Sheets makes it easy to collaborate and share information. This is useful for remote workgroups or working with teams that have different schedules.
When you share your Sheet, you can select from the following permissions:
- editing, which allows others to make direct edits and changes to the file;
- commenting, which allows others to add comments and make suggestions; or
- view only, which allows others to view the file but not edit, comment, or suggest. This is a good choice if you don’t want any changes to be made to the file.
Once you’ve shared your Sheet with your team and given them either editing or commenting permissions, you can all collaborate directly in the Sheet by leaving comments. You can also assign a task or action item through comments.
Check out these resources from the Google Help Center to learn more: Share and collaborate on files
Format your spreadsheet
Create lists
To create any list in Sheets, simply label each list — for example, Name, Date, Notes, or other criteria — in the header row. (The header row is the top row in the spreadsheet.) Then, add a different list item in each cell underneath the list title. Most lists are arranged vertically, but you might prefer a horizontal list depending on the types of items. Note: You’ll learn how to build out a full project plan and prioritize tasks later in this course.
Display and hide content
Freeze rows or columns
Freezing the header row (or column for horizontal lists) and formatting headers is a great way to make the list titles stand out. Freezing a header row keeps the row in place when you scroll down the list, so you can always see what the list titles are. Format the headers just like you would text in a document by changing the font, style, size, or color.
Group and hide related content
Grouping rows and columns allows you to expand and collapse larger categories of data or information with a single click. This makes your spreadsheet easier to read and manage by hiding the grouped data when it’s not needed, or quickly ungroup and show when it is needed.
Check out these resources from the Google Help Center to learn more:
Add checkboxes
Checkboxes provide a quick and highly-visual way to track progress on a task or indicate whether certain criteria have been met.
Check out these resources from the Google Help Center to learn more: Add checkboxes to spreadsheets
Organize data
Create dropdown lists
If you’re working with others on a shared Sheet, you might end up with inconsistent responses or descriptions of data. For example, one teammate may list their task as “done” while another notes it as “complete.” Adding a dropdown list (also referred to as data validation) ensures the data entered into your spreadsheet is exactly what you specify. It also makes it much faster for others to add data since they’re selecting from a predetermined list of options.
Check out these resources from the Google Help Center to learn more: Restrict data entry with lists
Add color coding
Sometimes it’s helpful to change the color of cells and text so you can quickly see how your project is progressing. You can add color coding to your spreadsheet by applying conditional formatting. Conditional formatting formats cells in a certain way if they contain specific information. For example, track progress status as “Not started” in red, “In progress” in orange, and “Complete” in green.
Check out these resources from the Google Help Center to learn more: See data changes with conditional formatting
Sort and filter data
Large sets of data can be difficult to manage. Sheets helps you sort and filter data to limit what is visible and find specific types of data within your spreadsheet.
Sorting data allows you to arrange data in exactly the order you want it to appear in, like alphabetical, by date, or progress level. Filtering limits what data gets displayed and shows you only the data you are interested in, such as tasks that have not yet been started, or budget items within a certain value range.
Check out these resources from the Google Help Center to learn more: Filter data in a spreadsheet
Add links
It can be helpful to link to other project documentation or even external websites in your spreadsheet. This provides your team easy, centralized access to all relevant data they might need. For example, you could add a link to your project charter document from your project plan spreadsheet.
Check out these resources from the Google Help Center to learn more:
Visualize data
Create a pivot table
A pivot table is a basic data analysis tool. Pivot tables summarize your data and can help show the relationships between data points, making it easier to understand all the information contained in your spreadsheet. You’ll learn more about how to create and use a pivot table later in this course. Or, check out the Help Center article below.
Check out these resources from the Google Help Center to learn more: Create and edit pivot tables
Make a chart or graph
Presenting data in charts and graphs is a great way to quickly and effectively summarize important information about your project to stakeholders and make reports and presentations more engaging. Sheets enables you to automatically create different kinds of charts and graphs using the data in your spreadsheet.
Check out these resources from the Google Help Center to learn more: Insert and edit charts
Perform calculations
Use functions
Functions can be used to manipulate data and perform calculations. Google Sheets has a wide array of functions that are already built in, which means they will automatically perform the calculations you specify. Use functions to quickly calculate sums or averages, automatically determine start or end dates, generate financial reports, and much more.
Check out these resources from the Google Help Center to learn more:
Useful spreadsheet templates
Using spreadsheet templates for project management
Creating the same types of spreadsheets over and over can be time-consuming. You can save time by using templates such as those available from the Google Sheets template gallery. Choose from a variety of pre-made spreadsheets for budgets, schedules, and trackers, or create your own!
Some of the more common templates are described and linked below. To use the templates for these course items, click the links below and select “Use Template”:
- Project Timeline: A project timeline template is useful if you want to track an entire project from conception to close. This example includes a visual timeline that lays out the life cycle of a project and the major activities that need to happen during each phase. You’ll learn more about creating and tracking a project timeline later in the course and get to experience working with your own project timeline templates.
- Project Tracking: A project tracking template is useful for tracking your project’s budget, deliverables, and other data. This example includes columns for tracking costs and time, as well as a column with dropdown lists for tracking tasks’ priority levels. Other features include formulas for calculating hours, costs, and percentage of completed deliverables, allowing you to track your spending and progress on a project.
- Gantt Chart: A Gantt chart combines many of the aspects of other types of project management spreadsheets into one. It organizes tasks by day and is useful for showing the relationships between the many moving parts of a project. It’s also helpful for managing a project with multiple collaborators. Gantt charts often include conditional formatting that makes cells change color based on how far along the project is so you can immediately determine how much progress you have made on a particular task.
- Event Marketing Timeline: A timeline template is useful for creating a schedule, tracking events, and visualizing the tasks and milestones involved in a project. You might use this template to manage a project that involves a lot of marketing or public-facing tasks. You can track social media posts, market research, and coordinate content across multiple platforms.
Check out these resources from the Google Help Center to learn more: Create document templates
