We help IT Professionals succeed at work.

Shade an area of a graph in excel

5,971 Views
Last Modified: 2017-11-26
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
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2013

Commented:
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.

Author

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2013

Commented:
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

Author

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2013

Commented:
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).

Author

Commented:
Cheers MASQ
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.