Line Chart Data point colour change?

DexonII

New Member
Joined
Sep 8, 2005
Messages
34
Hi,

Does anybody know how I can get the series valuse on my line chart change colour if they are above or below the value in a specific cell?

I need to plot data to see if it falls inside two given limits. If it is above or below I would like it to change the data points of the series to red, but if it is within the limits remain as the default colour (black).

Any ideas? - Not VBA if possible!

Thanks in advance... :)
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Airfix9

Well-known Member
Joined
Sep 23, 2005
Messages
886
OK, here's my solution - there are bound to be others, though.

I created two dummy series for the graph, one showing what is inside the specified range (for this example, I chose 2-4) and one showing what is outside the range.

I then created a line graph using all three series.

For column C, I chose not to show the points
For column D (outside), I chose not to show the line and colour the points red
For column E (inside), I chose not to show the line and colour the points black

Ta-daaaaaaaaah!
Book5
BCDE
2Rangeis2-4
3
4ValueOutsideInside
5A11#N/A
6B3#N/A3
7C2#N/A2
8D2#N/A2
9E4#N/A4
10F55#N/A
11G4#N/A4
12H55#N/A
13I4#N/A4
14J66#N/A
Graphing Solution


With thanks to Jon Peltier's most excellent site at http://peltiertech.com/Excel/Charts/index.html
 

DexonII

New Member
Joined
Sep 8, 2005
Messages
34
Thanks Airfix9,

I canot use more than 1 series for the chart. I am monitoring a process, and plotting the averages of 5 readings to create the plotted line.

I have seen somewhere - and I cannot now find where! - a line chart that appeared to have formatting of some kind on the actual series formula of the plotted line. It went alnog the lines of if the value if > or < specified values change the colour to red or black depending on which criteria was met.

ANy ideas???
 

Airfix9

Well-known Member
Joined
Sep 23, 2005
Messages
886
But surely you are plotting from one set of data (that may expand). My example just adds two extra series of identical data with the irrelevant values reduced to "#N/A" (which will not plot)...

To be honest, that is about as far as my graphing experience goes. Have a good look at Jon Peltier's website - http://peltiertech.com/index.html -which has loads of tricks that you can do to solve your graphing problems.

Sorry I can't help further.
 

DexonII

New Member
Joined
Sep 8, 2005
Messages
34

ADVERTISEMENT

Airfix9,

I cannot add in any additional columns as this will mess up the macrod running inside the sheet.

I will keep looking to see if I can find the example I saw. From memory the series had something along these lines in it to change the colour;

[Red][<5.8] #,##0.00;[Red][>7.8] #,##0.00;[Black] #,##0.00

Have tried all sorts, but cannot get it to work.

Thanks for your help.

ANy one else have any ideas??
 

Airfix9

Well-known Member
Joined
Sep 23, 2005
Messages
886
Just one more thought, before I give up...

Why not transfer the graph data to another sheet (which can remain hidden) and add the two extra series there and graph from THAT sheet?
 

Forum statistics

Threads
1,136,926
Messages
5,678,606
Members
419,775
Latest member
joh93

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top