Today, you’re going to learn exactly how to manage projects like a pro using Smartsheet. In this video, you’ll learn how to create new project plans, add tasks, use formulas and automations to speed up your work, centralize all project communication in one place, and create rollup reports and dashboards across multiple projects. Let’s dive in. To get Smartsheet, head to the following website. You can click on the card in the top right, and I’ve included a link in the description below. From the homepage, enter your work email address and then click on Try Smartsheet for free. This starts your 30-day free trial. Next, go to your email, look for the email from Smartsheet, and then click on Confirm Email. Create a password, agree to the privacy policy, and click Next. Next, enter your name, then select your role so Smartsheet can provide relevant templates to help you get started. We’ll select Project Management. Choose what type of work you manage. I’ll select Agile, Task Prioritization, and Waterfall, and then click Next. Now you can go through the first project wizard, but we’ll start from scratch so you understand the fundamentals. Click No Thanks. Let’s get you familiar with the Smartsheet interface. We’re here on the Create tab with project management templates which match our interests. These templates are solutions, which have sheets, reports, and dashboards all in one. It’s really fast to get started with these, but if you don’t know the fundamentals, you might get stuck when you need to customize. Let’s avoid that and go to the Browse tab. Here we’ve got a folder structure for sheets and workspaces. Items and workspaces are shared with others, and items in the Sheets folder are not by default. Let’s go back to Workspaces and create a new workspace called Marketing. Click on Create and then Workspace. Type Marketing and press OK. Click on the Marketing workspace, and let’s create our first project plan. Click Create, then click on Grid. We’ll call this Q3 Ad Campaigns and then click OK. Then click on the sheet name to launch the Smartsheet editor. Here we’ve got a spreadsheet view, but it’s more like Excel and an Access table rolled into one. Every row in this sheet should represent some item, like a task. The primary column is the human readable identifier for each item. We’re making a project plan, and a project plan has a list of tasks. So, let’s rename this column by double clicking on it, typing task name, and then clicking OK. The next column is the assigned person. Double click on column two and rename the column assigned to. Then for column type, select Contact List. Let’s add some names to our list. First, I’ll add myself. Next, let’s add Kevin by typing in his email address and pressing Enter. We’ll also add Elizabeth, and then click OK. Next, let’s track our start and end date. Repeat the process by double clicking on the next column, typing start date and selecting the column type date. Check restrict to dates only to ensure only dates can be entered and click OK. Repeat the process for end date. Next, I’ll add a days worked column to track time spent. Let’s add a task status column called Status. Under column type, select dropdown list, in which we’ll allow one of four statuses. Then check restrict to list values only to prevent users from typing in their own statuses. Then click OK. We’ll add one more column. Move your mouse over the status column and click on the three dots in the top right. Then click on Insert Column Right. Type Health and then select Symbols for the column type. Let’s use the red, yellow, green traffic lights. Check restrict to symbol values and click OK. Let’s organize our columns to see health and status first. Click and drag on the Health column header and move it over Assigned To. Then click and drag the status column over Assigned To. Let’s add some tasks. Click on the cell in row one under Task Name and then start typing like it’s a spreadsheet. The Health column and the Status column allow you to select from a dropdown list. When you assign a person to a task, you can opt to automatically notify them. Let’s click Enable. Then you can add a start date and an end date. You get the idea, so let me fill in the rest of these tasks. Okay, now that we’ve got the September ad campaign tracked, I realized that we need to track the October campaign too. Fortunately, we can nest tasks. Right click on the first row and then click Insert Row. In the Task Name cell, type in September campaign. Then select rows two through seven by selecting row two, then holding Shift and selecting row seven. Then click on the indent icon in the toolbar to make these subtasks. Now select all rows, copy them by pressing Control C or Command C, then select row eight and press Control or Command V to paste. This project got added as a sub task instead of a top-level project, so let’s click on the Outdent icon in the toolbar to correct it. Let’s correct row eight to say October campaign and then resize the column so we clearly see every task. Now Smartsheet doesn’t know that these dates should all be changed for the new campaign, but instead of us manually setting all the dates, let’s explore how the Gantt View can make setting updates and dependencies much easier. Click on Grid View and then select Gantt View. There’s now a Gantt chart hiding on the right side of the screen, so let’s exit out of the sidebar so that we can see it. Now let’s set up some dependencies. Click on the gear icon in the top right of the toolbar, then check Dependencies Enabled. This adds two new columns, duration and predecessors to control both the start and end dates of tasks. Click OK, then click OK again. Scrolling over, we can see both of these new columns. Time to set up some dependencies. I’ll move this column over so it’s easier to see that column and the task name at the same time. Let’s make storyboarding dependent on ID8 messaging. Click on the pencil icon in the predecessors row. From the task name dropdown, select row nine ID8 messaging. Finish to start is the most common type of dependency, but you can use finish to finish for tasks that should end at the same time, start to start for tasks that should start at the same time, and start to finish to state the other task is dependent on this one to start. You can also add a lag period in case there should be a break between the dates. For example, if a task should be started an extra day after a previous task finishes, you can add one as the lag. Let’s erase the lag and click OK. You see that the project plan updates to mark the dependency. You can also add a predecessor by just adding the row number. For example, to make ad proposal dependent on storyboarding, just type in the row number of storyboarding, 10. Let’s update the rest of these dependencies and then set the go live date to September 30th. Finally, let’s set the start date of the ID8 messaging task to September 2nd. Changing that start date shifts all of the tasks for the October campaign to be completed in September. A significant event in project planning is the project milestone. Both go live dates are project milestones. To set them as milestones, scroll over to the duration column and set the value to zero instead of one. Then you’ll see those tasks marked with a milestone marker. Next, if you’ve managed any project in your life, you’ve probably seen it slip. Smartsheet tracks how much we’re slipping with baselines. Click on baselines in the toolbar. Baselines snapshots your current start and end dates. And as those dates change over time, Smartsheet tracks how far off you are from the original plan. Click set. Now you can see those baselines in the Gantt chart as black lines for tasks and white milestone markers for the milestones. And if you change an end date for a task, you can see the difference in the Gantt chart and in the new variants column. One other aspect of project planning is capacity management to ensure everyone working on the project isn’t overbooked with work. Smartsheet has a solution for this called resource management. It’s an add-on for your project planning needs, so we won’t dive into it today. Let’s say you want to track percent complete for each of our projects. One way to do that is to track the effort completed divided by the total effort. Let’s add some columns to track this and showcase some formulas along the way. Let’s create a new column to the right of days worked called total work days. This is meant to measure days of effort, not actual duration of the project. So first we need to calculate this for individual tasks. Click on the cell for the first task row and then type equals. Then type net work days, which calculates the number of working days between two dates. Then click on the start date cell in the same row, type comma, then click on the end date and press end parenthesis. Let’s break that formula down. Cells are referenced by their column name first and then a row number or Zac Brown Band, which is really a reference to the current row. Press enter to commit the formula and you’ll see there are five working days between August 1st and August 7th. You can copy and paste this formula into the remaining task rows by using control C and control V. For the project rows, we’ll need to sum up all the individual task rows. Click on the top row and type equal sign, sum children parenthesis. Children is a special formula, which references the values of the first level children of the current item. Press enter to commit. We can copy and paste this for the October campaign and for the days worked column. Next add a new column to the right of total work days called percent complete and make it a text number field. Let’s add a formula to the first row that divides days worked by total work days and hit enter. That works for one cell, but what about all the cells? Right click on the first cell and then click convert to column formula to have it apply to every cell in this column. Finally, we need this number to be a percentage, so, let’s adjust the formatting. Click on the column and then click on the three dots in the toolbar and then the percentage format. Just like in other spreadsheet apps, you can use conditional formatting to make it easier to emphasize certain parts of your sheet. Let’s make it easy to see which tasks are behind by highlighting them red. Click on the conditional formatting icon in the toolbar, then click add new rule. Every rule follows the same format. If condition, then apply format to cells. Click on set condition to change the condition. The condition should highlight incomplete tasks that are past due. Click on status and then completed and check the box that says apply format when condition is not met. So, the total condition is status is not completed and click okay. We need another condition for past due tasks. So, click on the arrow next to the rule and then click add condition. Then click on set condition again. This time select end date and then select is in the past from the dropdown menu. Then click okay. Next, let’s change the format by clicking on this format link. Then click on the paint bucket icon and select pink to change the background. Now click okay to apply this rule to the entire row. Nothing changes pink right now, but if we change the end date from today’s date, August 7th to yesterday, August 6th, this row turns pink. Effective project management needs effective communication. Let’s walk through how Smartsheet can keep you out of email through its conversations feature. To even have a conversation, you need to share this worksheet with others. So, click on the share button in the top right, then click on workspace. Let’s share this workspace with Ginger, our head of marketing. I can paste her email address up top and then customize the invite message she receives. I’m okay with the default, so let’s click on share. Let’s get out of this dialogue and scroll over to the left. Every task has a conversation associated with it. You can access it by clicking on the comment box icon next to any row. Oh, it looks like Ginger is already typing something here. She’s asking if we’ll have an update on the task that’s behind today. Let’s respond quickly. You can at mention people by their name or their email address. This sends them an email notification that they can reply to through email or directly in Smartsheet. You can also catch up on all conversations in the document by clicking on the unread tab and have conversations at the sheet level instead of the row level. In addition to straight conversation, you can attach files in two different ways. For the first type, click on the attachment icon in any row. Then click the attach files button. Next, you can upload a file from your desktop, from the cloud, any website, or even from a digital asset management tool called Brand Folder. The second type of attachment is called a proof. You can access them by clicking on the proof icon in any row. Proofs help you manage content creation and approval directly in Smartsheet. Let’s illustrate with a PDF. You can annotate anywhere on the document by clicking and then adding a comment and pressing enter. You can also make an annotation on any area of the document or make a specific annotation using an arrow. You can go back and forth here instead of over email and then when all comments are addressed, you can mark the review as complete and close it to new feedback. Next, let’s explore how Smartsheet can make your life so much easier through automations. Click on the automation menu. We already created one automation or workflow earlier when Smartsheet suggested it to us and that was to alert a person when a task assigned to them changed. Let’s add another one which is the most annoying thing to do manually. Click on request and update every week under templates and click use template. Type in a title you’ll recognize later. We’ll call it request weekly updates. Workflows have three elements, a trigger, conditions, and actions. There is a single trigger or type of event that kicks off a workflow. Here, it’s date based but you have other options like when rows are changed. Conditions then filter which triggers cause actions. In this case, we don’t want updates for every task. We just want updates where the start date is in the past. So, let’s update the condition to say is in the past. Let’s also filter tasks whose status is not completed. The action is to request an update and we need to assign it to the right person. Click on send to specific people and instead choose send to contacts in a cell. In select a contact field, select assigned to. Smartsheet is smart enough to only ask people for updates for tasks they’re assigned to. Now click save. If you click on the three dots next to the workflow and then click on run now, you can test your workflow. But to test it without annoying your coworkers, check this send notifications to myself instead checkbox and then click run workflow. Let’s see what Smartsheet sent. Click on OK and then click on the back button in the top left. Then click the notification bell on the left-hand sidebar. Click the update request and you’ll see a simple form for each task that you’re assigned. This saves a ton of time manually asking for updates every week. You now know enough to get started with Smartsheet for a single project. But if you want to manage a portfolio of projects, stay tuned for the rest of the video. Let’s go back to our workspace by clicking on the folder icon in the sidebar. We’ve looked at ad campaigns in detail, but the marketing team also has conferences and product releases to manage. And Ginger, our head of marketing, needs a weekly status update of every project. Using reports, you can combine data from multiple sheets and summarize them for upper management. But we need to make a couple of tweaks to our ad campaign sheet first. Let’s go back to the ad campaign sheet and let’s create some more space by clicking on grid view, and removing the proof panel. Reports pull in all rows from their associated sheets, so we need a way to distinguish between tasks and projects. So on the task name column options, click on insert column left. For the column name, type is project and then select checkbox as the column type. Then select the star icon and click OK. Then let’s check the rows for September and October campaign. Next, Ginger wants to know how on track or off track we are for any project. We need a couple of new columns for this. Let’s scroll to the right and add a new column after percent complete. Call this expected days worked and choose text number for the column type. Then click insert column right again and call this column expected percent complete. Again, using text number as the column type. Click on row one under expected days worked and use the following formula, which you can get from the description. This calculates the expected days of work based on project baselines. Next, calculate expected percent complete using this formula, which you can also grab from the description below. Next, right click on each cell and convert them to column formulas so they apply to each row. Finally, let’s add another column called latest update, so management can see the explanation behind project status. Let’s scroll all the way to the right, click insert column right on the variance column, type latest update for the name, then select latest comment as the column type. This will show the last comment in the row itself. Now we’re ready to create the report and the reason behind each of these columns will become clear. Go back to your workspace using the folder icon, then click on create, then click on report. Let’s call this marketing project status. This is a row report since we want a report on the row level data. You can also make a sheet summary report, which pulls sheet level field data from each sheet. Click on OK, then click on the report. First, select the sheets whose data you want to import. We’ll import all the sheets under marketing. When you click next, all rows from those sheets get combined into one report. You can now choose which columns you want to use based on any column in any selected sheet. It’s up to you to maintain similar column names and data types, so the data gets combined. Let’s use health, latest update, start date, end date, percent complete and expected percent complete. Then click next. We currently have all rows imported and we just want to see project status. So, let’s add a filter for that is project column from the select field dropdown select is project, then select is checked, then click apply. And now we’ve got a place to easily see the status of every project across teams. If you need to drill into the tasks, you can click on the sheet name link. And if needed, you can also summarize the data into groups. Click the group icon in the toolbar and then select health from the column list and click OK. When you have a lot of projects, it might be helpful to see how many are green, how many are yellow and how many are red. To do that, click on the summarize button and choose sheet name from the column dropdown and leave the default count setting. Then click OK. Notice how even though the expected percent complete column has the same data type in all three sheets, the column still retains its original formatting from each sheet. And if you need to make any changes, you need to go back to the source sheet. Now, this report is an OK start, but when we really want to understand a lot quickly, nothing beats the visual power of a dashboard. Let’s go back to the main workspace and create one. Click create and then click on dashboard slash portal. We’ll call this marketing project dashboard and click OK. Click on the dashboard to start editing it. Our goal with this dashboard is to help upper management visually understand the status of the entire project portfolio and see how far off track we are. Let’s click add widget to get started. Here you have a drag and drop interface to create and customize your dashboard. Let’s first add a chart that shows the number of projects based on their health. Click on the chart widget, then click add data. Browse to the report that you just created and click OK. The data is automatically summarized by health to match our report. Let’s change it to a horizontal bar graph by clicking on chart type and then selecting bar. The X axis is skewed and doesn’t represent the data well. Let’s click on horizontal axis to adjust this. Uncheck scale to data values to normalize the axis. Next, let’s summarize the completion percentage versus the expected completion percentage for every project. Unfortunately, we’ll need to create a new report without the group summary in order to create that. Let’s exit the dashboard and go back to the workspace. Click save to save your progress. Right click on the report we just created and then click save as new. Let’s call this raw marketing project status, then click save and open the report. Click on group in the toolbar, click X next to the health row and then click OK. Click on summary in the toolbar and remove that as well. Let’s then head back to the dashboard by clicking on the folder icon and clicking on the dashboard. To edit the dashboard, click on the edit menu, then click edit dashboard. Then click the plus icon in the right sidebar. Let’s add another chart and let’s connect this chart to the report that you just saved and click OK. You’ll notice that the Y axis doesn’t use percent formatting because one of the sheets doesn’t have that number formatted as a percent. I’ll fix that quickly on the ad campaign sheet and refresh the page. Now you can really see the difference. Finally, let’s embed the full report, so if management needs details, they’re all in one place. Click on the plus sign in the right sidebar, then on report, click on add report, then select the raw report we just created and click OK. Next, you can expand this report to the width of the entire sheet using the drag handles in the top right. Not only will you see each project, but you’ll also see an overall Gantt chart too. This is a good checkpoint. So, click on save to preview your dashboard. Congratulations. Now you’ve got a total project and portfolio management plan. Now you’ve seen Smartsheet in action. What are you most excited to try first? Roll up reports, smart formulas. Leave a comment and let me know. Remember, you can get started with Smartsheet with a 30-day free trial by using the link in the description below. This is David signing up and I’ll see you next time.