Create Gantt Chart in Excel (Part 1) | Gantt Chart for Project Management & Project Planning

can you create a gantt chart without using any project management tool like ms project in this video i'll tell you how you can do that also at the end of this video i'll share one bonus step which will also be very useful for you to create gantt chart so keep watching this video till end without taking any more time let's start the video hi guys welcome back to tech reflections i am vikram and today we will see how we can create a gantt chart using excel this video is the first part of the series of videos to be published on gantt chart in this video we will see step wise how a basic gantt chart is created in excel and in subsequent videos we will add more features and utilities to this chart for creating basic gantt chart we would be extensively using conditional formatting with formula and if you are not aware of conditional formatting and how it works i suggest you should check out the videos related to conditional formatting by clicking on i button at the top of this video link of the same will also be provided in the description let's start with the gantt chart now this is the data we would be using for creating our gantt chart so in this data we already have defined few tasks like task 1.1 1.2 then we have duration also defined for these the class 1.1 is for six days and then we have start date for these tasks so this is the start date for uh task 1.1 and 1.2 so let's start first of all we need to understand that what is the end date for these tasks so how are we going to arrive to the end date so first of all let's add one column here and we'll add the caption as end date okay and now how this end date would be calculated would be start date plus the total number of days in the duration so that would be i'll use the formula equal to then this date and plus the number of days in the duration when we press enter we'll get the end date but but we just need to check here that right now is just adding six to the first day six days to the first day whereas our task would be starting when we say the starting date is second of september then the date second of september is also part of our activity so in that case uh what we will do we need to subtract one from this otherwise it will give one extra day every time so ideally this star should be having an end date as 7th of september which starts on 2nd of september with six days of duration okay so now we can just drag this for all the other activities also so you can see some error in between because those are the parents tasks and there is no start date and date defined for them so we can just remove these okay so now we have got the end date so most of our tasks are within september and october so we'll start creating our uh the right part of our grant chart where you will see the visual representation of these tasks so let's first add the data so first date here would be first of september we just enter first of september here and we just want to see only the day part here so we'll just go and change the format and then in the custom format i just remove the month and the year part so now it's only showing the day now let us drag it for the first week like the first seven days so now uh although it is showing uh zero one zero two but it's having full date uh actually in that cell okay so now we also want to add uh the name of this week or the week starting so we starting here would be first of september so in this column or maybe we can merge this column later on so uh we want to show the week starting uh date here so in our case the week starting would be like 1st of september so we'll use the same date we'll refer column e3 here and right now it is copying that format also so we just don't want that same format to be used we can just go to format cells and use our custom format and in this format let's say i want to show the name of the month first three letters of the name of the month so so this is shown as the week starting date so we can just now merge this all these cells so this will this is the first week of september we'll just first condense this cell sorry let's select all these cells and double click on at the end and it will condense all the cells now i want the same thing to be repeated for the second week also so i'll just drag it this is for the second week again we can take it further this is for can we get the october so we can again select all these and condense because it's going out of the view so let me select and double click at the end and i'll see all of them condensed okay so maybe we can just zoom a little bit more okay so we have zoomed out now we can see that the dates are available from 1st of september till 6th of week a week starting 6th of october that is 12 but we still have our last date as 16th so maybe we need another one week so we can do the same thing select the last week and just drag it and you'll get the next week also and you can then just resize all the columns okay so we can just change the format so that it looks it's the same for the in the same format as the rest of our headers you can even have them bold okay so this is fine now we'll select all the cells where we want this conditional formatting or where we want to show the gantt chart so let's start and select all these cells till the end of the month end of the day till up to the point where we have created our calendar and now we go to home then condition formatting create a new rule select user formula and formula we will be using is and this state should be greater than and equal to the state and the same date should be less than equal to end date but before pressing and moving forward we need to remove this right now we have this as an absolute reference so for the value of the days that we we are comparing on our calendar we want um column to be free so in that case we remove the dollar from here we'll improve the dollar from here also and then in the from the start date and date we will just remove it we'll i will just remove it for the row here and same for d also and then we select the format which we want let's say this is the color we want go to ok and now it is visibly showing all these tasks as per the dates we have put in start date or end date so if you want to check it let's say the duration for this particular task gets increased to 10 days so you see the chart gets updated automatically it can be for any task let's say this task becomes now for 16 days so this will also be updated so make it little more appealing so we can just add the borders to this part of our grant chart just right click and add all others all of this area and then go to format cells and let's remove this part this line and these lines are still visible but that's because we have the grid enabled gridline enabled so that also we can remove you can go to view and uncheck this grid lines and those lines will be removed so this looks more uh visually appealing to us so we'll keep it like this so this is the gantt chart we created for the planned activities but we also need to track when these tasks will be actually performed next in this video we will see how to show the progress of these stars if you're finding this video useful then like the video and share it with your friends also subscribe to the channel and press the bell icon so that you get this notified when next video of this series will be published so now we are moving to the next part that is we want to show the progress so before that what we will do we will just add one more column here insert an entire column okay so a new column is added where we want to enter percentage complete okay so what this column is for that for any task now we have already started working on this task and there is a progress to report on this let's say this particular task gets complete 10 that's it this one is 20 okay so this month this is currently formatted in the date format so we just need to change this column format we go to format cells and then go to number and select the percentage without decimals okay so now again we will enter the value that we want 10 percent for this 20 for this that's a 30 for this 40 for this 50 percent and so on let's say this is this is the percentage complete defined now how to show the same thing on our chart this is little tricky here because uh for initially for uh showing the plan task we had the start date and the end date but in this case we just had this percentage complete so what we are taking into consideration here is that when we say 10 percent so this this first class let's take an example of task 1.1 so this is the task and when we say that it is 10 complete so that means 10 of 10 days which means that one day of task is complete so that the calculation we would be using to show the actuals for the progress on this particular gantt chart let's see how that would be done so again we will select before that uh what we will do uh because that would be a little complicated formula so we'll try uh you know building that formula here itself so let's say first of all we will just calculate what is the 10 percent of our duration okay so this is one day which is fine now we want to add this one day to our start date to arrive to the date up till which this task is completed so we will just use some state and then okay so now uh because this one is not in the date format show so it is showing a number here just for the checking purpose we can go and format this into a date format to see if it is correct okay so it's showing currently as third of september but when we say when we earlier talked about these dates we saw that adding one day means that ideally it should be coloring only the second of september so that means only when we color up till third of september it will become two days so as we did earlier also we just have to subtract one from here okay so now this is the correct uh date we are getting so now how are we going to use this in our formula so let's try creating that formula here so formula was end and we just want to check this date greater than equal to the same as start date is same so which is okay and then we'll have another criteria that would be this state should be less than equal to the date that gets calculated from this formula so if we press enter it says false what does that false means is that this particular date is not satisfying uh our criteria which is okay because this class starts on 2nd of september and not the 1st of september so false is fine but before checking it on the 2nd of september we just need to modify this for the absolute or relative reference so as we did earlier first of all this f3 f3 needs to be referenced and um you must be knowing that if we use f4 f4 toggle f4 and we will be able to change between the uh the values of um to set them absolute or relative reference how that is then we just press f4 and then the first press both the rows and columns would be locked and that would be absolute reference for both you press once again and this time i think it is uh it is the one we want because we don't want to log our columns row can be logged which is fine so this is what we want same we would be doing with this f3 also and we'll just press f4 again and x is what we want now for c5 we again use the f4 again f4 and once more and now here we have logged the column same we have to do uh with this values also so here c5 again needs to be here the column needs to be locked so okay and for e5 column needs to be logged for v5 also for all three values we will log column and then press enter and if we uh drag this to the next one let's say yeah so this is fine so this shows us as true which means that the second date is fine that needs to be colored so we've got the true here let's try for three also third of september and that is false so our formula is working fine that means that only the date second of september needs to be modified or formatted according to this condition so we'll just copy this formula and then we'll press an escape to come out of the edit mode and we just select all these rows where we want this definition formatting to be applied go to home condition formatting new rule use the formula and this time we enter this formula and let's say we format it with a little darker color let's see if it works okay it seems to be working let's first delete these values that was just for checking okay so let's see how uh whether it is working or not so this ten percent is one day which is fine that's the second twenty percent is two days that would be third and fourth because this star starts from third and third thirty percent for this task so thirty percent of twenty days would be six days so that would be one two three four five six days so this seems to be working absolutely fine here so we are getting this uh percentage complete plotted on our gantt chart we can add few more let's say this task is 60 percent done this one is 70 percent this one is again already 20 this one is 20 and this one is 20 and we can even change let's say this we move from 50 to 70 so this graph gets automatically updated so here you can see how a basic gantt chart can be created out of excel when we have this kind of data available and also it's not only the planned part we can also see the progress of these tasks through conditional formatting in excel itself we don't need any other project management tool like ls project okay now uh as we discussed this is the time for the bonus tip so what is the bonus tip here so let's uh think that if we want uh the current date also to be displayed or may be marked here so what we can do we can again select all of this area chart area and that this thing also can be uh done through conditional formatting let's see how that can be done we again go to conditional formatting add a new rule again use the formula and this time we need to check this date with today today is the formula for uh returning today's date so again because we want to check it with all the dates so we just free the column and we mark this as let's say we want to change the border we change the color to let's say red and we just add this order okay okay from here and you can see clearly see there is a red line drawn as a border for the date which is the current date when you open it tomorrow there will be a separate uh this this fifth will be shown as a as the current date so there is lot more uh we will be adding to this gantt chart in our next videos for this series but i would like to know from you also what more you want to be want to add to this gantt chart which can be helpful to all of us drop your comment and let me know this is all we planned for today so stay tuned for the next videos if you want to learn more about excel functions formulas or tools the link to those videos will be there on your screen now i'll see you in the next video till then stay safe stay healthy good bye

Channel: Tech Reflections

Views: 3,693

Rating: 4.9365077 out of 5

Keywords: project management, project planning, gantt chart, gant chart, gantt chart excel, gantt, excel gantt chart, advanced conditional formatting in excel, conditional formatting formula, conditional formatting with multiple conditions, conditional formatting in excel, conditional formatting, Office 365, excel tricks, Excel tips and tricks, #techreflections, gantt chart for project management, project management in excel, project plan in excel, create gantt, dashboard

Id: yjbSdJrVCW0

Length: 24min 20sec (1460 seconds)

Published: Sat Sep 05 2020