How to group vertical bars in Excel2007 to get one gap between groups? 1

[katrina11]

Hi everybody,

I am working on the vertical bar chart for 2 products: Comm & Med to represent the rates for three years (2010, 2009, 2008) for each product. It should look like 3 vertical bars without a gap for the Comm program (92%; 95%; 88% ) then gap and then 3 vertical bars for Med Program(93% ,88%, 87%). Tried as I might I was unable to do it.
*********************************************************
1st I tried to arrange numbers in Excel 2007 in the following order:
92% 95% 88% 93% 88% 87%
(which is 92% for 2010; 95 % for 2009 and 88 for 2008 for COM product and the same for MED product: 93 % for 2010 and etc.)

The result is that I got 6 vertical bars as one series without any idea how to group them in order to have just one gap between Comm. & Med products’ groups
***************************************************************

2nd I tried the following order:
92% 95% 88% (for COMM 2010, 2009, 2008)
93% 88% 87% (For MED)
…But got even more meaningless result. I got two series which groups vertical bars from different groups:
92%&93% 95%&88% 88%&87%

How can I get two groups (with three vertical bars for each)?
Could you please tell me what is my mistake ?

Thank you!

Katrin

 

[SkipVought]

Excel has no "vertical bar chart", but it does have a Clustered Column Chart wich is vertical and a set of bar charts that are horizontal.

Set up you data like...
[tt]
2010 comm 92%
2009 comm 95%
2008 comm 88%
0%
2010 med 93%
2009 med 88%
2008 med 87%
0%
[/tt]
Use this 8 row x 3 col range as the source for your clustered column chart.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

and here's another data option
[tt]
2010 2009 2008
92%
comm 95%
88%
0%
93%
med 88%
87%
0%
[/tt]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[katrina11]

Skip,

Following your way (keeping just percentage column with 0% "breaks") I got much more meaningfull output. I did get a big gap between the groups. Thank you very much.

However I still have gaps between clustered columns within each group and just wonder if there is any way to get rid of them....

Thank you once again,

Katrin

 

[SkipVought]

vary the GAP and/or OVERLAP of the series format.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[lionelhill]

and if you have to do much of this sort of thing, seriously consider getting a decent graph-plotting package. Excel is really pretty limited in its graphing abilities. I personally use SigmaPlot, but have no reason to recommend it above any other package

 

[SkipVought]

You would be surprised what Excel Charts can produce.

I'd suggest checking out John Walkenbach's "Excel Charts" reference.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[katrina11]

Thank you very much. Now it looks much better and I ordered
John Walkenbach's "Excel Charts" (not sure if I can install SigmaPlot at work).

Now there is an only thing left for my assignment. I am not sure if there is any way to do a small text note at the bottom of the graph...Is it possible at all?

Thank you!

Katrin

 

[SkipVought]

Activate your chart.

Insert a Text Box, AND with the InsertText Bar in the text box active, In the Formula Bar enter

=

and then select an empty cell on your sheet, where you will enter the text you want in your text box in the chart

Enter your text in this cell.

You can CUT 'n' PASTE this cell into another sheet or anywhere you want it. It is easier to enter data in a cell than it is to enter data in a textbox in a chart. AND you will be able to use a formula to vary the data in your textbox is you wish.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]