You can make Gantt charts in Excel using stacked horizontal bar charts. The bars have the appearance of floating, because the lower series in the stack is hidden via formatting (no border and no fill), leaving the upper series to show the duration of each task. I’ve written two tutorials about Gantt charts, and Advanced Gantt Charts in Microsoft Excel, and I’ve compiled a list of links to other tutorials, templates, and programs related to Excel Gantt charts in .
A common question about Excel Gantt charts is how to display multiple floating bars for a single task. bar series can only have one bar per category, right? That’s true, but you aren’t stuck with using one series to represent repeating tasks. There’s a hidden series for all the time before the task started, and a series for the first occurrence of the task. Simply add another hidden series for each gap between occurrences of the task, and another visible bar, formatted like the first, to represent each repetition of the task.
The data represents the times that ambulances went out on call and returned. One ambulance went out three times, the other three once each.
The data needs to be rearranged to put all data for each ambulance in a single row.
The formula in cell H10 is =B10 and this is filled down the column to row 13. The formula in cell I10 is =IF(C10>B10,C10-B10,0), and this is filled right to column M and down to row 13.
The chart source data is highlighted yellow in the figure above. Select one area, then hold CTRL while selecting the other. Then insert a stacked horizontal bar chart. You’ll have to change the orientation of the data to series in columns, since by default Excel will plot this with series in rows (more columns than rows, so Excel tries to make fewer series with more points). This is what the default chart looks like in Excel 2003. It’s less ugly but still needs work in 2007 (not shown). Note the three series called “Available” and the three called “On Call”.
Let’s clean up by removing the chart area border, the plot area border and fill, and the legend border. Let’s use a very light gray for the vertical gridlines and a medium gray for the axes. We don’t need the tickmarks on the vertical axis. For the horizontal axis, set the minimum to 9/29/2010 in Excel 2003 or 40450 in Excel 2007 (which has lost the ability to accept value axis scale parameters in date or time format). Set the major unit to 3:00 in 2003 or 0.125 in 2007, and the minor unit to 1:00 in 2003 or 0.046666666667 in 2007.
Hide the three Available series by formatting with no border and no fill.
Format the three On Call series using the same border and fill.
To get the ambulances in the opposite order, format the vertical axis, and adjust the scale so categories are plotted in reverse order, and so the value axis crosses at the maximum category.
Hide the unneeded legend entries by clicking once to select the legend and again to select the unwanted legend entry, then pressing Delete. Lather, rinse, repeat.