One column and 2 lines chart in single axis

[Hari]

Hi,

Im using Excel 2003 and lets say I have sales data on 30 stores in
which specific promotions were run (Test Stores). Since its a
consolidate monthly data so I have 30 data points only. I also have
average sales of 30 other stores very similar to test stores in which
no promotions were run (Control Stores) which means 1 data point.

I want to compare the sales data of each of the test stores with the
average test store sales and also the average control store sales in a
single graph.

My idea was to draw a column graph with each bar representing sales for
each of the test stores. Then I can draw 2 straight lines in this graph
with each of the lines representing the average test and control store
sales. I have 3 series with me, with 1st series having data on test
stores, 2nd series having average test store sales for 30 data points
and similarly the 3rd series for average control store sales.

Subsequently I chose line-column type chart with 1st series as column
and second series as line. I want 3rd series also to be line but it is
showing up as column only. How to manpulate it

(I dont want line representation for all 3 series, as it doesnt make
sense to show 1st series as line. Neither do I want to plot my 3rd
series on secondary axis).

Pls guide me.

regards,
HP
India

 

[Jon Peltier]

You can create your own combination chart types very easily. Make a line
chart with all of your data, select the Sales series in the chart, go to
Chart menu > Chart Type, and select the Clustered Column type.

- Jon

 

[Hari]

You can create your own combination chart types very easily. Make a line
chart with all of your data, select the Sales series in the chart, go to
Chart menu > Chart Type, and select the Clustered Column type.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

Thanks a lot for your response. I chose the sales series and did right
click --> Chart type. There are 2 tabs Standard Types and Custom types.
In both I see lots of varieties of charts but there is no "Clustered
Column" variety in it. I also tried choosing the "User-Defined" radio
button within Chart Type but it is displaying only the "default" which
is fully column type for all series.

Please let me know as to where Im going wrong.

Also, I see something strange. When I select any chart series it
selects alternate points starting from the first point in the series. I
undertsand that even when it selects aternate points it actually
selects the whole of the series, but why has MS designed it to not
select all points

regards,
HP
India

 

[Jon Peltier]

Thanks a lot for your response. I chose the sales series and did right
click --> Chart type. There are 2 tabs Standard Types and Custom types.
In both I see lots of varieties of charts but there is no "Clustered
Column" variety in it. I also tried choosing the "User-Defined" radio
button within Chart Type but it is displaying only the "default" which
is fully column type for all series.

You're looking too hard. On the Standard Types tab, select Column in the
list of chart types (left side of dialog), and choose the chart subtype
Clustered Column (top left choice in right side of dialog, the default
anyway).

Also, I see something strange. When I select any chart series it
selects alternate points starting from the first point in the series. I
undertsand that even when it selects aternate points it actually
selects the whole of the series, but why has MS designed it to not
select all points

As the series gets more and more points, the selection indicators display a
smaller percentage of the selected points. I think this is for legibility.
You'll get used to it.

- Jon

 

[Hari]

You're looking too hard. On the Standard Types tab, select Column in the
list of chart types (left side of dialog), and choose the chart subtype
Clustered Column (top left choice in right side of dialog, the default
anyway).

Thanks for the pointer. Iam able to get it working right now. I have a
follow-up question. I have added this chart (Clustered column with rest
2 series as lines) as one of my user-defined charts so that in future
if I want I can quickl select it.

I would like to share this setting with other colleague(s) in an easy
manner. Is there any particular setting file I need to copy from my
computer and paste it in to a new one for it to automatically appear as
a user-defined chart?

As the series gets more and more points, the selection indicators display a
smaller percentage of the selected points. I think this is for legibility.
You'll get used to it.

Thanks this makes sense
regards,
HP
India

 

[Jon Peltier]

You can give them your user gallery file, at the risk of overwriting their
own custom charts. The best thing is to sent them a regular workbook with
your chart in it, and showing them how to add it as a custom type.

- Jon

 

[Hari]

You can give them your user gallery file, at the risk of overwriting their
own custom charts. The best thing is to sent them a regular workbook with
your chart in it, and showing them how to add it as a custom type.

Thanks for the suggestion. I was thinking that if I have a critical
number of charts as user-defined (some of my colleagues do quite a bit
of charting) probably it would be more easier to use this file (I now
see it as "XLUSRGAL.XLS") rather than adding in new workstations.

regards,
HP
India

 

[Jon Peltier]

If the other users know nothing of custom charts, it's probably okay. If
not, you'll annoy anyone whose custom charts have been obliterated.

Here is a programmatic technique to distribute custom chart types to other
users:

http://groups.google.com/group/microsoft.public.excel.charting/msg/210f8a219326de1a

- Jon