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 tasks 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 was for 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 used a scatterplot to do that, and I used the arrow bar technique to show the number of days that each of these tasks is going to take me. And then because I used a scatterplot, 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 planned 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 used the workday 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 actuals, but what I want to add on is to be able to change the view of the chart so that if the user selects plan, they see the planned Gantt chart. And if they select actual, they see the actual Gantt chart. So here I want to introduce a data validation where the user can select the scenario. I'm going to use list here. I'm just going to type in actual and plan. Okay, so let's just 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. Okay, so plan is something that I'm not going to change. It's going to be fixed. 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 five. Let's change this one to 10. And instead of starting on the 13th, let's start on the 10th 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 view. 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 and 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 stacked 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 change this to date format and do the same thing, you can see it 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. And for dates, it's not 100% sure because normally we do plot dates 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 stacked bar chart here. Now this part, that's the number of days it's going to take me and Excel didn't start from zero. 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 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 to four, two. Let's go with 730. Okay, so that looks better. Let's 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 and expand it a little bit more. And 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 mark, 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 it'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 arrow 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, has 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 two series. We have a bottom stack and we have the top stack. If we activate arrow 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 minus. 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 depend 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 to 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. And because I only want to show this for the actual series, I need an if. Let's fix it. Equals, I need 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, and 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. So like no cap from here. The color should be a nice green and it can be much thicker. So let's just go with eight. That looks good. Okay, so for actual, it's 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. Now this is completed. This we've done 50%. This we've done, let's do 20%. And this we've done 10%. 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 axis. And the axis 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 plan. Then it should show this task. And I'm going to add a space, an 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, WD, close bracket, quotation mark. And close the final if bracket. Okay, so what happened now? So I just wrote if this is planned and 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 part, paste it here because A5, well, 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. That's 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 task at seven, let's change the number of working days to 10 days. Okay, 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 like 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.