Saturday, September 19, 2015

Part 2 - How to Fill / Shade between lines in Excel Graph

Thank you everyone for the great comments!

This is a brief follow up to the original article found here: http://www.illuminateandenumerate.com/2013/11/how-to-fill-shade-between-lines-in.html

This follow up clarifies how to set up the data set, and attempts to answer the question about having three data lines.


How to Set Up the Data

In the first post I used the following data set where Bottom was a copy of Hidden Base:


From the first article:
  • The second column, “Hidden Base” and the last column, “Bottom” are the same data.  I actually used =C2, =C3, etc… for column F.

It works better the other way around!

  • Make the Hidden Base a copy of the Bottom as in, =F2, =F3, etc..
This way it's obvious that the Bottom column is your bottom data set.

You can download my example workbook here: Example Excel Workbook



Three Data Lines

Someone asked about having three data lines.  It works as long as any data line only intersects one adjacent data line.  This is because the gap is simply the difference between two adjacent lines.


This is an example where the top data line dips below the middle data line.


When the top line dips below the middle and the bottom, then we see this:

Gap 2 is showing the distance between the top and middle lines.

This is also in the example workbook above.

Thanks again for all the great comments!

Scott


5 comments:

  1. Excel 2013 doesn't have the option to change type for individual series in the chart. :( Is there still a way to create this type of chart in Excel 2013? I'd like to have a column type chart with a grey "cut-off zone" (line1=average+SD; line2=average-SD; and the area in between would be filled grey) to see which samples are within the allowed variation of the zone and which values are over that.

    ReplyDelete
  2. In Excel 2013 I am able to right click on the chart series and choose the option, "Change Series Chart Type"... I'll add a screenshot

    ReplyDelete
  3. Show me for an semilog plot scatter chart

    ReplyDelete
  4. Thank you SO very much for sharing this! I just used your template at work and saved it to my team's Templates folder as it will be invaluable for all of us forever.

    Truly, your work is admired and appreciated! Especially compared to the only other source I found online which was waaaay too long and felt too complicated.

    Nat in Canada

    ReplyDelete