Excel 2007: Horizontal line across vertical bars 1

[katrina11]

Hi everyone,

I want to add a simple horizontal line across a vertical bar chart .

Four(4) vertical bars represent race rates for Asians, Blacks, Whites and Latinos.
The horizontal line should represent an average rate value for all four races' rates.


Race rate average rate
A 80 74
B 60 74
W 70 74
L 86 74


I created a vertical bar chart (using first TWO columns, but try as I might I was unable to build the line)

Could you please help me with the issue?

Katrin

 

[MCuthill]

Katrina,

You should just have to have both series on the chart as columns, right click the "Average" series and select "Chart Type". Select "Line".

Basically, format one series as one chart type, and the second series as another.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[katrina11]

Mike,

Thank you, it did generated a line but unfortunately the legend generates just series1 and series2 while I need four colors for each of races.

Is there any way to overcome this limit?

Thank you!

Katrin

 

[MCuthill]

Katrina,

The same approach can be applied. Click on one of the columns, it will select them all (will place a little square in all the columns), click on the column you wish to change, it will now highlight just one "data point" (column). Right-Click and select "Format Data Point" - change the color here.

Hope this helps! Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[katrina11]

Mike, I did colored all four columns exactly as you described and they all look fine with different colors assigned.

However,the problem is in the way which the Legend object looks like.
When I had created a chart based on only series, the Legend showed four colors. However when I had created chart based on two Series my Legend for some reason showed just Serie1's color and Serie2's color only . It totally ignores the columns colors

 

[SkipVought]

To have 4 different colored columns you must have CLUSTERED Column Chart with 4 different series. So you must transpose the rows & columns definition.

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

 

[MCuthill]

If you require the legend, I trust in Skips solution (I haven't tried) - it makes perfect sense.

When I have needed this appearance, I would hide the legend and increase the size of the labes on the axis under the columns to identify which is which.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."