How to Make a Gantt Chart for Repeated Tasks

A Gantt chart for repeated tasks consists of a bar chart series showing the duration of a task, stacked on a transparent bar that pushes the visible bar out to the start of the task. My technique added two more series for each repeated task, one for the gap between repeats, the other for the duration of the repeat.

The attached excel workbook shows a Gantt chart set up to depict the utilization for three CT Scanners. The horizontal axis represents time and each bar represents appointment times. Please see attached Excel workbook.

How to Make a Gantt Chart for Repeated Tasks 02

Step 1. Create the stacked bar chart. Highlight the range b3:d51 click on “Insert” tab. Select “Bar Chart” then select 2D “Stacked Bar” chart. Column B is the number used for the X values of the chart.

How to Make a Gantt Chart for Repeated Tasks 02

How to Make a Gantt Chart for Repeated Tasks 03

Step 2. Delete the legend.

Step 3. Plot all items with the same x value in the same bar. Right click on vertical axis and select “Format Axis”. Select “Axis Options” click on “Date axis”. Click on “Dates in reverse order”. Under “Horizontal Axis Crosses” select “At maximum date” to display dates at the bottom.

How to Make a Gantt Chart for Repeated Tasks 04

How to Make a Gantt Chart for Repeated Tasks 05

Step 4. Display time increments on the y axis. Right click on horizontal axis and select “Format Axis”. Select the “Axis Options” tab and adjust the Minimum settings to accommodate the dates you want to display. For this step you must enter the values as date serial numbers, not actual dates. In order to determine the serial number type the date into a cell in Excel and format the cell using “General” number format. See Excel workbook cell C52 for the “Minimum value”, and D52 for 30-minute time interval.

Select “Fixed” under minimum and type the number 41397.33333 in the cell. Select “Fixed” under major unit and type the number .020833333 in the cell to display 30-minute time interval.

Right click horizontal axis and select “Format axis”. Select Number on the left hand side of pop up box and select “Time” and 13:30 format.

How to Make a Gantt Chart for Repeated Tasks 06

How to Make a Gantt Chart for Repeated Tasks 07

Step 5. Hide start time series data. Click on the chart and click on the first blue box in the first series to highlight the start time data series and right click select “Format Data Series”. Click on “Fill” tab on the left hand side of the box and select “No Fill”. Select “Border Color” and select “No line”.

How to Make a Gantt Chart for Repeated Tasks 08

Step 6: Add informative vertical axis labels. Click on the chart, click “Design Tab” and click on “Select Data”. Under “Legend Entries”, click on “Add”. In the “Series Values” box highlight cells A55:B57 and click “OK”.

How to Make a Gantt Chart for Repeated Tasks 09

How to Make a Gantt Chart for Repeated Tasks 10

How to Make a Gantt Chart for Repeated Tasks 11

Step 7. Right click on green bar and select “Format Data Series” and under ‘Series Options” select secondary axis. Click on “Close”.

Step 8. Change the values in cells B55 through B57 from 1 to 0.

How to Make a Gantt Chart for Repeated Tasks 12

Step 9. Click on “Design Tab” and select “Select Data”. Under “Legend Entries”, click on “Series 3”. Under “Horizontal Categories” click on “Edit”. Highlight cells A55:A57. Click “OK”. Click “OK”.

How to Make a Gantt Chart for Repeated Tasks 13

How to Make a Gantt Chart for Repeated Tasks 14

Step 10. Click on ‘Layout Tab” and click “Axis”. Select “Secondary Vertical Axis” and select “More Secondary Vertical Axis Options”. Click “Categories in Reverse Option” and Click on “Automatic” under “Horizontal axis crosses”.

How to Make a Gantt Chart for Repeated Tasks 15

How to Make a Gantt Chart for Repeated Tasks 16

Step 11. Delete the primary axis labels. Right click on primary vertical axis (1, 2 and 3) and press DEL key.

How to Make a Gantt Chart for Repeated Tasks 17

Step 12. Delete Secondary Horizontal axis labels. Right click on secondary horizontal axis on the top of the chart and press the DEL key.

How to Make a Gantt Chart for Repeated Tasks 18


Learn more about Gantt Charts in my May 17, 2013 newsletter “Visualize a New Approach“.

Share
This entry was posted in Editing Charts & Graphs, Excel Tips & Tricks. Bookmark the permalink.

20 Responses to How to Make a Gantt Chart for Repeated Tasks

  1. Syed Shamim says:

    This is perfect! I’ve been looking for something like this for a while, thank you!

    Just wondering, is there a way to further customize this? Let’s say for instance, instead of just showing the CT scan being “on” or “off” (as currently shown), is there a way to have the graph show “on” (as shown currently with no color), “malfunctioning” (as another color), and “off” (as shown currently with the color red)?

  2. Pingback: a Gantt chart.. well, kinda. to chart events on a schedule

  3. Pingback: Project Timeline with multiple events

  4. Christine says:

    Thank-you SO much for this! I have been searching for ages to find a template or program that can produce a gantt chart with recurrence of events in a single category, you’ve saved me an enormous amount of time manually creating the chart with illustration software!!

    This is amazing!

  5. ning says:

    this is super helpful. I love how simple it is explained here. I was able to use this for my current work assignment and possibly for future ones. many thank yous!

  6. Harish says:

    Hi, Thanks a lot for the above information. Was very helpful in meeting my charting objective. Thanks.

  7. shanker says:

    Wow, Its really amazing and very very useful. Thanks for this clear explanation.

    Regards
    SSR

  8. Ed says:

    Step 10 doesn’t work with Excel 2013 where the secondary vertical axis is always added to the right side the stacked bar chart. Any solution with Excel 2013? Thanks.

  9. Pingback: Loss of precision in chart

  10. Carlo says:

    Is it possible to create this chart with a scatterplot on the secondary vertical axis?

  11. S C says:

    2nd the doesn’t work right in 2013. When you change the Horizontal Axis labels, it changes it for all 3 series, whereas your example shows them the same for the 1st two, then the last based on the Yellow Axis subgrid.

  12. Pingback: Creating a (Gantt) sleep chart

  13. Raj says:

    Step 10 doesn’t work – Change the chart style and you can see the secondy vertical axis and finish the graph in excel 2013. Cheers

  14. Brett says:

    Hi,

    This is really helpful, thanks for posting.

    Is there a way to have unique color shading based on the value in the Y axis i.e. each “color scanner”?

    Thanks!

  15. Amit Soni says:

    Great! I was looking for this…
    Thank you for this useful tip!

  16. Amit Soni says:

    Great! I was really looking for this!

    Thank you for this useful tip!

  17. Pingback: Gantt chart in excel

  18. Pingback: Missing statements

  19. Mikko S. says:

    Can somebody help me with this chart? I was successful created chart like this but how I get different names those repeated tasks? e.g. I have sql jobs with steps, so repeated tasks are steps and need those names into view.

Leave a Reply

Your email address will not be published. Required fields are marked *


7 − five =