Establish a well defined process to manage, track and visualize your projects - all in one place
Download the Template Set
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!
How Does It Work?
What's Included?
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.
2. Start with the Projects sheet
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.
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.
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
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.
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.
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!
4. Review and Customize Metrics
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.
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 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 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 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
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.
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.
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
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!
Last Updated: 03/29/21
Powered by Smartsheet Dashboards