Conditional Format of Scatter Chart

[joer55]

Hello

I have a scatter chart created from a data series in a column.
Is it possible to change the color of the points that fall below a certain value to red?
Can someone point me in the right direction please?

Thanks
Joe

 

[RickXL]

Hi,

Forn anything non-standard with Excel charts I always look at Jon Peltier's site. In this case I found this: Conditional Formatting of Excel Charts - Peltier Tech Blog

The basic process is that you will probably need to plot three data series, not just one. Start with your original x and y-axis values then create formulas in the next two columns that spliut the points according to how you want them plotted. So, red points in one column and the others in the next one.

Then you plot all three sets of y values. The first one is complete and you keep the lines but hide the markers. The second and third ones you hide the lines but keep the markers.

My test data looked like this:

Excel 2013
	A	B	C	D
1	X	Y	Low	Normal
2	1	0.229564	#N/A	0.229564
3	2	0.50972	#N/A	0.5097204
4	3	0.058307	0.058307	#N/A
5	4	0.607066	#N/A	0.6070662
6	5	0.075497	0.075497	#N/A
7	6	0.467152	#N/A	0.4671523
8	7	0.261374	#N/A	0.2613741
9	8	0.667641	#N/A	0.6676411
10	9	0.984997	#N/A	0.984997
11	10	0.735486	#N/A	0.7354864
12	11	0.206556	#N/A	0.2065561
13	12	0.099664	0.099664	#N/A
14	13	0.939593	#N/A	0.9395929
15	14	0.847493	#N/A	0.8474926
16	15	0.326043	#N/A	0.3260425
Sheet3

Cell Formulas
Range	Formula
B2	=RAND()
C2	=IF(Sheet3!$B2<0.2,Sheet3!$B2,NA())
D2	=IF(Sheet3!$B2>=0.2,Sheet3!$B2,NA())