Excel Scatter Chart

[joma]

I have Excel scatter chart showsing stock weight% on X axis and stock return% on Y axis. I'd like to plot the background of this scatter chart using different colors. For example, the background with the stocks with more than 5% return will be pink, then the background with the stocks with 2- 5% return blue etc.

Is there any way to do this in Excel?

 

[fairwinds]

Welcome to the board!.

I'm not sure how you want it to look but check out

http://www.peltiertech.com/

for some real nice charts.

 

[joma]

Thanks for the info, but I've already check the site you mentioned. Couldn't find anything on the subjct.

joma

 

[fairwinds]

So, you want different background colors in the same chart, or what do you mean?

 

[joma]

I want horizontal bands in my XY scatter chart. For example, the area above 5% return on Y axis has pink background, 2% - 5% on Y axis blue, (2%) - (-2%) white, (-2%) - (-5%) red. These bands will show the degree of stock performance regardless of stock weight%.

joma

 

[fairwinds]

You could do something like this:

(I'm sorry but I cannot show the resulting chart here.)


-Add a new column next to the data like in my example.
-Copy it, select the chart, edit - paste special, as new serie.
-Right click the new serie, chart type - bar chart.
-Format the new data serie as (Options) Overlap: 0, Gap: 0, vary colours by point.
-Format the individual bar to the color you want. Adjust X axis scale so the bars goes all the way out to right.

Book1
	A	B	C	D
1	X	Y	Extra
2	1	3	10
3	2	2	10
4	3	1	10
5	4	6	10
6	5	5	10
7	6	4	10
8	7	7	10
9			10
Sheet3

 

[joma]

How do I adjust X axis? I have difficulty extending the bar all the way to the right.

joma

 

[fairwinds]

Right click axis
Format axis
Scale
Set Maximum to the same as your bars.

 

[joma]

It worked! Thank you. Now do you know how to put labels in each point of the scatter? I can make scatter charts using only X and Y axis, but I want to name each point with something like "Stock 1", etc.

joma

 

[fairwinds]

Go back to the beginning and add a column with the names for your lables before your data. Now when you create the original scatter you have A1:C8 selected.
When the scatter is ready you Right click the chart - Chart options - Data lables - and check Lable containes: X value

That way the text in A column will appear as lables.

Book1
	A	B	C	D
1		X	Y	Extra
2	name1	1	3	10
3	name2	2	2	10
4	name3	3	1	10
5	name4	4	6	10
6	name5	5	5	10
7	name6	6	4	10
8		7	7	10
9				10
Sheet1