Friday, November 8, 2013

How to fill / shade between lines in an excel graph



I recently had the need to create an Excel chart with the space between two lines shaded.  After searching the Internet I was still confused on the best way.



I figured there had to be a way and after a few hours I came up with the following idea.  The simplest way I have found is to use a mix of stacked bar chart and line charts.  

The first thing to do is layout our data.  For this example I used the following data:




The things to pay attention to in the data set are:

  • The X (horizontal) axis label is offset to the left of the first column of data to let Excel know that this column is the x-axis and not another data set.
  • The second column, “Hidden Base” and the last column, “Bottom” are the same data.  I actually used =C2, =C3, etc… for column F.
  • The Gap column calculates the difference between the Top and the Hidden Base, =E2-C2, =E3-C3, etc..

Select the entire data set, A1 through F8, and create a Stacked Area chart with the following result:


Notice how the x-axis has “Period” as a label.

Next, select the “Bottom” series, which should be on top, by clicking on it and changing the series type to a Marked Line.


Do the same for the “top” series:



Now, select the “Hidden Base” series, right click and select “Format Data Series” and choose “No Fill”, “No Line” and turn off shadows:




Now select the legend and then select the “Hidden Base” key and delete it:



At this point you can adjust the markers, fills, line weights, titles, etc.. to achieve whatever your looking for.
           



You can also adjust the data and the shaded area continues to work well.




 Enjoy!  I hope you find this helpful!




30 comments:

  1. This was excellent and a life saver!

    ReplyDelete
  2. Thanks, this method is a lot less "hackier" than other methods I've seen for accomplishing this. Very helpful walk through as well, thanks again!

    ReplyDelete
  3. awesome!!!! thanks so much

    ReplyDelete
  4. thanks much very helpful to show pricing range

    ReplyDelete
  5. You made something that was extremely complicated and made it as simple as it could be. "Life saver" is appropriate. Thank you.

    ReplyDelete
  6. "Life saver" indeed. I

    I wish more of life complexities could be irrefutably solved so simply.

    ReplyDelete
  7. Great post, thank you! Do you have any thoughts about how to change the color of the gap fill depending on if it's an increase or a decrease from the first line?

    ReplyDelete
    Replies
    1. I've not tried to do that... will give it some thought.. thanks for the idea!

      Delete
  8. The best method I have found thus far to achieve this...bravo!

    ReplyDelete
  9. Legendary, used a few times now! Thanks for sharing.

    ReplyDelete
  10. Still works even now so much better than other more complicated descriptions. Thanks a bunch!

    ReplyDelete
  11. Scott, do you have a spreadsheet for three shaded lines? Please share if so or share some wisdom on how to build upon the two shaded line approach. Thanks.

    ReplyDelete
    Replies
    1. See the newest post, linked at the bottom of this post.

      Delete
  12. not working for me. my data looks more like your second example you posted. is there a different method when they dont intersect?

    ReplyDelete
    Replies
    1. Mark,

      See my comment below... if that doesn't work, reach out to me directly and we'll see if we can make it work for you.

      Delete
  13. i used your exact steps and im not getting the right chart. my chart looks more like the second one you posted, the 2 lines do not intersect. is there a different approach when they dont intersect?

    ReplyDelete
    Replies
    1. thanks for the question!

      When the lines don't intersect try these steps:

      1) Enter your top and bottom data in the top and bottom columns
      2) Change the Hidden Base column to be a copy of the Bottom column like so, "=F2", "=F3" and so on...

      Delete
  14. Thanks man, this was awesome and a huge help for our equity research report.

    ReplyDelete
  15. I would love to understand why this works - why does the "Gap" data attach itself to the bottom and top lines? Is this a deliberate peculiarity within Excel? I have used it and seen it used and it's fantastic, so thank you very much.

    ReplyDelete
    Replies
    1. I think it is that the gap is sitting on top of the hidden base and only goes up to the top line

      Delete
  16. Hi Scott, thanks it works well. Tips to have different colors if gaps is negative or positive: create 2 gaps data, 1 positive and 1 negative. then you can change the color of each.

    ReplyDelete
  17. Simple and worked perfectly! Once I figured out exactly how this was working, it was easy to apply this to 3+ lines/multiple shaded areas on a single graph. Thanks!

    ReplyDelete
  18. its good
    i have one more issues.. what if have 3-4 four scenarios as such and want to show them in same graph

    to be clear...i have 3-4 pair of lines inside which i need it to be filled ..so that i can see the changes...

    ReplyDelete
    Replies
    1. Thanks for the comment - I show an unsatisfying example of this on the related post here: http://www.illuminateandenumerate.com/2015/09/fill-shade-between-lines-in-excel-graph.html

      It will only work if your data is orthogonal

      Delete
  19. Works great however in our company we use smooth line graphs. Is there any way to make a similar graph but with smooth lines? Area chart does not support smooth lines...

    ReplyDelete
    Replies
    1. Great question! I have no idea how to do that :) Anyone else ?

      Delete
  20. amazing. easy. you are great. thank you.

    ReplyDelete