An error has occurred in Smartsheet
Please log out and log back into the app before continuing. If the problem continues,
please request assistance at www.smartsheet.com/gethelp.
Welcome!

Establish a well defined process to manage, track and visualize your projects - all in one place

vertical grey line.png
  • icon-leader-dark-background (1).png
  • Beginner
    Smartsheet Experience Level
  • icon-configuration-dark-background.png
  • Medium
    Smartsheet Complexity Level
  • icons-supportive-green_help.png
  • Scroll to download, learn what's included, and for a step-by-step guide
  • icon-HB-lighter-consulting.png
  • Want help building your solution? Click to ask about Solution Services
gray-line-arrow.png

Download the Template Set

  • Click the button below and the template set will be added to your sheets folder. Your sheets folder will automatically open up on download. You can access this folder at any time by clicking on the browse tab.
  • download button_bold_EN.png

Plan Requirements: This template set has been designed to utilize the full suite of functionality available in Smartsheet. If you are on a limited plan you can still use the template set however not all premium functionality will be available. Click here to see a list of discontinued plans and identify your plan and user type here.

Capabilities, Benefits, and Overview

Use this template set for: 

  • Get started customizing a projects and portfolio management solution using best practices with Smartsheet
  • Create a process to manage, track, and provide visibility to all projects, their status, and key performance metrics
  • Provide leadership, managers and individual contributors with access to key insights via real-time dashboards



Read on for a step by step guide on how to get started, and navigate to the Learning Center if you have questions along the way!

vertical grey line.png

How Does It Work? 

What's Included?

Template Set Folder
  • file-type-folder.png
    All the components of the solution are in this folder on download
Project Portfolio
  • Dashboard.png
    Rollup view of project metrics - perfect to share with key stakeholders who need only the critical details
Projects
  • Grid.png
    Primary sheet for capturing new submissions and tracking project details, such as approval status, project status, and dates.
New Project Submission
  • Form.png
    Requestors can submit new projects to the Projects sheet using this form
Project Template (Save as New)
  • file-type-folder.png
    Folder to house Campaign Plans, and contains the Campaign Plan Template
Task Sheet
  • Grid.png
    Simple sheet to organize project tasks. Includes the basic fields for project planning
Task Summary
  • Report.png
    Uses grouping and summary to organize tasks in the task sheet by status
Overdue Tasks
  • Report.png
    Looks at tasks and filters non-complete tasks with a end date in the past
Project Dashboard
  • Dashboard.png
    Rollup view of project metrics, including status, % complete, and overdue tasks
Reporting
  • file-type-folder.png
    Contains the reports that look across task sheets and the projects sheet
In-Progress Projects
  • Report.png
    Looks at the Projects sheet and returns any project currently in progress
Metrics
  • Sheet using cross-sheet formulas to calculate status, priority, health, and budget metrics for use on the overview dashboard

Setup & Customize

1. Create a New Workspace

A. Open Browse. From the browse tab, hover over Workspaces with your cursor, right click, and press Create New Workspace. In the menu that appears, name your Workspace and press OK.

B. Select the template set folder in your Sheets folder. Drag and drop the folder in to the workspace you just created. 


Note: Using a workspace is a best practice when building a collaborative process. Workspaces allow you to centralize your work and control sharing with different stakeholders.

gray-line.jpg

2. Start with the Projects sheet

A. Project Submission Form
Projects
  • Grid.png
    Primary sheet for capturing new submissions and tracking project details, such as approval status, project status, and dates.

The Projects sheet is the primary sheet and used to capture and catalog upcoming, current, and past projects. The sheet is designed to intake projects through the attached form, or by direct entry into the sheet. Projects must be submitted with a name, description, owner, priority, start/end dates, and budget.

A. Look at the example projects and submit your own using the form. At the top left of the sheet, navigate to Forms, then Manage Forms, and click on "New Project Submission" form in the menu that appears. Click Open Form at the top right and complete the form using mock data. Head back to the sheet and observe. You'll notice your project has been added to the bottom of the sheet.

Pro tip: copy and save the form's URL. This is the URL that stakeholders will use to submit new projects to the sheet. Don't worry, you'll be able to go back later and customize the form and/or contents of the sheet to you liking.

New Project Submission
  • Form.png
    Requestors can submit new projects to the Projects sheet using this form

Learn more: Forms, Automation

B. To effectively use the sheet to manage projects, it is important to understand how Approval Status and Project Status columns work, and the role they play in keeping you and your stakeholders informed.

Approval Status can be managed manually or via an automation rule. If adjusting manually, remind your approving stakeholder to check the sheet often and provide their approval on submitted projects. There are three approval options - Submitted, Approved, and Declined. Setting Approval Status for each project is the first step in managing the project through its lifecycle. Forgetting to Approve or Decline projects prior to their start date will impact their Project Status.

To automate Approval Status, navigate to the Automation tab, select Manage Workflows, and Edit the inactive Approval Request automation. The logic is already setup to send an approval request when projects are submitted to the sheet with the required fields. To activate the automation, designate your approving stakeholder in the "Request an Approval" action and save the workflow.

Project Status is managed using automation triggers, and automatically updates based on a project's Approval Status and Start Date/End Date relative to the current (today's) date. While the status can be manually updated, it is suggested to focus on managing projects by providing accurate Start Dates/End Dates opposed to manually adjusting status. Logic below.

B. Approval Status

Project Status outputs and conditions:

  • If Approval Status is Submitted and Start Date is in the future, Project Status is In Review
  • If Approval Status is Submitted and Start Date is in the past, Project Status is Missing Approval
  • If Approval Status is Declined, Project Status is Declined
  • If Approval Status is Approved and Start Date and End Date are in the future, Project Status is Planned
  • If Approval Status is Approved and Start Date is in the past and End Date is in the future, Project Status is In-Market
  • If Approval Status is Approved and Start Date and End Date are in the past, Project Status is Complete
B. Project Status

C. Adjust Priority drop-down, Health symbol column, and remember to Keep Start Date, End Date, Planned Budget, and Actual Budget updated. Also some more tips.

Starting off with a strong foundation for managing projects in Smartsheet is important, and means that core components are already connected to support your success. But we know not all processes are the same, so we make sure to leave room for you to learn, be inspired, customize, and operate as you see fit. Part of setting up this solution may require adjusting how your business addresses priority, and if you do that by the suggested priority levels or something else completely. Pay attention to the following, and make sure you take note of what you can customize and how that impacts the reports and dashboards:

  • If you want to adjust the dropdown options for Priority, double click the column header to modify the values listed. Revisit this later after you've had a chance to view the metrics sheet, reports and dashboards. The sheet is setup to report on 5 priorities; anything less or additional will require you to adjust the metric sheet and/or remap what range the charts on the dashboard are looking at. Changing the names of these values will require you to modify values in the first column on the metric sheet.
  • If you want to adjust the options for Health, double click the column header and select which symbols you would like to use. This column is setup to report on 4 symbols. Each symbol has a rich text value, such as "Red", "Yellow", "Green", and "Grey". Try selecting symbols with 4 options.
  • Keep Start Date and End Dates updated. You can have project managers do this manually, or if you've got a bit of Smartsheet experience under your belt, you can cell link the date cell to a project's plan that has start/end date information. This is the best way to automate updates to a campaign's status/row.
  • Similar to the above, keep budget figures updated. These feed the chart on the dashboard. If you wish to automate budget updates, connect these cells to a project plan that contains budget information for the specified project.
  • Multiple owners for a given project? No problem. Double click the Owner column header and select "Allow multiple contacts per cell." This will enhance reporting if you have several stakeholders participating in one project. Alternatively, you may add an additional contact column, but make sure to update the reports to look for this additional column.
  • We think automation is slick, but if you want formulas to control "Project Status" opposed to automation triggers and conditions, try using this formula in one of the Project Status cells. Make it into a column formula to apply it to all entries!

=IF([Approval Status]@row = "Approved", (IF([Start Date]@row > TODAY(), "Planned", IF([End Date]@row < TODAY(), "Complete", "In Progress"))), IF([Approval Status]@row = "Submitted", (IF([Start Date]@row > TODAY(), "In Review", IF([Start Date]@row <= TODAY(), "Missing Approval"))), IF([Approval Status]@row = "Declined", "Declined")))

3. Review Project Template (Save as New) Folder & the Sheet, Reports, Dashboard.

Project Template (Save as New)
  • file-type-folder.png
    Folder to house Campaign Plans, and contains the Campaign Plan Template

Without a way to plan, track, and manage individual projects, the Projects sheet is not much more than a calendar. That's why we included a Project Plan folder that your project managers can save as new to manage new projects. The project dashboards within these folders should be hyperlinked in the associated project row in the Projects sheet.

A. Review the example Project Plan Folder. We've created a basic architecture for how projects can be managed in Smartsheet. We've broken it down into a task sheet, two reports, and a dashboard. The folder contains the same sheets, reports, and dashboards that are spun up by the "Project Assistant" experience in the Create menu. Keep this in mind if you ever need to quickly spin up a reliable project architecture.

Task Sheet
  • Grid.png
    Simple sheet to organize project tasks. Includes the basic fields for project planning
Overdue Tasks
  • Report.png
    Looks at tasks and filters non-complete tasks with a end date in the past
A. Linking a row to its Project Dashboard
Task Summary
  • Report.png
    Uses grouping and summary to organize tasks in the task sheet by status
Project Dashboard
  • Dashboard.png
    Rollup view of project metrics, including status, % complete, and overdue tasks

B. Modify the contents of the Project Template folder to your liking.

For the Task Sheet, here are a few areas we recommend exploring: adding additional rows/hierarchy with default project tasks, adding an automation (like reminders or update requests), adding conditional formatting to create visual indicators of status, and adding columns to track budget across tasks.

For the Overdue and Task Summary reports, we recommend adding any columns to the reports that you added to the sheet, and adjusting the specificity of each reports filter criteria to your liking.

The Project Dashboard is infinitely customizable. We've provided widgets to help you link to project assets quickly, summarize overdue tasks, and see task status and % completion at a glance. We recommend customizing the theme (think colors & layout), and exploring adding widgets that could help project managers better contexulize project details -- like a rich text widget, or chart breaking down planned versus actual budget.

Now every time stakeholders replicate the folder to run a new project, the updated sheet, reports, and dashboard will be included. There is nothing stopping you from adding additional sheets, reports, or dashboards that may help your stakeholders manage, track, and report on their projects!


B. Customizing Project Dashboard

4. Review and Customize Metrics

Metrics
  • Grid.png
    Sheet using cross-sheet formulas to calculate status, priority, health, and budget metrics for use on the overview dashboard

Metric sheets are helpful for aggregating data so you can extract insights to a dashboard using charts and metric widgets. These sheets use cell links and cross-sheet formulas to gather insights.

A. Review the layout. We've setup the metric sheet to report on 4 distinct areas: count of project status, count of projects by priority, count of projects by health, and summary of budget across approved projects. Expand each section, and double click on the cells within the Output (Calculation) column to review the formula. The Output (Interpretation) column enriches the Output (Calculation) with text, helping further contextualize the result.

B. Customize the Input Values. You'll notice how each formula looks at columns in the Projects sheet (to see this hover over the range names within { } brackets and click Edit Reference). The formula searches for the value in the Input column within those ranges. If you adjusted the dropdown values in the Status, Priority, or Health columns on the Projects sheet, update the cells in the Input column with the new values so the formula knows what to look for. Doing this will ensure the widgets on the dashboard update correctly.

B. Adjusting Dropdown Values and Updating Metrics

5. Review and Update Reports

Smartsheet reports allow you to work with real-time data from across multiple sheets in a single view. They are great for filtering information across any size dataset, and help stakeholders gain targeted perspective on various initiatives, such as projects that are in progress, tasks that are past their end date, or even a general summary of tasks by status.

Reports are bi-directional, so information updated on a report will automatically update back to the underlying sheets and vice versa. You can build a row report using the rows and columns on a sheet, or a sheet summary report from the sheet summary fields - like the At Risk, Overdue, Complete, In Progress, and Not Started fields in Sheet Summary on the Task Sheet. Grouping and summary in reports provides quick and easy aggregation and calculation across data from multiple sheets.

In-Progress Projects
  • Report.png
    Looks at the Projects sheet and returns any project currently in progress

In-Progress Projects report looks at the Projects sheet, filtering out campaigns with a status of "In Progress".

If you have changed the drop down options for Project Status, in addition to updating the automation rule and metric sheet, you will also have to update the reports filter criteria.

Overdue Tasks
  • Report.png
    Looks at tasks and filters non-complete tasks with a end date in the past

Overdue Tasks report looks at the associated Task Sheet, filtering out tasks where the end date is in the past.

A dimension you can add to this report to specify further is looking at tasks that are X days past their end date. To do this, go into the filter criteria and adjust the logic.

Task Summary
  • Report.png
    Uses grouping and summary to organize tasks in the task sheet by status

Task Summary report looks at every task within the associated Task Sheet, pulling out any row that has a status and grouping it by its status

You can enhance this report by adding columns you may have added to your sheet, such as budget. Or checking against date, so tasks where the due date is within 5 days only surface.

Learn More: Reports

6. Review and Customize Project Portfolio Dashboard

Project Portfolio
  • Dashboard.png
    Rollup view of project metrics - perfect to share with key stakeholders who need only the critical details

Smartsheet Dashboards are a communication tool that provide real-time visibility into critical data and centralize all the information that your team needs in a single place - such as a overview of project status, breakdown of health, budget usage, and real-time view of what's in progress.

Dashboards are made up of easy-to-configure widgets--the building blocks for dashboards--that display the live data from your sheets and reports. Metric and Chart widgets looking at the metric sheet, and a report widget linked to the In-Progress Projects report make up most of the information on the dashboard. These are just inspiration, you're in control.

Dashboards can be shared with anyone, including teammates, executives, and even external customers, and are easily viewed on the desktop or the Smartsheet mobile app. We've designed this dashboard for the executive stakeholder - the people that need to know, at a glance, project KPIs.

A. Observe how information flows into the dashboard.

  • The metrics under "Pending", "Launched", and "Declined" titles are simply metric widgets pulling from the Output (Calculation) cells on the Metric sheet. Get creative with your text formatting and colors to make these pop.
  • Similar to the above, the Approved Budget semi-circle chart is a Chart widget looking at the budget section of the metric sheet. Changing the color of the series can help you visualize how much budget is remaining.
  • Health Breakdown pie chart, Status at a Glance, and Priority Breakdown follow the same logic as the above. Chart widgets have lots of configurations--explore and be creative!
  • In-Progress Projects is a Report widget that enables the Report to be viewed in real-time from the dashboard. You can embed many reports - we just felt this was the most impactful for the executive stakeholder.
  • Finally, Shortcuts are simple (but good looking) hyperlinks to easily get to parts of the solution. You can also link to URLs and attachments. Instead of returning to the Browse menu, try linking the items you visit most and using Dashboards as a landing pad for your stakeholders.
A. Modifying a Chart's Colors

B. Add a widget, it's easy! Begin customizing your dashboard by clicking the pencil icon at the top right, or by clicking Edit and Edit Dashboard... You have lots of widgets to choose from, and can reference this article for a list of descriptions, but we suggest starting with changing out the logo and adding the Projects sheet's Project Submission Form (referenced in step 2).

If you have an embed link for your company's logo/likeness, you can link to it using the web content widget. Double click the "Company Logo" placeholder widget and, in the menu that appears, replace the widget's configure link with the new embed link. If you do not have an embed link, replace the web content widget with an image widget, which allows you to upload files from your computer directly to the widget.

To add the Projects sheet's Project Submission Form to the dashboard, add a new Web Content widget to the dashboard using the "+" symbol. Position the widget near the bottom of the dashboard, expand its borders as desired, and paste the form's URL into the widget's configure link. Embedding a form on a dashboard is a great way to manage how, and where, stakeholders come to review and submit information. Or, if you don't want the widget taking up space, you can always add the form URL to the existing Shortcut widget.

B. Add Form to Dashboard with Web Content Widget

7. Delete Sample Data and Share Your New Solution

The rows on the Projects sheet are for demonstration purposes and not required to stick around. Delete the rows after you've analyzed how they work with the solution and you've had a chance to customize! Deleting the sample data will not affect the design of the automatons, conditional formatting, forms, or formulas.

Ready to use your solution? Eager to collect input on things to customize to adjust to your business processes? Sharing is the best way to collaborate with others involved in your projects. You can share your entire workspace with members of your department, or share individual items within the workspace.

Learn more: Workspace Sharing

Enhance Your Template Set

Recommended
  • Smartsheet Control Center enables consistency of work execution with reduced operational risk.
Recommended
  • An interactive and flexible calendar view that allows you to build shareable calendars with custom details.
  • Summarize and analyze large data stored in sheets or reports and create meaningful comparisons, patterns, and trends.
  • Eliminate typos, duplicative data entry, and unnecessary work by mapping data between large sheets and reports.
Recommended
  • Streamline your work and simplify collaboration with easy-to-navigate apps built directly from your sheets, forms, dashboards, reports, and more.
  • Smartsheet Dynamic View enables business owners to share information to the right people at the right time for viewing or editing.
  • Upload or offload your data from ERPs, CRMs, and databases to and from Smartsheet. Data Shuttle allows you to create a centralized source of truth and gives you the ability to make confident, real-time decisions.

Helpful Resources

We have lots of resources to help you get started and become even more productive in Smartsheet. We also love hearing from our customers, please use the form at the bottom to submit feedback about this template set! 

  • icon-HB-lighter-consulting.png
  • Want help building your solution? Click to ask about Solution Services
  • Check out the Learning Center to learn more about Smartsheet
  • Boost your Smartsheet knowledge and skills! Explore Smartsheet University
  • Go Mobile - Download Smartsheet for iOS or Android
gray-line.jpg

Last Updated: 03/29/21

Powered by Smartsheet Dashboards