Fill Under or Between Series in an Excel XY Chart

Shading between plotted lines with a light color can enhance some charts. The shading may help to indicate a target range for the data.

I’ve written earlier tutorials about this topic, but I have had to change sequences of steps in the protocol because more recent versions of Excel were not as flexible with order of operations as Excel 2003 (RIP).

XY-Area Combination Charts

Recently in Shaded Quadrant Background for Excel XY Scatter Chart I showed how to generate a background grid of colored rectangles.

XY Chart With Colored Quadrant Background

This technique plotted the XY chart data on the primary axes and the Area chart data on the secondary axes. It also took advantage of a trick using the category axis of an area (or line or column) chart: when used as a date axis, points that have the same date are plotted on the same vertical line, which allows adjacent colored areas to be separated by vertical as well as horizontal lines.

To fill under or between XY series, we’ll make use of this same approach: XY data on primary axes, Area data on secondary axes, with a secondary date axis.

Fill Under One XY Series

Let’s start with the simple case of filling color below an XY plot. Simple data, simple chart of type Scatter With Straight Lines and Markers.

We need to do some calculations to produce data for the area chart series (see below). The min and max X for the XY chart’s X axis are entered in B11 and B12, in this case manually based on the scale in Excel’s XY chart above. To provide reasonable resolution, we’ll scale the area chart’s X data from 0 to 1000. The formulas for the area chart’s X values in column D are simple interpolation formulas to properly rescale the data.

The X values are extended so that the first calculated X value in D5 is repeated in D4, and a zero is placed in D3; also the last calculated X value in D9 is repeated in D10, and the X max of 1000 is entered into D11. These duplicate X values will provide the vertical edges of the shaded region.

The formulas for the area chart’s Y values in column E provide the same Y values for both chart series. The Y values are extended by two cells above and below the calculations, which contain zeros.

The chart already contained the XY data in the first two columns of the data range (B4:C9). Copy the next two columns (D2:E11) , select the chart, then click on the Paste dropdown on the Home tab and choose Paste Special (below left). The Paste Special dialog will appear; make sure the same settings are selected as shown below right.

Here is the computed data and the chart. The added series is an XY chart type, like the first. Don’t change it yet.

Select the newly added series, press Ctrl+1 (numeral one) to open the Format Series Task Pane (Excel 2013) or the Format Series Dialog (2010 or 2007), and under Series Options, select Secondary Axis (below left). Excel adds a secondary vertical axis along the right edge of the chart (below right).

Excel adds a secondary vertical axis along the right edge of the chart.

In Excel 2013, click the plus icon beside the chart, click on the right-facing arrow beside Axes, and check the Secondary Horizontal box. In Excel 2010 or 2007, you’ll have to trudge up the the Chart Tools > Layout tab, click on the Axes dropdown, click Secondary Horizontal, and finally click Draw Left to Right Axis.

Now all axes are present and accounted for.

Right click on the “Area” series (which is still of type XY), and choose Change Series Chart Type. In Excel 2013, the Change Chart Type dialog appears. Click the Chart type dropdown in the Area series row, and select Area or Stacked Area (doesn’t matter which in this case, since there’s only one area series). You actually could have switched the area series to the secondary axis in this dialog (as long as you do it before changing the chart type), but I usually forget that it’s become this simple.

In Excel 2007 and 2010, select Area or Stacked Area from the pop-up window.

The series has now been converted to an area series.

Select the secondary horizontal axis (top of chart) and press Ctrl+1 to open the Format Axis Task Pane or the Format Axis Dialog. Under Axis Type, select Date Axis (below left). The area chart series is now perfectly aligned with the XY series. Since it’s plotted on the secondary axis, the area series fills the space between the secondary horizontal axis at the top of the chart and the data points. So it’s above the data, not below. But we’re clever enough not to panic.

Delete the secondary vertical axis (right edge of chart) and the secondary horizontal axis (top of chart). The fill moves into position below the XY plot.

Fill Between Two XY Series

The protocol for filling between two plotted lines is pretty much the same as above. We start with two XY scatter chart series, one above and one below the filled area. We will also need two stacked area chart series, one for the clear region below the lower XY line, and one stacked on top to fill between the XY lines.

Here is the data and the initial chart for the two XY curves. It takes up space, but I’ll leave the legend there until the end to help us keep track of which series is which.

The formulas to calculate the area chart X and Y values are like those used in the simpler example above. Note that the Bottom Area data is the same as the Bottom Line data, while the Delta Fill data is the difference between the Top Line and Bottom Line data.

Copy the area chart data (E2:G11) , select the chart, then click on the Paste dropdown on the Home tab, choose Paste Special, then make sure the settings are correct. The new data is added as additional XY series, which is fine.

Select one of the Area data series (still plotted as XY), press Ctrl+1 to open the Format Series Task Pane or Dialog, and choose Secondary Axis. Excel gives us the secondary vertical axis along the right edge of the chart. Repeat for the other Area data series.

In Excel 2013, click the plus icon beside the chart, click on the right-facing arrow beside Axes, and check the Secondary Horizontal box. In Excel 2010 or 2007, you’ll have to trudge up the the Chart Tools > Layout tab, click on the Axes dropdown, click Secondary Horizontal, and finally click Draw Left to Right Axis.

Right click on the “Area” series (which is still of type XY), and choose Change Series Chart Type. In Excel 2013, the Change Chart Type dialog appears. Click the Chart type dropdown in each of the Area series rows, and select Stacked Area. You could have switched the area series to the secondary axis in this dialog (as long as you do it before changing the chart type).

In Excel 2007 and 2010, select Area or Stacked Area from the pop-up window. The chart probably looks like it’s broken right about now, but don’t panic. Right click on the other area series, choose Change Series Chart Type, and again select Stacked Area.

Select the secondary horizontal axis (top of chart) and press Ctrl+1 to open the Format Axis Task Pane or the Format Axis Dialog. Under Axis Type, select Date Axis. The area chart series are now perfectly aligned with the XY series. Since they’re plotted on the secondary axis, the Bottom Area series fills the space between the secondary horizontal axis at the top of the chart and the Bottom Line data points. So it’s above the data, not below. The Delta Fill area has filled the space between the XY lines.

Delete the secondary horizontal and vertical axes. The Bottom Area now fills below the Bottom Line, while the Delta Fill fills between Bottom Line and Top Line.

Format the Bottom Area series so it has no fill.

Finally, delete the legend.

Fill Between Overlapping Regions

It is possible to extend this approach even further. Suppose we want to fill between two pairs of lines. The following simple data has four series, min and max for set A and min and max for set B. We want to fill between Amin and Amax, and between Bmin and Bmax. If these areas overlap, we’d like to see both.

We can set up the area chart data below, where we have an area below Amin (“Bottom”), one between Amax and Amin (“Afill”), one between Bmin and Amax (“Blank”), and one between Bmax and Bmin (“Bfill”). The “Blank” area between Bmin and Amax extends from Bmin down to Amax, if Bmin is greater, or from Bmin UP to Amax, if Bmin is smaller.

I won’t go through the whole procedure, because it’s really the same as filling between two lines, above. The lines are shown on the left, A in blue, B in orange. The finished overlapping filled regions are shown on the right, again A in blue and B in orange. I’ve used a transparency setting of 50% so we can see gridlines and other data behind the area fills.

The four charts below show each area in turn with the others hidden. First, “Bottom” fills from the axis up to Amin, then “Afill” fills from Amin up to Amax, “Blank” fills from Amax up (or down) to Bmin, and finally “Bfill” fills from Bmin up to Bmax.

Peltier Tech Chart Utility

Comments

  1. Hi Jon,

    I have an immediate need for exactly this solution.
    I was asked to chart statistics from our help desk to reflect the abandoned calls versus the total calls. I’ve been struggling with this for a bit to see if I could get it to work.

    Thank you for a very timely post.

    Cheers

  2. Hi Jon

    Thanks you so much for the informative posts, they have helped me countless times.

    I do however, have a question that I’m struggling to find a straight answer to on the net. That is, I want to know how to improve the speed of VBA scripts which are creating and using many charts with many data series. Being an engineer, I like to push things to the limit so I went bananas with producing many charts from large data sets produced from batch studies. This code works perfectly, however, I find it to be a bit slow for my liking. I also find that it gets slower the longer it runs. Assuming that this was a memory leak issue, I decided to watch the performance monitor whilst running the script and saw that the memory usage of Excel steadily increases. I tried to clean this up a bit by setting unused objects to nothing but the problem is still there. Is the lack of speed perhaps due to the chart being activated when it is created, is there a way to deactivate it and add series and formatting without using the ActiveChart command?

    I am using similar code to your previous examples:

    Sub EmbeddedChartFromScratch()
    Dim myChtObj As ChartObject
    Dim rngChtData As Range
    Dim rngChtXVal As Range
    Dim iColumn As Long

    ‘ make sure a range is selected
    If TypeName(Selection) “Range” Then Exit Sub

    ‘ define chart data
    Set rngChtData = Selection

    ‘ define chart’s X values
    With rngChtData
    Set rngChtXVal = .Columns(1).Offset(1).Resize(.Rows.Count – 1)
    End With

    ‘ add the chart
    Set myChtObj = ActiveSheet.ChartObjects.Add _
    (Left:=250, Width:=375, Top:=75, Height:=225)
    With myChtObj.Chart

    ‘ make an XY chart
    .ChartType = xlXYScatterLines

    ‘ remove extra series
    Do Until .SeriesCollection.Count = 0
    .SeriesCollection(1).Delete
    Loop

    ‘ add series from selected range, column by column
    For iColumn = 2 To rngChtData.Columns.Count
    With .SeriesCollection.NewSeries
    .Values = rngChtXVal.Offset(, iCOlumn – 1)
    .XValues = rngChtXVal
    .Name = rngChtData(1, iColumn)
    End With
    Next

    End With

    End Sub

    Sorry this comment isn’t really related to this particular post but I couldn’t work out how else to ask you. If you do have an answer, a post on improving speed whilst working with charts in VBA would be much appreciated.

    Thanks.

    Kind regards
    Brian

  3. Brian -
    This particular code runs pretty fast. It may benefit from inserting
    Application.ScreenUpdating = False
    right before you define the chart data, and
    Application.ScreenUpdating = True
    right before you exit the sub.
    You might like to read my piece on Fixing a Recorded Macro, which covers a number of other ways to improve recorded code.

  4. where do i click Draw Left to Right Axis?

  5. Samuel -
    Excel 2007 and 2010: Chart Tools > Layout tab > Axes dropdown.
    Excel 2013: Click the plus icon next to the chart and click on Axes.

  6. Thanks Jon for the useful post. I could create a chart I was looking for with the method you provided above!!

  7. Any idea how I can do this on xy-series which is on a log-log scale? I’ve managed to get to the point where I need to delete the secondary axis and ‘flip’ the area, but it doesn’t work…

  8. Paul -
    Is it fine up to that point, or is it not really following the example?

  9. I got the example to work and I’m trying to apply it to new data on a log-log scale (y = 1E-05 to 1E-01 and x = 1 to 1000).

    I managed to get so far by basing the area chart on the log of the xy base data. However when get to the step where I delete the secondary x and y axis it can’t seem to cope. I ended up getting something by colouring the secondary axes white, setting the chart background blue and setting the 1st series area (above the xy line) white. Not elegant but just about gave me what I needed! Would be interesting if there is a better way to do this as I’m often plotting log-log charts.

  10. I am trying to do something similar to the “Blank” Example above. I have two sets of data; receipts per month and completions per month, and would like to highlight the areas between each. I’m not sure if the overlap in the data (see below) plays a factor, but would appreciate any assistance.

    Month Receipts Completions
    Apr ’13 689 669
    May ’13 857 796
    Jun ’13 780 740
    Jul ’13 655 738
    Aug ’13 726 507
    Sept ’13 943 534

    Here’s a link to a quick graph to show the overlaps. http://i.imgur.com/fLb5wbl.png

    Thanks in advance for your help.

  11. I kept going through your tutorial and have gotten everything to plot, but for some reason it won’t align correctly. I’m not sure if it has something to do with the formula used in column E of your tutorial or not. Here is a link of what is happening.
    http://i.imgur.com/SEzHhS9.png

  12. Tim -
    I’ve recreated the chart, using my protocol and your data, and I found the problem.
    Fix the last three X values. The last one should be 1000, and the two before should be 857.1 (that is, 6/7 of 1000).

  13. Tim -
    Also, get rid of the 0 and 7 at the bottom of the first column, to avoid confusion.

  14. Hi

    I managed to copy tutorial you made for filling between chart.

    Anyway, is there anyway to control the order of the chart animation when we use it on excel, because on the example made, the order comes where the fill between chart come in first before the chart it self.
    The aim is to animate the low and down chart first, and then the fill comes next.

    Any help would be appreciated

  15. I think PowerPoint just animates in stacking order of the chart series. If so, the fill appears first, then the lines.

  16. Hello,
    I’d first like to thank you for the helpful advice you’ve provided thus far. I have a question regarding how to plot the area under the curve for a normal-log chart (so say, a y-axis going from 0 to 100, and an x-axis going form 1 to 100,000). I’ve tried the method you’ve described here, and it doesn’t seem to line up with the log scale very well. I’ve also tried integrating the functions for the values described and plotting that, but that didn’t work, either. Any advice or help on this matter would be appreciated.

  17. Roland -
    You will need to use a two-step transformation of the X axis values. You start with 1 to 100,000. First you take the logs of these, so you have 0 to 5. Second you have to spread this out on a date axis with sufficient resolution, to get from 0 to 1000.
    Basically, the formula in D5 of my example becomes
    =1000*(log(B5)-log(B$11))/(log(B$12)-log(B$11))
    The X axis that has the XY series should be converted to a log scale, but the date axis for the area series is a linear axis based on the logs of the values it is based on.

  18. Hi Jon,
    Thanks for your prompt response. I did as you said, and the result is that I get an area chart that seems to be the right shape overall, but is offset on the X axis. Here’s a print screen of the problem:
    http://i1213.photobucket.com/albums/cc480/rothgar13/OffsetIssuePrintScreen_zpse4e560bb.png
    Thanks for any advice on this matter.

  19. Roland -
    Looks like your formulas are using 10^5 instead of 10^6 for the maximum.

  20. Hi Jon,

    Similar to one of the questions already asked, I don’t see “draw left to right” for the secondary horizontal axis in the dropdown in Excel 2010. Instead the dropdown includes: show axis in thousands, show axis in millions, etc. What do I need to do to see that drop down?
    Thanks!

  21. David -
    Select the chart, than go here on the ribbon:

  22. Thank you, I have been looking for these for weeks now! But I’m having trouble following you cause my graphs never fully align
    http://postimg.org/image/oijxvpj89/

    I’m using excel 2013

  23. Angel -
    Change the chart type of the blue line from line chart to XY scatter.

  24. Hi Jon,

    Great instructions. I have an Excel 2010 line chart with both year and quarter labelled on the X axis. I want to shade between the lines to the right of the green dashed line, i.e. between the purple and orange line and also between the purple and blue line.

    http://s8.postimg.org/5wlqpu99x/Paul_Chart.png

    Can it be done?

    Thanks a million

    Paul Corcoran

  25. Paul -

    That can certainly be done, following exactly the protocol in this tutorial.

  26. Is there a way to make the area fill color change when it passes a certain threshold. To make it turn red as it surpasses 5% and green when it goes below.

  27. dear sir
    i’m a civil engineer
    in my excel sheet after i applied ur way to draw a filled moment diagram! with a execl chart ,but the excel keep “crash” when i click on it!

  28. soco -
    What version of Excel? How complicated is the chart (how many series, etc.)?

  29. Hi Jon,
    I have a simple graph showing Hours Variation from Target. The Target is always 0, so it’s a horizontal line in the middle of the plotting area. The Hours Variation points are plotted above target (positive numbers) and below target (negative numbers) to show late or early submission of reports.
    Using Target as a reference/baseline, I’d like to shade the area between Target and any points above Target in red (they are late submitting), and the area between Target and any points below Target in green (they have submitted early).
    An Area Chart looks like exactly what I need – I just need to be able to change colour based on position above or below the zero line.
    I’m running Access 2007 btw.
    Any advice would be most welcome!
    Regards,
    Ernie

  30. I don’t know about Access… But for an Excel area chart, try Area Chart – Invert if Negative.

  31. Hi Jon,
    Thanks so much for your help! I’m onto it already.
    (I actually meant Excel – not sure what happened to my fingers there – sorry.)
    Regards,
    Ernie

  32. Hi, thanks for the information above, however I am really struggling getting it to work for my purpose!
    Basically I have a plot which has depth on the y axis and strength on the x axis. with depth the strength generally increases however we add a factor to the original data which gives us our 2 series. The example above is great for if you want the shading focussing in the x axis direction. how do I get it to focus ion the y direction?
    I’m in a new job and really could do with impressing my boss so would be really grateful if someone could help. if you need anymore information please email me and I can send a screen shot to help explain.
    Many thanks,
    Adam

  33. About the ‘Fill Under One XY Series’, I am really struggeling to manage a range that contains negatvie values i.e. (min =-120 | max =120). Can one provide an example please?

  34. Jason -
    The routine actually doesn’t “fill under” a series, it fills between the series and the horizontal axis. You would need to use the approach like that for filling between two series, and the lower filled area (the one that is made totally transaparent) would be a horizontal line across the bottom (most negative value) of the chart.

  35. thanks Jon. I am somewhat stucked with this. Could you help me with the right outer part please? https://db.tt/D0ncWto6

  36. Jason -
    Here’s the data.

    Here are the steps to building the chart (click to open a full-size view).

  37. Hi Jon,
    I’m facing nearly the same problem : x-axis goes from -6 up to +6 under a standardized normal distribution.
    Everything works fine until the point when the chart-type is changed to a stacked area.
    My 2nd horizontal axis when changed to date-type starts with zero and it is impossible for me to align the curves properly.
    If could give some advice I’d be grateful for that.
    Thanks in advance for your kind assistance.
    Best regards
    Lutz

  38. Lutz -
    Make sure the calculations of the scaling of the X axis has been done properly (column E in my example).

  39. Hi Jon, thanks a lot for helping me out in details. One last question, if I want to start the chart plotting at ’0′; what would I need to do https://db.tt/szLmcIne ?

  40. Jason -
    You mean at X=0? Start the data at X=0 instead of X=1.

  41. Adam -
    You have a data sample to help me see what you need?

  42. As near as I can figure, this doesn’t work if the initial series you want to shade under is constructed so that it is rectangular.

    I did this by creating the vertical and horizontal faces of the rectangles, basically so that an XY looks like a bar chart. This is the primary series I want to shade under.

    The thing is, there is no equivalent for the shaded series. The 2-d area chart you recommend does not literally “connect the dots”. Instead, the connections are interpolations that are all at angles. So, the shading does not match the line.

    You can see what I’m trying to produce in the top post entitled “Correcting the Wall Street Journal” at this link http://suumacroblog.blogspot.com/

  43. Dave -
    That original chart is pretty bogus. The year axis isn’t drawn proportionally, so it’s not as much of a timeline that it appears. The spacing between years is not proportional, so I don’t know what to make of the widths of the bars.
    In any case, the shape of the XY series should have no bearing on this technique, as long as it doesn’t ever move backwards.
    Could you post your data so I can look at it?

  44. I followed the link to the NBER trough/peak data. I must not be thinking clearly tonight, because I can’t visualize how they turned a table of months and years into a square timeline.

  45. Sorry Jon … Super Bowl … offline for many hours …

    There are two figures. The top figure was published in The Wall Street Journal on Friday. Yes, it is bogus (but not for the reasons you cite). The width of the bars appears accurate. The widths between the bars are not accurate. The labels along the horizontal axis are “accurate”, but are for the left side of each bar.

    The bottom one is my version. There’s includes only expansions. Mine includes both expansions and recessions/contractions. The widths, heights, and areas above or below the 0% axis are correct in my diagram. But I can’t figure out how to shade them.

    There is some spreadsheet work involved in the background that I won’t bore you with. The data that is appropriate for my chart (the second one) is pasted below. Connect the dots, and you get my chart.

    1953.375 0.000
    1953.375 -0.024
    1954.375 -0.024
    1954.375 0.040
    1957.625 0.040
    1957.625 -0.039
    1958.375 -0.039
    1958.375 0.056
    1960.125 0.056
    1960.125 -0.004
    1961.125 -0.004
    1961.125 0.049
    1969.875 0.049
    1969.875 -0.001
    1970.875 -0.001
    1970.875 0.051
    1973.875 0.051
    1973.875 -0.025
    1975.125 -0.025
    1975.125 0.043
    1980.125 0.043
    1980.125 -0.043
    1980.625 -0.043
    1980.625 0.043
    1981.625 0.043
    1981.625 -0.010
    1982.875 -0.010
    1982.875 0.043
    1990.625 0.043
    1990.625 -0.026
    1991.125 -0.026
    1991.125 0.036
    2001.125 0.036
    2001.125 0.006
    2001.875 0.006
    2001.875 0.028
    2007.875 0.028
    2007.875 -0.029
    2009.375 -0.029
    2009.375 0.024
    2013.875 0.024

  46. Convert the fractional years to dates (e.g., 1953.375 = 5/16/1953). Now you don’t have to fuss with converting axes, etc.

    If you want to show up and down areas in different colors, use a formula to put MAX(value,zero) in one column and MIN(value,zero) in the next.

    In the following screen shot, your data is in columns A and B. To compute the date, this formula is in D2 and filled down to the end of the data:
    =DATE(INT(A2),1,(A2-INT(A2))*365.25)

    To separate into positive and negative values, cells E2 and F2 contain these formulas, which are filled down to the end of the data:
    =MAX(B2,0)
    =MIN(B2,0)

    At this point you just have to copy the data in D:F and insert an area chart. The date axis is used automatically, you just need to apply some formatting.

    And I doubt you need the line, but if you do, it’s probably easier to make a line chart with column D as the X values and the original data in column B for the Y values.

    Area chart showing economic conditions

  47. Thank you very much. I will digest this on Monday.

    FWIW: The dates are in a format I prefer for the purposes of the chart (and the underlying raw data).

  48. Digested. Yes, this works. I will credit you, as is appropriate.

    FWIW: I want the dates in that particular format generally … but I’ll make an exception for such a slick method. I didn’t realize that I wasn’t thinking outside the box when I chose an XY chart in the first place.

  49. Great post, thank you.

    I was able to match your examples, but when I tried it with my data the location of the shaded area did not quite match up with the lines: http://www.flickr.com/photos/37472531@N03/12480046905/

    Thank you for any help you can provide.

  50. It’s probably a mismatch between the primary and secondary horizontal axes.

    The reason to use the complicated approach here is that area charts use a line chart’s axis, which by default does not show X values proportionally, doesn’t consider fractional numbers, and also doesn’t allow for an area with a vertical edge. We convert the axis to date scale so we can show points at irregular horizontal intervals and also show vertical area edges.

    Since your data is equally spaced whole numbers, we can make the chart much more easily.

    First, you can use a line chart for the original values. Convert the axis to a date-scale, to get the vertical edge. Use the year values as the X values for all four series. There is no need to use both primary and secondary axes.

    Prepare the data by inserting a row before 1983, repeat 1983 in the first column, leave the next two cells blank, and put zero into the last two cells. Add a row at the end, repeat 2013, leave the next two cells blank, and make the last two cells zero.

    Plot all four series as a line chart. Convert the last two series into area type, and format the lower series with No Fill (I just made it transparent below, so you can still see it).
    Convert the axis to date scale, set the minimum to 1980 by entering 6/2/1905 in the box, and the maximum to 2015 by entering 7/7/2015 (just type the years into a cell, and format as a date). Use 5 for the major tick spacing.

  51. That fixed it. Thanks, Jon! I really appreciate the help.

  52. Thanks for your great posts!

    First, I have used your earlier post – Histograms Using Excel XY Charts – to be able to make a dynamic histogram which excludes #N/A. The reason is because I want to be able to change the data inputs, which allows the histogram to automatically fit the lowest and highest x-values in the sample used. Second, I have now tried to fill the “bars” (below the XY-chart) using this post. If I let the primary x-axis’ MIN and MAX be set automatically, the Stacked Area chart (on the secondary x-axis, using the date format) will mismatch the XY-scatter perfectly. Using manually MIN and MAX on the primary x-axis (sat equal to MIN and MAX from the calculations in this post), I am able to match the two series, but then the histogram is not dynamic as i would like it to be. Do you know if there is a way to make the histogram dynamic?

    Best Regards;
    Tom

  53. How can the MIN and MAX functions in your example return 0 and 8 while they are obviously 1 and 7? Can you show the formulas you used there?

  54. Jos -
    No formulas. Those are values I typed into the cells, based on how Excel plots the XY data. I’ve tried to clarify this in the text.

  55. Thank you for this very informative post.

    I would like to create 2 shaded series, similar to this plot: http://jvoigts.scripts.mit.edu/blog/assets/plot_shaded_pretty.png

    I simply create two sets of data, in the same order as above, but the result is not correct. It seems that excel chooses two different secondary axes for the two sets, which messes things up. When I remove all data from one of the series from the chart, it looks fine again. Is it possible to solve?

    Christoffer

  56. Looks great, but I messed it up. Is there a complete demo-file to download?
    thanks

  57. Hi,
    Perhaps i’m missing the obvious, but how could I get it to work with dates on the X axis?
    I have monthly data that I would like to use this idea for.
    For example:
    date low high

    01/01/2013 35.12 69.28
    01/02/2013 33.12 65.34
    01/03/2013 31.34 61.83
    01/04/2013 29.77 58.73
    01/05/2013 28.39 56.00
    01/06/2013 27.14 53.54
    01/07/2013 26.01 51.31

  58. ok, I WAS missing the obvious, I just had to change it to Stacked Area rather than just Area Chart!!
    Thank you for this article!!

  59. I need to highlight a range within a chart to show whether the data points fall within, above or below average.

    I can’t seem to figure out how to make this work. PLEASE HELP!

  60. Hi Jon,

    I’m having the same issue as Adam Barnard on 1/22/14. I’m working with depth profiles and am having a hard time with the fill area in the horizontal direction. I’ve got depth from 0-60 meters and dissolved oxygen values from 0-10 ppm on the x-axis. Any suggestions?

    Thanks,
    Alicea

  61. Brittany -

    Check out Excel Charts With Horizontal Bands.

  62. Jon –

    I’m not sure if the horizontal bands link was directed at me, but it doesn’t quite help me out. I’ve got the following graph and I want the area to the left of the blue bands shaded:

    Alicea's Chart

    I think the photo will help describe what I’m needing help with.

    Thanks,
    Alicea

  63. Alicea -

    Keep in mind how area charts fill in areas. They fill from a lower point to an upper point. If a second area intrudes between these two points of a first area, the first area can’t fill partway, stop when the second area intrudes, then continue above. This means that you’ll have to fill the areas piecemeal, which I have partly done below (it’s slow and tedious). Even if all you wanted was to fill between the two lines, you still need all of these areas, just many of them will be transparent.

  64. Hello, i appreciate your work, it helped me a lot with presenting my data.
    I am a PhD student in agriculture, and for my needs, have to combine a chart like those in this tutorial, i made it to present climate data and construct a Walter-Lieth climate diagram which is not possible with the base chart types in excel. after making it, i used your tutorial for a gnitt chart to make a feeding timetable for animals.
    Is there any possibility to combine these two charts so that the major vertical grid lines overlap? The gnitt chart has to be attached at the bottom of the XY chart?
    If further feedback on this is needed i can send a link with both of my charts.
    Thank You in advance.

  65. @Elmir Sedic

    I am interesseted in visualizing climate-data too. how can I get in touch with you ?
    Kurt (kurtwerbe-mailATyahoo.de)

  66. @Kurt

    You can mail me to sedic.elmir@hotmail.com or e.sedic@univpm.it for further communication.

  67. Jeff Phillips says:

    I ran into a problem using Excel:Mac 2011, where formatting the secondary horizontal axis as “Date” failed with an error. The fix is in Excel Preferences, Calculation, check (on) “Use the 1904 date system.”

    That was an interesting problem to track down!

  68. Hi Jon,

    What about shading in between two vertical lines?

    i.e. two quartiles on a chart.

    Thanks,
    Joe

  69. @Joe
    Its possible, just as shown by Alicea, its just important to set the limits of your background fill and the highlighted area.

  70. Thanks Jon, great stuff….owe you a lobster dinner!!

  71. Jon,
    Thanks for the great post. I am running into the same problem as Tim Hoy did, I cannot get my two graphs to align.
    Here is a link to the chart. Is my problem that my origin is zero?
    any help would be greatly appreciated

    http://imgur.com/3eXlYZp

Subscribe without commenting

Write a Comment

I welcome comments from my readers. If you have an opinion on this post, if you have a question, or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion. If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.

Subscribe without commenting

Peltier Tech Chart Utility

 

Create Excel dashboards quickly with Plug-N-Play reports.

 

Site by: Dawud Miracle, Business Coach & WordPress Websites