How to Make a Gantt Chart in Excel with Progress Bars (actual, planned & percentage completed)

in this video I'm going to show you how you can create a fully featured Gantt chart and by fully featured I mean it can do two more things than the quick Gantt chart I showed you in a previous lecture one is that we're going to see the percentage completion so we're going to have the bars be conditionally formatted depending on how much of that task we've completed and two is that as we're getting into the project we're going to end up changing things around for some tasks we might need longer for some tasks we might end up using less time and for some times we might end up shifting the date so we always want to keep an eye on the original time plan and be able to compare that to our actual time plan so it should allow us to select between the different two scenarios the way I'm going to create this is to use an actual Excel chart now in a previous lecture I showed you how to create a quick Gantt chart so that will try the case when you've just come up with your project time plan and you quickly want to present it in a meeting so you haven't actually started with a project you just want to have an idea of the timeline of the number of working days each task takes and so on but once you start working on the project you actually want to show how you end up changing things around and which of the tasks you've already completed to make the quick Gantt chart I use a scatter plot to do that and I use the arrow bar technique to show the number of days that each of these tasks is going to take me and then because I use the scatter plot I actually put in the tasks themselves in Excel cells and then I positioned this chart right in front of the tasks I'm going to put a link to that video in the descriptions below for the fully featured Gantt chart I'm going to use a combination of two techniques I'm going to use the arrow bar technique to show the percentage completion but I'm going to use a stacked bar chart to show the length of the actual tasks themselves so let's see how we can set that up here I have the list of my original tasks now that's going to be the plant version I'm just going to copy and paste this here and here I'm going to keep track of the actual times each of the tasks is going to take me originally I use the work day function to calculate the end date based on the number of working days which excluded weekends and the holidays that I have listed right here I'm going to leave that same logic for actual but what I want to add on is to be able to change the view of the chart so if the user selects plan they see the planned Gantt chart and if they feel like actual they see the actual Gantt chart so here I want to introduce a data validation where the user can select the scenario you I'm going to use lists here I'm just going to type in actual and planned okay siliceous format this accordingly just so that they know they can select from here this is plan this is actual I'm going to press control 1 to format this as well and use center across selection while we're here let's just add a bottom border to this as well the caso plan is something that I'm not going to change it's going to be fixed so let's just make that green this is what I'm going to change so just to be able to see when we switch things around let's change the number of working days in actual to 5 let's change this one to 10 and instead of starting under 13th let's start under tents here so now because my chart needs to change its view I am going to add a data preparation table right here let's just put these out of you here is going to be my data prep table I need to show the start date and not the number of working days though I want to show the number of full days I want to include weekends and include holidays because that's going to be the length of my task but my start date is going to depend on what the user wants to see do they want to see actual or do they want to see plan the if function can work great here if this cell I'm going to fix it with the f4 key equals plan and it's not case-sensitive then it should show this otherwise it should show this day for the number of days we're going to do a similar thing so if this one let's fix equals plan then I don't want to show the number of working days I want to say the end date minus the start date otherwise it would be the actual end date minus the actual start date okay so here I selected actual even though the number of working days is five the actual full number of days is eight so let's start to create our Gantt chart I'm going to highlight this I'm going to hold down the control key and I'm going to highlight this in this now notice I haven't shifted this to date format yet that's for a good reason I'm going to show you quickly if I go to insert and select the Stax bar chart you can see it looks right it has my tasks on one side and the dates on the other side but if I changed this to date format and do the same thing you can see gets a bit confused it's actually plotting my dates on the y-axis which is something I don't want so that's why I'm going to keep it as general as numbers because Excel takes the best guess of where it thinks you want to put it now for dates it's not under percent sure because normally we do plus based on the category side of things so I'm going to hold down control hold down control and highlight these go back to insert and insert the fact bar chart here now this part that's the number of days it's going to take me and Excel didn't start from 0 it took a good guess it automatically put the minimum at this number now I'm going to adjust this minimum to start somewhere from here just so I don't have a big gap but one thing I'm going to do before is I want this the other way around I want to design on top and sign off on the bottom so I'm going to double click this and tick mark categories in reverse order now for this axis let's increase the minimum two for two let's go with 7:30 ok so that looks better to expand this I'm going to remove this now this stack here this bottom stack we don't want to see that so I'm going to hide it from view by taking away the fill color the other thing I'm going to do is increase the gap width just so that I get thicker bars and change the color to my favorite which is a gray color remove these lines and now let's change these numbers to dates now because by default the formatting of the axis is linked to source I can already change it here and it's going to reflect it in here okay so let's position the chart right here let's remove the outline let me expand it a little bit more let's check if this works yes that looks really nice so one thing I'm going to do is give some breathing space to the title I do want to make it obvious which view I'm in right now I'm looking at right now in the chart so let's make that dynamic by putting it up here so I'm going to move that up and type in project timeline space quotation marks and this cell and space view I'm going to click on the chart title say equals and click on this so now that's the actual view I switch that's the plan view okay so I'm on the right track what I want to add right now is the percentage completion because we'll be nice when I'm presenting this in the meetings to show which tasks I've fully completed and which tasks are in the process of being completed I want to add that here and let's just copy the formatting here but this needs to be a percentage so here let's say I've done 100% here is 80 here is 20 and so on so I want this to be reflected in here I'm going to show this using Aero bars remember when I say that if you want to show horizontal arrow bars you need to use a scatter plot because scatter plot has values as numbers on the X as well as the y axis now the bar chart also has horizontal arrow bars because it has categories in the y axis and values in the x axis this means that we don't need to introduce a new scatter plot series right here we can use the existing series that we have and activate the arrow bars for that series but we have to series we have a bottom snack and we have the top stack if you activate Aero bars for the bottom stack it puts a point right here at the end of the first stack and the beginning of the second stack so that would actually be perfect in this case so I'm going to highlight that stack from the plus here activate the arrow bars now let's go to more options my point is right here I don't want these lines to go on both sides I don't want it on the - I just want it to go this way so I just need to select the plus now I need to specify the length of these lines well that really depends on this percentage but not directly on the percentage because that's going to be the percentage of the bottom stack which I don't want I actually want to translate these two days so if it's a hundred percent its length should be identical to this it should be eight then it should be 80% of 14 and 20% of 16 so I need to add that in here that's going to be number of days completed because I only want to show this for the actual series I need an if let's fix it equals the quotation marks' equals plan then it should be zero otherwise it should be this percentage multiplied by this number okay so that looks good now let's bring this in here so the length of these is now going to be controlled by this if we go to custom say specify value for the positive arrow bars I'm going to select this data okay so now the length of it looks good I don't want the cap select no cap from here the color should be a nice screen and it can be much thicker so let's just go with eight that looks good okay so for actual is showing it let's switch to plan it doesn't show anything which is exactly what I want so now let's say we are going to our next meeting that this is completed this we've done fifty percent this we've done that's the twenty percent and this we've done ten percent they all get reflected here now as a last last thing what I want to do is to also add the number of working days to this chart but I don't want to add it inside here because it might be confusing about what that number is representing if it's the number of days that's remaining the full number of days originally planned or not so I'm actually going to add it here in the axes and the axes can also be dynamic it doesn't have to be static so you can make it dynamic using formulas what I'm going to do is because we want to show different days if they select plan and actual I'm going to again use an if this let's fix it equals porn then it should show this task and I'm going to add a space and Open bracket so inside the bracket I want to hold the number of working days so bracket open and that's the number of working days and let's put some text there so quotation marks space w d close bracket quotation mark and close the final if bracket okay so what happened now so I just wrote if this is plan than this but I haven't given it an otherwise so we can continue so otherwise if this is actual well I'm going to copy this card paste it here because a 5-volt that's going to be the same task but I don't want to show this number of working days I want to show the G column so I'm just going to change that to G let's drag this down and see if it looks nice here great last step is add this instead of this click basically anywhere in your chart and go to select data now these are the values in the chart I don't want to change them this is my axis my y-axis categories I'm going to edit this and instead of my original one I'm going to select this one I can see them there they just need some more breathing space okay so now we can see that's actual that's the number of working days we need for each task of how much percentage we've completed and we can switch to plan and that was the number of working days we had originally planned so if we do change anything in our data table everything should be updated as well because all of this side here these are just formulas to make this chart dynamics I'm just going to highlight them put them in a green color the only inputs I'm doing are these here so if I change now the number of working days let's say for this test at seven let's change the number of working days to ten days the Kate that shows up here and that pulls through here as well so this is how you can create a fully featured and dynamic Gantt chart in Excel if you liked this video don't forget to give it a thumbs up and if you like these type of videos don't forget to subscribe to my channel so that you can get notifications when new videos like this one come out

Channel: Leila Gharani

Views: 226,982

Keywords: gantt chart excel, gantt chart excel template, XelplusVis, make gantt chart, gantt chart tutorial, exclude holidays and weekends, project plan in excel, project management, excel gantt chart with progress, excel gantt project planner, gantt chart progress, excel gantt chart bar graph, excel gantt chart actual vs planned, Advanced Excel Tricks, planning in excel, Advanced Excel Tutorials, Advanced Excel Online Course, Leila Gharani, Excel 2016, Excel 2013, Excel 2010

Id: KtR-CVxC5qA

Length: 17min 23sec (1043 seconds)

Published: Fri Jun 23 2017