Conditional Format of Scatter Chart

joer55

Board Regular
Joined
Mar 27, 2008
Messages
88
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
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,314
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:

<b>Excel 2013</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #5B9BD5;;">X</td><td style="font-weight: bold;border-top: 1px solid black;border-bottom: 1px solid black;color: #FFFFFF;background-color: #5B9BD5;;">Y</td><td style="font-weight: bold;border-top: 1px solid black;border-bottom: 1px solid black;color: #FFFFFF;background-color: #5B9BD5;;">Low</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;color: #FFFFFF;background-color: #5B9BD5;;">Normal</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DDEBF7;;">1</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">0.229564</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">#N/A</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">0.229564</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">0.50972</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">#N/A</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">0.5097204</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DDEBF7;;">3</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">0.058307</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">0.058307</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">4</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">0.607066</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">#N/A</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">0.6070662</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DDEBF7;;">5</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">0.075497</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">0.075497</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">6</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">0.467152</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">#N/A</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">0.4671523</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DDEBF7;;">7</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">0.261374</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">#N/A</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">0.2613741</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">8</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">0.667641</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">#N/A</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">0.6676411</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DDEBF7;;">9</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">0.984997</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">#N/A</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">0.984997</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">10</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">0.735486</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">#N/A</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">0.7354864</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DDEBF7;;">11</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">0.206556</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">#N/A</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">0.2065561</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">12</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">0.099664</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">0.099664</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DDEBF7;;">13</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">0.939593</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">#N/A</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">0.9395929</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">14</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">0.847493</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">#N/A</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">0.8474926</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DDEBF7;;">15</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">0.326043</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">#N/A</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;background-color: #DDEBF7;;">0.3260425</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B2</th><td style="text-align:left">=RAND(<font color="Blue"></font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C2</th><td style="text-align:left">=IF(<font color="Blue">Sheet3!$B2<0.2,Sheet3!$B2,NA(<font color="Red"></font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D2</th><td style="text-align:left">=IF(<font color="Blue">Sheet3!$B2>=0.2,Sheet3!$B2,NA(<font color="Red"></font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,095,803
Messages
5,446,560
Members
405,407
Latest member
apat

This Week's Hot Topics

Top