How to build a Timeline Gantt Chart in Google Sheets

แชร์
ฝัง
  • เผยแพร่เมื่อ 1 ต.ค. 2024

ความคิดเห็น • 53

  • @tobylier5790
    @tobylier5790 หลายเดือนก่อน +3

    Oh my god thank you I have been trying to figure this out for ages and even chat gpt could not do it. Thank you

  • @susanwoods-nattrass7586
    @susanwoods-nattrass7586 3 หลายเดือนก่อน +1

    I purchase the template but as the status box is check, the project remains as opposed to automatically elliminating projects as the are checked off - how do I fix this?

    • @spreadsheetwise
      @spreadsheetwise  3 หลายเดือนก่อน

      Hi, ticking the status box will grey the line out and adjust the dates along the top of the Gantt Chart. The dates along the top only show active tasks (Not completed). You can hide rows in the table that are completed. This is shown in the video, so take another look if you're not sure how to filter rows out. This will mean you can view only outstanding tasks is you use filters in the table and the date along the top will relate only to open tasks.

  • @nooneremote7451
    @nooneremote7451 3 หลายเดือนก่อน +1

    very helpful. do you know how to do this with project management dependencies? Linking tasks together by WBS would be so helpful

    • @spreadsheetwise
      @spreadsheetwise  3 หลายเดือนก่อน

      Glad you found this helpful. You might be interested in this Project Management template as this has a built-in Gantt chart which can be filtered on Category/Project: www.spreadsheetwise.com/product-page/project-management-template 😉

  • @ChrisJones-mi7wi
    @ChrisJones-mi7wi 2 หลายเดือนก่อน +1

    Thank you, I enjoyed this. Can you tell me more about, or share another video on how the check the status checkbox might affect the sheet and timeline? Also, how do you use and configure the Progress and Progress % columns?

    • @spreadsheetwise
      @spreadsheetwise  2 หลายเดือนก่อน +1

      Thanks for your questions reference the Automatic Daily Gantt Chart: bit.ly/automatic-gantt-chart
      You can mark tasks as completed with the status checkbox which will grey out the row but this will still be displayed in the Gantt Chart. It's possible to hide completed tasks (rows) by using the filter in cell A4 to filter out TRUE values in column A, so that you will only see outstanding tasks in the table and in the Gantt chart.
      In terms of the Progress and Progress % columns (I and J), you can manually add a percentage completed in column J and this will visually represent as a bar chart in column I for the chosen colour in the label dropdown in column B.
      The Gantt chart in the Project Management template is slightly different as this updates the progress bar automatically based on the the number of net working days remaining for a given task.
      bit.ly/project-management-template
      I hope that helps 😉

  • @PamelaYammine
    @PamelaYammine 2 หลายเดือนก่อน +1

    Very helpful thank you but I have a question: why when I change the starting the date , the dates on the timeline change also? is there any way to keep it fix and the timeline do not change every time when I change the stating date?

    • @spreadsheetwise
      @spreadsheetwise  2 หลายเดือนก่อน

      Thanks for your question @PamelaYammine. If you are referring to the Gantt Chart template bit.ly/automatic-gantt-chart, the start date is actually calculated based on the oldest date from the list of tasks, so when you mark tasks as completed a new start date will become the oldest date which alters the dates along the top automatically.
      The benefit here is that the Gantt Chart will automatically move along with active tasks so older completed tasks will not waste valuable space and you'll never need to add new dates at the end of the chart as this will dynamically hide older dates and show newer dates at the end.

  • @paulcostello4731
    @paulcostello4731 5 หลายเดือนก่อน +1

    Hi, how can I remove weekends and add in holidays to this please? thanks, Paul

    • @spreadsheetwise
      @spreadsheetwise  5 หลายเดือนก่อน

      Hi Paul, I have another template that excludes weekends, but including holidays is not easy as the dates can change every year, particularly depending on country. If you've purchased the Gantt Chart template from my website just drop me an email and I'll share the version without weekends with you.

  • @AndyLuce
    @AndyLuce 10 วันที่ผ่านมา

    hello - great doc and video! Quick question - I have an excel doc that is pretty elaborate. Do I try to import into your template or am I aligning columns and copy and pasting from my sheet into yours?

    • @spreadsheetwise
      @spreadsheetwise  5 วันที่ผ่านมา

      Hi @AndyLuce
      This template is only designed for Google Sheets, things will work slightly different in Excel. In terms of setup, you could go either way really, it depends on whether you want to rebuild your existing sheet into this or vice versa.😉

    • @andrewluce3015
      @andrewluce3015 5 วันที่ผ่านมา

      @@spreadsheetwise I asked for a refund please - are you getting my emails?

    • @spreadsheetwise
      @spreadsheetwise  5 วันที่ผ่านมา

      I just received an email from you a few minutes ago, but nothing before that, sorry.

  • @lilianignatius
    @lilianignatius 5 หลายเดือนก่อน +1

    You're Godsent! I have been battling with this particular issue and then realized that my first date was L5 instead of L4

    • @spreadsheetwise
      @spreadsheetwise  5 หลายเดือนก่อน

      Glad you found this helpful 😉

  • @neils6214
    @neils6214 5 หลายเดือนก่อน +2

    What a clear and concise teacher you are. Thank you!

    • @spreadsheetwise
      @spreadsheetwise  5 หลายเดือนก่อน

      That's very kind of you, thank you!

  • @wei-ching_lin
    @wei-ching_lin หลายเดือนก่อน +1

    MAGIC !!

  • @anamariacallemurillo2996
    @anamariacallemurillo2996 5 หลายเดือนก่อน +2

    Thanks a lot! 😘

  • @Firzj
    @Firzj 3 หลายเดือนก่อน

    I don't know whats wrong but my custom formula doesn't work. Once I add new cells after comma inside brackets, it says invalid formula. for example =AND(G$1>=$D2) works ok, but if I continue adding like =AND(G$1>=$D2,G$1

    • @spreadsheetwise
      @spreadsheetwise  3 หลายเดือนก่อน

      Hi @Firzj
      Try adding a dollar sign before the F2, just like this: $F2="Blue"
      =AND(G$1>=$D2,G$1

    • @Firzj
      @Firzj 3 หลายเดือนก่อน +1

      @@spreadsheetwise I solved it with Chatgpt. I changed , to ; and it works now. I don't know why google sheet doesn't recognize the formula if it has , after $D2

    • @spreadsheetwise
      @spreadsheetwise  3 หลายเดือนก่อน

      Google Sheets uses commas and semicolons differently depending on the country you're in. Here is a useful help page on the topic: support.google.com/docs/thread/4271054/change-semicolon-to-commas-in-formulas?hl=en

    • @Firzj
      @Firzj 3 หลายเดือนก่อน

      @@spreadsheetwise thank you. Makes sense now. I didn't even know some countries use , ; differently.

  • @carolynmendoza4109
    @carolynmendoza4109 5 หลายเดือนก่อน

    Hello, how do I change the timeline to read US date style? month/date/ year. Also full month dates not weeks. Thank you!

    • @spreadsheetwise
      @spreadsheetwise  5 หลายเดือนก่อน

      You can adjust the date format to MM/dd/yyyy easily, full details are included in the setup instructions.
      This template is built to display days and weeks, so it you wanted months this would require a unique setup. If you contact us via our website we can explore options for you.

  • @jaymeepadilla6856
    @jaymeepadilla6856 หลายเดือนก่อน +1

    Super helpful! I immediately purchased this template.

    • @spreadsheetwise
      @spreadsheetwise  หลายเดือนก่อน

      Thanks for your support @jaymeepadilla6856 I hope you enjoy the Gantt Chart template. 😊

    • @jaymeepadilla6856
      @jaymeepadilla6856 หลายเดือนก่อน +1

      @@spreadsheetwise do you have classes so I can learn from you? 🥹🥹

    • @spreadsheetwise
      @spreadsheetwise  หลายเดือนก่อน

      @jaymeepadilla6856 I would like to offer online classes. It's a work in progress.😉

    • @jaymeepadilla6856
      @jaymeepadilla6856 หลายเดือนก่อน

      @@spreadsheetwise HOW DO I ENROLL????

    • @spreadsheetwise
      @spreadsheetwise  หลายเดือนก่อน

      I would suggest subscribing to my website to get receive emails and news and announcements👍🏻

  • @vitobfr
    @vitobfr 5 หลายเดือนก่อน

    does it apply my conditional formatting when I create more rows?

    • @spreadsheetwise
      @spreadsheetwise  5 หลายเดือนก่อน

      The conditional formatting rules should update with the newly added rows automatically, but you can always open the rules up to double-check if you need to. 👍🏻

  • @SecuTechSecuTech-n6m
    @SecuTechSecuTech-n6m 4 หลายเดือนก่อน

    thanks for the Automatic Gantt Chart Template, i need help to make the Gannt chart based on months please

    • @spreadsheetwise
      @spreadsheetwise  4 หลายเดือนก่อน

      Thank you for your message. The Start and Due dates are pivotal to this set up, so looking at weeks or months would require a different structure in the conditional formatting and the display of dates along the top. You could try the native Timeline feature inGoogle Sheets by going to Insert > Timeline and choosing the table of data to build a timeline, but I find this a bit restrictive personally.

  • @barclaybryan9955
    @barclaybryan9955 2 หลายเดือนก่อน

    I tried the “And” conditional formatting; however, I presently only have the projected End Dates.
    Without any end dates, the conditional formatting is applied to all cells in the first column.
    And if I do not put in a start date but only the end dates, all cells in every row have the conditional formatting applied.
    Is there a way to adjust the formula so that zero conditional formatting are applied if there are no start dates found?

    • @barclaybryan9955
      @barclaybryan9955 2 หลายเดือนก่อน

      There is a similar error in the “Days” column. Fixed both with Embedded “If isblank formulas”

    • @spreadsheetwise
      @spreadsheetwise  2 หลายเดือนก่อน

      Glad you figured this out @barclaybryan9955 👍🏻

    • @spreadsheetwise
      @spreadsheetwise  2 หลายเดือนก่อน

      Glad you figured this out @barclaybryan9955 👍🏻

  • @antoniodzlo6558
    @antoniodzlo6558 7 หลายเดือนก่อน

    Hello thanks for you effort and work. If I add a task to futures, Excel does not automatically create columns. I'm a little lost here.

    • @spreadsheetwise
      @spreadsheetwise  6 หลายเดือนก่อน

      Hi @antoniodzlo6558,
      I may be off the point here, but I'll attempt to answer your question if I can.
      If you add a task with some date way into the future (beyond 3 months, for example) then this would not show in the Gantt Chart since the chart only shows up to 3 months, but as you mark tasks as completed and effectively hide them with filters, the dates along the top of the Gantt Chart will automatically collect a new MIN date from the task table. This will effectively change the start date and end date. No extra columns are actually added, but the dates will change as you hide completed tasks.
      Any task you create with a date greater than 3 months will eventually show up as it moves into the 3 month window.
      Also bear in mind, this Gantt Chart bit.ly/automatic-gantt-chart was designed in Google Sheets, so it may behave differently in Excel

  • @stuffstudy-x3h
    @stuffstudy-x3h 6 หลายเดือนก่อน

    This is wonderful:) thanks alot. I'm looking for something similar just for an hours gantt chart, is your template suitable for that as well? do i have to create a new sheet for every day?

    • @spreadsheetwise
      @spreadsheetwise  6 หลายเดือนก่อน

      Thanks for your comment!
      This Gantt Chart would not work for hours of the day in its current set up, but it could be achieved with some modifications to the conditional formatting and the start and end times as opposed to start and end dates.
      If you filtered out tasks that were completed then each day you would see the new tasks, but effectively if you had this set up just to look for hours then any uncompleted tasks would simply continue to show every day since it's not that date that matters, but the hour of the day.
      Perhaps I'll post a video on Gantt Charts for hours in the day rather than days in the week 😉

    • @stuffstudy-x3h
      @stuffstudy-x3h 6 หลายเดือนก่อน +1

      thanks for the response. I'll give it a try

  • @MartinKirkley-j6g
    @MartinKirkley-j6g 6 หลายเดือนก่อน

    Hi, this is a great video. How would I exclude weekends and holidays in my chart?

    • @spreadsheetwise
      @spreadsheetwise  6 หลายเดือนก่อน

      This template bit.ly/automatic-gantt-chart excludes weekends in the day count column, but excluding the weekend dates along the top of the Gantt Chart itself would not be that straightforward and excluding holidays would be even more complicated.
      If you wanted to exclude weekends the best way to achieve this would be to use the IFS function to check the day of the week, but it's probably not required if you use the NETWORKDAYS function in column H.

    • @MartinKirkley-j6g
      @MartinKirkley-j6g 6 หลายเดือนก่อน +1

      @@spreadsheetwise Got it. Thanks for the reply!