Hello everyone ! Today we are going to create a Gantt chart in Google Sheets, without going through the graphic tool and therefore, as you will see, in a much more practical, much faster and ultimately much simpler way. This diagram will take the form of a classic Gantt diagram. So on the left we will of course find the list of tasks as well as all the different attributes that will be assigned to each of these tasks. So we start with the task number, the description of the task itself, the responsible for this task, a start date and an end date. Then a number of days which will be calculated according to the end date and the start date. On the penultimate column we will also be able to mention the status of the task so either it will be in progress, finished, pending or at the very beginning, it will not yet be started. And finally in the last column, we will find the percentage of the task that has already been completed, so of course from 0 to 100% when the task is completed. Then on the top of the table as you can see we will find the different dates which will therefore be used to delimit the slots of our Gantt chart. So quite simply each of the boxes or each of the blocks of each of the slots in our diagram will correspond to a whole week and not to a single day. So quite simply on row 2 we will find the number of the week so from 1 to 52 or 53 throughout the year. And on the row located just above, we will find a date which will in fact correspond to the Friday of the week in question. And finally we will find the main part of our table where we will find the visualizations of the different slots assigned to the different tasks of our Gantt chart and which will therefore have to be updated automatically according to the different updates that we will do by ourselves on the left of the board. So for example the most basic thing to manage is going to be the case where we change the start date and the end date. So here for example I'm going to choose to finish the task two weeks ahead of schedule and as you can see the visualization has also updated here for our slot. Then what we will also be able to do is play with the different statuses assigned to this task. So we will be able for example to put it at the beginning in "Pas démarrée" ("Not started") when the task has not yet started. Then we will change it to "En cours" ("In progress") and finally we can change it to "Terminée" ("Completed"). So as you can see so far it does not affect the visualization of our slot but on the other hand for the fourth and last option which is called "En attente" ("Pending"), we will simply choose here to display the diagram in red to simply show that there is a problem since the task is pending and therefore there is something blocking that we will have to solve. And finally now we will also be able to play with the percentage of a task in question. So for example here when we have 0% of the task that is done we see that the slot is displayed in light blue. All the blocks of the slot are displayed in light blue and on the other hand if we now pass it to 50%, as you can see there is a small change since the first two blocks, corresponding to 50% of the slot, will now display in dark blue, which therefore constitutes the completed part of the task. And the last two blocks which are not yet made remain in light blue. So now we find ourselves in a brand new Google Sheets file, where we will be able to create our Gantt chart from A to Z. So we will start by putting the titles of the different attributes of our different tasks. So we start with the task number, then a description of the task itself, the person responsible for this task, the start date, the end date, the number of days assigned to this task, the current status of the task and finally the percentage of the task. So now for the first five columns, since it's just filling, what we're going to do is we're just going to copy the data from our template to our new Gantt chart file. Here it is like this and on the other hand what we must also do on the fourth and fifth columns, it is to change the format to display dates and not numbers. And what we can also do in these columns, to check that the user only enters valid dates, is that we will use data validation to consider only the date format. So we go to "Données" ("Data"), "Validation des données" ("Data validation"), we add a rule and what we want is not a drop-down menu but only a correct date. We click on OK. And this way if you want to change the date later, all you have to do is just double click on the cell and there you will have a small calendar that appears where you can therefore select the new date of your task. Then we must calculate the number of days that will be assigned to each of our tasks. So for that it's very simple, we start a calculation and we simply subtract the start date from the end date of our task, which we also copy of course on all the rows to calculate for all the different tasks. So now for the status, what we're going to do is we're going to limit the choice to four different options through a drop-down menu. So for this drop-down menu, the first thing we will do is create a list with the different statuses that will be available in a new sheet that we will simply call "Status". Once this is done, we will select the entire data range and we will therefore create our drop-down menus by clicking on "Données" ("Data"), "Validation des données" ("Data validation"). So we have a new pane that is displayed on the right where we will simply add a rule. So for this rule we will choose as criterion not "Menu déroulant" ("Drop-down menu") but we will choose "Menu déroulant depuis une plage" ("Drop-down menu from a range") and we will select this range by going to the sheet we have just created and therefore selecting the range of data where the different statuses available have been listed. So we click on OK. We have our four statuses that are displayed here and what we will also be able to do is give a color to each of these statuses to be able to differentiate them in our diagram. For example for "Pas démarré" ("Not started") we will put it in yellow, "En cours" ("In progress") we will put it in light green, "Terminée" ("Completed") in blue and "En attente" ("Pending") we will put it in red. We click on OK. And if we go back to our diagram, we see that we now have different drop-down menus. And if we click on one of these menus, we see that we have the choice between our four different options which are displayed according to the colors that we have just selected. And finally for the last column, what we will of course want to do is display percentages and not normal numbers. So for that the first thing we will do is simply select the entire data range and click on the small % sign to simply tell Google Sheets that we want to display percentages. So we will put for example 50% for the first task and 0% for all the following tasks. So as you can see, Google Sheets defaults to two decimals for percentages. And as you can imagine in our case it will not be really useful since it is an approximate percentage of completion of our various tasks. So we select our data range again and we will click twice on this icon to delete these two digits after the decimal point. So the filling of the left part is now complete and before moving on I'm just going to do a little reformatting to have a better visualization of it. So now we will be able to take care of the top part of our Gantt chart where we will therefore be able to manage the different time slots of our schedule. So we will start by inserting the different dates corresponding to the different weeks of our Gantt chart. And in our case we will choose to show the date of Friday for each of the weeks but it is not mandatory, you do of course as you wish. So we start with January 6, 2023, which was the first Friday of the year and for the next column, we will use a formula by referring to the previous date, so the date of the previous column, and adding to it 7 units for 7 more days to, of course, be able to find the date of the Friday of the following week. What we can also do is already reformat that to make the visual a little more compact, by putting these dates vertically rather than horizontally. So quite simply we select the entire column or rather the row and we will select this icon, where we will therefore have the possibility to give different orientations to the text in the cells. So in this case we will choose to show the dates vertically from bottom to top. We can also recenter all that in the cell. The formula that was valid for the second date is of course still valid for the following dates. So we simply copy it as many times as we want to get all the dates for the Fridays in our Gantt chart. And what I'm still going to do is just reduce the width of the columns a little bit to make it a little more compact and a little more visual. Then what we will be able to display on the second row is also the numbers of the different weeks, from 1 to 52. And for that we will simply use a formula called WEEKNUM (NO.SEMAINE for the french version of Google Sheets), which will take into input a date and which will simply return the number of the week corresponding to this date. We copy this same formula on all the columns of our Gantt chart and we therefore obtain for each of the dates of this one, the number of the week which is associated. So we have finished the structure of our Gantt chart and now we will be able to focus on the main part of it, where we will find the different slots which will be made up of cells colored in one way or another, depending on the different parameters that we will insert on the left side of our Gantt chart. So to do that we will first select the entire data range where we will want to insert our different slots. So in this data range, we will have to color the cells in a certain way according to the different parameters on the left. For that we will use the tool which is in the "Format" menu and which is simply the "Mise en forme conditionnelle" ("Conditional formatting"), which will therefore simply allow us to color the cells in one way or another, depending on a certain condition. And now as you can see, we have a new pane that appears on the right of our screen, where we will be able to define all the parameters of our conditional formatting. So the first thing to configure is the data or application range of this formatting. So we have nothing to modify since we had already preselected this range before selecting the tool. And the second thing to select is going to be the formatting rule to apply. So if you click on the drop-down menu, you can see that we already have access to several predefined rules, but the ones that will interest us, which will probably be the most complex as well, is the last rule which is a "Formule personnalisée" ("Custom formula "). So for this first example of conditional formatting, what we're going to want to do is simply display in light blue all the cells of a row which will correspond to dates (therefore the date located on the same column) which will be between the start date and the end date of each tasks from our Gantt chart. So with that of course we will be able to display, for each of the tasks, the time slot within which this task must be completed. So for that we will use the following formula. So we start with an equal sign. And which then starts with an AND function, which will therefore allow us to combine two different conditions. The first condition being that the associated date on the same column is greater than the start date of our task. So that translates mathematically in Google Sheets to J1, which is the date of the cell in the same column and which must be greater than D4, which is the start date of our task. And finally the second condition, after the semicolons in our AND function, it will obviously be that this same date, associated with our cell in the same column, must also be less than the end date of our task. This is therefore translated once again by J1, which is always the date associated in the same column as our cell, must be less than or equal to E4, which this time is the end date of our task. And finally for the formatting style. So as I told you we don't want to display these cells in green but instead, we will change the fill color and display it in light blue like this. Once it's finished, we simply click on OK and we can therefore already admire the first result of our Gantt chart which is displayed on the screen. Then what we will also be able to do to improve our Gantt chart a little bit, this is simply to display in a little darker blue all the cells which will correspond to the completed part of the different tasks of our Gantt chart. So for example for the first task, what we see is that we have already completed 50% of this task. So to see that visually on our graph, what we're going to want is simply to display half of the cells, so 50% of the cells corresponding to our task slot in blue, a little darker. So we're going to add a new conditional formatting by clicking on "Ajouter une règle" ("Add a rule") in the menu that appears. And we open the same pane that we had for the previous rule and where we will define the parameters in a roughly similar way. So we will still use a "Formule personnalisée" ("Custom formula") which will be this one. So as you can see this formula starts out similar to the previous rule with an AND function to combine two conditions. And the first condition will also be exactly the same as for the previous rule, ie the date associated with the cell must be greater than the start date of the task. On the other hand, as you can imagine, the second condition will of course be different since in this case, it will no longer be enough, for the date associated with the cell, to be less than the end date of our task. But instead it will have to be less than the start date, increased by the number of days assigned to the task, itself (the number of days) multiplied by the percentage completed of this task. Then for the formatting, as I told you in this case we will display it in a slightly darker blue. That's how it is. We click on OK and there, as you can see, nothing happens since in fact, if we look at the order of the rules that we have just established, we see that the first rule which therefore has the first priority is is still the rule that we wrote first, which was therefore the rule allowing to display in light blue all the cells corresponding to the slots of our different tasks. So to give priority to the second rule that we have just written, we will simply click on the four points that we will find here on the left of this ruler. And while remaining clicked on these four points, we will move it upwards to be able to give it priority. So as you can see, now we can see that the cells corresponding to the completed part of the different tasks are displayed in dark blue. Finally there will be a last case where we will also be able to use conditional formatting. This will simply be the case when a task will be put on hold and therefore when we will want to display all the cells corresponding to the time slot of this task in red. So we add a rule and we will configure this rule in the same way as for the previous rules, with a "Formule personnalisée" ("Custom formula") which will be the following formula. And in this case what we want is to display the cells in red. So we select red like this and click OK. Again in the order that the rules are displayed, this means that red cells will not have priority. So the cells will automatically be displayed in blue since the condition is sufficient to be able to display the cells in blue, before going to see if these cells can also be displayed in red. So to avoid that, we're once again going to give priority to the last rule we just wrote by passing it here first in the list of rules. And now, normally if we change the status of the second task and put it on hold, as you can see we have the cells that are displayed this time in red as we have just defined it with our rule. So now our Gantt chart is functional, the only thing we can still do before leaving is to put it in shape a little bit with a few lines to make it really presentable. So we're going to put lines in light gray so that it's even a little cleaner, like this. In the same way, we will only put lines for the left part. Here it is. And you can also remove the grid to display a clean Gantt chart. And that's it for our Gantt chart. I hope you enjoyed all this and I say thank you and see you soon!
