For those who have trouble with 1:14:40 and formula: "=IF(ROW(id)=ROW($B$13)+2,1,MAX(prev_col_range)+1)" You need to make sure that: - you define formula "id" while having cell B15 selected. Select B15 with you mouse before defining the formula. Refer it to "=Gantt!$B15" - same as video. - you define formula "prev_col_range" while having (VERY IMPORTANT!) cell B23 selected. Not B15. With B23 selected, refer it to "=Gantt!B$13:B22". Took me some time to figure it out. Hope it helps!
Thank you! I was bashing my head and couldn't figure out what was wrong. Evaluating the formula produced the "right" result right after the last iteration, it would print 1, instead of the value of (max(prev_col_range)+1)...
This is a real goldmine for Project Managers. My excel knowledge has increased two fold .The best part of this tutorial is simple explanation and ease of navigation. Kudos to the Author who has made this freely available.
These kind of tutorials are worth their weight in gold. I've been working with Excel for years, doing evaluations, planning smaller projects and I see here how awkwardly I've done many things so far. It takes a little longer to prepare the files, but then you have a really fantastic basis. Thanks for the work that went into this video. Really really good
Thank you! This is so wonderfully helpful. One small request for your next one. As a user I'm often pausing youtube on your screen to copy the formulas you are writing. As you near the end of the formula (e.g. &"...",d.name),"") at around 1:21:47 you write the end of the formula quickly and then click out. It's difficult to pause at the exact right moment where the formula is complete and I often need to go back 4 or 5 times. My suggestion would be as you are finishing writing the formula, say something like 3, 2, 1. Have a great day!
This is the best video available on RUclips. you can watch 20 videos on Gantt chart and none of them will come in comparison to it. this video earned my subscribe forever.
I have finally finished this tutorial and it was amazing. I added a few of my own things to it - icons for Deliverables and Stage Gates and dynamic date increments. There may be better ways to do this but it works for me. Deliverables working very similar to milestones. Stage gates slightly differently as they will be coloured gold to stand out regardless of colour option unless it is marked as an issue. If you have followed the tutorial this should be simple enough. You end up with a few more colour codes that you need to sort the conditional formatting for. For the date increment I added another dropdown named "view" with option to select Days, Weeks or Months. In cell Y10 I used the formula: =CHOOSE(MATCH(view, {"Days","Weeks","Months"}, 0), WORKDAY(X10,1), X10+7, EDATE(X10,1)) Note that 'view' should point to data validation cell of the additional dropdown. In cell X13 I have used the formula: =CHOOSE(MATCH(view, {"Days","Weeks","Months"}, 0), LEFT(TEXT(X10, "ddd"), 1), WEEKNUM(X10), LEFT(TEXT(X10, "mmm"), 1)) This changes the row to show weekday initial, week number, or month initial based on what view choice has been selected. The formatting carries over correctly for any dropdown selection based on colour and progress etc. The only thing that drops off are milestone, deliverable or stage gate icons when changing view to weeks or months. Not sure if there is an easy fix for this to show the icons regardless of what view choice is selected. If anybody has a fix for this let me know. The scroll buttons also need amended as they only move in 1 day increments. Not sure if this can be changed based on a dropdown selection? My gantt runs incredibly slow using my work laptop which is a shame. On my personal laptop it runs fine!
What? Are you for real?! This is amazing. Never thought it is possible to implement such an extensive dependency system with forward and backward project scheduling in an Excel Gantt Chart O.o
@@89DerChristian Having everything in Excel is really convenient. You can have many projets tracked using this tool that are reconsolidated into a global dashboard. If you add a small VBA, you can also automatically send reminders of pending task to all the actors or a project progress report
In real project, a task can be dependent on more than 1 predecessor tasks. The method in the video also hasn't solved gantt chart readability problem: Readers must juggling their sights between activity name on the left, time axis in the top and activity block position and length to determine tasks' names, durations, dependencies, start and end dates My solution is Vertical Gantt Chart which is based on making the vertical axis as time axis. It produces much more compact and readable chart. As horizontal space isn't limited by duration anymore, activity names and descriptions can be written directly within the block. Duration can be simply pictured as the block's height. Dependencies can also be pictured directly as block adjacency. No more arrows needed. I put some examples in LinkedIn article but can't put the link here as youtube will delete it.
Incredible tutorial - I almost just bought the template but decided it was in my best interests to follow along and build it myself and I'm so glad I did. I've been wanting a tool like this to manage project timelines with task dependencies forever, and this functions amazingly well and ticks off 99% of my wish list. I'm incredibly grateful you took the time to think this through so thoroughly and create this resource for others to benefit from. One thing that would be helpful in future tutorials - I wish you'd pause for a second or two after typing a long formula in before clicking "ok" and moving along. It would make it easier to pause the video on the completed formula and get it typed in, and double-checked before restarting the video. Thank you again! You're an amaaaazing teacher!
Hey Lisa, thank you so much for your feedback 😊Glad you decided to build it yourself and enjoyed the process. I will consider your suggestion with the pause for my next tutorials.
@@theofficelab You have done a great job. I found that you have done very well with pauses as well. Could you please advise on how to convert this considering the hours as well as the date? I would like to build this plan breaking down to hours level of detail. Is this possible? Infinite thanks
@@arulrajesh Wow! I never knew you could advance these videos frame by frame with the < or > symbols. I've learned something new that will help with all future YT vids. Thank you.
I've just watched the whole video after a hard day at work, and was still able to follow all instructions. What an amazing video, I can only imagine how much preparation it required! Hut ab ;-)
Hi Sandra, Your comment really made me smile 😊 I am so happy you enjoyed the tutorial even after having a hard day at work. And yes, the development and preparation of this tutorial was some big effort 😄 but whenever I can provide new value to my community, it's absolutely worth it in my eyes. Beste Grüße
This is by far the BEST tutorial I have followed on RUclips. Hands Down! Everything was so clear and concise, the formulas are beautiful, the conditional formatting is amazing. I consider myself an intermediate Excel user, and there are things that you showed that I did not know how to do. Please don't quit doing these types of tutorials! You broke everything down so well, so I understood not only what each formula was doing, but WHY. I love that you did not use quick-buttons to do anything...definitely a help for someone like me who does not use those often. I was able to follow along with the instructions and create my own Gantt chart. Now that I have finished the tutorial, I can customize it for my needs very easily without breaking ANYTHING in the file. Everything is set up so that I can lock cells easily so as not to overwrite formulas. Thanks again! It is greatly appreciated!!
That was an awesome tutorial thanks. Really clear and well paced. Using the options on screen rather than using shortcuts like some do in tutorials, also, breaking down the formulas into stages before applying, to help us understand why we are doing what we are doing, both really helped me not to get lost. I've certainly picked up a load of excel knowledge and have a shiny new Gantt chart to show for it. Thanks for sharing this, very much appreciated.
I finished mine today and I want to thank you for your incredible work and generosity! I made some adaptations, translating everything into Brazilian Portuguese, increased the role capacity from 8 to 20, inverted the employee logic (first I choose the role and then only the employees assigned to that role appear for me next), added a new larger category before the Stage called "Project" following the same logic you applied (and creating a unique ID column for the projects), and I also transformed the formulas made to copy&paste in the Projects and Stages rows into the Independent Start, Working Days and % columns, in calculations with dynamic references that establish the intervals automatically... so far it is working wonders!
@@helpinghands4875 I used the entire tutorial in this video, step by step, pausing and returning infinite times. Everything I modified from the original template and everything I did extra to make it adapt to my specific needs was based on free videos available on RUclips, because I I'm a self-taught newbie to Excel, and that's exactly how I learned the little I know, without shortcuts. You can do it too!
@@MariMariou Olá, preciso desse excel em português e infelizmente não tenho tempo nem conhecimento para faze-lo do zero. Existe alguma chance da gente conversar sobre eu adquirir seu excel?
@@bryanmartinez291 That's the biggest problem that I've cross with... I'm considering the possibility of creating a cemetery with all the concluded projects inside a separated spreadsheet that will basically be a duplicate of the original one, but I don't know if all the named intervals will have to be remade. If that's the case, is a lot of work hours ahead, and I'm not actually using the file to make it worth the effort 😅
This is an enhancement to a previous version. And it is magnificent. Im a PM by profession and am enthused by the power in this tool The functionality dispenses with the need to carry out a conventional network diagram and critical path analysis. By allowing dynamic forward and reverse scheduling, the critical path is produced based on dependency matching by both task and stage; when a dependent task changes, the critical path - expressed through stages to a changeable end date ("defined earliest finish point") - also changes dynamically. In other words you can go straight from sequenced task/duration/predecessor listing and then jump direct to Gantt with confidence - you dont need to labour over LS, LF, ES, EF with the forward and reverse pass cycles etc. as you can simply enter revised start and end dates as required, to then directly build in the required judgement on tolerance (or float) per row/task - and the dependency scheduling automatically updates. A game changer. Effortless! 👍 Im purchasing direct; I could build (as I did for the predecessor) but I do not have the time. Will be bringing this into service with my work, there is so much added value here. Thanks for posting, it is difficult to quantify the time you will save me, onward to cost efficiency and upheld quality standards
Wow. I really appreciate your positive and extensive feedback!! I am really happy that you - as someone who obviously has a good understanding of this topic - sees the value in this 🙂
I don’t comment on youtube very often, but here I have to. Thank you, thank you, thank you! I would seriously recommend this tutorial to anyone who wants to take his/her skills with Excel further than "A+B=C" usage. This is pure gold tutorial - clearly explained ,step by step, not too slow, not too fast. Building up every step on previously made steps so you can understand how the whole process and hierarchy works. Showing up many actions and tricks which you can use every day in Excel if you were unaware of them until,now. This, dear friends, I call a professional tutorial. I have spent several days in my free time following this tutorial and building up the whole gantt sheet and besides the fact that I have a cool gantt now, I feel really enriched by new knowledge. Thanks again.
Done! After months of following every single minute of your tutorial, I finished today. Like you say the excel "Works Like a charm" and "Perfect!" Thanks for sharing. I learned far beyond what I expected. I am going to use it with clients right away. Chapeau!
I was looking EVERYWHERE for this. I even stumbled upon your site while looking. I ve seen the old version, and happened to need something like that for work. Tried to find it back and was nowhere. I was searching for like a week straight. Until i finally saw the video was retracted. Got very sad tbh. Now i'm happy for this. Ty for the new version.
Hi, thank you for your big work on this template. I followed each step and finally I can appreciate all new things I learned with this video. I've added a formula in order to calculate automatically the number of items for each stage and then to put a unique formula to calculate the start for the stage. In this way when an item will be added, the formula does not need to be changed to match the number of rows. I've added a column after the R id, named N. Row; the formula to calculate the number of rows in the stage will be: =IF(type="S";num_item_per_stage;-1) where num_item_per_stage is a defined name with the formula: =IFNA(MATCH(Gantt!stage_id+1;INDIRECT("R"&ROW()+1& "C" & COLUMN(Gantt!stage_id) &":R500C"& COLUMN(Gantt!stage_id);FALSE);0);MATCH(-1;INDIRECT("R"&ROW()+1& "C" & COLUMN(Gantt!stage_id) &":R500C"& COLUMN(Gantt!stage_id);FALSO);0))-1. Then, in the colum ind.start, in the stage rows, the new formula: =MIN(INDIRECT("R"&ROW()+1&"C"&COLUMN(Gantt!plan.start)&":R"&ROW()+Gantt!num_item_per_stage&"C"&COLUMN(Gantt!plan.start);FALSE)), that can be used with a defined name auto_stage_start. Hope this will be useful. Thank you a lot for this video and for this great job.
This is fantastic! One feature I would like to see is a means of tracking worker capacity. If a task has been assigned and allocated say 5 work days, this does not imply the individual cannot do other work that overlaps this time. However, there is a point at which workers cannot handle additional tasks in the same day. For some of our workers, our organization has well-defined units of work. We would like to track human resource availability, both at the individual level and among all workers of the same type. This will allow work managers the ability to see who has spare capacity whenever assigning new tasks. Thank you again!
That's a true game changer for me! Have been looking for a way to do backward and forward scheduling with dependencies many times, but always got stuck and all templates out there didn't manage to do that either. Keep up the good work!!
Absolutely fantastic! So many new approaches for me that will apply to other scenarios. Two questions: 1. Have you considered using Excel tables so the formulas, formatting, etc. automatically apply by simply starting to type in the next empty row? 2. Do you have something on the design process? Obviously, A LOT of work went into getting to this finished tutorial, but there also must be a lot of advance planning to know things like the relationships between all of the defined names and their use in cell and conditional formatting formulas. It would be fascinating to see how you go about designing something like this before you get started vs how much is trial/error/incorporate new idea as you build.
Man its been 2 years you publish this video but you saved my day with this. People say you deserve like you awsome etc. But man forget them this is real gold mine for anyone wants to manage a project. Im planink to add raci chart and progress notes by just linking to new sheet as you done in settings button.
I'm building this model following your turorial and I am now almost halfway through it. It's incredible how much I learnt about using named cell-ranges in a very creative way and the same is true for the incredible power you show in using conditional formatting. An experienced user for almost 30 years now I still learned a lot of new tricks. I can recommend following the tutorial even if you bought the file. Chapeau. (I'm not French but Dutch but the expression is the same here; I tip my hat)
@@fransverwoerd1901 Ik volgde de bovenstaande video. Het kostte me een eeuwigheid. Kleine tikfoutjes grote gevolgen (Engels een komma als scheidingsteken in functies en in het Nederlands punt-komma om maar een voor de hand liggende fout te noemen). Maar hij is klaar en hij werkt. Alles bij elkaar zeker 20 uur werk om het te kunnen volgen en overtypen. Maar als je wilt kun je mijn model krijgen. Voor niks. Daarom vroeg ik je mailadres.
@@RondenBreems goeden avond Ron, ik heb met veel plezier de video bekeken en enthousiast geworden over de mogelijkheden die dit te bieden heeft. Is het brutaal als ik vraag om van jouw template gebruik te mogen maken? Ik zou willen kijken hoe ik het kan toepassen. Ik hoor graag van je. Groet Raymond
@@raykohlmann4122 Geen bezwaar. Pas wel op, m.i. is het erg fout gevoelig. Neem in ieder geval de video door zodat je ziet wat ingevuld mag en moet worden en wat niet. Geef me je e-mail adres en ik stuur het ding op.
I have no words to express my gratitude to you. It was fantastic to see how a normal excel can deliver so much. Learnt so many good things from this video. Thanks a lot ..lots of love and blessing to you
Brilliant! The HUGE amount of Knowledge for BOTH Excel & Project Management is unbelievable. Thank you for sharing that Work. SMALL TWEEK: I created this PM Gantt Chart with "Selecting a Calendar" feature. Since Workdays are not similar in other Countries & also some Projects run on 24/7 basis, I added a "Calendar Selection" option & adjusted all Functions using WORKDAY to WORKDAY.INTL along with other minor modifications. Once again, thank you so much for sharing this Piece of Art. Greetings from Egypt.
I’ve just completed the build and cannot thank you enough for what is a great demonstration of using the vast options of nesting functions, cell referencing and named formulas. I will probably never use the automatic conditional formatting again ever now that I’ve seen the power of the formula approach. The only thing left for me to work out is the calculation for a second % column that allows me to show each task as ahead or behind the position it should be. (I.e. Status =35% but it should be @ 70%) as an additional ready value. Great job and I am hugely envious of your ability with Excel.
This is really amazing tutorial. This has boosted the understanding of XLS to a great extent. While implementing my own version, I could figure out >10 improvements that one has to do for a complex project.
You are simply the best teacher, I have come across, everything well articulated, well planned, very concise and getting the most from an excel in a simple manner without using Macros and VB,I have just two things I have stumbled across,1- My Unicode symbols are not as milestones or flags, rather they are taking the symbols from Calibri font and 2- Since we make the planning for 4 years, so a tip to convert the time scale to month will be highly appreciative. Thanks for teaching excel functions in a very practical way and in your tone simply amazing.
Truly an amazing tutorial. I was able to follow it all and after about 12 hours, I completed building the entire Gantt. I did have two questions: 1. How easily could I add 1 more level of the tasking hierarchy (the WBS). Example being wanting to track different areas or sub projects separately. Currently this is a two level structure and was curious about making it a 3 level structure. 2. Have you considered a float column to allow the movement of activity durations separate from a lag? Would this be a difficult add to the calculations and integration of the "logic" relationships.
Took me something like 3 weeks to go through it (and a lot of late nights) but here it is ! My Formula Student team now has a proper Gantt chart !! thank you very much for this piece of work. I would have loved to have some arrows coupled with the dependency engine tho
Genius. Worth the investment. For advanced users, time-wise it takes about 2x the video length to build. For intermediate users, get comfortable and stage the build over a few days to absorb the learning that is excellently demonstrated and explained here. Allow 3x the time. Thanks for sharing your knowledge at community level. As a poor PhD student, I’m extremely grateful for this free gift of learning you’ve shared!
It is a perfect lesson. But be careful about the point that not to conditional format the whole row or column. Only format where you work. Otherwise, Excel will freeze, which is so annoying. He said that at the end of the video 1:42:44 bu kısım önemli ikinci görevde nasıl geri planlama yapılıyor 1:44:25 nasıl daha fazla özelleştirebiliriz 2:38:19 eğer renk paletini atlar iseniz tam da burada patlarsınız 3:00:14 RENK PALETLERİNİ DÜZGÜN YAPMADIĞIMI İÇİN bu kısmı yapamadım eksikler var koşullu renklendirme aşamalaırnı düzenlemem gerek 2:20:07 bu kısımda kaldım tektar döneceğim kişilerin titleleri eksik bende
Hi, at which minute does he say not to do the conditional formatting? or in general where is this conditional formatting mentioned at first? I made this gantt and Excel is indeed freezing :(
This is an absolutely amazing piece of work. I am so grateful for this, the presentation and the way you have explained everything is a 10/10. I have just completed building the spreadsheet and would just like to say a huge thanks. Incredible
I had so much fun creating this while watching your video. Learned SO much and I unlearned some bad excel habits I had along the way too. Thanks so much!!! You are an excel genius! 😂❤
Incredible Gantt chart and tutorial video!!! Thank you so much for taking the time to do this! I successfully made the charts and it’s amazing to me how well it works. I think an extra dropdown list at the top for the option to show weekends, show holidays, show everyday and show default would be a good addition. Unfortunately for me, I'm not advanced enough to just add it in. Maybe you could make a video on this addition in the future?
I have started a new channel with a new ID to make Excel tutoril videos and now I am not so sure. U r definitely one of the best tutors out there; thank you for helping me visualize bigger, further and near impossible.
Thank you so much for the amazing tutorial! It was very clear and great fun to work through with you! For those using an older version of excel, without the "SWITCH" function, you need to use nested IF() functions. I used the following formulas which have the same effect and work according to the video: @2:02:20 “color_code_default” =IF(Gantt!type"", IF(Gantt!type="S", 1, IF(Gantt!type="T", 2, IF(Gantt!type="M", 3, -1))), -1) @2:03:30 =IF(color_by="Default", color_code_default, IF(color_by="Project Structure", "ps_cc", (color_by="Team Roles", "tr_cc", IF(color_by="Issues", "i_cc", color_code_default)))) @2:09:40 "color_code_project_structure" =IF(color_by="Default", color_code_default, IF(color_by="Project Structure", IF(stage_id=-1, -1, NUMBERVALUE(IF(MOD(stage_id, 8)=0, 8, MOD(stage_id,8)) & IF(type="S", "01", IF(type="T", "02", IF(type="M", "03"))))), IF(color_by="Team Roles", "tr_cc", IF(color_by="Issues", "i_cc", color_code_default)))) @2:18:07 "color_code_team_roles" =IF(color_by="Default", color_code_default, IF(color_by="Project Structure", color_code_project_structure, IF(color_by="Team Roles", IF(role_id=-1,color_code_default, NUMBERVALUE(role_id&IF(type="T","01",IF(type="M","04")))),IF(color_by="Issues", "i_cc", color_code_default)))) @2:30:26 =IF(colour_by="Default", color_code_default, IF(color_by="Project Structure", color_code_project_structure, IF(color_by="Team Roles", color_code_team_roles, IF(color_by="Issues", IF(issue="⚠", IF(type="S", "901", IF(type="T", "901", IF(type="M", 902))), color_code_default), color_code_default)))) For conditional formatting of the chart itself, I also found you need to use quotation marks but only on 901: OR(“color_code=”901”, color_code=902)
I have taken inspiration from this video & created template. The video is about 3.30 hours but believe me for me it took 15 days to make this template. Further I become successful to make 4 stage dependency engine (S,T,st,""). Now based on every stage dates, S, T & st stage dates are recalculated & automatically adjusted. If T,st depends on others tasks then also these dates are changes. I still feel that there lot to do for more value addition. Thanks a lot.
Brilliant piece of work...and a very easy to follow tutorial. Love the way that you are using named references for complicated functions. Thank you so much.
A-MAZING! I started building this from scratch following your instructions (played back at 0.5x speed)... and about half way through decided it was well worth the purchase of the completed template. I did still listen to portions of your video to understand how they function. I would LOVE to see how to incorporate this Gantt chart into a "Master Project" Excel to track all progress of multiple projects at once, and would be willing to purchase that template too!
This was brilliantly well presented and I totally love finished product too. I can also see that I will be referencing this tutorial for concepts to apply in other projects too. Since you asked for possible improvements and the only one I can think of would be to add a critical path analysis calculation and allow an give an option to visually highlight the relevant tasks. Well done and thank you.
I don’t even know what to say cus you definitely have received a lot of thanks! But honestly, I will forever appreciate the work you’ve done on me through this video🎉
This is an awesome tutorial. There was nothing in here I didn't already know, but the way you hang it all together to create the result is exceptional.
What a fantastic piece of work! Follow your tutorial, I was amazing about what you've done and learnt many excel skills. Many thanks for sharing. I've completed and have some my own modification and additional: - Delete issue color mode and just make red color for the progress bar when alert icon was choosen. - Change plan vs base, progress dropdown list to checkbox. - Modify the little green bar (for all project duration) to a mini milestone chart (like timeline bar in ms project). - Make a dropdown list about timeline scale with Day and Week option to view long duration project.
Very brilliant. You simply rock and have a great command of excel. There is an improvement I would love to suggest. Could you add a botton to automatically show the critical path? This is useful to help the project manager know the tasks they can accommodate some slack on and those they cannot. Hope you would consider that. Cheers.
Even though it's been heavily emphasised in the previous comments, I need to say this was amazing. I had just finished a personal template for PM, but when I saw your video, I spared the weekend to make it through the tutorial and man, what a joy! A lot of fresh and useful knowledge that will be applied on my day to day. My target now is to enhance the template so we can have an option to visualize the Gannt area both in a daily view and a weekly view, and also add the holidays into the WORKDAYS calculations.... this will fit perfectly to my needs. I couldn't thank you enough for providing such a prime content for free. Kudos👏👏👏👏👏
This is just amazing, your knowledge and delivery is amazing. A lot of this was beyond my level of skill, but I was still able to follow and complete in its entirety, and learn a lot along the way.
just being tasked with getting a rather complex project on the road, i came across your tutorial. many thanks, in particular for some of the more sophisticated excel functions.
This is great! Just want to ask if there is a way to collapse and expand the dates? Like switch from a weekly view to a daily view of the dates. How would you do it?
Thank you so much for this high quality content! I already finished the tutorial, and this is by far the best Gantt Chart template here on RUclips, once again, thanks 👍
Thank you for bringing such a useful example forward. It shows the power of Excel many overlook and enables many to rethink and build more intelligently for the future. Curious if you would share what you use for Theme\Color to get the selectable color pallet you use??? I know how to ref the hex colors but not how to set the GUI to match. Thanks again!!!
For those of you on newer versions of excel, you may need to wrap certain merged cells in an Index function to avoid spillover. E.g. the display value needs to be INDEX(display,1,1). Took me almost 90 minutes of checking to finally figure out what was happening!
This is great Gantt Chart that filled with features. Thank you. I would like to have version that can easily (1) change number of working days (i.e. 5 working day per week and 6 working days per week) and incorporating non-working public holidays, (2) Change the Gantt chart area by days, weeks or months (good for long duration projects), (3) show overall progress achieved (preferably with each work activities weightage). I see there are a lot of good request in the comments as well.
This video was incredible. I was so impressed with how all formulas were executed with extreme precision and accuracy. This video might be the best excel video tutorial that I have ever seen. Great work! Thanks very much for sharing it.
Very cool. I learned a lot of new tricks. And far superior to any other excel model I've seen! One feature I'd love to see is expanding the chart area. For projects stretching over long periods of time it would be great if the chart area could be expanded dynamically to reflect that. Another minor adjustment that is easy to od would be expand the team members' list to include basic contact information. This would be useful when running project with external team members; i.e. construction with multiple construction companies involved. Next would be budget/expense tracking. This would be especially useful in construction where the ability to track expenses related to each task, both in terms of hours and materials would be highly useful. And maybe even some form of payment tracking to ensure that invoices are paid in due time. I got a model in place I use, but having it build into the same excel file would be awesome, having just one file to run each project.
Great tutorial and Gantt. I have just completed the section on the Auto-Colouring engine 4 Colour modes. My spreadsheet now runs too slow, to the point of being unusable. Anybody have anyway to resolve this? Would hate to give up when I am so close to finishing!
I'm having the same issue- just after adding all the conditional formatting colors. Now it's painfully slow to work through the rest of the tutuorial- even after changing from auto-calculation to manual. I'm curious if you found any solutions.
Hello! Thank you for your excellent teaching. I created this Gantt chart based on your detailed explanations, and it has proven to be very effective in brand management. I took inspiration from the timeline of this Gantt chart to create a template for calculating ROP, but I couldn't do it! If you plan to provide more tutorials in the future, please consider designing an ROP for an industry and the procurement of packaging items that need continuous recharging. It could be a very useful topic. Thank you.
Amazing work! The best excel work I have ever seen..! Hope you'll consider an option to set multiple team members for a task/milestones in the future:)
It took al long time to follow along, but the result is a absolute joy. I also bought the template to support the great work you are doing. Thank you so much!
Wow. So much new information on how Excel works, amazing. One point to note for anyone following along with the video in Excel for Mac, creating the custom colour palette has to be done in PowerPoint. I think the developers of Excel for Mac should work through this presentation to see how dire doing conditional formatting is compared to the Windows product, but it was all doable. Thanks so much for doing this tutorial, simply incredible.
Hi Iogan 😊 Yes, that might be something for a future version of that Gantt Chart. But it has to be worked out properly in order to smoothly differentiate between feasible and non-feasible combinations of dependency connections, especially since the four (FS, SS, SF, FF) different dependency connection types can be selected for every single dependency connection. But it's on my list 👍
Thank you for an excellent video. I am stuck on a function, about 1:13:00 hours in your video. =IF(ROW(id)=ROW($B$13)+2,1,MAX(prev_col_range)+1) when I try to add it on the other rows I get the following message: "There are one or more circular references where a formula refers to its own cell either directly or indirectly. This might cause them to calculate incorrectly. Try removing or changing these references, or moving the formulas to different cells." Could someone please help me? Tried to fix it for some time now but it´s not working for me.
Had this issue, found out when I created the prev_col_range, I include the cell I was on at the time, as mentioned in the video, stop just above and all works fine, made the same mistake!
This is by far the BEST Excel tutorial I have ever seen. You are without doubt extremely knowledgeable in MS Excel and also sense some programming/coding background. Your approach with named calculations was super clever and enabled me to integrate functions that I do not have on my system since I'm running an older version of Excel. For instance my version does not have a SWITCH function, so I was able to use 'IF statements' to do a portion of the formula presented in your tutorial. I would afterwards complete the formula via the named calculation in order to get the same outcome that is presented in the tutorial. Another one that I found extremely fascinating was how you used the Concatenate feature for the Color Coding; this for me demonstrated that you are an Excel expert. It took me exactly 2 months to watch the entire tutorial while populating my own on the side. To be honest it didn't even feel like it took that long because the whole period was an enjoyable & much needed learning experience. As I was tweaking my Gantt Chart to suit my needs, I felt it would be an extra benefit to track several projects within one Chart. This would be most beneficial for Programme Managers (Project Managers implementing several Projects running at the same time). So I introduced:"Project" in the Description column (type=P) in my Chart in order to have the ability to track several projects in one Chart. This feature would also be beneficial when producing Programme Specific Reports that present when each project started and ended. With the knowledge I've acquired from this tutorial it shouldn't be a mission to get the desired outcome with the introduction of "Project". Cannot thank you enough for the knowledge you have imparted in me and many others that have watched this. The Gantt Chart is Truly AMAZING, BLESSINGS!!!
Hi Mike, I was reading the comments to see if anyone asked about an order version and how to get passed the SWITCH formula section, and then I found your comment. I created an IF formula in the color codes column but now I cannot seem to get the conditional formatting to work in column G. Would you mind sharing how you did it?
Hi, apologies for the late response. Please check Paddington's response in comments below, it should help you out if you're are still seeking for a solution.
Amazing video and thank you for teaching us too. For the ID system, wondering why you did not choose something like row()-N (in your case, N=14)? That way, only the newly added row will have to be filled with a new ID. Another question is there a way to include holidays from multiple geographies included in the plan? For example, Planning might be done in the US and Implementation in the EU, and the geographies have different holidays.
Hi Deepakkumar 😊 Thanks for your questions. Regarding the first one: I thought about doing that, but wanted to stay consistent in the technique I use for the different id columns. The stage_id column definitely requires some range lookup of previous values like in prev_col_range, so it made sense to use this technique as well for the main id. Regarding the second one: That's definitely possible. You simply need to set up a range in which you define all the holidays. I suggest to assign a name to that range like 'holidays'. Then you can pass this as the third (optional) argument in all the WORKDAY and NETWORKDAYS functions in the worksheet, so it will be accounted for.
@@theofficelab Thank you for your response. Probably, my 2nd question was not clear. In case 2 geographies have different holiday calendars, example 23-Sep is an holiday in US, but working in EU, the tasks assigned to EU team can be done on that day, however, we cannot account for that in our planned days in US task during the same period. Also, I learnt a lot about using defined names and logic in this video, thank you for that. I also found the following way to generate the S ID, the formula was much simpler. =COUNTIF($E$15:E15,"S"), this will also count the # of Stages till a given range and assign them.
@@theofficelab hello o/ - when I used your formula a circular references pop up msg appears and the value is not 1 as it should be, but 0. However, when I used the technique @Deepakkumar V suggests, works as a charm. Any ideas on what am I doing wrong perhaps? Thank you in advance.
@@winne101 Hi Michal, I get the same issue, and I use the same idea of Deepakkumar for id column 👌🏻👌🏻 but after that I stuck with stage_id. do you have an idea for that issue
This is indeed a piece of art, the steps are very clear. I hope this natively includea Holiday (add additional days based on Holiday lists, auto-coloring), and also visual options for printing out, yet these should be quite easy to implement on my own.
Hi Iogan 😊 Yes, the template is fully editable. You can easily insert a new column for tracking additional metrics like estimated hours. If you have any follow-up questions, don't hesitate to ask.
Such a fantastic walkthrough. I genuinely learned so much from your clear explanation of everything. I bought the template because I realized 1 hour into this video that it was totally worth every penny. Such great content. Thank you again.
What an amazing Gantt chart thank you for sharing this knowledge with us❤ Took me about 5 days, I had to translate the whole sheet to Hebrew and adjust weekends according to the local schedule (working days Sunday to Thursday). Thanks again and I’m glad I found your Chanel!
@@theofficelab Hello! Hope you are doing well. When i am applying formula against dependencies "SF" and "FF" then it's not working please help me out. Thanks!
Hi Masood, that sounds like you accidentally included the cell (from which you call that formula and the prev_col_range reference) into the prev_col_range reference. That would make it reference itself. As a quick check, just select a cell in the 'id' column, open the Name manager, and in there select the prev_col_range name and click into the formula field. If you have done it right, it should then highlight only those cells right above the selected cell. Otherwise, if it includes the selected cell, that would explain the circular reference. Hope that helps.
You have mad Excel-Skills! I´ve just completed recreating the template based on your excellent tutorial. The template will be a very useful tool. But what´s even better is all the things that I learned along the way about the Name Manager, cleaning up conditional formats, using custom number formats and so much more. Thank you so much! I would give this 100 Thumbs Up if I could!
This guy has mad excel skill and decided to share his knowledge. What a hero.
For those who have trouble with 1:14:40 and formula: "=IF(ROW(id)=ROW($B$13)+2,1,MAX(prev_col_range)+1)"
You need to make sure that:
- you define formula "id" while having cell B15 selected. Select B15 with you mouse before defining the formula. Refer it to "=Gantt!$B15" - same as video.
- you define formula "prev_col_range" while having (VERY IMPORTANT!) cell B23 selected. Not B15. With B23 selected, refer it to "=Gantt!B$13:B22".
Took me some time to figure it out. Hope it helps!
@yurystepanov - THANK YOU! couldn't figure out what I was doing wrong!
Thanks man I was stuck at this.
Thank you! I was bashing my head and couldn't figure out what was wrong. Evaluating the formula produced the "right" result right after the last iteration, it would print 1, instead of the value of (max(prev_col_range)+1)...
This was helpful thanks, although mine only seems to show numbers 1-9. not a clue
you are a genius buddy!!!!
This is a real goldmine for Project Managers. My excel knowledge has increased two fold .The best part of this tutorial is simple explanation and ease of navigation. Kudos to the Author who has made this freely available.
why not use MS project rather than this excel setting?
I not only came out of this tutorial a better PM but also a better Excel user, THANK YOU!
These kind of tutorials are worth their weight in gold. I've been working with Excel for years, doing evaluations, planning smaller projects and I see here how awkwardly I've done many things so far. It takes a little longer to prepare the files, but then you have a really fantastic basis. Thanks for the work that went into this video. Really really good
Thank you! This is so wonderfully helpful. One small request for your next one. As a user I'm often pausing youtube on your screen to copy the formulas you are writing. As you near the end of the formula (e.g. &"...",d.name),"") at around 1:21:47 you write the end of the formula quickly and then click out. It's difficult to pause at the exact right moment where the formula is complete and I often need to go back 4 or 5 times. My suggestion would be as you are finishing writing the formula, say something like 3, 2, 1. Have a great day!
This is the best video available on RUclips. you can watch 20 videos on Gantt chart and none of them will come in comparison to it. this video earned my subscribe forever.
Thank you, Mohammedd! Really appreciate your kind words and feedback ☺
This is simply genius. I did not know I was creating a gantt chart until i saw this video. I just thought this is another excel template.
I have finally finished this tutorial and it was amazing. I added a few of my own things to it - icons for Deliverables and Stage Gates and dynamic date increments. There may be better ways to do this but it works for me.
Deliverables working very similar to milestones.
Stage gates slightly differently as they will be coloured gold to stand out regardless of colour option unless it is marked as an issue.
If you have followed the tutorial this should be simple enough. You end up with a few more colour codes that you need to sort the conditional formatting for.
For the date increment I added another dropdown named "view" with option to select Days, Weeks or Months.
In cell Y10 I used the formula:
=CHOOSE(MATCH(view, {"Days","Weeks","Months"}, 0), WORKDAY(X10,1), X10+7, EDATE(X10,1))
Note that 'view' should point to data validation cell of the additional dropdown.
In cell X13 I have used the formula:
=CHOOSE(MATCH(view, {"Days","Weeks","Months"}, 0), LEFT(TEXT(X10, "ddd"), 1), WEEKNUM(X10), LEFT(TEXT(X10, "mmm"), 1))
This changes the row to show weekday initial, week number, or month initial based on what view choice has been selected.
The formatting carries over correctly for any dropdown selection based on colour and progress etc.
The only thing that drops off are milestone, deliverable or stage gate icons when changing view to weeks or months.
Not sure if there is an easy fix for this to show the icons regardless of what view choice is selected.
If anybody has a fix for this let me know.
The scroll buttons also need amended as they only move in 1 day increments. Not sure if this can be changed based on a dropdown selection?
My gantt runs incredibly slow using my work laptop which is a shame. On my personal laptop it runs fine!
Dear Hope you are doing well. You are humbly requested to share the file. Thanks!
What? Are you for real?! This is amazing. Never thought it is possible to implement such an extensive dependency system with forward and backward project scheduling in an Excel Gantt Chart O.o
Thank you!
It is impressive, but if you are at that point you might as well pay for MS Projects or any other project management tool
@@89DerChristian Having everything in Excel is really convenient. You can have many projets tracked using this tool that are reconsolidated into a global dashboard.
If you add a small VBA, you can also automatically send reminders of pending task to all the actors or a project progress report
In real project, a task can be dependent on more than 1 predecessor tasks.
The method in the video also hasn't solved gantt chart readability problem:
Readers must juggling their sights between activity name on the left, time axis in the top and activity block position and length to determine tasks' names, durations, dependencies, start and end dates
My solution is Vertical Gantt Chart which is based on making the vertical axis as time axis.
It produces much more compact and readable chart.
As horizontal space isn't limited by duration anymore, activity names and descriptions can be written directly within the block.
Duration can be simply pictured as the block's height.
Dependencies can also be pictured directly as block adjacency. No more arrows needed.
I put some examples in LinkedIn article but can't put the link here as youtube will delete it.
Incredible tutorial - I almost just bought the template but decided it was in my best interests to follow along and build it myself and I'm so glad I did. I've been wanting a tool like this to manage project timelines with task dependencies forever, and this functions amazingly well and ticks off 99% of my wish list. I'm incredibly grateful you took the time to think this through so thoroughly and create this resource for others to benefit from.
One thing that would be helpful in future tutorials - I wish you'd pause for a second or two after typing a long formula in before clicking "ok" and moving along. It would make it easier to pause the video on the completed formula and get it typed in, and double-checked before restarting the video.
Thank you again! You're an amaaaazing teacher!
Hey Lisa, thank you so much for your feedback 😊Glad you decided to build it yourself and enjoyed the process. I will consider your suggestion with the pause for my next tutorials.
you can always pause the video and use the greater than less than symbols to move video by one frame.
@@theofficelab You have done a great job. I found that you have done very well with pauses as well.
Could you please advise on how to convert this considering the hours as well as the date? I would like to build this plan breaking down to hours level of detail. Is this possible? Infinite thanks
@@arulrajesh Wow! I never knew you could advance these videos frame by frame with the < or > symbols. I've learned something new that will help with all future YT vids. Thank you.
could you tell me please how can i convert working days from 5 days per week to working days all week
I've been waiting for this template for 30 years. WOW
I've just watched the whole video after a hard day at work, and was still able to follow all instructions. What an amazing video, I can only imagine how much preparation it required! Hut ab ;-)
Hi Sandra,
Your comment really made me smile 😊 I am so happy you enjoyed the tutorial even after having a hard day at work. And yes, the development and preparation of this tutorial was some big effort 😄 but whenever I can provide new value to my community, it's absolutely worth it in my eyes. Beste Grüße
This is by far the BEST tutorial I have followed on RUclips. Hands Down! Everything was so clear and concise, the formulas are beautiful, the conditional formatting is amazing. I consider myself an intermediate Excel user, and there are things that you showed that I did not know how to do. Please don't quit doing these types of tutorials! You broke everything down so well, so I understood not only what each formula was doing, but WHY. I love that you did not use quick-buttons to do anything...definitely a help for someone like me who does not use those often.
I was able to follow along with the instructions and create my own Gantt chart. Now that I have finished the tutorial, I can customize it for my needs very easily without breaking ANYTHING in the file. Everything is set up so that I can lock cells easily so as not to overwrite formulas.
Thanks again! It is greatly appreciated!!
could you tell me please how can i convert working days from 5 days per week to working days all week
=WORKDAY.INTL(INDEX(Sheet1!$W:$W,Sheet1!d.row),1+d.lag,weekend,holidays)
Dear Hope you are doing well. You are humbly requested to share the file. Thanks!
That was an awesome tutorial thanks. Really clear and well paced. Using the options on screen rather than using shortcuts like some do in tutorials, also, breaking down the formulas into stages before applying, to help us understand why we are doing what we are doing, both really helped me not to get lost. I've certainly picked up a load of excel knowledge and have a shiny new Gantt chart to show for it. Thanks for sharing this, very much appreciated.
I finished mine today and I want to thank you for your incredible work and generosity! I made some adaptations, translating everything into Brazilian Portuguese, increased the role capacity from 8 to 20, inverted the employee logic (first I choose the role and then only the employees assigned to that role appear for me next), added a new larger category before the Stage called "Project" following the same logic you applied (and creating a unique ID column for the projects), and I also transformed the formulas made to copy&paste in the Projects and Stages rows into the Independent Start, Working Days and % columns, in calculations with dynamic references that establish the intervals automatically... so far it is working wonders!
Could you please send me your template😅
@@helpinghands4875 I used the entire tutorial in this video, step by step, pausing and returning infinite times. Everything I modified from the original template and everything I did extra to make it adapt to my specific needs was based on free videos available on RUclips, because I I'm a self-taught newbie to Excel, and that's exactly how I learned the little I know, without shortcuts. You can do it too!
@@MariMariou Olá, preciso desse excel em português e infelizmente não tenho tempo nem conhecimento para faze-lo do zero. Existe alguma chance da gente conversar sobre eu adquirir seu excel?
are you having issue with it beinbg slow with many rows?
@@bryanmartinez291 That's the biggest problem that I've cross with... I'm considering the possibility of creating a cemetery with all the concluded projects inside a separated spreadsheet that will basically be a duplicate of the original one, but I don't know if all the named intervals will have to be remade. If that's the case, is a lot of work hours ahead, and I'm not actually using the file to make it worth the effort 😅
This is an enhancement to a previous version. And it is magnificent. Im a PM by profession and am enthused by the power in this tool
The functionality dispenses with the need to carry out a conventional network diagram and critical path analysis. By allowing dynamic forward and reverse scheduling, the critical path is produced based on dependency matching by both task and stage; when a dependent task changes, the critical path - expressed through stages to a changeable end date ("defined earliest finish point") - also changes dynamically. In other words you can go straight from sequenced task/duration/predecessor listing and then jump direct to Gantt with confidence - you dont need to labour over LS, LF, ES, EF with the forward and reverse pass cycles etc. as you can simply enter revised start and end dates as required, to then directly build in the required judgement on tolerance (or float) per row/task - and the dependency scheduling automatically updates. A game changer. Effortless! 👍
Im purchasing direct; I could build (as I did for the predecessor) but I do not have the time.
Will be bringing this into service with my work, there is so much added value here. Thanks for posting, it is difficult to quantify the time you will save me, onward to cost efficiency and upheld quality standards
Wow. I really appreciate your positive and extensive feedback!! I am really happy that you - as someone who obviously has a good understanding of this topic - sees the value in this 🙂
I don’t comment on youtube very often, but here I have to. Thank you, thank you, thank you! I would seriously recommend this tutorial to anyone who wants to take his/her skills with Excel further than "A+B=C" usage. This is pure gold tutorial - clearly explained ,step by step, not too slow, not too fast. Building up every step on previously made steps so you can understand how the whole process and hierarchy works. Showing up many actions and tricks which you can use every day in Excel if you were unaware of them until,now. This, dear friends, I call a professional tutorial. I have spent several days in my free time following this tutorial and building up the whole gantt sheet and besides the fact that I have a cool gantt now, I feel really enriched by new knowledge. Thanks again.
Done! After months of following every single minute of your tutorial, I finished today. Like you say the excel "Works Like a charm" and "Perfect!" Thanks for sharing. I learned far beyond what I expected. I am going to use it with clients right away. Chapeau!
I was looking EVERYWHERE for this. I even stumbled upon your site while looking. I ve seen the old version, and happened to need something like that for work. Tried to find it back and was nowhere. I was searching for like a week straight. Until i finally saw the video was retracted. Got very sad tbh. Now i'm happy for this.
Ty for the new version.
Hi Alex 😊 Your welcome. Glad you like it.
Hi, thank you for your big work on this template. I followed each step and finally I can appreciate all new things I learned with this video. I've added a formula in order to calculate automatically the number of items for each stage and then to put a unique formula to calculate the start for the stage. In this way when an item will be added, the formula does not need to be changed to match the number of rows. I've added a column after the R id, named N. Row; the formula to calculate the number of rows in the stage will be: =IF(type="S";num_item_per_stage;-1) where num_item_per_stage is a defined name with the formula: =IFNA(MATCH(Gantt!stage_id+1;INDIRECT("R"&ROW()+1& "C" & COLUMN(Gantt!stage_id) &":R500C"& COLUMN(Gantt!stage_id);FALSE);0);MATCH(-1;INDIRECT("R"&ROW()+1& "C" & COLUMN(Gantt!stage_id) &":R500C"& COLUMN(Gantt!stage_id);FALSO);0))-1. Then, in the colum ind.start, in the stage rows, the new formula: =MIN(INDIRECT("R"&ROW()+1&"C"&COLUMN(Gantt!plan.start)&":R"&ROW()+Gantt!num_item_per_stage&"C"&COLUMN(Gantt!plan.start);FALSE)), that can be used with a defined name auto_stage_start. Hope this will be useful. Thank you a lot for this video and for this great job.
Dear Hope you are doing well. You are humbly requested to share the file. Thanks!
WOW!!! Just WOW!!!
The first tutorial ever I have actually watched from the very beginning to the very end with great attention!
Brilliant!
This is fantastic! One feature I would like to see is a means of tracking worker capacity. If a task has been assigned and allocated say 5 work days, this does not imply the individual cannot do other work that overlaps this time. However, there is a point at which workers cannot handle additional tasks in the same day. For some of our workers, our organization has well-defined units of work. We would like to track human resource availability, both at the individual level and among all workers of the same type. This will allow work managers the ability to see who has spare capacity whenever assigning new tasks. Thank you again!
That's a true game changer for me! Have been looking for a way to do backward and forward scheduling with dependencies many times, but always got stuck and all templates out there didn't manage to do that either. Keep up the good work!!
Glad it was helpful!
Absolutely fantastic! So many new approaches for me that will apply to other scenarios.
Two questions:
1. Have you considered using Excel tables so the formulas, formatting, etc. automatically apply by simply starting to type in the next empty row?
2. Do you have something on the design process? Obviously, A LOT of work went into getting to this finished tutorial, but there also must be a lot of advance planning to know things like the relationships between all of the defined names and their use in cell and conditional formatting formulas. It would be fascinating to see how you go about designing something like this before you get started vs how much is trial/error/incorporate new idea as you build.
Man its been 2 years you publish this video but you saved my day with this. People say you deserve like you awsome etc. But man forget them this is real gold mine for anyone wants to manage a project. Im planink to add raci chart and progress notes by just linking to new sheet as you done in settings button.
I'm building this model following your turorial and I am now almost halfway through it. It's incredible how much I learnt about using named cell-ranges in a very creative way and the same is true for the incredible power you show in using conditional formatting. An experienced user for almost 30 years now I still learned a lot of new tricks. I can recommend following the tutorial even if you bought the file. Chapeau. (I'm not French but Dutch but the expression is the same here; I tip my hat)
Hi Ron, i am also planning to buy the file, which tutorial are you following, the RUclips video or are there another one ?
@@fransverwoerd1901 hi Frans, ben je nl? Geef me je email.
@@fransverwoerd1901 Ik volgde de bovenstaande video. Het kostte me een eeuwigheid. Kleine tikfoutjes grote gevolgen (Engels een komma als scheidingsteken in functies en in het Nederlands punt-komma om maar een voor de hand liggende fout te noemen). Maar hij is klaar en hij werkt. Alles bij elkaar zeker 20 uur werk om het te kunnen volgen en overtypen. Maar als je wilt kun je mijn model krijgen. Voor niks. Daarom vroeg ik je mailadres.
@@RondenBreems goeden avond Ron, ik heb met veel plezier de video bekeken en enthousiast geworden over de mogelijkheden die dit te bieden heeft. Is het brutaal als ik vraag om van jouw template gebruik te mogen maken? Ik zou willen kijken hoe ik het kan toepassen.
Ik hoor graag van je. Groet Raymond
@@raykohlmann4122 Geen bezwaar. Pas wel op, m.i. is het erg fout gevoelig. Neem in ieder geval de video door zodat je ziet wat ingevuld mag en moet worden en wat niet. Geef me je e-mail adres en ik stuur het ding op.
I have no words to express my gratitude to you. It was fantastic to see how a normal excel can deliver so much. Learnt so many good things from this video. Thanks a lot ..lots of love and blessing to you
Brilliant! The HUGE amount of Knowledge for BOTH Excel & Project Management is unbelievable.
Thank you for sharing that Work.
SMALL TWEEK:
I created this PM Gantt Chart with "Selecting a Calendar" feature.
Since Workdays are not similar in other Countries & also some Projects run on 24/7 basis, I added a "Calendar Selection" option & adjusted all Functions using WORKDAY to WORKDAY.INTL along with other minor modifications.
Once again, thank you so much for sharing this Piece of Art.
Greetings from Egypt.
Hi could you please share your modifications since our workdays are on 24/7 basis. Thanks in advance.
I’ve just completed the build and cannot thank you enough for what is a great demonstration of using the vast options of nesting functions, cell referencing and named formulas. I will probably never use the automatic conditional formatting again ever now that I’ve seen the power of the formula approach. The only thing left for me to work out is the calculation for a second % column that allows me to show each task as ahead or behind the position it should be. (I.e. Status =35% but it should be @ 70%) as an additional ready value.
Great job and I am hugely envious of your ability with Excel.
This is really amazing tutorial. This has boosted the understanding of XLS to a great extent. While implementing my own version, I could figure out >10 improvements that one has to do for a complex project.
This is just next level Excel. Beats all Advanced Excel trainings.
Wow 😊 that's a big compliment! Thank you, Punnya.
You are simply the best teacher, I have come across, everything well articulated, well planned, very concise and getting the most from an excel in a simple manner without using Macros and VB,I have just two things I have stumbled across,1- My Unicode symbols are not as milestones or flags, rather they are taking the symbols from Calibri font and 2- Since we make the planning for 4 years, so a tip to convert the time scale to month will be highly appreciative. Thanks for teaching excel functions in a very practical way and in your tone simply amazing.
I agree, it would be awesome if you can convert the time scale to month or even quarters or weeknumbers. Maybe there is a simple workaround?
The only thing that I can say is "OMG". I thought I knew something about Excel until ı saw that unbelievable video. Thank you so much!!!
Truly an amazing tutorial. I was able to follow it all and after about 12 hours, I completed building the entire Gantt. I did have two questions:
1. How easily could I add 1 more level of the tasking hierarchy (the WBS). Example being wanting to track different areas or sub projects separately. Currently this is a two level structure and was curious about making it a 3 level structure.
2. Have you considered a float column to allow the movement of activity durations separate from a lag? Would this be a difficult add to the calculations and integration of the "logic" relationships.
Probably the best online tutorial I've come across. Absolutely packed with advanced excel tips and tricks. Thank you!
Took me something like 3 weeks to go through it (and a lot of late nights) but here it is ! My Formula Student team now has a proper Gantt chart !! thank you very much for this piece of work. I would have loved to have some arrows coupled with the dependency engine tho
Would you mind how you went about adding the milestone diamond, it is not working for me, thank you.
Genius. Worth the investment. For advanced users, time-wise it takes about 2x the video length to build. For intermediate users, get comfortable and stage the build over a few days to absorb the learning that is excellently demonstrated and explained here. Allow 3x the time.
Thanks for sharing your knowledge at community level. As a poor PhD student, I’m extremely grateful for this free gift of learning you’ve shared!
Hello! Hope you are doing well. When i am applying formula against dependencies "SF" and "FF" then it's not working please help me out. Thanks!
It is a perfect lesson. But be careful about the point that not to conditional format the whole row or column. Only format where you work. Otherwise, Excel will freeze, which is so annoying. He said that at the end of the video
1:42:44
bu kısım önemli
ikinci görevde nasıl geri planlama yapılıyor
1:44:25
nasıl daha fazla özelleştirebiliriz
2:38:19
eğer renk paletini atlar iseniz tam da burada patlarsınız
3:00:14
RENK PALETLERİNİ DÜZGÜN YAPMADIĞIMI İÇİN bu kısmı yapamadım eksikler var koşullu renklendirme aşamalaırnı düzenlemem gerek
2:20:07
bu kısımda kaldım tektar döneceğim kişilerin titleleri eksik bende
Hi, at which minute does he say not to do the conditional formatting? or in general where is this conditional formatting mentioned at first? I made this gantt and Excel is indeed freezing :(
You sir, you bow to no one - This is excellent
This is an absolutely amazing piece of work. I am so grateful for this, the presentation and the way you have explained everything is a 10/10. I have just completed building the spreadsheet and would just like to say a huge thanks. Incredible
I had so much fun creating this while watching your video. Learned SO much and I unlearned some bad excel habits I had along the way too. Thanks so much!!! You are an excel genius! 😂❤
It's so great to hear that ☺ Thanks for your kind words!!
Incredible Gantt chart and tutorial video!!! Thank you so much for taking the time to do this! I successfully made the charts and it’s amazing to me how well it works. I think an extra dropdown list at the top for the option to show weekends, show holidays, show everyday and show default would be a good addition. Unfortunately for me, I'm not advanced enough to just add it in. Maybe you could make a video on this addition in the future?
I have started a new channel with a new ID to make Excel tutoril videos and now I am not so sure. U r definitely one of the best tutors out there; thank you for helping me visualize bigger, further and near impossible.
Thanks Imran 😊 I really appreciate it.
An excellent tutorial, quite challenging for a begginer in excel but the reward is incredible. Thanks a lot for your work and explanations !
Thanks for your positive feedback 😊 happy to hear it was an rewarding experience.
Thank you so much for the amazing tutorial! It was very clear and great fun to work through with you! For those using an older version of excel, without the "SWITCH" function, you need to use nested IF() functions. I used the following formulas which have the same effect and work according to the video:
@2:02:20 “color_code_default”
=IF(Gantt!type"", IF(Gantt!type="S", 1, IF(Gantt!type="T", 2, IF(Gantt!type="M", 3, -1))), -1)
@2:03:30
=IF(color_by="Default", color_code_default, IF(color_by="Project Structure", "ps_cc", (color_by="Team Roles", "tr_cc", IF(color_by="Issues", "i_cc", color_code_default))))
@2:09:40 "color_code_project_structure"
=IF(color_by="Default", color_code_default, IF(color_by="Project Structure", IF(stage_id=-1, -1, NUMBERVALUE(IF(MOD(stage_id, 8)=0, 8, MOD(stage_id,8)) & IF(type="S", "01", IF(type="T", "02", IF(type="M", "03"))))), IF(color_by="Team Roles", "tr_cc", IF(color_by="Issues", "i_cc", color_code_default))))
@2:18:07 "color_code_team_roles"
=IF(color_by="Default", color_code_default, IF(color_by="Project Structure", color_code_project_structure, IF(color_by="Team Roles", IF(role_id=-1,color_code_default, NUMBERVALUE(role_id&IF(type="T","01",IF(type="M","04")))),IF(color_by="Issues", "i_cc", color_code_default))))
@2:30:26
=IF(colour_by="Default", color_code_default, IF(color_by="Project Structure", color_code_project_structure, IF(color_by="Team Roles", color_code_team_roles, IF(color_by="Issues", IF(issue="⚠", IF(type="S", "901", IF(type="T", "901", IF(type="M", 902))), color_code_default), color_code_default))))
For conditional formatting of the chart itself, I also found you need to use quotation marks but only on 901: OR(“color_code=”901”, color_code=902)
This video shows some exciting new ways of expanding on the cell-based Gantt chart. I love seeing innovation with spreadsheets!
Thank you, Vertex42. I really appreciate it 😊
I have taken inspiration from this video & created template. The video is about 3.30 hours but believe me for me it took 15 days to make this template. Further I become successful to make 4 stage dependency engine (S,T,st,""). Now based on every stage dates, S, T & st stage dates are recalculated & automatically adjusted. If T,st depends on others tasks then also these dates are changes. I still feel that there lot to do for more value addition. Thanks a lot.
Brilliant piece of work...and a very easy to follow tutorial. Love the way that you are using named references for complicated functions. Thank you so much.
A-MAZING! I started building this from scratch following your instructions (played back at 0.5x speed)... and about half way through decided it was well worth the purchase of the completed template. I did still listen to portions of your video to understand how they function.
I would LOVE to see how to incorporate this Gantt chart into a "Master Project" Excel to track all progress of multiple projects at once, and would be willing to purchase that template too!
Amazing!!!! My current project team don’t deserve this level of beautiful planning so I’ll use for my next project hehe Thank you 😊
This was brilliantly well presented and I totally love finished product too. I can also see that I will be referencing this tutorial for concepts to apply in other projects too. Since you asked for possible improvements and the only one I can think of would be to add a critical path analysis calculation and allow an give an option to visually highlight the relevant tasks. Well done and thank you.
Finally after grinding for a week on my free hours, I’ve done it. 😂
Congrats!!! 🙂 Hope you enjoyed the process and learned a thing or two along the way 😄
@@theofficelab Thank You! Are you planning on doing new ones? The is the best one I've done so far. Extremely Complex. You are superb.
😂
Please help me
How many hours roughly did you spend
I don’t even know what to say cus you definitely have received a lot of thanks!
But honestly, I will forever appreciate the work you’ve done on me through this video🎉
This is an awesome tutorial. There was nothing in here I didn't already know, but the way you hang it all together to create the result is exceptional.
Thank you, Steve 😊
What a fantastic piece of work! Follow your tutorial, I was amazing about what you've done and learnt many excel skills. Many thanks for sharing.
I've completed and have some my own modification and additional:
- Delete issue color mode and just make red color for the progress bar when alert icon was choosen.
- Change plan vs base, progress dropdown list to checkbox.
- Modify the little green bar (for all project duration) to a mini milestone chart (like timeline bar in ms project).
- Make a dropdown list about timeline scale with Day and Week option to view long duration project.
How did you manage to finalize the last one?
@@merjehlasabanow i dont even know how can do that 😂 (cant remember variables and functions used)
Very brilliant. You simply rock and have a great command of excel. There is an improvement I would love to suggest. Could you add a botton to automatically show the critical path? This is useful to help the project manager know the tasks they can accommodate some slack on and those they cannot. Hope you would consider that. Cheers.
Hi Ogidi 😊
thanks for that suggestion. That's a great idea. I'll put it on my list for future ideas.
@@theofficelab Yes, I really love to see these features.
@@theofficelab please reply to my emails to obtain latest update of the purchased template
@@AmletoGuerra Did you improve the spreadsheet to automatically show the critical path??
Even though it's been heavily emphasised in the previous comments, I need to say this was amazing. I had just finished a personal template for PM, but when I saw your video, I spared the weekend to make it through the tutorial and man, what a joy! A lot of fresh and useful knowledge that will be applied on my day to day.
My target now is to enhance the template so we can have an option to visualize the Gannt area both in a daily view and a weekly view, and also add the holidays into the WORKDAYS calculations.... this will fit perfectly to my needs.
I couldn't thank you enough for providing such a prime content for free. Kudos👏👏👏👏👏
I've already done the day and week view months ago, and add some my modifications to this file. Now i even dont know how can i do that 😂
This is just amazing, your knowledge and delivery is amazing. A lot of this was beyond my level of skill, but I was still able to follow and complete in its entirety, and learn a lot along the way.
This taught me so much. Better than attending a class. Clear, consise and logical. Thank you so much!
OMG, this is fantastic. Never know excel can be use to visualize schedule like this. Thanks so much for sharing this to us
Thank you for your positive feedback! I am happy you liked it 😊
just being tasked with getting a rather complex project on the road, i came across your tutorial. many thanks, in particular for some of the more sophisticated excel functions.
This is great! Just want to ask if there is a way to collapse and expand the dates? Like switch from a weekly view to a daily view of the dates. How would you do it?
Thank you so much for this high quality content! I already finished the tutorial, and this is by far the best Gantt Chart template here on RUclips, once again, thanks 👍
Thank you for bringing such a useful example forward. It shows the power of Excel many overlook and enables many to rethink and build more intelligently for the future. Curious if you would share what you use for Theme\Color to get the selectable color pallet you use??? I know how to ref the hex colors but not how to set the GUI to match. Thanks again!!!
For those seeing this now. HEX values are in the description :) "Color Palette: F0E936, 40D492, F9B710, FE1684, 9237BC, 4633F2, 3285F3, 1DD7F3"
Haha, had to pause a lot to absorb the many many hints in this tutorial. Thanks a lot for all the precious information. 😀
My pleasure, Norbert 😊 Happy to hear you enjoyed the process and learned something along the way.
Hello, thanks for showing an incredible work.
I just want to ask a question, how to include weekend/holiday in the timeline?
You should use the formula WORKDAY.INTL in this case. I applied this formula when following the tutorial.
For those of you on newer versions of excel, you may need to wrap certain merged cells in an Index function to avoid spillover.
E.g. the display value needs to be INDEX(display,1,1).
Took me almost 90 minutes of checking to finally figure out what was happening!
Great video. Didn't know you could use the icons as customized formats! Awesome, thanks for sharing!
The best advanced excel tutorial ever
Thank you, Sergio 😊 I really appreciate it!
This is great Gantt Chart that filled with features. Thank you. I would like to have version that can easily (1) change number of working days (i.e. 5 working day per week and 6 working days per week) and incorporating non-working public holidays, (2) Change the Gantt chart area by days, weeks or months (good for long duration projects), (3) show overall progress achieved (preferably with each work activities weightage). I see there are a lot of good request in the comments as well.
Thanks for your positive feedback and these great suggestions, SimeonKong 😊
@@theofficelab Having optionality for weeks and months would be great.
This video was incredible. I was so impressed with how all formulas were executed with extreme precision and accuracy. This video might be the best excel video tutorial that I have ever seen. Great work! Thanks very much for sharing it.
Very cool. I learned a lot of new tricks. And far superior to any other excel model I've seen!
One feature I'd love to see is expanding the chart area. For projects stretching over long periods of time it would be great if the chart area could be expanded dynamically to reflect that.
Another minor adjustment that is easy to od would be expand the team members' list to include basic contact information. This would be useful when running project with external team members; i.e. construction with multiple construction companies involved.
Next would be budget/expense tracking. This would be especially useful in construction where the ability to track expenses related to each task, both in terms of hours and materials would be highly useful. And maybe even some form of payment tracking to ensure that invoices are paid in due time. I got a model in place I use, but having it build into the same excel file would be awesome, having just one file to run each project.
I was looking at it and thought :" this is very familiar", i went to your site and i already bought this template. Great work
Can you share dear?
Great tutorial and Gantt. I have just completed the section on the Auto-Colouring engine 4 Colour modes. My spreadsheet now runs too slow, to the point of being unusable. Anybody have anyway to resolve this?
Would hate to give up when I am so close to finishing!
I'm having the same issue- just after adding all the conditional formatting colors. Now it's painfully slow to work through the rest of the tutuorial- even after changing from auto-calculation to manual. I'm curious if you found any solutions.
@@kimchi6538 Were you able to find a solution
Hello! Thank you for your excellent teaching. I created this Gantt chart based on your detailed explanations, and it has proven to be very effective in brand management. I took inspiration from the timeline of this Gantt chart to create a template for calculating ROP, but I couldn't do it! If you plan to provide more tutorials in the future, please consider designing an ROP for an industry and the procurement of packaging items that need continuous recharging. It could be a very useful topic. Thank you.
Unicode Icons, codes : 26A0 , 25C6 , 2691 , 2196 , 2199 (FONT : SEGOE UI SYMBOL)
I totally admire your work and the way you apply and teach in this video. The Gantt Chart is very helpful.
Amazing work! The best excel work I have ever seen..! Hope you'll consider an option to set multiple team members for a task/milestones in the future:)
Hi Christian 😊
That's a great idea. Thanks for the suggestion.
How would you do that for this sheet I’m making this Gantt chart now and its so good!
@@theofficelab Do you have any suggestions on how to approach the presence of multiple members per task and milestone? thank you so much
It took al long time to follow along, but the result is a absolute joy. I also bought the template to support the great work you are doing. Thank you so much!
Hi, How do I go about adding dependency tracers between the tasks? It is the only thing missing from this great tutorial.
That's a great idea! Have been thinking about this as well before, but the implementation of this might get complicated. But it's on my list 🙂
@@theofficelab Hey, any update on these tracers?
Wow. So much new information on how Excel works, amazing. One point to note for anyone following along with the video in Excel for Mac, creating the custom colour palette has to be done in PowerPoint. I think the developers of Excel for Mac should work through this presentation to see how dire doing conditional formatting is compared to the Windows product, but it was all doable.
Thanks so much for doing this tutorial, simply incredible.
Have you considered a methodology for adding multiple dependencies to a task?
Hi Iogan 😊
Yes, that might be something for a future version of that Gantt Chart. But it has to be worked out properly in order to smoothly differentiate between feasible and non-feasible combinations of dependency connections, especially since the four (FS, SS, SF, FF) different dependency connection types can be selected for every single dependency connection. But it's on my list 👍
Your use of the "define names" option is great!!!
Thank you for an excellent video. I am stuck on a function, about 1:13:00 hours in your video. =IF(ROW(id)=ROW($B$13)+2,1,MAX(prev_col_range)+1) when I try to add it on the other rows I get the following message: "There are one or more circular references where a formula refers to its own cell either directly or indirectly. This might cause them to calculate incorrectly. Try removing or changing these references, or moving the formulas to different cells." Could someone please help me? Tried to fix it for some time now but it´s not working for me.
I am also having this same issue, 4 hours into the build! And now I feel stuck, did you manage to figure this out? 😅
@@bbxhunter No sorry ;/
Had this issue, found out when I created the prev_col_range, I include the cell I was on at the time, as mentioned in the video, stop just above and all works fine, made the same mistake!
@@Ian-xq4rt God, so simple mistake, hehe! Thank you very much!
Double-check your prev_col_range calculation - 100% this is the cause of the problem... 🙂
This is by far the BEST Excel tutorial I have ever seen. You are without doubt extremely knowledgeable in MS Excel and also sense some programming/coding background. Your approach with named calculations was super clever and enabled me to integrate functions that I do not have on my system since I'm running an older version of Excel. For instance my version does not have a SWITCH function, so I was able to use 'IF statements' to do a portion of the formula presented in your tutorial. I would afterwards complete the formula via the named calculation in order to get the same outcome that is presented in the tutorial. Another one that I found extremely fascinating was how you used the Concatenate feature for the Color Coding; this for me demonstrated that you are an Excel expert.
It took me exactly 2 months to watch the entire tutorial while populating my own on the side. To be honest it didn't even feel like it took that long because the whole period was an enjoyable & much needed learning experience. As I was tweaking my Gantt Chart to suit my needs, I felt it would be an extra benefit to track several projects within one Chart. This would be most beneficial for Programme Managers (Project Managers implementing several Projects running at the same time). So I introduced:"Project" in the Description column (type=P) in my Chart in order to have the ability to track several projects in one Chart. This feature would also be beneficial when producing Programme Specific Reports that present when each project started and ended. With the knowledge I've acquired from this tutorial it shouldn't be a mission to get the desired outcome with the introduction of "Project".
Cannot thank you enough for the knowledge you have imparted in me and many others that have watched this.
The Gantt Chart is Truly AMAZING, BLESSINGS!!!
Wow! Thank you so much. Really appreciate your kind words and your suggestions on how to tweak it😊
Hi Mike, I was reading the comments to see if anyone asked about an order version and how to get passed the SWITCH formula section, and then I found your comment. I created an IF formula in the color codes column but now I cannot seem to get the conditional formatting to work in column G. Would you mind sharing how you did it?
Hi, apologies for the late response. Please check Paddington's response in comments below, it should help you out if you're are still seeking for a solution.
Amazing video and thank you for teaching us too. For the ID system, wondering why you did not choose something like row()-N (in your case, N=14)? That way, only the newly added row will have to be filled with a new ID.
Another question is there a way to include holidays from multiple geographies included in the plan? For example, Planning might be done in the US and Implementation in the EU, and the geographies have different holidays.
Hi Deepakkumar 😊 Thanks for your questions.
Regarding the first one: I thought about doing that, but wanted to stay consistent in the technique I use for the different id columns. The stage_id column definitely requires some range lookup of previous values like in prev_col_range, so it made sense to use this technique as well for the main id.
Regarding the second one: That's definitely possible. You simply need to set up a range in which you define all the holidays. I suggest to assign a name to that range like 'holidays'. Then you can pass this as the third (optional) argument in all the WORKDAY and NETWORKDAYS functions in the worksheet, so it will be accounted for.
@@theofficelab Thank you for your response. Probably, my 2nd question was not clear. In case 2 geographies have different holiday calendars, example 23-Sep is an holiday in US, but working in EU, the tasks assigned to EU team can be done on that day, however, we cannot account for that in our planned days in US task during the same period.
Also, I learnt a lot about using defined names and logic in this video, thank you for that. I also found the following way to generate the S ID, the formula was much simpler.
=COUNTIF($E$15:E15,"S"), this will also count the # of Stages till a given range and assign them.
@@theofficelab hello o/ - when I used your formula a circular references pop up msg appears and the value is not 1 as it should be, but 0. However, when I used the technique @Deepakkumar V suggests, works as a charm. Any ideas on what am I doing wrong perhaps? Thank you in advance.
@@winne101 Hi Michal, I get the same issue, and I use the same idea of Deepakkumar for id column 👌🏻👌🏻 but after that I stuck with stage_id. do you have an idea for that issue
This is indeed a piece of art, the steps are very clear. I hope this natively includea Holiday (add additional days based on Holiday lists, auto-coloring), and also visual options for printing out, yet these should be quite easy to implement on my own.
Can I edit the template? I would be interested in adding an estimated hours to field to calculate the estimated engineering cost of the project.
Hi Iogan 😊
Yes, the template is fully editable. You can easily insert a new column for tracking additional metrics like estimated hours. If you have any follow-up questions, don't hesitate to ask.
Such a fantastic walkthrough. I genuinely learned so much from your clear explanation of everything. I bought the template because I realized 1 hour into this video that it was totally worth every penny. Such great content. Thank you again.
Is there a way to make the timeline into weeks instead of days? I'd like to have a version that showed the Gantt chart by weeks. Thanks much!
Hi Mike 😊
Thanks for that suggestion. It's on my list of future ideas.
What an amazing Gantt chart thank you for sharing this knowledge with us❤ Took me about 5 days, I had to translate the whole sheet to Hebrew and adjust weekends according to the local schedule (working days Sunday to Thursday). Thanks again and I’m glad I found your Chanel!
hello ..could you tell me how can I make working days =working days all the week
How do you type in the second line in formula bar (1:02:49) ?
I use Alt+Enter 😉
@@theofficelab Hello! Hope you are doing well. When i am applying formula against dependencies "SF" and "FF" then it's not working please help me out. Thanks!
learning excel by doing project like this has reveal itself faster and more effective than following actual excel tutorial
In the =IF(ROW(id)=ROW($B$13)+2,1,MAX(prev_col_range)+1) is it showing circular error how to fix this ?
Same thing happened with me and I had to use normal numbering system
Hi Masood,
that sounds like you accidentally included the cell (from which you call that formula and the prev_col_range reference) into the prev_col_range reference. That would make it reference itself.
As a quick check, just select a cell in the 'id' column, open the Name manager, and in there select the prev_col_range name and click into the formula field. If you have done it right, it should then highlight only those cells right above the selected cell. Otherwise, if it includes the selected cell, that would explain the circular reference.
Hope that helps.
I am also getting the same error , have checked prev_col,_range, it's working fine.
Same here
Do you have any other ideas how to solve the problem?
Best Gantt I've ever seen or used without having the use of Primavera or MS Project. Thanks for sharing and the lessons!
Hello! Hope you are doing well. When i am applying formula against dependencies "SF" and "FF" then it's not working please help me out. Thanks!
Amazing step-by-step tutorial on how to create this Excel Gantt chart - thank you!
You have mad Excel-Skills! I´ve just completed recreating the template based on your excellent tutorial. The template will be a very useful tool. But what´s even better is all the things that I learned along the way about the Name Manager, cleaning up conditional formats, using custom number formats and so much more. Thank you so much! I would give this 100 Thumbs Up if I could!
Everything is clear. Brief and clear. No water. Thank you)
HE JUST UPGRADEDDDDD!