How to create Ultimate Excel Gantt Chart for Project Management (with Smart Dependency Engine)

How to create Ultimate Excel Gantt Chart for Project Management (with Smart Dependency Engine)

This Excel tutorial teaches you how to create the ultimate Excel Gantt Chart Template with many advanced and incredibly powerful features making this a professional project planning and management tool.

This template introduces a novel and intuitive way of creating and managing fully dynamic project schedules in Excel with items depending on each other based on the four common dependency connection types: Finish-to-Start (FS), Start-to-Start (SS), Start-to-Finish (SF), or Finish-to-Finish (FF). Furthermore, the template includes a smart Project Role and Team Management system, a fully automated Colouring Engine with four different color modes, and many more amazing features.

This tutorial covers every single step of the template creation and is an amazing training for your set of advanced Excel skills.

Tutorial Outline
------------------------
0:00 Demo
8:52 Initial Design Setup
28:52 Basic Item Details
34:36 Project Role and Team Management
46:50 Manual Planning with Independent Start or End Dates
1:09:52 Dynamic Planning with Dependency Engine
1:48:33 Dynamic Project Timespan
1:56:48 Auto-Coloring Engine with 4 Automated Color Modes
2:33:40 Base vs Plan Comparison
2:46:27 Progress Tracking
3:02:59 Dynamic and Static Date Highlighting
3:09:45 Scroll Buttons
3:12:26 How to Manage and Filter Items

Resources
-----------------
Set of Unicode Icons: ⚠, ◆, ⚑, ↖, ↙
Color Palette: F0E936, 40D492, F9B710, FE1684, 9237BC, 4633F2, 3285F3, 1DD7F3

Social Media
---------------------

Other Channels
------------------------
As this tutorial covers a very advanced Gantt Chart approach, here I have a list of channels that were the first popular on YouTube to explain the basic concept of cell-based Excel Gantt Charts in case you are looking for an easy start into the topic:

NOTE: This video has been produced for educational purposes as I want to have my community benefit from freely accessible training material. You are free to use the knowledge provided for your own personal or business-related projects. However, the reproduction, copying, and general reuse of the video or republishing of the creative process demonstrated is not permitted. Furthermore, the unauthorized redistribution of the final template or any sort of derivate of the template (for commercial gain or without a proper license) is not permitted. A violation thereof is considered copyright infringement and is subject to legal actions.

#excel #gantt #chart

[dimasanggriawan]

This guy has mad excel skill and decided to share his knowledge. What a hero.

[Hafensaenger]

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

[yurystepanov]

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!

[lomondscot]

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.

[richardhainsworth]

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.

[erinkesterson]

This is by far the BEST tutorial I have followed on YouTube. 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!!

[sandravukovic]

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 ;-)

[hallowelt]

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!!

[frodo]

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!

[stevegarwood]

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.

[harryaliasg]

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

[samdevlin]

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

[justlease]

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!

[MariMariou]

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!

[jakubhajek]

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.

[aakashbhadoria]

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.

[leewilliams]

I've been waiting for this template for 30 years. WOW

[jamiewalker]

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.

[onequietnight]

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.

[garyosborne-]

Absolutely fantastic, took me sometime, mainly due to not using Excel much over the past 4 yrs but I now have a perfectly functioning tool to manage my up & coming conversion project. As most people have mentioned previously, the ability to show holidays and annual leave would enhance this product greatly.👌👍