+ Reply to Thread
Results 1 to 6 of 6

Gantt Chart Help

  1. #1
    Registered User
    Join Date
    06-26-2017
    Location
    London
    MS-Off Ver
    365
    Posts
    16

    Gantt Chart Help

    Hi all,

    I have created the attached Gantt chart, loosely based on a template that I found somewhere else online. I have had some feedback from colleagues with desired amendments they would like to see added, and was wondering if anyone here could help out?

    For context, this gantt chart is being used to plan an event in November. Just as a default, I have set all of the start dates as 01/06/17 and the end dates as 25/11/17 which is when the event is. These dates will obviously change as we input tasks into the table.

    Questions:

    1. Can a line marker for today's date be added onto the chart? I believe so looking at YouTube tutorials but if anyone can suggest something easy to implement that would be great.

    2. For some reason, the start date on the legend entries axis starts on 06/03/2017. Is there a way to change this to 01/06/2017, as this is when the first task started? I cannot find a way to change this..

    3. Is it possible to add vertical weekly grid lines onto the chart to give more of a visual overview of tasks requiring action by specific work weeks? A label per week (or at least fortnight) would be ideal (I don't mind users having to scroll across).

    4. Is it possible to add dots to the chart to reflect meetings that will take place around the tasks? These meetings would ideally be able to be inputted into the table. As an alternative, perhaps meetings could be added instead of a task with the same start and end date and I would have to format the lines of the chart to ensure it is visible?

    5. Can duration in column G be displayed as working days?

    6. Finally, is it possible individually edit the text formatting of the Horizontal Category Axis Labels? I can only seem to find a way to change all text. Ideally, I would like to make the labels follow a similar format to the tables, so the overall sub-categories (e.g. Contracts, Stand content etc) stand out more from the rest (i.e. larger font, Bold, Underlined). What would be even better is if I could shade the background of these values to reflect the table on the left.


    Sorry for the long list of requests, I am hoping that I can at least implement most of them without having to start over?!

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Gantt Chart Help

    I may be misunderstanding, but here's what I understand for each item:

    2 and 3) All of this can be handled in the Format Axis dialog. The minimum of the horizontal axis is Mar. 6 2017 because you have manually fixed the minimum of the horizontal axis to the number 42800 -- the serial number for Mar 6 2017. In order to change the axis minimum, simply enter the serial number that corresponds to the desired starting date (June 1 2017's serial number is 42887). To get the vertical gridlines to correspond to weeks/multiples of weeks, while in the Format Axis dialog, change the major and minor unit to fixed and enter whatever multiples of 7 you want (I would be tempted to go 28 for the major unit and 7 for the minor unit, but you can choose whatever values seem most appropriate for you).

    1 and 4) The key to implementing parts one and four is to change your Gantt chart from a simple stacked bar chart to a stacked bar (for the Gantt chart bars) + XY scatter (for the today and other milestone markers) combination chart. The basic procedure is to enter the desired scatter chart data in the spreadsheet, then add those data to the chart as additional data series and change the chart type for the additional series to XY scatter (format with or without lines and markers as desired). Can I assume you are already proficient in creating this kind of combination chart?

    5) I am not sure what you mean by working days. If you are trying to show number of days that does not include weekends, then I am optimistic that you can show this. In order to keep your Gantt chart's calendar correct, you will need to move the current values in column G to another column and make sure that the chart references this new column. Then enter a suitable formula in column G that will show "workdays". The key is to keep the calendar days duration column that you currently have in column G somewhere accessible and make sure that the chart refers to the calendar days duration and not the working days duration.

    6) Because Excel does not allow individual formatting of the category axis labels, the key to getting this effect is to completely replace Excel's built in axis and axis labels with a dummy series with attached data labels. The basic technique for adding a dummy series and attaching data labels to that dummy series as an axis is illustrated here: https://peltiertech.com/Excel/Charts...egoryAxis.html

    In short, all of what you have requested should be doable. What parts or steps do you have difficulty with?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    06-26-2017
    Location
    London
    MS-Off Ver
    365
    Posts
    16

    Re: Gantt Chart Help

    No, you understood perfectly. Thanks for your very helpful post. I have sorted out points 2&3, thank you for that. This is the first chart I have created on Excel, so it may stretch my capabilities somewhat but will give it a try and revert back here if I get stuck. On reflection, we can probably live without point 5.

    Thanks again for your help

  4. #4
    Registered User
    Join Date
    06-26-2017
    Location
    London
    MS-Off Ver
    365
    Posts
    16

    Re: Gantt Chart Help

    Hi again,


    I have tried to tackle points 1&4 using various online tutorials. Would I be right in thinking that I need to use a combo chart for this (i.e. bar + XY scatter)? I am using Excel 2010, but cannot figure out how to combine both charts, any ideas?

    Thanks

  5. #5
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Gantt Chart Help

    Yes, to get those markers you need to make a stacked bar + XY scatter combination chart. Here's how I would have expected to make the combo chart with a today marker:

    1) Enter =TODAY() in an out of the way cell. Enter the number 0 in an adjacent cell.
    2) Select these two cells -> Ctrl-C to copy to clipboard -> Select the chart -> Paste special -> As new series with X values in first row/column.
    3) Select the new series -> Change Chart type -> XY scatter -> Marker without line (or other desired subtype). (Make sure you get the XY scatter chart group and not the line chart group). This should create the combo chart.
    4) Continue adding data series (and changing them to XY scatter chart type) as desired.
    5) Format and adjust as needed.

    I like this tutorial: https://peltiertech.com/Excel/Charts/BarLineCombo.html though the command locations are given for Excel 2003 and earlier, so you would need to know where to find the same commands in 2010.

  6. #6
    Registered User
    Join Date
    06-26-2017
    Location
    London
    MS-Off Ver
    365
    Posts
    16

    Re: Gantt Chart Help

    Thatīs sorted it. thank you very much for your help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 0
    Last Post: 01-07-2015, 01:22 PM
  2. Gantt chart Help
    By skyland1 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 10-07-2013, 05:08 AM
  3. Gantt Chart
    By Nago01 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 09-23-2013, 12:51 PM
  4. Gantt Chart Bar
    By DrPenn777 in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 01-20-2013, 03:54 PM
  5. Replies: 5
    Last Post: 10-04-2012, 07:01 AM
  6. Replies: 0
    Last Post: 07-13-2012, 06:40 PM
  7. Gantt Chart
    By S K ROY in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 11-21-2011, 03:51 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1