Make a Gantt Chart in Excel - Part 3: Dynamic Display

[@markmeng433]

Superb! Thanks for sharing/teaching! 👍👍😊😊

[@ratovononyjamesharisson183]

Wow, wonderful as work, I've never seen. Quick and full of knowledge. Thank you.

[@indradevg3030]

Just amazing how you have included so much in so less time without loosing track and delivering more than expected. Bravo!!! Wish had teachers like you. Keep up the great work

[@sujungpark823]

Thank you it is very useful!

[@Kenanislife]

i'm actually fell in love with your gantt chart tutorials. I never though Excel that way especially to create an advance and dynamic gantt chart with such detailed features and options. You are great.

[@David199701]

I'm feeling the same

[@cosmicdetective8052]

Really thankful to you .... awesome videos

[@sajadbashar-doust8120]

Great teacher, You are out standing, amazing. Thank you very so much ♡♡♡♡♡♡♡♡♡♡♡♡♡♡♡ :'D

[@jimmyneyhart]

Your work is phenomenal! I watched pt. 1&2 the other day and was SO bummed that pt. 3 wasn't available yet...until today! I thought I was an expert-level user of Excel...until I saw what you can do! Great content, great teacher! Keep up the great work!

[@vertex42]

Thank you, Jimmy!

[@peterjames8046]

This is a great, I like your work

[@pirata13]

MOOOORE....we need mooore tutorial :) loved it! followed it step by step. it's like a drug :)

[@vertex42]

There is still Part 4 that you could watch. :-)

[@bennyoh7735]

Absolutely Brilliant & Love Your Speed !!!

[@843292]

These videos are truly terrific! Thank you for the time and effort it took to prepare them!

[@sena9650]

great! thank you so much.it's very helpful.

[@DP-xz8xr]

That is bloody awesome - I've done some similar but your solution is so elegant and appears so simple!! Top work!

[@rosyvargas1717]

your work is absolutely awesome thank you!

[@raulalejandrogarciaboldt2465]

outstanding... i have a lot to learn

[@larryn9031]

You are a rockstar dude! This is phenomenal. Thank you!!!

[@naresh2039]

Thank you, Sir, for providing such great learning In the display section. Could you please guide on how to show data on an hourly basis, similar to the daily and monthly formats you demonstrated?

[@halilenesdaghan4385]

Perfect!

[@laurenashley7776]

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

[@Elysium2411]

GENIUS!!!

[@dineshprasadgupta4625]

Great. Very helpful.

[@wombatbreath]

Thank you (now subscribed). Excellent pacing, great level of detail, precise instruction, and really useful functionality being described.

[@HockeytownNate]

This is amazing work!! Thank you so much!

[@xandrapendergrass8984]

I have spent 2 days on this, I've had so much fun learning how to do this! Now I get to 7:31 of this video and get the and the scroll bar now I have to click it 30 times before the display changes months...??? . And this is the part I REALLY wanted to get right. Has anyone else figured this out? Update: I figured it out. You have to copy the quarterly formula, there is something different the second time he does it. It fixes the monthly issue! YAY!

[@caronte4564]

superior

[@rdyjur]

These are great videos man thank you for this. My only complaint is that my form control scroll button scrolls all the way left or right with a single click, as if the mouse button is stuck, as long as it is kept hovering over the button. Apparently it's an excel bug and it happens when it takes sufficiently long enough to recalculate everything, as it does in my 1.5 year gantt. someone posted the bug in stack overflow in 2010 and there is still no solution lol. Other than that, amazing content man, so clear and concise. Edit: when I open this spreadsheet on a much newer and better PC, the scroll bar buttons work perfect. Something to do with the extended calculation time causes that bug.

[@vertex42]

NOTES / CORRECTION / Q&A COLOR-CODING: I know that I said at the end of Part 2 that the next video would show how to color-code the chart. That will be Part 4, instead. The reason: Color coding will involve creating a bunch of new conditional formatting (CF) rules, and I wanted to modify the rules to work with the dynamic display before I create all of those new rules. 5:26 Q: Why do I press CTRL+D after inserting a row? A: To copy the formulas down (including the hidden columns). 6:30 Note - Changing a named range via the Name Manager will automatically change the reference in cells that are already referencing that name! 6:40 Using the Name Box to create a name will use the "Workbook" scope for the name (which makes the named range something you can reference in other worksheets). If you want the name to be only used within the current worksheet, create the named range using the Name Manager. 8:42 Q: Why does using arrow keys mess up the formula? A: Using arrows keys when editing the CF formula will insert a cell reference. Try it and you'll see what I mean. TIP: Press F2 prior to using arrow keys within the dialog box when editing the formula. 9:03 Q: Why do you use CHOOSE instead of math to calculate the quarter number? A: Because the CHOOSE function is fun. Here's another way to do it: ROUNDUP(MONTH(K6)/3,0) 9:18 Q: Why use the WEEKNUM function with the 21 option instead of the ISOWEEKNUM function? A: WEEKNUM works in older versions of Excel. By the way, if you wanted to display the week # that represents the week since the beginning of the project, you can use ( COLUMN()-COLUMN($K$6)+display_period ) instead of ( WEEKNUM(K6,21) )

[@furaosentu]

I believe you, as the uploader, should have the option of pinning this comment to the top, so nobody misses it. I'll delete this comment when it's pinned.

[@sgngbnnoverload]

Good day, can you put also if its possible to put an arrow dependents/precedents tutorial. Thanks

[@TheFuture36520]

Genius

[@TheKing-rq8ey]

You are doing amazing work Can we also see actual vs planned? Very important to show that late starts results in late delivery. It would be fantastic if we can show in the Gantt the difference You are a STAR 😊😊😊

[@vertex42]

Thanks for the suggestion. Here is a template that shows actual vs. planned: www.vertex42.com/ExcelTemplates/project-planner-template.html You'll see that it uses two sets of start/end columns, and another conditional formatting rule to display the plan cross-hatched. So, you essentially already have what you need in terms of how to set up the conditional formatting rule. You just need another set of columns.

[@lcg_wormtrader83]

I am impressed with what was done in this spreadsheet, without using VBA! I am looking forward to the outcome of this class just as I have been waiting for "Avengers: Infinity War". Learning is exciting!

[@vertex42]

Wow! Comparison to Infinity War ... maybe difficult to live up to. 😀

[@marioochoa1458]

Awesome tutorials!! Your Kung Fu is Strong!!

[@jenskautler]

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

[@doraocchipinti6103]

I would like to see this too

[@coolmonkey619]

Why is this video unlisted. This is sooo good . Thank you

[@amoslu1579]

amazing!!!

[@LeFreak]

Thanks for this tutorial - really great. The text with tasks e.g. Task 1.1 in the bar chart is not using the indenting of row A - any ideas? the text is just aligned to the y-axis. This cannot be seen in your example because all tasks have the same name length

[@LongTran-uo6iq]

Your videos are amazing!! I never thought Excel could be this beautiful, advance and dynamic like this before! Thank you for the time and effort to make everything! P/s: One more thing, is it able to set to display hours in a day?? Thanks! :D

[@paulratterich5128]

thanks for these great videos, it's fun to join in and try it out yourself. one question about the first video - part1, "to make the chart area dynamic, 2:19": when you change the project start, the days in the calendar slide down in the video, but in my table everything moves to the right, what could be the reason for that? thank you!

[@vertex42]

I don't know. Hard to diagnose without more detail.

[@daniellenel7118]

My progress conditional formatting for the progress is messed up for the weekly, monthly and quarterly view. Is there another link that I could possibly revert to to fix this issue?

[@vertex42]

The files for these later parts to the video series are available with the purchase of Gantt Chart Pro (link in description). Or you could set it up so that it only shows the progress for the Daily view (because it's only very accurate in the daily view).

[@imankustaman637]

I finally found the one who creates the Gantt chart on the Microsoft excel template, and this template already been my favorite and my default gantt chart on my day to day job and did a lot of modification also to meet with the project. Thank you very2 much. I'm interest to have the pro version seem it has a lot of feature that I can use for and it is also no password and open for modification, isn't?

[@vertex42]

The pro version can be modified, limited by the license agreement of course.

[@georgekollias3442]

How can I write some text in the cells that will be linked to a certain date as I move the dates with the control?

[@vertex42]

You can't really do that with this method (without VBA). You could use the Stacked Bar Chart and use data labels to show text near the bars of the chart.

[@damiankraska4766]

Fantastic video! When we can expect part 4?

[@vertex42]

I've got the general outline figured out and I'm working on detailing the steps. I'd like to get it done by mid-March.

[@DomVellaUTube]

Dependencies? This is the best series by far on the topic of gantt charts. Not just be the way spreadsheets can represent the data, but also on the way we manage project data. I would like to see how you guys handle dependencies. Would you use a separate sheet for that data? I would think calculating start would require a new field 'Dep Work Days' (+ or -) from dependent end or from project start if no dependent provided. May need clever recalc. of start if changing a task dependent so that the progress bar doesn't move and in turn change its following tasks. And, would you create lines or use some clever Conditional Format stuff again (although I guess it's not required if we only stick to one rule, dependent end to task start, many-to-one)? Maybe an arrow at end of progress bar for relied on task, and an arrow before a task progress bar if dependent on other tasks. Maybe the front arrow can go Green if dependents are complete. Maybe many to one is too much, maybe one to one is enough although I think it would be good to build task groups, the group totally dependent on child min. start - max. end dates. Ok, I'm thinking I've asked too much, but still, I saw no harm in asking. :) I'm inclined to try it sometime, time permitting, arrrgh.

[@vertex42]

The Pro version has been evolving over time, due in large part because of different ways of defining dependencies and task durations. The following YT video shows how the newest version works, which may answer some of your questions: kzbin.info/www/bejne/h2m0hoaaYq6Yn5o www.vertex42.com/blog/business/pm/gantt-charts-made-easy.html As for this video series ... I haven't decided yet what technique I'm planning to demonstrate for dependencies, except that I will likely show the WBS lookup method (this is the Predecessors method that is built into the Pro version), because it handles the most common type of dependency (starting a task after another is complete). On the Simple Gantt Chart and Construction Schedules pages, I've written about some formulas that can be used for manually creating other types of dependencies: www.vertex42.com/ExcelTemplates/simple-gantt-chart.html www.vertex42.com/ExcelTemplates/construction-schedule.html Although VBA could be used to do nearly anything, such as drawing lines or arrows for dependencies, I prefer to think of custom Excel gantt charts as the keep-it-simple tool, and if you really want a lot of these other features, it may be better to use dedicated PM software. Without using VBA, I think the most practical way I've found to note dependencies is to add a WBS column and add another column that you can use to list the WBS of the dependent task(s).

[@DomVellaUTube]

@@vertex42 Brilliant

[@MultiSo3]

These are phenomenal tutorials!! Would it be possible to add dependent tasks? Could you show us that? Thank you very much!!

[@vertex42]

You can create dependencies using formulas. Some ways of doing that were explained in Part 2.

[@shaikdada9410]

Do you have any video where RAG status update dynamically with colour based on the testing percentage completed. Example 50 to 90 as green and 0 to 40 as Amber

[@rianaotto7029]

Love your videos how do I add a delay start (If the customer is delaying my project)

[@vertex42]

Depends on whether you want to show the original dates or not. If you just want to change the start date, you can of course do that. See this blog post regarding showing actual vs plan: www.vertex42.com/blog/help/gantt-chart-help/actual-dates-vs-planned-dates-in-a-gantt-chart.html

[@marinater-ananyan8574]

Hello, first of all thanks for the video, it is awesome. Question: My project start is 1 June 2020 till 31 December 2020.When I add quarterly view display, my timelime shows till January 2074:-)))) how can I make it expand till 2023 only.

[@tahasaid7997]

your explain is awesome and very professional , excuse me i really want to know how to compare the planned schedule with the actual work done in the same sheet and distinguish it by colors and link it to the progress

[@vertex42]

See Part 4, and you can get the Pro version if you want to show planned vs. actual.

[@tahasaid7997]

@@vertex42 ok i will buy the pro version .. i need the link for the pro excel sheet that have all all the benefits of part 4 and also actual vs. planned

[@audayal-salihi617]

Inserting rows above doesn't reflect it to the chart and it's not picking the data source from the table, any thought why?

[@vertex42]

After inserting a row you'd need to copy formulas down, and make sure you don't add rows above or below the table. Inserting rows in the middle of a range stretches the reference, but not if you add rows above or below the referenced range.

[@lauramatia448]

Thank you for the video! It was great and to the point! Is it possible to show the holidays when you are in the weekly or monthly view?

[@vertex42]

Not with this particular design, because each column represents an entire week or month and you can't partially shade a cell with conditional formatting.

[@siljaesren4417]

There is "Display Period" function which refers to the start of the Project date. Is it possible to add "Display Week number" -search bar, which would refer to the calendar's week numbers? In a project a calendar week is most often referred to. It would be handy to scroll the Gantt chart based on calendar week numbers as well. I've already learned so much, thank you for your great work!

[@vertex42]

Could probably be done, but would require a custom formula, and would be more complicated if it spanned multiple years. I don't have an answer.

[@sashyjanet8904]

These videos are awesome! I'm such an excel beginner and your entire website has helped me immensely!! so THANK YOU! I'm in the process of creating this chart with a few modifications (no weekdays/quarter/month name displayed, skipped the progress, and weekend parts). But my daily view messed up when I was inputting the formulas for the different display views, which messed up the chart bars. Any idea where I might've went wrong?

[@vertex42]

Sorry, I don't, but you can download the templates instead.

[@zuk_kay]

I want part 4

[@nagaganas7927]

Is it possible to create an S curve with this? Showing progress and baseline?

[@jameswilde555]

Hi, when i try to create a stacked bar chart, it says "Some chart types cannot be combined with other chart types. Select a different chart type." - Any ideas?

[@BrettThomason]

Can you provide the weekly display function for the date box? the video only contains monthly quarterly and daily.

[@vertex42]

If it's weekly, add 7 days to the previous cell.

[@deluxembourg]

Thanks for these excellent videos! Is there any way to format individual labels among the vertical task labels in the bar chart (indenting, bolding, etc)? I guess your example looks clean simply because your "Phase Titles" are longer than your task titles?

[@vertex42]

You can use all of the formatting capabilities of Excel, including indenting, bolding, colors, etc.

[@deluxembourg]

@@vertex42 Would you be able to explain how? I've googled plenty but can't find a solution. If I click the task label box I can change the font/color/alignment of all labels in the box, but not individual labels. I've looked in the Design, Format and "Format Axis"-options menus as well but can't find anything that works. I'm trying to have certain labels in the bar chart stand out (bold and indented) to better visualize milestones.

[@gjovannj69]

Great tutorial, thank you! I have a question though: is there a way to make the "bounds" values dynamic in the Axis , so when we change the start date of a project Minimum and Maximum will change accordingly? Thanks.

[@vertex42]

Do you mean in the chart, or with respect to all of the individual tasks? If you mean the tasks, then you can use formulas to link the tasks. A few examples of that were mentioned in the previous video.

[@dominikwei5668]

@@vertex42 Let´s say, he meant the chart. The minimum bound you changed at 2:23, can you make this one dynamic in correlation with the project start date? Also for the maximum bound as the last date in the "END" column.

[@ROGERHUANGLDS]

First of all, thank you for sharing this video with us. It's super helpful. I do have a question regarding how to change the chart title to the Project title. I use a MacBook Pro laptop to generate this Gantt chart. However, when I selected the chart title and clicked on the equal button, the name of the chart title became =Chart Title. It would be much appreciated if you could show me how to change the chart title to the Project title on a MacBook Pro laptop. Thank you for your help in advance.

[@vertex42]

Sorry, but I don't use a Mac, so I'm not sure what the process is on the Mac.

[@mkmotivation6323]

Great work! Thanks for uploading this video. Would this work with google sheets also?

[@vertex42]

Most of this can be done in Google Sheets, although the chart editing interface is extremely different. You can't follow these videos exactly step by step in Google Sheets, especially since GS doesn't have any relative named ranges. But, I do have a Gantt Chart for Google Sheets that does almost everything that the Excel version does.

[@rhaffysaudbaluyut2322]

This is great sir. Is it possible to put the task name into the gantt chart??

[@vertex42]

With VBA, you could do that (or manually), but every time you change the date range, you'd have to move/hide/delete/show the task names over the new locations of the bars. So, generally not something worth doing when creating a gantt chart like this. However, you can display the task names via the stacked bar chart method.

[@jessicaalvarezlampson4185]

I did alot of this work yesterday and it was all working correctly. However, i open the file today and none of my weekly, monthly, quarterly, drop down options on my gantt when i use them but formulas are all there. Are you familiar this issue and know a trick to correct without redoing it?

[@vertex42]

If the file was saved while it was working, I don't know what might have happened. If the file has not become corrupted somehow, I'm not sure what might cause it to stop working just from reopening. Sorry.

[@jessicaalvarezlampson4185]

@@vertex42 I figured out it was my excel program acting up. I was able to restore the work i had done and the gantt was working accordingly. Thanks for the msg.

[@jessicaalvarezlampson4185]

Your tutorial was super helpful!!

[@HumanEye91]

Hi, my scroll bar chart for for the "Show weekend" portion doesn't have the Arrow head < and >, how to make it show?

[@vertex42]

I'm not sure ... it seems like this is a weird Excel bug, not sure why the arrows don't show up for some people.

[@timothynwanwene4378]

Please part 4. 1. Show interdependence relationship in tasks 2. More Colors 3. Texts display on hover over tasks.

[@vertex42]

See the link in description for part 4: www.vertex42.com/ExcelTips/how-to-make-a-gantt-chart-in-excel.html

[@goherjee1]

I am on 3rd Video for Gannt Chart, my month display function is not working, I can share my file if required to check what is wrong, I have tried the described formulas , every thing was working till this stage. Any one can help please

[@ΣυμεώνΠαπαδόπουλος-λ4ο]

Top level. You are great. One question. when I change the period to any field other than the daily one it doesn't work the conditional formatting although I have increased the days (everything is purple). Am I doing something wrong? Thank you in advance!

[@vertex42]

If it's not working the same way it works in the video, then you may have done something wrong. Check formulas and references carefully for $ in the correct places, commas, the right number of parentheses, etc. If you can't figure out what's wrong, then you can download the templates instead.

[@ΣυμεώνΠαπαδόπουλος-λ4ο]

Thank you very much for your response.

[@paulstory8474]

I've done my best to follow step by step in recreation of this. However, at 5:26 when I hit Control D to copy down Task 2.3, the chart does not automatically update. I can't figure out my error. Any advice on the part I failed?

[@vertex42]

You could try another method of copying down.

[@samjames1]

This is great but it really slowed down my excel and I can't understand why? The CPU keeps jumping to 100% and I've done only what was on the tutorial :(

[@vertex42]

Hmmmm ... these are all pretty reasonable formulas, and there aren't even very many conditional formatting rules. Typically you'd have to have a LOT more formulas and CF rules before you start to see a slow down. When diagnosing issues in Excel that may be causing high CPU usage, I would typically recommend making one change at a time to figure out what the root cause is. We haven't used any volatile functions, but CF rules are inherently volatile, meaning that they are refreshed every time the display is changed. So, my guess at the moment is that your computer/Excel is having a hard time processing conditional formatting rules. So, if you can identify which one(s) are causing the issue, then maybe you'll have to remove those rules and figure out another approach. I don't know a lot about hardware issues, but maybe the issue has to do with a graphics card/processor, or maybe all you need to do is reboot to get Excel out of its funk. If the spreadsheet after Part 2 worked fine, then I'm not sure why the changes we've made in Part 3 would make Excel have a harder time.

[@samjames1]

@@vertex42 thanks, it started slowing in section 2. I'll back work what we did and let you know if it improves.

[@vivlee3347]

How does one change the timescale to show just the first and last of the month? Help! (Please)

[@vertex42]

I suppose you could hide all of the columns in between the first and last.

[@ege82]

From the 9:00 minute of the video, I wrote the same formula you specified to extract the quarter, month and week information in the relevant lines; but weekly values are not coming. When I select Weekly, only J appears in cells. I kindly request your support. Thank you. The formula is as follows; =IF(display="quarterly";"Q"&CHOOSE(MONTH(K6);1;1;1;2;2;2;3;3;3;4;4;4);IF(display="monthly";LEFT(TEXT(K6;"aaa");1);IF(display="weekly";WEEKNUM(K6;21);LEFT(TEXT(K6;"ggg");1))))

[@vertex42]

I'm not familiar with what "aaa" or "ggg" would mean in your formula, or why you are using semicolons instead of commas. This video was not designed for OpenOffice.

[@williampope775]

When I insert the bar chart, it only shows one single task in the vertical axis.. not sure how to fix this

[@Akshay_9932]

Did you get the solution?

[@laurensb7136]

Will part 4, color-coding also include how to set it up, so that each individual can have another color in the time-line, instead of the standard purple color that is used now?

[@vertex42]

Yes. One of the things I will show is how to create a table in the settings for assigning colors to individuals so that you can use a lookup formula to assign the color based on the name in the "Lead" column. This is something explained in the Help worksheet of the Pro version, by the way.

[@chulakapitigala5147]

Can u give a method to change the tasks like days months and weeks.. I'll be more useful Thanks you for all videos

[@vertex42]

Hmmmm ... very interesting request. Choosing the End Date by entering the duration as days, weeks, or months could involve making a column with a drop-down for selecting the interval, and then the Work Days column would become a generic "duration" instead. Then, the End Date column could use a formula like =IF(interval="caldays",task_start+duration-1,IF(interval="workdays",existingformula,IF(interval="weeks",task_start+duration*7-1,IF(interval="months",EDATE(task_start,duration)))). I just tried this out, and it's very cool! It's different than what I've done in the Pro version (which lets you enter calendar days or work days), but I like it because it lets you choose weeks and months as well.

[@chulakapitigala5147]

@@vertex42 good idea 😍😍 I'll try.. thanks buddy

[@amitfore]

why your web site is not opening in my laptop????? Is there any technical issue???????

[@meipengchew5788]

Where is the reference of task_start and task_end?

[@hemadrilekkala9220]

Can we get the link to download this file?

[@vertex42]

The files for these later versions are available with the Pro version. See the link in the description.

[@otvickers7074]

Still trying how to change the colored cell into a letter.

[@vertex42]

Would need text (even if it's blank text like ="") in every cell within the chart area. Then you could use custom number formatting to display characters instead of changing the color.

[@satsharma585]

how to make it just monthly ?

[@vertex42]

Choose the monthly option. Use the formulas associated with that option.

[@satsharma585]

Vertex42 No No... That I know.... the problem is I did monthly according to your next Gantt video but I dont want to see the dates which are aligned at 90 degrees... just want month instead of that.... how to do it..

[@jacquesvandebeld7590]

Thanks a lot for this video. I have a minor thing with copying the formula's down, when inserting a new row. this doesn't seem to work in my sheet. i followed the vid exactly but i can't make that happen :(

[@vertex42]

If the CTRL+D shortcut doesn't work, then you can select the previous row and drag the fill handle down to copy the row.

[@jacquesvandebeld7590]

never mind XD. got it to work. thanks for your great vid's