How can I vary the thickness of each bar in my chart?

[Kim-W]

I work for an organisation that awards grants to research projects.

I am using Excel 2007 and I want to create a timeline (as an Excel chart) to show the duration of each project, and the size of the grant.

Using the start date and duration for each project, I have managed to create a timeline - please see http://www.flickr.com/photos/giantradish/5634187879/sizes/l/in/photostream/ for an image.

However, I cannot work out how to vary the thickness of each horizontal bar. I would like to use the data in column D to set the thickness, so that the bar for Grant 3 (£200,000) is ten times thicker than the bar for Grant 2 (£20,000), for example.

I have a feeling this relates to adding a secondary vertical axis, but I have tried quite hard to google an answer and not got anywhere.

Can anyone help me?

I'd be so grateful for your advice.

Kim

 

[AlphaFrog]

Here's a step-by-step guide from Jon Peltier
http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=533

 

[Kim-W]

Thank you very much.

Wow... this is helpful, but I had no idea it was so complicated.

Doing it this way would mean having to work out the exact area for all the blank spaces. I could try this, but it seems very fiddly, especially as I want to plot about 30 floating bars of varying thicknesses.

Do you or does anyone have any other ideas of a simpler way to do this, i.e. where you simply specify that you want the bar thickness to correspond to the value in column D, for example?

Many thanks again for your help.

Kim

 

[AlphaFrog]

Stephen Bullen shows a dynamic range approach:
oaltd.co.uk/Excel/SBXLPage.asp#Charting, download FunChrt5.xls.

Andy Pope demonstrates a couple different approaches:
andypope.info/charts/colwidth.htm
andypope.info/charts/colwidth2.htm