Link to home
Start Free TrialLog in
Avatar of vipaman
vipamanFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Shade an area of a graph in excel

If you look at the Orography tab in the attached file there is a graph. The blue line represents a hill or ridge. The dots represent the position of a scaffold on the hill. On the left is the upwind side and there is an orange line. If the scaffold is above this line then it is affected by the terrain and a set of equations need to be checked. I want to shade above the line. On the right is the downwind side. I want to shade below the grey line down to blue. I can introduce dummy nodes to place a node on the blue line directly under the grey line node. I can do the same the other side at 0,900. I cannot find how to shade these areas?
graph.xlsx
Avatar of ☠ MASQ ☠
☠ MASQ ☠

You need to add additional values to the series which represent the areas to shade.
For example on the leeward side of the graph you need to provide values for the difference between the grey and blue lines while for your orange line you want to shade any area where the difference between points is a positive value greater than 450.
You can't overlay shaded areas you need to work out in advance if two colors will overlay each other which is the one you want shown (or choose a third color to indicate an overlap - for example you could have yellow for one area, blue for another and green where they coincide).
There are worked examples here which should help you get to where you want to go.
Avatar of vipaman

ASKER

I have reviewed the data at https://peltiertech.com/fill-under-between-series-in-excel-chart/ and rebuilt my data to provide the extra points excel requires (for the left hand side of the graph). I am not 100% certain on the dataset however. In addition when I try and change to area type the graph gets horrendously corrupt. I have followed the process 4 times now with null results. The guide is for 2013 and some of the steps don't quite flow.
graph2.xlsx
Did you get your head around the Area Graph format? You need to look at each point on your X axis and the total amount of area that your Y axis needs given the maximum is 1000m (100%)
See if the sheet I've added helps at all in how you need to calculate the values from your raw data.

If there's any trick to this it's about drawing the area graph first then adding the scatter graph lines afterwards but you need to make sure you've identified all the x-axis points at the outset.
graph-MASQ-example.xlsx
Avatar of vipaman

ASKER

Hi MASQ, I have been trying to understand how to build area graphs. It has become a battle of wills. What I ended doing was creating as a scatter chart and then swapping over to an area chart watching what it did with the points. I realise now that extra data points are required for the origin and you need to put extra points in to keep it tracing along the x axis, if for example it is zero. I have realised with my chart if I layer it correctly by moving the series data I will get what I want. I have a couple of queries on the attached. Why is the green data (crest data) not displaying correctly. The value 400 is specified and only 300 is being displayed?
Why does downwind data (grey area) not extend to 1040. It appears to be constrained by the 900 value of the hill data. I daren't tell you how long I have spent playing with the data. LOL!
Book1.xlsx
ASKER CERTIFIED SOLUTION
Avatar of ☠ MASQ ☠
☠ MASQ ☠

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of vipaman

ASKER

MASQ understand the X axis comment. I am however totally confused why the green cannot overlay? I got the hill - blue area, to overlay the grey, thus creating a zone of influence as I call it. I need to replicate another zone of influence on the wind side - left side of the blue. Am I pushing excel too far with this? My line graphs work perfectly - LOL
Your Blue/Grey area is controlled by the Y axis and doesn't overlay, there are two separate areas of color (stacked areas).
I'm pretty sure that Excel's graphs don't natively allow you to place blocks of color over each other (without faking this and artificially plotting a "third area" to represent the overlap in different color).
Avatar of vipaman

ASKER

Cheers MASQ