how to create percentage bar chart in excel, similar to "100% stacked column" but with one variable against total value

I thought over a lot for this case, I need to plot a series as percentage value of another series.

e.g, series A is the capacity and B is the usage. I want a percentage pl开发者_开发知识库ot of B on A. Initially I did this by creating a 100% stack chart with series B vs (A-B). I computed (A-B) on a separate column and used that as a series for this purpose. So far so good.

But now I want to generate multiple charts like this (say 12 different graphs for 12 months' usage). I have 12 columns of usage data and one column of fixed capacity data.

Is there a way to auto-generate this chart without having to create 12 intermediate columns with formula. It seems logical that Excel should have a chart type for representing A on B, just as they have one for A,B on A+B.

Sorry if I am too lengthy or confusing in my description. I did my best here to explain.

---

Actually I am trying to do the same thing I figured it out. If you have Excel 2007 (not sure if this feature is available in older versions). Select all 3 colums, 1 with the names of your variables, total count, and subtotal of what you are counting. Select column chart. Once the chart is created, right click to format the data series. Under series options there is a slidebar for series overlap, change this value to 100% and wahla, the the 2nd series becomes a percent of the total. Hope this helps!

---

Excel has common chart types, but even Microsoft doesn't have the resources to provide every possible combination of charting styles. And users are confused enough with the currently available styles.

Excel charts plot the data that they are given. If you need to plot A as a percentage of B, you will need to compute the percentage in a range, and plot this range.

---

The way I would do this is not to modify the ranges on the worksheet at all. Write a new function that creates the modified range data you need so that you can return this and use it inside of ApplyTemplate method when creating the chart. The only downside to this is you would need to run a macro/vsto ribbon button to update the chart. as opposed to getting that behaviour for free with a worksheet bound approach.