How to Make a Gantt Chart in Excel with Progress Bars (actual, planned & percentage completed)

[@LeilaGharani]

Grab the file I used in the video from here 👉 pages.xelplus.com/gantt-chart-full-file

[@msrana4469]

Thanks for pivot table . Is there any table or chart regarding planing .suppose we have 100 acitvities and all activities time shedule is different .supose one activity schedule is after 35 days .so is there any provision excel by which we can get its alarm 5 days earliar and marked with different color.

[@Vade1313]

You are an Excel Goddess!!!!

[@Jedli7]

Can you have a different planned/actual colored bar?

[@Jedli7]

I fixed it with VBA, but if there is another way I would still like to know

[@עדיעקיבא-ד4ו]

Hi, sorry but I find this method clumsy. Specifically the manual aligning of the titles and chart. I realize that this is an old video, and your skills and tricks probably imroved, but I think it might be worth saying. I would use a stacked bar chart: The tasks are the categories, series 1 is the start date, series 2 is end date. Then I would format series 1 to be tranpatent with no outline.

[@nicadi2005]

It is very unusual to use a Gantt chart to track and report progress, as it is NOT the right tool for the job. Also, you seem to be equating time expenditure with the progress made; they are NOT necessarily the same... For example, it is quite possible (and it happens annoyingly often) to use up the hours allocated to a given task, yet not achieve completion.

[@nasasiradaniel8714]

Hey Leila.. Am a great fun n am becoming Excel pro coz u. I want to add completed percentage data label onto the garnet. My boss wants both colors.( completed n uncompleted) and percentage data labels onto the garnet chat already made

[@rudiansyahsyah9738]

classroom symbol commonecation software 🏛🌍🌎🌏 year in year 2022 / 2023 🏪🏫🏬💒🏩🏨🏧🏦🏥🏤🏣

[@sridharpv9431]

Leila, excellent narration and reference material. I am struck, while inserting the completion data. I am getting the completion information for plan data. Is this fine to show up. Thanks

[@abdanomer]

Great as usual. Hope you are well. As a project manager I usually need to show the difference from actual and plan! I mean i should see them both at the same time to figure out if i within , behind or ahead from plan! This could be done by adding a series to the chart as I think. In addition what about using the conditional formatting to cells instead of chart ? What are the advantages and disadvantages? ( space or numbers of cells , colors for formatting, dynamic issues, ... etc) Keep up the good work.

[@LeilaGharani]

Hi Abdelrahman, Thanks for your input. To get the graphs right beside or on top of each other, I would probably duplicate it - i.e. create one just for Actual - then duplicate and replace series with the plan one. To get them in one graph, I'd probably put them on top of each other - by taking away the fill color and possibly making the plan series more transparent so it doesn't distract too much from actual. Generally, I prefer to work with charts when I can - this way I have an object that sits on top of the cells - with conditional formatting cells method, I have to make sure the cell widths always remain the same, otherwise the visualization would be impacted....

[@junnelduluguin5324]

Hi Leila, Could we put a formula in column I (percentage completion)? If so, what would that be?

[@TheSsteiner]

Great chart! I like to add another series to the chart with a vertical error bar and an x value of =today() to help show how we are tracking to our plan each day. Excellent tutorial!

[@Soederlund]

I'm sorry to say, but your IF formulas doesn't work at all for me :( I have to consult the help section to pass through that part.

[@Annichya]

The markers for planned dates are appearing far from the plan and progress bars , instead of appearing right beneath. How do I fix this?

[@Tasice08]

Excellent tutorial video, that's exactly what was i after

[@gerancostibolo5568]

What if I only want specific dates on each task to be shown on the chart, how am I gonna do it? Help. :(

[@Masumaz6]

Good night. Will you please help me to make a production plan using grant chart?

[@prashanth_nayak]

I tried adding a new task to the list, however that task is not showing in the scatter plot. I tried to reselect the data source as well.

[@nationalfoodauthorityagsd-9512]

Is it possible to put some vertical lines there?? just for visual guide.

[@75bisc]

Can you please tell me how do I get the error bar as in my chart the error bar option is disabled.

[@corporatetutor9089]

Hi, is there any way to change timescale in Gantt Chart in Excel??

[@ImranAhmed-tt5zz]

Thanks and it's awesome.. Can I show a actual abd planned in same chart

[@Sal_A]

Amazing! The Date formatting in charts is something I always had trouble with but you have helped unravel this mystery for me by not changing the serial numbers to actual dates...Very cool!

[@LeilaGharani]

Great. Glad to hear that.

[@h4niali]

Its good work , but its better if you can linked and dependant tasks

[@alanfogarty1791]

Hi Leila my name is Alan! Thank you very much for the videos on Gantt charts with Excel. Nothing to difficult but the way you put yourself across was very good for me... simple and to the point, softly and no bravado! I have one question though if you could please help me? Where you changed the Y axis task name to include the (**WD) formulae to show number of the work days for each task in the description, how can I edit the formulae (syntax) to display only the (**WD) in RED BOLD text and, the rest of the task description in BLACK text? Sorry, I'm sure its very simple but I am a newbie! Thank you!

[@flowcharttocodeconverter936]

Hi, great chart ! Can it predict end date using % done job?

[@mehmetkaryagar898]

Hi Leila. You have around ten activities in your schedule. I want around a 100. And when i try to create the chart, only half of the Y axis activities are being shown. I made sure that "specify interval unit" is 1 under axis options. Could you please help me find a way around it?

[@dagwould]

I came to this from the more recent of Leila's gantt charts, all good instruction. I was just wondering about dependencies. That is, an activity starting at the completion of a predecessor, or even multiple predecessors. For just one predecessor, a simple calculation would do it, but if there is more than one? I guess a 'max' function of the identified predecessors. I'll get onto it and see what I can do.

[@akashmohite6231]

Thanks for such awesome video saved me from my Boss ;but getting error while displaying graphs for actual & plan it is showing same graph ;for simple chart it worked

[@micheldemers4029]

How to represent work effort instead of workday between the start and end dates, knowing that theses 2 dates are fixed and it's the work effort that is variable ? Example: I need 10 days-persons efforts to realize a task in 5 days elapsed time (end date - start). Thanks in advance.

[@deepeshgole2848]

Please make video on ... how to make gantt chart without use of chart. Thanks for sharing your awesome knowledge. my favorite mentor is Leila .

[@whatifsolutionsarefasterth2358]

You should create Gant chart in office 365

[@HockeyPurist]

Outstanding video. You are my go to resource on KZbin. Thank You for what you do!

[@LeilaGharani]

Great to hear!

[@berniemarcham7392]

I love the video. Question, how would you go about adding the percentage inside the bar chart? I tried adding the labels but if is pulling the # of days.

[@magedmousa5511]

excellent tutorial, How can I display Start & End dates on the tasks bar (like value)

[@anilkumar-pi6ep]

Great explanation.. If i need to add dependencies which points through arrows between tasks? Will that be possible to create in excel?

[@catherinehanssens-passeri9272]

What if I add tasks at the bottom, how do I update the chart? I used the Select Data button and selected my new range but it did not update my chart.

[@guerrerosolitario67]

Not fully. What about planned bars ???

[@rudiansyahsyah9738]

classroom the fact in quality utility software year 2022 / 2023 🏛🌍🌎🌏

[@valerianverdy4546]

I'm using a TABLE format with header, i want to make headers with dynamic dates - can I do that?

[@MyAvi1991]

What will happen if my task complete on same day

[@nbs8989]

Super. Thank you. If we want to show both plan and actual together on the chart, can you please show that?

[@patrickkelly4845]

Leila I have a question is there a way to stop the green bar if you have completed the task early?

[@chennasathya]

Brilliant. Thank you Leila. I will use your idea to create one suitable for my work.

[@dodgecarlincila879]

Hi Leila your videos are really awesome. It really helps me learn new things and polish the old ones. Regarding gantts, i have this scenario though, that instead of tracking days, im tracking sales per person on a time range. Is it possible to do it on gantts? Thanks! and More power.

[@DINESHMaharjan-pe8mt]

This is another excellent trick to draw a bar chart. Is it possible to link with one task to another as tasks are inter linked?

[@LarsIngeHolen]

Can you make it dynaminc to be able to select month and year? Maybe also be able to show different views, like daily, weekly and monthly?

[@mahdin.2650]

Hi Leila. Great video. Perfect speed, video and sound quality. The Task titles in the chart are out of alignment. How can I right align them?

[@BarneyHorvath]

Thank you Leila! Is there an option to set the minimum and maximum values automatically, e.g. 30 days before the earliest date, and 30 days after the latest date? Can I reference these values when formatting the Axis Bounds Min/Max?

[@1988Ssk]

Hello Leila :) I have a querry regarding Gantt Chart. How can i display time taken by a specific task ( if its achieved within the planned time it should show 1 color & if it exceeds the planned time it should flag in red color.) is there any specific formula for showing this..? Also can i co relate the % of task completion with above task.

[@ginohendriks6803]

Thanks for the video. Is there a way to add dependencies in the gantt chart through arrows?

[@charliecaballero1977]

This a great.....but can you show a bar chart showing actual dates start earlier.

[@prateekchitambare9878]

Thanks a lot for the video very useful ! What if we have multiple countries in drop down instead of "plan" & "actual"? The if statement in the prep data won't work. How to set up the similar thing for multiple countries?

[@Shellboxx]

Great video! Really show what you did and how to make it yourself, however how would I change this to work by weeks instead of dates?

[@nationalfoodauthorityagsd-9512]

WOOOOOOOOW THAT WAS F*CKING AWESOME!!!!!!!!!!!!!!!!!!!!!! GOD BLESS YOU GIRL MAY GOD POUR YOU A MILLION BLESSING THIS WAS REALLY AWESOME THANK YOUUUU!!!!! (AND THANK YOU TO MICROSOFT TOO THE BEST THING THAT EXISTED HEHE)

[@Shri32]

Hi Leila, Thank you vrey much for the informative video, It helped a lot with office work. In my work, I have to sometimes hold some activities for some time and then again start after few days, so do you have something that can help me show it on gantt chart?

[@jayrichzhanra4988]

Definitely an Excel Goddess!!!!

[@bharadhanenenu]

Thank you very much.... that's a quick release of dynamic Gantt video as as per my request..... really helpful.... thanks again..... I also requested for dynamic map tutorial. it would be really great if you can upload dynamic map tutorial too..

[@LeilaGharani]

You're welcome. Sure. Have already added it to my list - should be in the next weeks...

[@bharadhanenenu]

thank you very much......

[@Adam_K_W]

You're the best! I'd like to see your take on using a "Named Formula" within the Name Manager, where you create a new named item, that is just a formula so you can use that name as a shortcut to nest that formula within another. In the Microsoft Calendar Template, this is used as "JanSun1", "FebSun1, etc...

[@LeilaGharani]

Many thanks for the feedback Adam. I'll think about it.

[@ramanagoteti2574]

very nice explanation Leila, I liked very much. Can we also create reports with Excel?

[@edmundtumaca2334]

Great Mentor, mam Leila Gharani. God bless you always.

[@srini154]

Thank you so much and this is very helpful

[@dsetiya305]

Wow, this is Excellent tutorial. Thank You

[@Bijesh369]

Thank you for sharing your knowledge. Really appreciate. I have enrolled for our course also in Udemy. Can you explain, how to show the break in the schedule.. for example the work is started and then there was a gap due to some issues and it resumed after a week. How to show the split like they show in MS project.

[@Jade3175]

Wow!! Thank you. :) any way you can show us how to create a collapsed view of the planned durations + percent complete? I’m trying to show a row for each project (broken out by dependent phases/task) and which allows me to include multiple projects, 1 for each row. Dashboard format...you’re the best!

[@thequietroom3991]

Hi Leila, great tutorial, do you have any videos for adding dependency lines at all?

[@outingshout9246]

is the video part of any of your courses in udemy?

[@6thOCT_BKK]

Fully thank you for your video training that it is super useful to my jobs

[@mikeberoth9074]

Bravo! You are a guru. Thanks for presenting in such an understandable way.

[@KienNguyen-ks3vo]

Really really helpful. Thank you very much. I have subscribed, start watching other tutorials and look forward to more interesting ones.

[@LeilaGharani]

Welcome aboard!

[@renasuby3262]

Im not finding the error bars

[@wingame52]

Hi Leila. Tremendously enjoy your excel series. In an excel Gantt chart, is there way to display the current cumulative total percentage of the progress? Thank you for all the spreadsheet tutorials in your channels

[@adeyemikehinde18]

Great presentation! Thank you Leila.

[@amandak1396]

Can it become dynamic by adding a column subtracting =Today() function with the start date or number of working days so that it updates everyday?

[@LeilaGharani]

As long as you update the dates and the data prep table with dynamic formulas, the chart will update automatically as well.

[@Rob.Martin]

Love the video and was able to prepare a gantt chart quickly and easily. One thing is that my projects tend to be in months not days which produced a very long gantt chart. I tried doing this in weeks (ie 0.2 of a week, to 16 weeks for arranging a trial. My gantt chart had lots of gaps in it - any way of re-writing the time scale required for projects that can take up to 12-18 months from conception to completion? Thanks in advance.

[@Annichya]

Yes. Select the date field on the top and go to properties. Under Axis options, go to major units and add the number of days you want your dates to be separated by. For instance, for a weekly view, one would add 7.

[@velmuruganr1491]

Over burn should highlight in red color.. can you suggest ne

[@fancyyu0410]

I saw the plan bars will be changed as actual bars is selected, how can fix these plan bars?

[@susanmullett1091]

Thank you for your videos. It's helping me to explore parts of Excel I wasn't coming across in my job but already I am excited to try out new techniques

[@uvesca]

Thank you, Leila. How can I change the date on the x-axis? E.g. to quarters?

[@alizagalinsky1593]

Hi Leila, When I create my stacked bar chart - the Y axis shows up as numbers - not a the actual steps. What am I doing wrong? Thanks!

[@LeilaGharani]

Hi aliza - try downloading the workbook (link is in the description of the video) and cross-checking with your version.

[@lucasjohnson5871]

Is there a way to make a gantt chart to plan a 12 hr work day in 10 minute increments minus lunch and breaks??

[@LeilaGharani]

by adjusting the data set so it's just restricted to 12 hours - yes.

[@colecoleman1499]

when i insert the chart, it just shows one bar , not multiple , what am i doing wrong ? please help

[@colecoleman1499]

its resolved, i contacted my senior had to place the task and data preparation in the same row

[@LeilaGharani]

Ok, great!

[@ChrisLivingston]

Is it possible (without VBA) to get the bounds of the chart area to be dynamic?

[@LeilaGharani]

We can’t link it to a cell value yet but hopefully soon. Please place your vote here so the request ranks high for microsoft developers: excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/9046207-link-the-min-and-max-values-of-a-chart-axis-to-cel

[@asafnb]

Wow so nice thanks a lot!

[@ishaq79]

Hi, This is my 1st question to ask, I'm making the production plan with Gantt Chart, however, the Bar will be in a cell not on the chart. with data. Could you please advice

[@LeilaGharani]

The Gantt charts I've created so far are all based on charts....to use the cell method, you can use conditional formatting inside the cells. I personally don't have a video on that, but I am sure if you search for it on Google, you will probably find something....

[@carolburns3698]

Thank you for this video on Gantt charts. I'm a grad student and truly appreciate the breakdown in excel that you shared, and user friendly tips you provided.

[@bellemabelmoche7245]

Thanks Leila, really appreciate this. I love it!

[@shijeshvijay]

Very simple explanations, this much more helped for improving my excell skills

[@GeertDelmulle]

Dear Leila, can you integrate both “planned” and “Actual” Gantt charts in one overall chart (that is including the progress bars) just like MS Project does? In that way we wouldn’t need the drop-down menu.

[@LeilaGharani]

That's a good one. You can show it right above or right below the actual bars using error bar technique, scatter plot and secondary axis. It's a good one for a video so I'll add this to my list. I'll let you know once it's out....Thanks for the suggestion.

[@samijudjun303]

thank you very much video tutorials ..... but can not access the link to download please at repair link

[@LeilaGharani]

Thanks for pointing that out. I updated the link for the download. Should work now.

[@dennisroote9145]

Good Evening Leila, Your videos are well thought out, perfectly presented, very insightful. Question: If possible, how can this be modified to show the Critical Path?

[@rambdrchand6354]

I appreciate the way you explained this in this vidio, it was great help though the formulas took long to understand, thanks expect more such vidios

[@hamoonmadady1997]

Hi, thank you for your cool video. How can you compare the plan and actual at the same time then?

[@LeilaGharani]

Very good question. I actually have this one on my list of to-do's. Should be able to get it up in the next weeks.

[@hamoonmadady1997]

Awesome! Thank you Leila! Looking forward to see that.

[@mr_blue8208]

Hi Leila, great tutorial! Many thanks. I'm using Excel 2013. For some odd reason at min 6:51 I wasn't getting the same result, the chart came out odd every time. I've tried several options and eventually the only one that worked for me was to select the "Data Prep." table first and then the "Task" list. Many thanks.

[@LeilaGharani]

You're welcome Ricardo --- hmmm interesting that Excel 2013 didn't pick it up. Glad you found a way :)

[@gauravmahajan9654]

Thank you 😊. This video helped me a lot

[@LeilaGharani]

Glad it helped!

[@sosoboss]

Hi, thank you for the tutorial, it's very helpful. However, I would like to make a Gantt Chart to compare two methods for a project. Do you think it's possible to show for each tasks the time it's took with method 1 and method 2 ?

[@ptichka1371]

Thank you very much for all your videos, Leila! It's been almost 2 weeks now that I can't plot time (not date) into my Gannt chart. Setting min and max bound on horizontal axis won't work for me, as I am setting a dynamic chart range like in your video published on Jul 6, 2017. How can I get my x-axis plot the time correctly on a stacked bar chart?

[@LeilaGharani]

hmmmm...I'm not sure about this..

[@tarek.grisha]

Thank you Leila

[@zerocool200380]

Hi Leila, I've used your Gantt chart and I find it great ! I have a question , can we introduce dependency between different tasks so task 3 can't start before task 1 and 2 have completed. then the next part is how we can do it visualy . I will welcome your suggestions.

[@LeilaGharani]

That's great! .... hmmm... I'll think about this and let you know.

[@CodewithKython]

Thankyou so much..i been looking for this for a long time..could you help me figureout if we can show plan and actual in the same view, with same information as reflecting in actual..i couldn't figure it out..would appreciate an answer to the query please. You got amazing skills in excel

[@LeilaGharani]

Hi Karan, Yes - you can. Create a second graph just to budget, take away the fill color of the plot area and chart area, make the bars much thinner and in another color, delete the x and y axis and then put it on top of the actual gantt chart. Group the two charts together. That's it. I should probably make a video on this...but hope this helps for now.

[@thomasyang1803]

@@LeilaGharani Thx. This is what im looking for