XY Charts

Histogram With Normal Curve Overlay

Monday, November 6, 2017 by Jon Peltier 6 Comments

Monday, November 6, 2017 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

The Problem

I received an email from a university professor who wanted to show a histogram of model residuals overlaid with a normal distribution curve. He wanted the histogram to be displayed as filled bars.

Histogram of Model Residuals, Overlaid with Noral Curve

You can follow along with this tutorial using the sample workbook at the link below:

The Normal Curve

The normal curve data is shown below. C1 and C2 have the normal distribution mean and standard deviation. Column E has the values for which we’ll plot the normal distribution (from -380 in cell E3 to 380 in cell E41), and column F has the calculated distribution values. Cell F3 has this formula

=NORM.DIST(E3,$C$2,$C$3,FALSE)

and this is copied down to cell F41. Cell C5 has the maximum calculated distribution value (which is in cell F22), and cell C6 has the maximum height of any of the histogram bars. These latter values are used in column G, which “normalizes” the normal curve to the histogram, using this formula in cell G3:

=$C$6/$C$5*F3

which is filled down to cell G41. We normalize these normal distribution values so that the normal curve and the histogram can be plotted on the same vertical axis scale.

Chart 1 is an XY Scatter Chart (lines and no markers) showing the normal data from columns E and F:

Chart 1: Normal Distribution Data | Peltier Tech Blog

Chart 2 shows the normalized normal data from columns E and G:

Chart 2: Normalized Normal Distribution Data

We’ll return to chart 2 in a moment.

The Histogram

The residual data is shown in P2:Q8, where P3:P8 holds the bin edges, and Q3:Q7 the counts between these values:

Residual Data: Binned (left) and Reformulated for Plotting (right)

I’ve reformulated the residual data in columns S and T so I can plot the outlines of the histogram columns. This data is plotted in an XY Scatter chart, chart 3:

This is right out of my tutorial, Histograms Using Excel XY Charts.

If you don’t needed your histogram to be shaded, you could easily combine these two data sets into a simple XY Scatter chart (chart 4):

Chart 4: Easy XY Scatter Chart Histogram with Normal Curve Overlay

But shaded histogram bars require a little bit more work, as described in my tutorials Filled Histograms Using Excel XY-Area Charts and Histogram Using XY and/or Area Charts. To include the normal curve, you’ll need a combination chart, which I’ll show in the next section.

For the combination chart, I’ve modified the residuals data in columns AC and AD. The X values cannot be negative (I’ll tell you why later), so I’ve increased the X values by 400. I’ve also included extra zero values at the minimum (X = 0) and maximum (X = 800).

Modified Residual Data for Line and Area Charts

Chart 5 shows this data in a Line chart:

Chart 6 shows this data an Area chart:

I’ve formatted the area chart series with a medium blue outline (the default is no outline), and a light blue fill with 50% transparency, to let the gridlines show through.

We’ll ignore the line chart (chart 5), since it results in unfilled bars, and continue with the area chart (chart 6). Even though the area chart doesn’t give us nice bars (yet), we’ll beat on it until it does.

In Chart 7, I’ve set my X axis scale to be a Date scale:

Chart 7: Histogram Created by Treating X Values as Dates

The values in column AC are now treated as dates. In Excel there are no such thing as negative dates, so we had to offset these histogram X values by 400, so they are all greater than or equal to 0. A Date scale axis automatically uses the minimum and maximum X values as its endpoints (0 and 800), the X values are plotted proportionally, and each time an X value is repeated, the Y values are plotted on the same vertical line at that X value. This gives us our nice histogram bars.

Combination Chart with Normal Curve and Histogram

Chart 8 is the original normal curve from chart 2:

Copy the residuals data in AC:AD, select the chart, and use Paste Special so the data is plotted as a new series with X values in the first column and series name in the first row:

Chart 9 is the result. The histogram is plotted as a second XY Scatter series, and it’s offset to the right by 400.

Chart 9: Normal Curve with Offset Histogram

In chart 10, I’ve changed the series type of the histogram data to an Area chart, and moved it to the secondary axis. It looks weird, but it’s too early to panic.

In chart 11, I’ve formatted the secondary horizontal axis (top of the chart) so it uses a Date axis scale. Don’t worry about formatting this axis. The bars are in the right place, just colored outside, not inside, the outlines.

Chart 11: Date Scale Fixes Histogram Shape

In chart 12, I’ve hidden the secondary horizontal axis (don’t delete it). I did this by changing the line to no line, and changing the labels to no labels.

Chart 12: HIding Secondary X Axis Nearly Finishes the Combo Chart

In lucky chart 13, I’ve selected and deleted the secondary Y axis (right side of the chart). This makes the area chart fill below the line, so inside the bars. And the chart is complete.

Chart 13: The Combination Chart Is Complete

Here are a few tutorials about Histograms:

Here is one about filling below a normal curve:

Fill Under a Plotted Line: The Standard Normal Curve

Area Chart Shading Under XY Chart's Line

Here are some tutorials about shading of charts:

Posted: Monday, November 6th, 2017 under Chart Axes.
Tags: Area Charts, Combination Charts, Histograms, Statistics, XY Charts.
Comments: 6

Bar-Line (XY) Combination Chart in Excel

Friday, February 19, 2016 by Jon Peltier 19 Comments

Friday, February 19, 2016 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

Combination charts combine data using more than one chart type, for example columns and a line. Building a combination chart in Excel is usually pretty easy. But if one series type is horizontal bars, then combining this with another type can be tricky. I’m here to help with Bar-Line, or rather, Bar-XY combination charts in Excel.

Combination Charts in Excel

I’ll illustrate a simple combination chart with this simple data. The chart will use the first column for horizontal axis category labels, the second column for actual values plotted using lines with markers, and the third column using columns (vertical bars).

We start by selecting the data and inserting a column chart.

We finish by right clicking on the “Actual” data, choosing Change Series Chart Type from the pop-up menu, and selecting the new chart type we want. I’ve also used a lighter shade of orange for the columns, to make the markers stand out better.

Let’s do the same for a bar chart. Select the data, insert a bar chart.

Okay, the category labels are along the vertical axis, but we’ll continue by changing the Actual data to a line chart series. That didn’t work out at all. The markers are not positioned vertically along the centers of the horizontal bars, nor horizontally where the data lies in the Actual column of the worksheet.

In the chart below I’ve shown all axis scales and axis titles to illustrate the problem. When we converted the Actual series to a line type, Excel assigned it to the secondary axis, and we have no ability to reassign it to the primary axis. The primary axes used for the bar chart are not aligned with the secondary axes used for the line chart: the X axis for the bars is vertical and the X axis for the line is horizontal; the Y axis for the bars is horizontal and the Y axis for the line is vertical.

We can’t use a line chart at all. If we want to line up the markers horizontally with their proper position along the lengths of the bars, we need to use the Actual data as the X values of an XY series. We will need to generate some additional data for the Y values of the XY series.

Bar-XY Combination Chart

We will not try to make a Bar-Line combination chart, because the Line chart type does not position the markers where we want them. We will make a Bar-XY chart type, using an XY chart type (a/k/a Scatter chart type) to position markers.

Here is the new data needed for our Bar-XY combination chart. The factor labels and Target values will be used by the Bar chart series, and the Actual values and Heights for the XY series. Don’t worry about the Height values: I’ll show how they are derived in a moment. The nice thing is that we can use dummy values now and type in the proper values later and the chart will update.

Select the first two columns of the data and insert a bar chart.

Since we probably want the categories listed in the same order as in the worksheet, let’s select the vertical axis (which in a bar chart is the X axis) and press Ctrl+1, the shortcut that opens the Format dialog or task pane for the selected object in Excel. Check the box for Categories in Reverse Order and also select Horizontal Axis Crosses at Maximum Category to move it next to Factor 5.

Bar chart with categories in reverse order

I’ve also recolored the bars orange, because blue markers show up better against light orange than orange markers against light blue.

Now copy the last two columns (Actual and Height), select the chart, and on the Home tab of the ribbon, click the Paste dropdown arrow, choose the options in this dialog (add cells as new series, values in columns, series names in first row, categories in first column), and click OK.

The data is added as another set of bars, which I’ve colored blue, but we’ll change that in a second.

Right-click on the added series, select Change Series Chart Type from the pop-up menu, and select XY with markers and lines.

We see that the horizontal positions of the markers is just what we want to show.

Now we can see where the values in the Heights column comes from. The right hand vertical axis is used for the Y values of the XY series. Looking at the positions of the horizontal bars and the markers in their correct positions, we can see that the Factor 1 bar is centered on Y=4.5, the Factor 2 bar is centered on 3.5, etc. If you hadn’t guessed this at the beginning, type these values into your data range, and let the chart update.

Calculation ov Heights (Y values for XY series)

A few minor changes and we’ll be done. First, change the name of the XY series from Heights to Actual. The easiest way is to click on the series, then look at the highlighted ranges in the chart. The X values (C2:C6) are highlighted purple, the Y values (D2:D6) are highlighted blue, and the series name (cell D1) is highlighted red (highlight colors in Excel 2010 and earlier are different, but the concept is the same). Click on the red border of cell D2, and drag the highlighting rectangle to cover cell C2 to change the series name.

Click on the red border of cell D2, and drag the highlighting rectangle to cover cell C2 to change the series name.

Then, use a lighter shade of orange for the bars, so the blue markers stand out. Finally, hide the right-hand vertical axis: format it so it has no labels and no line color.

And there’s our completed Bar-XY Combination Chart.

Histogram Using XY and/or Area Charts

Thursday, October 24, 2013 by Jon Peltier 1 Comment

Thursday, October 24, 2013 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

The default category labels in Excel column charts produce histograms that don’t look very good.

Typical Excel Histogram with Ugly Labels

In Histogram with Actual Bin Labels Between Bars I showed a column-line combination chart that centered bin edge values between the bars of a histogram.

In Histogram on a Value X Axis Scale I showed a column-XY combination chart that simulates a horizontal value axis with markers that resemble tick marks and data labels.

In this tutorial I will go one further and show how to plot your histogram on a value-type horizontal axis.

XY Chart Histogram

The table below summarizes fifty random values between 4.95 and 6.45. The gold shaded cells contain the list of bin values, which are the values at the top of each bin. The blue shaded cells contain the results of Excel’s FREQUENCY function, which tells us the number of values below the first bin value, the number of values between each successive pair of bin values, and the number of values above the top bin.

We can expand the range of bin values to show the overall upper and lower limits of the data.

We can stretch the range by inserting blank rows in the frequency table.

Finally we fill in the blank cells with the coordinates of the outlines of each histogram bar. For example, the first bar is drawn by starting at {4.95, 0}, drawing a line up to {4.95, 4}, across to {5.10, 4}, and down to {5.10, 0}. The second bar starts at {5.10, 0}, goes up to {5.10, 5}, across to {5.25, 5}, and back down to {5.25, 0}.

Expanded Histogram Data Populated for XY Chart

Select the range above and insert an XY Scatter Chart, using the lines without markers option.

Rescale the horizontal axis, and you’ve got your XY chart histogram.

XY Chart Histogram with Adjusted X-Axis Scale

This is a pretty easy chart to generate, if you don’t mind the duplicated data, and if you don’t need filled bars in your histogram.

Area Chart Histogram

The XY Chart Histogram is simple, and probably suitable for most tasks. If you want the bars to be filled, the XY chart won’t do it, but you can use an area chart.

To get vertical edges of the histogram bars, you need to use an area chart with a date axis. A date axis has a resolution of 1 day, and our scale requires decimals, so I took the XY chart data range above, and multiplied the X values by 1000. I chose 1000 instead of just 10 because I can use a number format of “0.00,” to display 4950 as 4.95.

Here is the data for our Area Chart Histogram.

Expanded Histogram Data Populated for Area Chart

Select this range and insert an area chart.

This has no border and a saturated fill. Lighten up the fill and apply a border using the original fill.

Ah, less intense on our eyeballs. (My daughter, a PhD student in the field of vision and cognition, insists that the word “eyeball” is much funnier than simply “eye”.)

Those truncated pyramids are reminiscent of those awful 3D charts. Make them into proper bars by formatting the horizontal axis, and choosing the Date Axis option.

If the axis labels are converted to dates, you can change them back by changing the number format to “0”.

To get a similar axis range to that in our XY Chart Histogram, change the minimum and maximum of the horizontal axis scale. In former versions of Excel, you could enter whole number values like 4500 and 7000, but that was broken at some point along the way. You need to enter the dates that these values represent. To determine these, enter them into two cells, and format the cells as dates. So enter 4/26/1912 and 3/1/1919 as the axis minimum and maximum.

Format the horizontal axis and change the number format to “0.0,” (without the quotes). The comma divides the value by 1000, and the result is displayed with one decimal digit.

You can add light gray vertical gridlines if desired.

Notice how the area chart histogram originally used the smallest and largest X values as the horizontal axis limits? We can make use of this automatic scaling behavior by extending our input data by one row in each direction, and inserting the desired minimum and maximum axis scale. This avoids the need of manually scaling the axis, and of determining the corresponding dates for the scale.

As above, select this range and insert an area chart.

Lighten up the fill and apply a border with a darker color.

Format the horizontal axis, and choose the Date Axis option.

There’s no need to rescale the axis, so change the number format to “0.0,” (without the quotes) and add vertical gridlines if desired.

The only problem with extending the data sideways is that there is a thin line that extends to the ends of the axis. You can fix that by reducing the border width from the 1.5 default to 0.75.

If you need the thicker border, use the first approach. Otherwise using the data range that includes horizontal axis endpoints is easier.

Area-XY Combination Histogram

You can make a filled histogram using an XY-Area Combination Chart. The combination part makes it more complicated, but an XY chart has more descriptive chart tooltips.

The area chart above displays the series name and point label when the cursor passes over a point. The XY chart below also displays the actual X and Y values.

Start with the XY Chart Histogram from the first section of this article.

Copy the expanded data range for an area chart; you can use either range, but the range with added first and last points that match the intended axis scale limits will be easier. Select the chart, go to the Home tab of the Ribbon, click the down arrow on the Paste button (the Copy button in older Excel ribbons), and choose Paste Special. Add the data as a new series with rows in columns and with category (X) labels in the first column.

Format this added series, and assign it to the secondary axis.

Use the plus icon in Excel 2013 or the Chart Tools > Layout tab > Axes dropdown to add the secondary horizontal axis.

Right click on the new series, choose Change Series Chart Type from the pop-up menu, and select an area chart. Lighten up the fill color.

Format the secondary (upper) horizontal axis, and change the axis type to Date Axis. Depending on which data range you used, you may have to enter appropriate axis scale limits.

Select the secondary (upper) horizontal axis. Format it so it has no line, no markers, and no labels.

Select the secondary (right) vertical axis, and press Delete.

This chart has the filled appearance of an area chart with the informative chart tooltips of the XY chart.

More About Histograms on the Peltier Tech Blog

Posted: Thursday, October 24th, 2013 under Chart Types.
Tags: Area Charts, Column Charts, Combination Charts, Histograms, Statistics, XY Charts.
Comments: 1

Microsoft MVP Logo

Histogram on a Value X Axis

Tuesday, October 22, 2013 by Jon Peltier 2 Comments

Tuesday, October 22, 2013 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

Most histograms made in Excel don’t look very good, because the category labels describing the bins are centered below the bars.

In Histogram with Actual Bin Labels Between Bars I showed technique that centered bin edge values between the bars of a histogram.

Much nicer.

In this tutorial I will go one further and show how to plot your histogram on a value-type horizontal axis.

Data for Histogram and Labels

Fifty random values between 4.95 and 6.45 are summarized in the table below. The gold shaded range is the list of bin values, corresponding to the value at the top of each bin. The blue shaded range is the result of the FREQUENCY function, which tells us the number of values below the first bin value, the number of values between each successive pair of bin values, and the number of values above the top bin.

We can expand the column of bin values so we know the overall upper and lower limit of the data.

By inspection of these upper and lower limits, coupled with decades of experience in labeling chart axes, I will use the following values for my histogram chart axis labels. The first column are the values, the second column is a bunch of zeros, because I will plot these points along the bottom of the chart, and hang the axis labels on them.

Constructing the Histogram

Start by selecting the blue range in the data range above. Ignore any of the other data.

Histogram with Value X Axis Scale - Step 1

Those are pretty spindly bars for a histogram. Change the gap width to a small number (10-15% works well). Purists will make their gap widths zero, but then you need a border around the bars. We’ll let the thin white gap serve as our border.

Histogram with Value X Axis Scale - Step 2

Copy the labels data (the last data range above), select the chart, go to the Home tab of the Ribbon, click the down arrow on the Paste button (the Copy button in older Excel ribbons), and choose Paste Special. Add the data as a new series with rows in columns and with category (X) labels in the first column.

Histogram with Value X Axis Scale - Step 3

Doesn’t look like much, but the values were zero, so the bars have no height. They did push the histogram bars aside to make room, though.

Select the added series (select the histogram bars and press the up arrow key), click the menu button (between the Alt and Ctrl buttons to the right of the space bar) to pop up the context menu, and choose Change Series Chart Type. Select XY Scatter with markers and no lines.

Histogram with Value X Axis Scale - Step 4

Excel automatically puts this XY series onto the secondary axes, and draws the axes for us. Select the secondary (right) vertical axis, and choose Horizontal Axis Crosses at Automatic Position (zero).

Histogram with Value X Axis Scale - Step 5

Select the primary (left) vertical axis, and choose Horizontal Axis Crosses at Maximum Axis Value.

Histogram with Value X Axis Scale - Step 6

Select the primary (left) vertical axis and press the Delete key, to turn those histogram stalactites into stalagmites.

Histogram with Value X Axis Scale - Step 7

Format the secondary (bottom) horizontal axis, and choose Vertical Axis Crosses at Automatic Position (zero).

Histogram with Value X Axis Scale - Step 8

Format the primary (upper) horizontal axis so it uses no labels, no tickmarks, and no line.

Histogram with Value X Axis Scale - Step 9

Format the secondary (horizontal) axis scale so its minimum and maximum are the same as the outermost bin limits, that is, 4.95 and 6.45. If the axis has visible tickmarks, change the tickmark settings to None.

Histogram with Value X Axis Scale - Step 10

If you hide the secondary (horizontal) axis tick labels but choosing None for labels, Excel will remove the space below the chart for these labels. Instead, change the number format of the secondary (horizontal) axis to ” ” (three spaces within double quotes) to preserve this spacing.

Histogram with Value X Axis Scale - Step 11

Add data labels to the XY series. Use the Below option for position and X Value option for what the label contains.

Histogram with Value X Axis Scale - Step 12

Finally, change the marker of this XY series to small plus symbols, and use a gray color that matches the axis line. These plus symbols now mimic axis tickmarks. Unfortunately there is no easy way to add tickmarks outside the plot area, which is my preference and should be yours, but we’ll put up with tickmarks that cross the axis just this once.

Histogram with Value X Axis Scale - Step 13

And our new histogram is finished.

More About Histograms on the Peltier Tech Blog

Posted: Tuesday, October 22nd, 2013 under Chart Axes.
Tags: Column Charts, Combination Charts, Histograms, Statistics, XY Charts.
Comments: 2

Microsoft MVP Logo

Fill Under or Between Series in an Excel XY Chart

Monday, September 9, 2013 by Jon Peltier 187 Comments

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 (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 Show 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). You may also have to change the Base Unit of the axis to Days.

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.

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.

Fill Under a Plotted Line: The Standard Normal Curve

For an example of this technique, see another tutorial I’ve written, Fill Under a Plotted Line: The Standard Normal Curve.

XY Charts

Histogram With Normal Curve Overlay

The Problem

The Normal Curve

The Histogram

Combination Chart with Normal Curve and Histogram

Bar-Line (XY) Combination Chart in Excel

Combination Charts in Excel

Bar-XY Combination Chart

More Combination Chart Articles on the Peltier Tech Blog

Histogram Using XY and/or Area Charts

XY Chart Histogram

Area Chart Histogram

Area-XY Combination Histogram

More About Histograms on the Peltier Tech Blog

Histogram on a Value X Axis

Data for Histogram and Labels

Constructing the Histogram

More About Histograms on the Peltier Tech Blog

Fill Under or Between Series in an Excel XY Chart

XY-Area Combination Charts

Fill Under One XY Series

Fill Between Two XY Series

Fill Between Overlapping Regions

Fill Under a Plotted Line: The Standard Normal Curve

More Combination Chart Articles on the Peltier Tech Blog

XY Charts

The Problem

The Normal Curve

The Histogram

Combination Chart with Normal Curve and Histogram

Related Topics

Combination Charts in Excel

Bar-XY Combination Chart

More Combination Chart Articles on the Peltier Tech Blog

XY Chart Histogram

Area Chart Histogram

Area-XY Combination Histogram

More About Histograms on the Peltier Tech Blog

Data for Histogram and Labels

Constructing the Histogram

More About Histograms on the Peltier Tech Blog

XY-Area Combination Charts

Fill Under One XY Series

Fill Between Two XY Series

Fill Between Overlapping Regions

Fill Under a Plotted Line: The Standard Normal Curve

More Combination Chart Articles on the Peltier Tech Blog