Stacked Bar chart excel 2011

Hi Guys,

Please help, I am having difficulty with Stacked Bar charts. I am trying to do a gantt chart.

I have 5 columns on my spread sheet these are:
Tasks - Text
start date - date format
end date - date format
days completed - numbers
days remaining - numbers

Now I can start a stacked bar chart by selecting the first and second columns.
I then right click on the chart choose select data
now, if I then add series which seems the logical thing to do, the whole chart is totally reformatted with dates at the bottom ranging from 00/01/1901 to 15/03/2023, and all the bars are the same colour and the same length.

What am I doing wrong. If you need me to post more info I can. I have been struggling with this now for nearly 4 hours, how do you do this?

I have been following a tutorial and downloaded his example, and it behaves how I would expect it to. I tried just putting my data into his example, but that doesn't work either. Can any one help me with please?

Kind regards

Rob K

Hi

this is probably the simplest way to do a gantt chart in excel - there are many more complex versions. Its limitation is that the value axis is actually time, so unlike a conventional time series chart you can't set the axis label intervals to eg months.

In the attached version the start value is actually series 1 in a stacked column with 3 series, but with series 1's fill and line colour set to none so it's invisible, leaving the completed and finished hours the only visible series. End time is not a series, it's derived by adding the start time, hours completed and hours outstanding.

NickyC,

Yes this is exactly what I am doing, but I can't get the Y axis to do what your version is doing, how did you format it for text?

Kind Regards

Rob K

With this approach you need to have the dates entered as numeric values, as this is what causes the coloured bars to start in the right place, and allows you to add the day values to the start date to create the 'stacked' chart (the first bar in the stack is actually the date value, but it is not visible because it has no lines or fill colour). If your date axis values are entered as text then they will default to zero and all your bars will start on the axis.

Do you need to enter the date values as text? as FDibbens suggested, it would be helpful to upload an example to look at. I'll be offline for a few hours, but will come back to this thread tomorrow.

or ... do you mean the labels aaa bbb etc? these are simply the category axis labels for this type of chart (stacked bar)

NickyC,

Thanks for posting that, I just went through it bit by bit, and now I have a wonderful Gantt Chart with all the data and things I need for my project, a thousand thank you's.

Rob K

Just one more thing I would like to do, on the x axis, Excel has formatted my dates for every 2 months starting four months earlier. How can I format it so it starts from hte beginning of the year and goes up one month at a time?

Regards

Rob K

Hi Rob

that's the trouble with this method. Because it is treating dates as values in the value axis, it cannot used date types which are only available for the category axis. The best you can do is use weeks or some other period with a fixed number of days. For example 30.4167 will give you 12 even "months" a year, which may suit if your display does not have to be 100% accurate, but for example the axis label "April" will occur on 2 April not 1 April