# Line Chart Data point colour change?

#### DexonII

##### New Member
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!

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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

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???

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.

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.

ANy one else have any ideas??

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?

*BUMP*

Has anybody any other ideas?

Replies
7
Views
215
Replies
0
Views
220
Replies
2
Views
594
Replies
1
Views
437
Replies
6
Views
732

1,219,971
Messages
6,151,221
Members
451,017
Latest member
peterlam84

### 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.

### Which adblocker are you using?

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

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