Make a Gantt Chart in Excel - Part 4: Color Coding

[@TS-yy6jb]

This is the Gold Standard of Gnatt Charts now!

[@AhmadSABBAGHch]

this is a new definition of GANTT Chart

[@alessandramacedo5486]

Perfect!!! Should you add pert analysis for it? It’” be awesome!! hello from Brazil!

[@tjpredictions]

Love it, love it, love it! Thank you so much for putting it out there for everyone!!! I needed a project management tool for just a small project, without having to find and learn a new software and being able to modify it to our needs. This was THE solution. (and it's fun building it) Two points: a) FYI: In order to have it online, I built it in Google Sheets. Unfortunately, Google Sheets seems to be more limiting, such as not having a similar easy feature of adding a scroll bar and for some reasons I couldn't make the MATCH function happen (for holidays), which was no problem in Excel. b) IMPORTANT NEEDED ADDITION: After someone on the team enters all start dates and durations, eventually one of the tasks will take longer and all (or some) start dates from a certain date on will have to be extended by a certain number of days. That would be an amazing and highly needed addition.

[@marklee3570]

Salute! I was happy with Part 1 & 2, and am amazed that you were able to layer on and gave us Part 3 & 4, essentially 'gold plating' the Gantt Chart! Thank you for taking the time to put these tutorials together. Kudos!

[@vertex42]

Thank you!

[@vertex42]

~~~~~ FAQ ~~~~~ No FAQ yet, but I'll mention ideas for future videos: (a) Grouping tasks and showing the overall duration of subtasks for each phase (b) Basic resource allocation, such as indicating work load (people) for each task and showing a sum of total daily work load. (c) Work Breakdown Structure and auto-indenting based on level (d) Lookup-based task dependencies (define a dependency based on WBS) (e) Showing planned vs. actual

[@muhammadhazman3064]

Learned a lot from this video. Thank you. Excited to see your next content especially on "e) Showing planned vs actual".

[@heidiboyce102]

I'd LOVE to see a video on grouping tasks and being able to expand and close those sections of the task.

[@Locomaid]

Yes! Yes! Yes! Yes! Yes! Please!

[@jackbond5960]

All 4 of these videos have helped me immensely, thank you for your insight. Is there any way of adding a milestone marker to the Gantt chart?

[@zdriver95]

Resource allocation would be amazing! Thank you so much for these videos and templates.

[@ruslanator]

I now feel like I need to pay you money. I checked your Excel suggestion list and now I feel like Microsoft has to pay you TONS of money and implement them all immediately. Truly genius! Brilliant work!

[@phuongnl_hn]

Thanks a lot, this video series is amazing.

[@nadahkhi]

Thanks for the subject lessons. The fourth part is heavy to absorb. 😃

[@chumjamero8214]

I am a beginner. I am always amaze whenever I watch your tutorial. I can only do basic typing, coloring and putting chart using excel. Watching your videos makes me feel excited to do it while watching. Thank you so much.

[@843292]

Hats off to your Excel wizardry. And thank you for the education! You've gotten another sub out of me!

[@rowles1982]

Liked all parts! Very well done! Thanks!

[@ignatiusjreilly1287]

Thank you so much for this amazing work, pure concentrated gold.

[@henrymiszelowski5130]

Wow,wow,wow. I love excel and the more I learn the less I know. Your explanation of Gantt Charts is amazing. I have learned so much thanks for your great work

[@hzguzman89]

Vertex, I just finished updating my Project Schedule Template to add all the beneficial features you covered in the 4 videos. You are an excellent teacher and appreciate your video editing to make it easy to follow. I look forward to learning more!

[@vertex42]

Awesome, thank you!

[@galoguevara6049]

This is so awesome. You are a great instructor. I like the logic and formulas you use. Can you please, please, please make a quick video of how to add a Milestone marker to this great excel gantt template, and how to make is show up in the Bar Chart as well. I am trying to do it on my own, but the "not" true conditional formatting is throwing me for a loop. Thanks this is great.

[@vertex42]

If you mean an actual diamond shape, that can be done by adding a formula to the cells of the chart rather than using conditional formatting. I may explain that in the future, but I mostly just use black as the color for milestones and don't worry about using a diamond shape.

[@hilaireoloughlin]

I agree, milestones and dependencies would be great. I am trying to figure out how to make the percentage more interactive by creating a table with the ask-you-go hours or days.

[@fytilis]

you are a rock star!!

[@ccognacc]

Amazing videos and this ends my years of search for practical ghantt chart in excel. Tons of techniques and knowledge. My respect utmost to vertex42 for sharing how this is made. Looking forward to your next episode from this.

[@razminazman7323]

Thank you so much been waiting for this video. Will definatly go thru your whole playlist to watch others. Thanks again.

[@chulakapitigala5147]

Thanks for the fantastic video, nowadays I'm making a system for production and your videos are very very effective

[@lxhon]

This is so good that I just went ahead and bought a copy of "Gantt Chart Template Pro". I wish you'd offer the same styling in the downloads as in these videos.

[@ianmosquera3741]

Thank you so much for this very superb tutorial, now i had made aversion of these in google sheet. Though it has a limited features in formatting but i do get away with just background color only. I like Google sheet more because you can group the rows.

[@soccerstudut]

On the Show Overdue option I noticed in my Gantt chart, it would show 3 colors. In the first equation you compared the today with task end, but in your conditional formatting, you compared today with the date above each cell. Comparing today with the above date allows a bar to be grey, red, and the selected color for that lead name. I think you meant to use the same equation you did at 6:36 in the video except using conditional formatting. Awesome Excel Skills though. This series of videos taught me so much that I will use going forward. You are very skilled.

[@6tmj79]

Great work! tks Learned a lot :)

[@richardwalton4862]

Completely amazed by all of this functionality. I would love it if you could collapse the Phases into the Project Name, so only the Start and End Date were visible for each project. Making it much more manageable and navigable for very large list of projects.

[@vertex42]

This will probably make it into one of the future videos when I show how to use features like Grouping in Excel. The Help worksheet in the Pro version explains the formulas for the Phase or Level 1 rows, but I'll also mention it here: You can use formulas like =MIN(startdates_of_subtasks) in the Start column and =MAX(enddates_of_subtasks) in the End column to calculate the Start and End for the Phase. You have to be careful about properly modifying these formulas if you insert or add more subtasks, but the functionality is not complicated. You can use the Grouping feature in Excel to collapse/expand the subtasks.

[@soccerstudut]

I thought I was pretty good in Excel, but clearly I'm a beginner compared to your skills. This Gantt Chart is awesome. I was able to duplicate everything from video 1 to 3, and I am sure I can follow video 4, I am just not sure I need those features in my chart yet. Thanks for all the awesome tips and tricks. I learned A LOT!

[@vertex42]

Glad it was helpful!

[@celine9852]

Those tutorials are absolutely amazing!! I will try to add a "today" button to jump to today's date in the calendar. Also, I wonder if one can break the views down to hours...

[@mackydorado9680]

Nice work, its verry helpful to my current work. Thank you so much for creating this tutorial video, i hope you upload more videos. 😁😁😁😁

[@paxtonmcpherson]

This is awesome! Can you please make another video showing how I can filter for current tasks on a given week when in the daily view setting?

[@laurenashley7776]

Great tutorial! 2 questions: 1. I know you showed how to show holidays using the daily view, is there a way to show what week/month/quarter also has holidays? 2. While the gray shading for the task progress works in the daily view, is there a way to replicate that for the weekly, monthly & quarterly views? I appreciate your help!

[@texasfc11]

Amazingly informative videos! Also, your voice sounds just like Matt Damon, and now that's all I can picture 😂 Just The Martian casually walking us though Excel formulas

[@jenskautler]

Thanks for the great video. I do have one questions: I would like to add a planned date and a real achieved date and show this in the chart to give full transparence of a project. How would I do this?

[@Fredrik454]

4 great videos how to make a very good gantt chart. One question is it possible to have an auto fill function for the timeline? If you knew your end date? I do not want to drag out the dates.

[@damensyah]

I really like the way you explain..really nice..By this gant chart..could you please create s curve?

[@ShravanKumar-op7cp]

Excellent bro..! Same format with planned dates vs actual dates for comparison

[@vertex42]

Noted. I might show that eventually, but it doesn't really require any new techniques. You can add two new columns for the planned start and planned end, then use the same technique mentioned in the first video to highlight the bars of the gantt chart based on those new columns (using cross-hatching so that the bars can overlap). See the following template: www.vertex42.com/ExcelTemplates/project-planner-template.html Also, this article: www.vertex42.com/blog/help/gantt-chart-help/actual-dates-vs-planned-dates-in-a-gantt-chart.html

[@rafaburgosriestra]

I am absolutely impressed! Thank you for these four fantastic videos. I would like to ask if you could explain how to "show progress" on a weekly, monthly or quarterly basis. Unless I miss something important, It works only for daily basis.

[@vertex42]

You can't partially shade the background of a cell via a CF formula rule, so showing progress within the bars of the gantt chart is very inaccurate with a weekly/monthly/quarterly view unless your tasks are very long duration. For example, let's say that you have a task lasting 1 week. In anything but the daily view, that task is represented as a single cell. Ideally, you'd want 50% to shade the bar 50%, but your only options are either no shade, or full shade (0 or 100%). The stacked bar chart method will show it more accurately.

[@brunesi]

Amazing

[@lumwaiwei5055]

Suggestion on the next topic : 1. Planned & Actual Gantt Chart 2. Summary Task % Progress calculation 3. Interactive Status Update Date This is freaking useful.Thanks!!

[@vertex42]

Thanks for the ideas! (1) is available in Pro version, (2) explained in help worksheet of Pro version (3) not sure what that means.

[@hilaireoloughlin]

Hi, I really worked hard at doing the Gantt chart and have succeeded. Now I would like to see Milestones and critical paths. Are you planing on a Video for this or have you one tucked away somewhere? Thank you so much for sharing this precious information with us.

[@vertex42]

Critical Path is actually a fairly complicated algorithm. See the following page for an explanation. I'm not planning on doing a video about it. Milestones are built into the Pro version. I may or may not do a video about that some day. www.vertex42.com/ExcelTemplates/critical-path-method.html

[@surangiweerakoon7447]

Thanks a lot, I am new to excel Gantt charts. I was not able to color the bars in different colors. The formula =task_color=''R'' was not getting accepted under conditional formatting. I included an apostrophe comma, in front of the formula, but that too didn't work. Grateful if you can give me a hint at where I maybe going wrong. Thanks

[@mbtijanii6305]

Prior my question a big thank you for sharing your knowledge with us. Was it in the past that I showed my colleagues the possibilty in excel, did I now find somebody who's more advanced than me, teaching me new things :D My question: Is there also an option to include a formatting to highlight overlapping activities in color?? Would be realy interessting especially when some of them collide with each other, but as soon as one task progress is on 100% finished, that the highlight disaper. Is that possible?? Because in the future I want to use this data-file to document the progress/work on construction sites. Big Thanks from Germany in advance.

[@maxcady5666]

Wow!!

[@lcg_wormtrader83]

Hello! This chart was spectacular, as I imagined from the first class. I would like to see what a Gantt chart would look like to control daily activities and not processes, using the same template created for this spreadsheet.

[@vertex42]

Do you mean like repeated daily tasks rather than tasks that take many days?

[@lcg_wormtrader83]

@@vertex42 Exactly! When you want to follow up, it implies that daily actions are followed up and these often become repetitive over a period of time. I thought the format you gave for this chart of Gant was magnificent, so I thought, how would it do for a daily condition?

[@gursimransingh3738]

Your videos are really helpful. Could you also help show - Planned & Actual Gantt Chart ?

[@vertex42]

Create new columns for the Planned Start and Planned End. Then, create a conditional formatting rule similar to the others, except use cross-hatching instead of background color.

[@stevenc3500]

Brilliant work Sir, thank you very much. I also have a query, just to be pedantic as I have not figured it out. I would like to hide the colour of the bar as it passes through a weekend or holiday, any input would be most appreciated.

[@vertex42]

Move the rule for the weekends before (above) the rule for the bars.

[@TikkaBeezy]

Amazing video once again. Thanks a lot. Random Q - Im a bit of an excel novice. I've made a Gantt chart based on part 1 in this video series except it has a column for 'status' . This column contains a dropdown list - each option fills the cell in either red, yellow or green. I would like the timeline bar color to reflect the status cell fill color but I'm not quite sure how to achieve this. Should I create a 'CLR' collumn to the right of the 'status' column and have the 'status' dropdown selection auto populate the CLR cell, allowing me to follow the rest of the steps in this video accordingly? I hope this makes sense. Thanks again

[@vertex42]

For your conditional formatting rule, you can just use a formula that checks the value in your status column (similar to the use of the color column).

[@cedricmichaud306]

Amazing work as usual !

[@vertex42]

That is possible. See this template: www.vertex42.com/ExcelTemplates/project-planner-template.html

[@МихаилКрук-э7ш]

Hello, and many thanks for the great work you did. QUESTION: I want to add, a collum similar to "display - daily, weekly.etc." for each task separetly, and to aply this conditions to some tasks that repeats every day, week, so they would be shown in the calendar. Is this possible? And wich formule should i use? Thanks in advance!

[@ferndaleonrepublicoperatio2243]

Thank you for helping to make gantt chart editing easier. How ever, there is a few things which I would still like to be able to do and need some assistance with: Can you set a formula to your % complete tab/row which automatically calculate the % complete? if tasks are done prior to "Today" it should read 100% complete, tasks with estimate completion date prior to "Today" which took longer than the estimate task time, to include the overdue in a different colour - the forula you've used does not calculate this correctly on my side, and only tasks which end day is past "Today" in a 3rd colour. I do not have to use the name asigned/colour function as you explained in this video. Thank you.

[@vertex42]

Thanks for the suggestions. There needs to be some way to indicate a task is "done" - and that is to enter 100% in the progress column. There would need to be another conditional formatting rule to create a bar that extends from the end date to today's date if you want to show an overdue period. This would be similar to one of the existing rules except use TODAY() instead of the end date.

[@ferndaleonrepublicoperatio2243]

@@vertex42 Thank you for your reply, I will give it a go and see how this work, will this then be able to indicate: example - that the task started on call it the 23rd of May the end date is on the 20th of June (green), from 20 June the task has been delayed and the chart will go on (orange). The second task which was then supposed to start on the 21st but had been delayed (red) until the 28th of June (task start day) and will continue in green until the current day and change color to green as the project progress. The task will then be in blue to the end day which is on the 19th of July (prospective task end day) and should the task delay again continue in orange until the task actual end date. I hope the example make sense? Currently I only have the layout and only "fill" the cells manually as the projects we're working on progress. I also had some difficulty with overlapping tasks which has start date is prior to the previous project end day, and when I try to change the formula I get a lot of errors. Can you perhaps do another tutorial to assist whit the above? Once again thank you very much. Take care.

[@simonreynolds5896]

Wow, these 4 videos have transformed my chart. Picking up on your last part in video 4, my charts would be used by multiple people all with different levels of excel skills. Is there a way to add a button to insert a line with all the formats in? This would take my charts to the next level if possible! Keep adding content as I’m now going to look back at your other videos!

[@vertex42]

When you insert a row, all formatting is copied automatically, so you shouldn't need a macro to insert a new row. All you need to do is insert a row, press CTRL+D to copy all previous formulas, and then edit description, start date, duration, etc.

[@simonreynolds5896]

Thank you!

[@72bigpoppa]

Yours are the best Gantt chart demos, period. I've been looking at videos for weeks. Yours are to impactful and to the point I have to pause your videos. AWESOME! What I'd like to do is have the task name displayed on the bar. i.e Task 1.1 is purple with red text and is displayed as such on the Gannt bar. Is it possible to toggle it on and off? (for me, "Task 1.1" is a person and will be assigned to tasks for several days/weeks at a time.) Within those tasks, there are 'additional duties' I'd like to have, i.e 'T' for trash, 'C' for cleanup, '. I've tried a few things but they don't work. Regardless, THANKS!

[@vertex42]

Using the stacked bar chart method, you could use data labels to display the description on the bar, but it's not going to work using formulas within the chart area created in these videos.

[@dacrazyitachi]

Hi, I have a problem that the bars are not showing colour in the last column in the gantt chart. The progress is still until the next display period. the conditional formatting already included in that column. Thank you.

[@louisfucini1387]

do you have a video on adding task dependencies and milestones to the product you end up with at the end of this video?

[@vertex42]

The dependencies are covered to some extent in Part 2. I typically just use a black color for milestones (with start=end date). The Pro version file has an example of how to use a formula to display a diamond unicode character. Using a black color is easier and that's explained fully in this video (Part 4). I just thought of an interesting trick, though. If you populate the entire set of cells in the gantt chart with spaces, in theory you could use a conditional format rule that changed the format of the cell to "◆". The custom number format would display nothing if the cell was completely blank.

[@MohammadArifRahim]

Is it possible to only use the formatted portion of the template and scroll over large chunks of Gantt Chart data, maybe using a vertical scroll bar?

[@vertex42]

Not sure why you'd need to do anything special for vertical scrolling, because that's already part of Excel. You could use Freeze Panes to keep the top rows in place as you scroll.

[@nadiabruno5806]

Thank you for very informative videos from Pt 1-4. I wanted to know, if a task is assigned to more than one individual, is there a way to represent that in the Gantt chart with multiple colours? Or is the only way to do it is to represent it via a "team" and a colour for that team? Thank you!

[@vertex42]

Not sure how you'd use multiple colors for a single bar. I'd probably go the 'team' route ... or use sub-tasks to show a separate version of the task for each person (and therefore unique colors).

[@dianeperez8431]

Pls, also have a tutorial on S-Curve

[@tahasaid7997]

awesome work ! I ask in part 3 about how to show actual vs planeed and you tell me that it is here in part 4 but its not here too .. i need your support please

[@vertex42]

Sorry, plans changed and life happened. Planned vs. Actual is available in the Pro version on my site, but I haven't created a video for this.

[@ashishtiwari6555]

Pls help, how to highlight activity beyond the plan? I meant, Activity couldn't complete within the time and took 5 more days.

[@vertex42]

You'll need another column for recording the new end date, then another conditional formatting rule.

[@steve924s2]

An absolutely fantastic 4 set tutorial with an amazing amount of features in a Gantt Chart. Well done and thank you! I noticed that the task progress colour change only works when the 'Display Period' in selected to Daily. Q. Is this correct or did I miss something?

[@vertex42]

Yes, that is correct. It's just really inaccurate in the other views because you can only shade one cell at a time, so if you are in the weekly view and the task is 1 week and it's 50% done, you can either shade the cell or not, but you can't shade it 50%. I really prefer to only use the Progress column, but a lot of people like to see the progress within the bars of the gantt chart. Part 3 shows how to create a stacked bar chart which can more accurately show the % within the bars of the chart.

[@IliaLAB]

Thank you, that looks awesome. I may have missed it but is it possible for this chart to be made with static period of time i.e. full calendar year?

[@vertex42]

Sure, if you copy enough columns to the right. Adding more columns was shown in the first video. Also, part 3 was mostly about that.

[@IliaLAB]

@@vertex42 I appreciate your reply, thank you.

[@AbhishekBagaria]

@vertex42 This is absolutely superb. This will make tracking our projects a cake walk. Are you able to donate this pro to our NGO? I can provide details. Thanks for your consideration.

[@nourammous1945]

Can we write on a text on the highlight ?

[@stavroshirtharis1991]

HELP!! but also thank you very much!.... Have gone through all Parts and has been a fantastic learning opportunity. Have managed to create everything apart from one issue I can't figure out. My light blue mesh formatting that highlights the weekends worked fine until Part 4. Since adding the colours with the Stop if True button ticked it now cuts through the progress bar with no colour visible rather than just being a slight pattern within the coloured bar as with your videos. I have experimented with moving it up and down the priority but cant get it to be as was during PART 3.. What am I doing wrong? Thanks again

[@vertex42]

If one rule defines cross-hatching prior to another rule, the first one overrules the later ones. So, make sure you haven't defined any type of pattern fill in any prior rules. To clear the formatting in a rule, you can press the Clear button when editing the format, then you can redefine only the formats you want to be associated with that rule.

[@ChallmanJames]

Can you show how to add tasks that may be recurring? Example, if I have to do task 1.1 every 2 days until further notice.

[@vertex42]

Requires a very different approach. I think there may be an example of that included in the Pro version just for the sake of showing an example ... but it's not easy.

[@mrizzo3646]

Awesome videos! Except, what if the week starts on a Sunday and you want to hide the weekends? I’m having trouble with the expression for that. Help!

[@vertex42]

Don't start it on Sunday. You're going to hide the weekends anyway, so just start the week on monday.

[@sivaprasadk3739]

Hello sir, this is a very nice gantt chart. Please provide a solution video demonstrating how to apply RAG (Red, Amber/Yellow and Green) colors dynamically based on progress % updates according to the following conditions/formulas If Progress is 80% and system/today's date is => task_start and

[@UnkleRuckus11]

Bars changing to red for overdue doesn't work until I move it to the bottom of the conditional formatting list. It was changing bar colors to red if progress wasn't beyond today's date.

[@sne1991]

when I try to apply the conditional formatting for the overdue tasks, it highlights every cell in the calendar red. what am i doing wrong?

[@kenhean3659]

check your $ sign, did you $ properly?

[@mzia2047]

Can you do an extra video for two additional functions. 1. Milestones. You touched on it by adding K for black but you never fully addressed it. 2. Can you add a predecessor function. Meaning that a task is dependent on a certain task completing.

[@vertex42]

Gantt Chart Pro has those features, but it's not something I'm planning to do a video about.

[@mzia2047]

@@vertex42 that’s a shame. I’ve learned so much from you!

[@tejasdeshmukh383]

The video has a playback error. Does not play.

[@balajigsvp]

Is it possible to add plan and actual?

[@vertex42]

Yes. Check out this template: www.vertex42.com/ExcelTemplates/project-planner-template.html

[@hilaireoloughlin]

I love your videos, they are really precise but so fast, for beginners. it is a challenge. Rewinding is a must. BUT, please quit this annoying music in the background. Do you know how many times you have to listen to it when you rewind? It is really ballbusting and one is almost ready to quit. It is like walking with a sharp stone in your shoes and not being allowed to take it out. Thanks.