# The Excel Gantt Chart That Replaces Your Project Management Tool ## Метаданные - **Канал:** MyOnlineTrainingHub - **YouTube:** https://www.youtube.com/watch?v=vTbW8aNnWwY - **Источник:** https://ekstraktznaniy.ru/video/44781 ## Транскрипт ### Segment 1 (00:00 - 05:00) [] Most teams end up paying for tools like Asana, Monday, or Notion just to manage a simple project timeline. Not because Excel can't do it, but because every time they try to build a Gantt chart in Excel, it ends up looking confusing, fragile, or just plain ugly. Today, I'm going to show you how to build this instead. A fully modern, color-coded Gantt chart that automatically updates when your dates change, and shows live progress on every task right here in Excel for free. And the best part is that this entire chart runs on just three inputs: start date, duration, and progress. Once those are entered, everything else updates automatically. Now, if the pace is too fast, use the cog icon to change the playback speed. And don't forget you can always download the completed template from the link in the description. The first thing we need is the task list. I'm going to start with a simple table that contains the core information about each task. The task name, which phase it belongs to, who owns it, when it starts, and how long it runs. Then underneath, I'll enter the tasks for the project. Next, I'm going to format it as an Excel table with the keyboard shortcut Ctrl T. My table has headers, so I'll leave that checked and click okay. Now, using a table is important because it allows formulas to fill automatically. I'll rename the table on the table design tab. We'll call it project. I'll also change the color theme for the page. Under colors, I'm going to scroll down to the bottom, and I want aspect. This is just going to save time when it comes to choosing colors. On the table design tab, I'm going to go with lavender table style light six. And I'm going to turn off the filter buttons. The table's not that big that I need to filter it. And then on the view tab, we're going to turn off gridlines. I'm going to make the table rows slightly bigger just to allow a bit more space. And then we're going to center the data vertically. Let's also bump this off to the right slightly. We'll do a little bit more formatting while we're here. I want the fill color to be this shade of purple and I want my font to be white. I'll also add fill color for the header in this dark shade of purple and we'll call it project tracker website redesign. Next, let's make a couple of these columns easier to fill out. For the phase column, instead of typing in the phase every time, we can create a drop-down list. By the data tab, data validation, in the allow we want a list and in the source I want planning, design, development, and launch. And you just comma separate them. Click okay. Now every row has a drop-down that we can quickly select from. I'll do the same for owner. So on the data tab, data validation, in the allow we want a list and we've got James, Lisa, Priya, Sarah, and Tom. Click okay and now we can select from the list, which makes assigning work a couple of clicks instead of typing. The start date is straightforward, so I'll just paste those in. And instead of the short date format, I'm going to control one. I'm going to create a custom number format for the date that shows the day hyphen and then I want the month abbreviated and then hyphen and just two digits for the year. Click okay and that's done. The duration is how many working days you expect each task to take. I'll paste those in. Next, we want Excel to calculate the end date for us and it should be based on the start date and the number of days the task takes. But we also want to skip weekends, so our timeline reflects working days. And for that, I can use the work day international function. We're going to take the start date minus one so that we include all of the start date. The number of days we want to calculate forward is the duration and then we specify what days our weekend falls on. You can see there's lots to choose from. Mine is Saturday and Sunday, so that's one. We can also insert a list of holidays or reference some cells containing a list of holidays. I'm going to leave it at that and press enter. Let's copy the format for this date to our end date. And we need the column a bit wider. Now, whenever a start date changes, the end date automatically updates as well. Likewise, if the duration changes, let's undo both of those because next we want to track our progress, and we'll do that with the completed and progress columns. The completed column stores how many days of work have already been finished. To be clear, the days completed value isn't something Excel can calculate automatically. It simply represents how many days worth of work has been finished on that task so far. For example, if a task takes 10 days and about 3 days worth of work have been completed, you'll enter three. How you measure that progress will depend on how your team tracks work. And the progress column simply calculates the completed days as a percentage of the duration. ### Segment 2 (05:00 - 10:00) [5:00] Let's format that as a percentage. Let's grab all these cells and we'll center them. Now, we can build the timeline across the top. I'm going to skip a column here and start my timeline in cell J5. Instead of typing every date manually, we can generate them automatically using the sequence function. The first argument is how many rows I want. Well, just one row across the top. And then for the number of columns, well, that will be determined by how many days in my project, and I can calculate that by taking the maximum of the end date of the project minus the minimum of the start date of the project. Close parentheses on minimum. I'm going to add one to the start date so that includes the whole start date. And then our starting date, well, that will be the minimum of start date. The last argument is the step. Well, the default is one, so I'm just going to close sequence, press enter, and it's returned a load of date serial numbers. Let's format them. Control one. We want a custom number format, and in here I want the day, and then I want the month wrapped down onto the next row, so I'm going to control J to insert a line break. You can't tell it's there, but bear with me, and then I want the month name. It doesn't show it wrapped in the sample, but I'll click okay, and then when I apply wrap text, it's now wrapped onto the next line. Let's center it, and we might make the font a bit smaller, and we'll make the row just a little bit wider to give it space, and align it vertically. So, now we have dynamic project calendar. If a task starts earlier or finishes later, the timeline is going to update automatically. For example, let's change this to the 1st of March. Now, our dates start on I'm going to control Z to undo that. Lastly, let's just grab these columns and make them slightly narrower, so it looks more like a grid. Now, we can create the actual Gantt chart, which is simply a grid that fills with color whenever a date falls between the start and end date of a task. To do that, I'm going to grab the cells that line up with my tasks and timeline. Now, if you expect the number of tasks and duration of the project to be variable, select some spare rows and columns to allow for growth. We're going to apply the colors using conditional formatting, and I want a new rule, and I'm going to use a formula to determine which cells to format. I have two criteria, so I'm going to use the AND function. The first criteria is to check whether the date, and here I need to press the F4 key once to lock just the row, so it checks the date in every column, and I'm checking whether it's greater than or equal to the date, and here I need to press the F4 key twice to lock the column but not the row, so it checks the start date on every row. The next criteria is again whether the date, F4 once to lock just the row, is less than or equal to the end date, and again we're locking the column. Close parentheses. The format I'm going to apply here is a cell fill in this pale shade of purple. Click okay and okay, and now instantly we get the Gantt bars that stretch across the calendar according to the start and end dates. Now, let's make it a bit smarter. Instead of one solid bar, we want the completed portion of the task to appear different to the remaining work. So with the cells selected, conditional formatting, new rule, use a formula, and again we've got multiple criteria, so we need and. The first criteria is whether the date here, F4 once to lock just the row, is greater than or equal to the start date, F4 twice to lock the column, and the next criteria is whether the date, F4 once to lock the row, is less than or equal to the work day after the start date. So here we'll use work day international to take the start date, F4 twice, minus one to include the full start date, plus the number of days completed, F4 twice to lock that, and our weekends are number one, which was Saturday and Sunday. Close work day international, close and the format will be another fill color in a slightly darker shade of purple. Click okay and okay. Now, as we increase the completed value, the dark section of the bar grows, and this gives us a live progress indicator directly inside the Gantt chart. Let's undo both of those changes because another nice touch will be to apply a different color in the tasks that are completed. So, let's select the Gantt area again, conditional formatting, new rule, use a formula, and here we've got one more criteria, which is to check whether the progress, and we want to F4 twice to lock the column, equals one or 100%, and then the rest of the formula is the same as the previous rule. So, I'll just ### Segment 3 (10:00 - 15:00) [10:00] paste that in. Let's go and apply the format. This time we're going to give it a green color for complete. Click okay and okay. Now we can see these two tasks are complete. Let's add another touch, and that's to apply a white border top and bottom to these rows. So, with them selected, control one to open the formatting, on the border, we want white, and we're going to set it top, middle, and bottom. Click okay. Let's repeat that on the progress here as well, and you'll see why later on. One more thing would be to shade the weekends, so the timeline's easy to read. So, selecting the Gantt area again, we're going to apply new rule using a formula, and here we can use the weekday function to check whether the date here, and I'm going to F4 once to lock just the row. The next argument of weekday allows me to tell it how to number the days. So, I want two here. That's going to number Monday with one and Sunday with seven. So, all I need to do is check whether the date is greater than or equal to six. That is Saturday will be six and Sunday will be seven. So, if it's greater than or equal to six, I want to format the cells in this shade of gray. Okay and okay, and now we can easily see where our weekends are, making it much easier to scan. Let's also not forget about today's date. I'm going to select the dates in the header. We're going to apply another format using a formula. This time all I need to do is check whether the date here, and F4 once to lock the row, equals today's date, and I can use the today function to automatically return that date. Which means tomorrow when I open the file, I won't need to edit it. We're going to format that in red, so it stands out. And you can see I'm recording on Saturday, the 7th of March. We've got a few more things to do, but let's just test how it works. If I change the start date here, let's say to the 13th of March, you can see my Gantt bar automatically shifted. If I change the duration of this task to say 10 days, it automatically expands to finish on the 18th of March. And let's say we change the progress here to six, which is now complete. This kind of automation is exactly the sort of thing we focus on in my Excel expert course. It shows you how to combine formulas, tables, and conditional formatting to build spreadsheets that update themselves instead of needing constant manual fixes. So, if you'd like to take your Excel skills beyond the basics and start building tools like this for your work, you can check out the course using the link in the description. All right. Let's keep building this Gantt chart. Back in the progress column, we can add a visual indicator here as well. So, with that selected, I'm going to add conditional formatting. This time, I want a color scale where green is complete, 100%, and red is not started. The color scheme's not in keeping with my theme, so let's go in and manage rules. I'm going to double-click to edit the rule, and let's just change the shades of green to something a bit more subtle, and likewise for red. Mm, that looks much better. So, even without looking at the exact numbers, you can instantly see task progress. Finally, let's add some quick summary metrics. For example, we can count the total number of tasks, and I want this header to wrap down onto the next line, so press Alt Enter to force it to wrap. Going to use the count A function to simply count the task names. And because this is in a table, it's super quick and easy for me to select the columns. Just hover your mouse above the column header until it turns into the down arrow. Click once. It puts in the table name and column name. Press enter, and that's done. Let's count the completed tasks. This one we need count if. So, we're counting the progress if it equals one or 100%. So, we've got three complete tasks. Next, let's count what's in progress. Again, I want to force it to wrap. So, that's alt enter. This is also going to be count if. This time we're counting where the progress and the criteria is greater than and zero. So, where the progress is greater than zero. This is obviously going to include those that are 100% complete, so I need to subtract the completed tasks, which is in that cell. Press enter. It's returned a percentage because I've counted percentages. Let's just put it back to general. And lastly, how much has not started? Alt enter to wrap down onto the next row. And this is simply the total tasks minus the completed tasks minus those in progress. Let's also add a color legend for our Gantt. So, we need to put in the formatting colors and explain what they are. So, this is complete. Our in progress is the dark purple. Remaining is a light shade of purple. The weekend is a shade of gray. And today's date is this red color. And that's it. From a completely blank ### Segment 4 (15:00 - 15:00) [15:00] worksheet, we've built a fully dynamic Gantt chart. Everything is driven by formulas and conditional formatting. No add-ins, no macros, and no project management software required. And remember, if you prefer not to build it yourself, you can download the finished template from the link in the description or pinned comment, so you can start using it at work tomorrow. Another tool that you can start using tomorrow is this self-updating work schedule. Watch this video to learn how to build one on your own or download the template. I'll see you there.