How to Create a Gantt Chart in Google Sheets

hello and welcome to vertex 42 watch this video to see how I create this Gantt chart in Google sheets it includes features like a dynamic timeline as well as showing the progress of each task [Music] first off I'll add the basic information we'll want including a spot to enter the project start date the critical information for a Gantt chart is a list of tasks each with a start date the duration in days and the end date we typically group tasks within phases or categories so I'll add for tasks under phase 1 next I'll add a start date and the number of days it will take to complete I'll calculate the end date by taking the start date adding the days and subtracting 1 I'll have tests to start one day after task one ends now I'll fill in the rest of the data for us to work with you can add and subtract dates because they are stored as numbers and just display two different formats I'll copy these formulas down to create a second project phase I'll make phase to start one day after the final task of phase one the next step is to create the date range or timeline for the chart area I'll start with June first and drag that six more cells to the right to create a week I want to display just the days of the month so I'll use the custom number format D it's important to realize these cells are storing the full date but only displaying the day of the month now I want to display the first letter of the day of the week in the cell below the date first I'll use the text function to display the abbreviation of the day of the week then I'll use the left function to grab just the first letter of that text next I'll merge these seven cells and display the date of the first day of the week to display the full date I'll need to change the number format back to the date default now I'll select the columns using control space and then copy them I'll use a keyboard shortcut to paste just the formatting then I'll drag the data to copy it to the right I want to add a fourth week so I'll insert seven more columns and copy the formulas one more week to the right to make the timeline dynamic I'll link the first cell of the chart area to the project start date then I'll use a formula to add one day to each of the next dates and drag that to the right now when the project start date changes the timeline will change too first I'll select the entire worksheet and change the vertical alignment to Center next I'll adjust the column widths if you're looking for a more precise adjustment you can click on the column drop down and select resize column then you can type in the exact number you want you can also select multiple columns and right click to get the resize columns option when formatting I also like to turn off the gridlines now I'll do some general formatting of the first couple rows to make the important information stand out even more I want to emphasize that the project start date can be modified so I'll also add a border around it next I'll select this area and set the horizontal alignment to Center I like table headers that have a slightly smaller font and are bold with a dark background and a light-colored font next I'll make each week's chart labels a bit more interesting I made the main date left justified in bold now I'll decrease the font size to match the table header I'll add a light background color and a dark gray border now I can use the format painter to apply those changes to the other three weeks to make the catch are easier to read I'll emphasize the phase rows with a background color and then add horizontal borders for the tasks I'll use the format painter again to apply those changes to the phase 2 section we could add bars by filling in the chart manually but that's way too time-consuming so instead we'll use conditional formatting to fill in these cells when the dates at the top of these columns are within the start and end dates first we'll select all of these cells and go to format conditional format under format cells if I'll select custom formula is from the drop down menu now we're ready to type in our formula it's important to create the formula based on the upper left cell in the range we use the logical function and setting the date in row for greater than or equal to the start comma and the date less than or equal to the end think of this formula is being copied to the rest of the cells in the applied range so we need to make the rows of the date references fixed by adding a dollar sign in front of the row numbers will also make the columns of the start and end dates fixed by adding dollar signs in front of the column letters I want the color of the bars to match the design better so I'll change them to a dark blue now we have a basic Gantt chart but let's continue to add more features our timeline currently starts on the same day as the project start but I want it to always start on Monday I'll edit this formula to subtract the week date of the project start after the comma I'll type 3 which makes Monday a zero now the weeks in the timeline will start on Monday regardless next I'm going to add an input that lets you scroll through the chart under project start I'll enter display week next to that I'll enter a 1 and then I'll use the format painter to make it match the project start I'll fix the 1 being changed to a date by going to format number automatic now I'll edit the first day in the timeline again to add the display week - one times seven now when I change the display week the dates in the timeline will update letting me scroll through the chart next I want to highlight the current day in the timeline which is actually pretty simple I'll select the rows in the timeline with a day number and letter then go to format conditional formatting and select custom formula is from the drop-down the formula will be a comparison of the date at the top of the column to the function today I'll make the highlight red and the font white and bold first I'm going to insert three new columns this first new column is for who the task is assigned to the next two columns are where we'll show the task progress I'll change these cells to the percentage format then I'll enter in some example data in the next column will create bars that represent these percentages to do this we'll use the sparkline function first I'll have it reference the cell with the percent for the chart type option I'll use bar then I'll set the color of the bar to gray finally I'll set the maximum value to one so 100% shows a full bar I'll take that formula and drag it down to the other cells in phase one I'll need to fix this error that happens when the reference cell is blank it's an easy fix we'll simply use the if error function that returns a blink when the % cell is empty I'll copy this updated formula select the cells I want to apply it to then go to edit paste special paste formula only I'll test it out in Phase two to make sure it works correctly I'm going to speed this next section up a bit where I update the formatting of the new progress columns we just created to finish things off I'd like to see the duration of each phase as well as group two tasks for easy viewing I'll first use the min function to grab the earliest start date from phase one's tasks then I'll use the max function to get the latest date so now that we have a start and end date in the phase row the bars will appear automatically now I'll copy and paste the min and Max formulas into Phase two grouping rows is a great way to toggle the data you want to see in print I'll simply select the rows I want to group then go to data group rows I can now see on the left of rows I have grouped and I can show or hide them by clicking on the icon for Phase two I'll use a keyboard shortcut to group the rows now you can easily show or hide the tasks for both viewing as well as printing I hope that along with this Gantt chart you've also learned some great new skills and Google sheets if you liked this video and want to see more please subscribe to our Channel you

Channel: Vertex42

Views: 842,331

Keywords: google sheets, gantt chart, project management, google sheets tutorial, how to use google sheets, using google sheets, google sheet tutorial, google sheet, google spreadsheet

Id: 8eKk0M2zGIk

Length: 10min 40sec (640 seconds)

Published: Mon Jun 22 2020