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

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

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 (these are Excel’s automatic scale limits in the 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.

Comments

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.

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?

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.

Brian –
This particular code runs pretty fast. It may benefit from insertingApplication.ScreenUpdating = False
right before you define the chart data, andApplication.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.

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.

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.

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

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

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.

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.

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.

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!

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.

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

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

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.

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

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.

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?

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.

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.

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.

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?

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?

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?

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.

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.

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.”

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

What can be done to fix the vertical axis if most of the data is quite close together say between the range of 1500-2000. All my data is pushed to the top area of the chart with the first stacked area series (the transparent one) taking up most of the chart. When I try to reset the axis it reverts back to initiating the scale from 0 automatically.

Hi Jon,
Do you know how to fill areas between 3 lines graph in excel.Let’s say we have 3 line graphs that do not intersect. Is there a way to fill the area between the top two with one color, and the area between the bottom two with another color. Look forward to hearing from you.Thanks,

Jon, thank you for this post!!
But I want to follow up on the question that Jos wrote some time ago, about the Xmin and Xmax in your example being 1 and 7 and not 0 and 8.
You wrote: “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.”
It’s the “based on” that looses me. I replicated your example and tried different values that seems just as valid as your 0 and 8, like 0.5 and 7.5 for instance. Well, for any other values than 0 and 8, the area misaligns.
I tried on my own XY scatter curve with 200 different values of x. There again I had to manually try and find an Xmax so that the area would align properly. After n attempts I got i t kinda ok, but I must say that this Xmax was not found “based on” the scale or anything else..
So, I’m sorry if I’m kind of redundant here, but how do you figure out Xmin and Xmax ??!
Thanks for your time, that would be super helpful!!
Enzo

Hey mate…thanks for the tutorial….I’m trying to do something a lil bit different….I have multiple “top” and “bottom” series, and I wanted to fill the areas between those series….is it possible to have multiple series (for different values in the x-axis, series never cross each other)?

Use a blank formatted series for the area below the lowest “Bottom” series. Use a filled formatted series for the area between this and the lowest “Top” series. So far it’s just like the example in this article. Then use a blank formatted series for the space between the first “Top” series and the second “Bottom”, and a filled series for the space between the second “Bottom” and the second “Top”. Keep using blank and filled pairs for each additional “Bottom” and “Top” pair of series.

Thanks for the quick answer…i tried another thing instead…instead of using different series, i grouped everything in just two series (bottom and fill series)….It’s almost perfect…however, I’m trying to represent the y-axis in log-scale, and I’m having some problems. I changed the formulas from “=C5″ to “=log(C5)” and “=D5-C5″ to “=log(D5)-log(C5)” but the first point isn’t right…is there a way to fix this? Here’s the graph http://www39.zippyshare.com/v/57040962/file.html

How are you getting a logarithmic axis? Are you checking the Logarithmic Axis checkbox in the Format Axis dialog? If so, you don’t need to then use =LOG(whatever) in the data you are plotting. Both primary and secondary series are using the primary Y axis, aren’t they?

I see that the first point is messed up, giving you a triangle instead of a rectangle. The vertex of the triangle is at log(1) on the log scale which is zero on a linear scale. What does the data look like?

I’m doing similar to Marcelo, but my shaded areas need to overlap in both x and y. I can get the overlap in y, but the second blank and filled series adopt the x values for the first set. I can’t find a way to decouple the x values. (I’ve only been on Excel 2013 for a couple weeks, so it feels like I’m starting over on so many things…)

This is great. I could make the chart so easily. However there is one more thing I would like to do. I am presenting sales data of two different regions with the lines. i want the area color to be different when one region is above and different when the other region is up. Is it possible?

You could have both colors in different series in the chart. Formulas in the worksheet would evaluate some condition, and make the source data of one or the other zero if certain conditions are met, so only one color appears.

Thanks for the quick response. Do you mind explaining a little bit how do i set that up. I have been trying to create another “gap” chart and keeping the value inverse of the original gap chart. However i dont get it absolutely correct

Set up your data something like this, with dummy values (1) for the blue and orange series:

Now enter a criterion (Blue) into a cell (A1), and enter formulas in the source data. In this case, I selected B4:C6 and typed this:
=IF($A$1=B$3,2,0)
then held Ctrl while pressing Enter so it was entered in all the selected cells.

Only the Blue series is visible, because the orange values are zero. Change the color in A1 to Orange, and the orange series appears instead:

Thanks again. I think I wasn’t clear in asking my question. can you look at the image below

I made this chart based off your instructions in the blog ( Thank you!!!!) if you see sometimes the orange line is above green and sometimes below. What I want is that the color of the shaded area changes depending which line is on the top. Is that possible?

Hi Jon Peltier,
I tried to follow the steps you have mentioned to fill between two XY series but the things didn’t go as you have explained/shown in your example. Can you please look at my data and point out where I made the mistake. I am using excel 2007. Please follow the below link to see what I did:

I’ve tried to copy your example “Fill Between Two XY Series”. I use the german version of excel but so far I was able to find all buttons. I only have one problem, which regards the point “draw left to right axes”. When I click on this button (I’ve seen that you produced a picture to show the way and I definitely use the same button”) I can’t find “draw left to right axes”. When I click on it it says something like “show none, show the standard axis, show axis in thousands, show axis in millions, show axis in billions….” Only AFTER I’ve turned the area series into a stacked area diagram I can find your described “draw left to right axes” when i click on the SAME button. But as you have described in your instruction, I have to click on this BEFORE.

Do you have a solution for that? Does it make a difference if I choose “XY scatter diagram” or “XY scatter with lines in between”?

https://app.box.com/s/lz9tuly50hfkc6eog3orm4agw5vpi1x6
Hi Jon,
I’m so close but I just can’t figure out what I’m doing wrong, the shaded area doesn’t line up.
I’m trying to show progress versus target of test cases. By date I have the target number of test cases (100%) then 79%, 89% and 97%. I want to shade under 79% red, 80- 89% yellow, 90-97% green and 98-100% white. Then as I fill in the actual test cases it will show the color coding of where we are and the size of any gap.
Help is appreciated. -Delaine

Great tutorial and I’ve enjoyed following the example but I cannot get the last step, i.e. deleting the second horizontal axis to flip the shading under the line in Excel 2003. You mention at the top having made previous versions of this tutorial, would these be more helpful to someone using Excel 2003? Can I find these anywhere?

is this possible in Excel 2003? I’m struggling following the example at the very last step to shade beneath a line. When i delete the second horizontal axis the shaded area moves off the left and remains above the line.

Hi,
Thanks so much for a very awesome tutorial. I followed your instructions for creating the XY Scatter diagram and then shading above, below, between. My problem is that I need 3 axes. My two Primary axes are fine, but I can’t seem to get the Secondary Vertical axes that I need (in date format) without messing up the shading. Can you help?

I have attempted to give you an image website link to an image of what I have thus far…. What I need is another axes on the right, in date format.

Thank you for this awesome tutorial. It has really helped me display the data I have in ways that people understand well. I have run into a tiny snag with the filled in range and I was hoping you could help me trouble shoot. The area chart seems to line up perfectly in the chart except for the beginning of the range where a sliver seems to descend nearly to zero.

https://app.box.com/s/lz9tuly50hfkc6eog3orm4agw5vpi1x6
Hi Jon,
I’m so close but I just can’t figure out what I’m doing wrong, the shaded area doesn’t line up.
I’m trying to show progress versus target of test cases. By date I have the target number of test cases (100%) then 79%, 89% and 97%. I want to shade under 79% red, 80- 89% yellow, 90-97% green and 98-100% white. Then as I fill in the actual test cases it will show the color coding of where we are and the size of any gap.
Help is appreciated. -Delaine

I think my problem is relatively simple, but I can’t quite work it out. Was wondering if you could help?

See attached excel sheet with 3 x graphs at the bottom. I am creating 3 x lines with shaded error bars (se). The issue is I can’t get the “WALKse.mmol LL” (lower limit of WALK se) area to display on the shaded graphs – it seems to get overlapped? I would love it to look like the graph on the right (but show the blue shading at the second nadir in curve)https://www.dropbox.com/s/q5zrqv7avbhr5oj/reward.cgm.meanbytime%20PD.xlsx?dl=0

Really hoping you can help,
Thanks so much in advance, Patrick.

Trackbacks

[…] MrExcel Message Board. Shading under a scatter plot is not easy. John Peltier has a tutorial here: Fill Under or Between Series in an Excel XY Chart – Peltier Tech Blog Can you make the x-axis points be evenly spaced – possibly by adding extra data points? If you can […]

[…] error is some how tied to setting the Secondary Horizontal axis to dates… I originally followed Fill Under or Between Series in an Excel XY Chart – Peltier Tech Blog but I can't get it to work in 2007. […]