Combination Charts

Precision Positioning of XY Data Points

Wednesday, October 14, 2020 by Jon Peltier 2 Comments

Wednesday, October 14, 2020 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

Often when I plot data in a column, line, or area chart, I want to plot additional points on that chart. For this I use an XY Scatter type series for the extra data points. These added points may be used for additional labels or other purposes.

There are a few tricks for positioning of XY data points. I use these tricks in many of my tutorials, and I describe them in the protocol. But it’s probably good to have a single dedicated page, like this, dedicated to precision positioning of these extra points.

Long ago, when this blog was very young and I was not so old, I wrote Stacked vs. Clustered, which compared stacked and clustered column charts and described what each is well suited for. I included the following throwaway image; well, I considered it a throwaway, until a reader asked how I added the lines and markers.

Thanks for the nudge, dear reader. What follows is the protocol for adding those markers so precisely to the chart.

Clustered Column Chart

Here is my data and my starting column chart.

Column chart of Quarterly Sales by Region

What I could do, of course, is add an XY Scatter series on the secondary axis, then adjust my X and Y values and the secondary axis limits until the points are positioned appropriately. But that is tedious to do in the first place, and if the original data changes, the tweaked X and Y values would probably need readjusting.

But Excel is nothing if not extremely flexible. I can plot my XY data points on the same primary axis as the column data, with my tweaked data points based on the column chart configuration. This means that all XY points will stay in the same position relative to the column chart data, with a minimum of adjustment.

Column Chart Axis Measurements

The two charts below illustrate how scatter chart X values can be calculated based on the column chart’s configuration.

First of all, each category of the column chart has a number, from 1 to the total number of categories. The chart below has categories numbered from 1 to 3. If I use these numbers for my scatter chart X values, the points would be centered on the categories (between the orange and blue columns).

I merely have to calculate how far to the left and right of each category I need my XY data points to be. I need to know my Gap Width and I need to know how many series there are.

The Gap Width is a number, stated as a percentage of the width of a column, that tells me how wide the gap is between the clusters of columns. A Gap Width of 100 means that the white space between clusters is 100% as wide as a column. In the schematic, I have indicated that the columns are 100 percent wide, so the distance between cluster centers is 100 times the number of series, plus the Gap Width. In this case, the Gap Width is 200%, so my categories are 600% from center to center.

Each category in a column chart has an X value of 1 to the number of categories. Columns are 100% wide, and the Gap Width is stated in percentage of a column width.

The X axis of a line chart or an area chart work in exactly the same way. A bar chart is a different story; the approach is similar, but it is more complicated, and you do need to use secondary axes.

The Algebra

The amount my data points need to be offset is easy to calculate. For example, to center a point on the tallest bar in the chart, I start with the category position, then add half the bar’s width divided by the distance to the next category:

X = 1 + 50 / (4 * 100 + Gap Width)

which turns out to be 1.08333333

Since I want my points at the top of the columns, I use the same Y values for the XY points.

Below is my data for the XY points.

I put the name of the category in the first column, and the offset (in column width percentages) from the center of the category in the second. The formula in cell C9 is

=MATCH(A9,$B$1:$D$1,0)+B9/(4*100+$F$9)

The MATCH function looks up the category name and returns the number of the category (1 for North, etc.). Cell F9 contains the Gap Width.

I’ve inserted a blank row between categories, so there is a gap in the line between categories.

Calculated X values for XY scatter points

Adding the XY Series

Copy the XY data in C9:D22. Select the chart, and use Paste Special from the Home tab of the ribbon. Click on New Series, Series in Columns, Series Names in First Row, and Categories in First Column.

Because it’s a column chart, Excel adds the data as another set of columns.

Right click on any series in the chart, and choose Change Series Chart Type. Scroll to the bottom of the list of series and select the added series (“Connect”). Use the Chart Type dropdown to select the XY Scatter with Markers and Straight Lines option (NOT a Line Chart option), and uncheck the Secondary Axis checkbox.

This results in the XY data points we want, except the lines and markers are in an ugly shade of green (well, for this particular color scheme).

The XY data points are there, but sure are ugly

But we know how to fix that.

Adjusting Gap Width

If I change the Gap Width in my chart, from 200% above to 75%, the XY data points are no longer lined up.

Data points misaligned by changing Gap Width

But all that is needed is to change the Gap Width in cell F9 of my calculation range; the X values recalculate and the chart looks great again.

XY data points realigned with a simple calculation.

Gantt Chart with Nice Date Axis

Monday, May 20, 2019 by Jon Peltier 15 Comments

Monday, May 20, 2019 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

Gantt Chart with Labels and Gridlines on First of Month

Simple Gantt Charts

A year ago I wrote an article about Gantt Charts in Microsoft Excel. It was pretty comprehensive, showing how to make simple Gantt charts using worksheet conditional formatting and using regular Excel stacked bar charts, and it covered some advanced techniques for adding embellishments, such as a reference line for a given date, multiple bars showing percent complete of a task, and milestone markers at the end of the bars.

Simple Gantt Chart in Excel Worksheet — Simple Excel Gantt Chart using Conditional Formatting in a Worksheet

Simple Microsoft Excel Gantt Chart — Simple Excel Gantt Chart using Stacked Bar Chart

Advanced Microsoft Excel Gantt Chart — Simple Excel Gantt Chart starting with a Stacked Bar Chart

These Gantt charts all looked great, but a clever reader asked about a nicer axis scale across the bottom of the chart. In my example above, I had a tick label and vertical gridline every week, which is great for the time scale of my data. But what if the chart spans a longer time, for example, a year, and I want a label and gridline at the first of every month.

Gantt Chart with Wrong Monthly Date Scale

Below is a similar data set, but spanning a year rather than a few months.

Following the protocol in the earlier tutorial, we can build a Gantt chart like this one. Never mind the steps, because we’ll have to do it differently. Read on.

Simple Gantt Chart with Inelegant Date Axis Scale

The chart spans the year nicely, but the axis scale is not what we’d like. When setting the bar chart’s axis scale, I can’t just say, put a label every month on the first. I have to pick a set number of days. Above I tried 31 days, which worked for January and February, but quickly went awry.

Below you can see the 31-day axis spacing, and a 30 day spacing, which is also wrong. If we recall that 365 days divided by 12 is 30.41666…, we might try 30.4 days, in the bottom scale, and it almost works.

Attempting different major unit settings for a date axis scale.

Excel Line charts can give us a nice axis scale. Let’s have a look.

Line Chart Date Axes

In the first example, we had a bar chart with a horizontal axis date scale that had a label every 7 days. The horizontal axis in a bar chart is a value axis.

Simple Microsoft Excel Gantt Chart with weekly date axis scale

We can use that same data to create a line chart, with the same 7-day axis spacing. The horizontal axis in a line chart is a category axis, in this case, a date-scale axis. The axis can have the same min and max as the bar chart’s value axis above, but there are more formatting options.

Below are the Format Axis task panes for the bar chart’s horizontal value axis (left) and the line chart’s horizontal date axis (right).

Format Axis Task Panes for Bar Chart's Horizontal Value Axis (left) and Line Chart's Horizontal Date Axis (right).

In the bar chart, the minimum and maximum are the numbers 43184 and 43289, which are actually the serial numbers (days since 1 January 1900) of the min and max dates in the line chart, 3/25/2018 and 7/8/2018. The
major unit (axis tick spacing) in the bar chart is 7, and in the line chart is 7 days. So these two axes are the same.

In the current example, we have a bar chart with a horizontal axis date scale that had a label every 31 days. Not exactly every month.

Simple Microsoft Excel Gantt Chart with not-quite-monthly date axis scale

Again, we can use that same data to create a line chart, with monthly axis spacing. But here, the spacing is exactly monthly.

Now we can see the real difference in the axis scales. Here are the Format Axis task panes as before.

In the bar chart, the minimum and maximum are the numbers 43466 and 43831, which are the serial numbers of the min and max dates in the line chart, 1/1/2019 and 1/1/2020. The major unit (axis tick spacing) in the bar chart is 31, which is only appropriate for some months, while in the line chart it is 1 month. Excel uses this setting and adds the appropriate number of days, 31, 30, 29, or 28, so the tick labels are one month apart.

Gantt Chart with Nice Monthly Date Scale

Sometimes different chart types can share incompatible axis types, but here we are not so lucky. We need to create a chart with bar chart series to display the Gantt chart bars, and we need to combine this with a line chart to produce the date axis with the desired tick spacing. It’s a lot of steps, but not too complicated, and for the most part, the order you apply them is immaterial.

The Data

Let’s start with the data, below. On the left is the data needed to create the Gantt chart itself, and on the right is the data for the axis scale. The Gantt data has Task names, Start dates, Duration, and End dates; only the first three columns are needed. The Axis Scale data has the first and last dates in the first column, dummy values of zero in the second column, and serial numbers of the dates in the third column. The formula in the third column links to the first, but the cells are formatted not as a Date but as General.

Data for Gantt Chart with Monthly Date Scale

Create and Format the Chart

Select the first three Gantt columns, and insert a bar chart.

Let’s format the bars and get it over with. Start has no fill, so it’s hidden, and Duration is given the desired color. I have applied a 50% transparency to Duration, so the gridlines show through the bars.

Format the vertical category axis (showing task names), choosing the Categories in reverse order. In the simple Gantt chart from the earlier tutorial, we also moved the time axis to the bottom of the chart, but we’ll keep them at the top for this chart.

Gantt Chart Step 3 - Reverse the Vertical Category Axis

Now add the data for the dummy axis. Copy the first two columns of the right hand data block shown above, with dates and dummy zero values. Select the chart, then go to the Home tab of the ribbon, click the Paste dropdown, and choose Paste Special from the bottom of the menu. Make sure these options are selected: Add Cells as New Series, By Column, Series Names in First Row, Categories in First Column.

The series doesn’t appear as much: it is a new set of stacked bars, but the values of zero means the bars have zero length and don’t appear. There is a new entry in the legend.

Use Copy and Paste Special to add the dummy date axis data to the chart.

Right click on any series in the chart, and choose Change Series Chart Type. All three series are listed as Stacked Bar on the Primary Axis.

Click the Chart Type dropdown for the series named Axis, and select a Line type (I used the line with markers subtype so the series shows more clearly in the following steps).

Excel automatically changes the Axis series from Primary to Secondary Axis.

Here’s our combination chart.

Ganyt Chart Step 5 - Convert Dummy Series to Line Chart Type

We don’t need the legend any more, so delete it.

Format the Date Axes

Now add the secondary horizontal axis, the whole reason we added the dummy Axis series. Easiest way: select the chart, click on the “+” icon that appears next to the chart, click the right-pointing triangle next to Axes, and check the Secondary Horizontal box.

Now move the new axis to the bottom. Format the secondary vertical axis, on the right edge of the chart. Under Horizontal Axis Crosses, choose Automatic.

Now double-click on the bottom horizontal date axis to format it. Make sure the minimum and maximum are correct, change Base Units to Days, and change Major Units to 1 Month. Under Axis Position, choose On Tick Marks. Also, click under the paint can, and choose No Line for the axis.

Change the Number Format of the axis labels. I like to use a two-line format, with the day above the month. You can get a line feed in the middle of a number format in the worksheet, by typing Ctrl+J in the Number Format box, and then use this data for the axis and check Linked to Source in the Format Axis task pane. Unfortunately there is no way to get the line feed in the Format Axis task pane, which is a severely annoying shortcoming.

I was so annoyed that I wrote a little VBA procedure to do this, and I’ve added it to the Advanced Edition of Peltier Tech Charts for Excel 3.0, pardon the shameless plug. I can right click on the axis, and select Axis Number Format from the context menu.

Chart Axis Context Menu, with a few of my custom menu items: Apply a Nice Axis Scale, and Axis Number Format.

A little dialog pops up, showing the current number format for the axis.

Axis Number Format Dialog with current number format

Then I enter the number format I want, inserting ‘<newline>’ where I want a line feed.

Axis Number Format Dialog with desired number format

I click OK and I get the desired number format.

The axis may have been fine with just the short month name, that is, a number format of “mmm”, which you can apply using the Format Axis task pane.

Now set the axis scale limits on the primary axis, showing dates at the top of the chart. The axis had used 43200 and 43900, the serial numbers of 10-April-2018 and 10-March-2020, which were autoscaled by Excel. We need to use the serial numbers of 1-January-2019 and 1-January-2020, which are 43466 and 43831. If you use a major unit of 365, you will get tick labels at only the min and max of the axis, as shown below.

Finish with a Little Clean-Up

Turn off the primary major vertical gridlines and turn on the secondary major vertical gridlines. Easy: click the “+” icon next to the chart, click the right-pointing triangle next to Gridlines, and use the checkboxes.

There are two axes we don’t want to show. Format the top horizontal axis so it uses no line (in this case it already had no line) and no tick labels. You could format the right-hand vertical axis the same way; since we really don’t need it any more you could also delete it, but don’t delete the horizontal axes!

Finally, hide the dummy axis series. Double click on the series, and under the paint can, choose No Line and No Markers for its formatting.

There’s our Gantt chart with gridlines and axis labels at the first of each month. You don’t need to squint too hard to see that the month between 1 February and 1 March is shorter than the months between 1 January and 1 February and between 1 March and 1 April.

Articles Related to Gantt Charts on this Blog

Horizontal Line Behind Columns in an Excel Chart

Tuesday, October 30, 2018 by Jon Peltier 15 Comments

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

When you plot some data, it’s common to want to add a line to the chart to provide some context to the data. The line may represent a target value, a budget, a typical level, or some other reference. This tutorial will show how to create a chart with a horizontal line that appears behind the plotted data.

Horizontal Line Behind Columns in an Excel Chart

Easy Horizontal Line Across Chart

In Add a Horizontal Line to an Excel Chart, I showed how to add a horizontal line to an Excel chart. I started with this data and column chart…

… and I added this data as an XY Scatter series to the chart.

It’s not too complicated, takes only a couple minutes once you know how.

The problem with this easy approach is that the horizontal line cuts in front of the bars, and you may prefer to have the line behind the data.

Let’s Try an Area Chart

I originally had a more convoluted approach to the use of area chart series for this purpose. A long-time reader of my blog, Liu Wanxiang, had a simpler approach. His approach saves numerous steps and is easier to understand and explain, and there is no benefit to my original protocol. With Liu’s permission, I have modified the rest of this post.

We can use an area chart instead of an XY chart to add the line. First I’ll show why it works, and then I’ll show how to do it.

Let’s start with the same simple data and column chart.

Now let’s add this data to the chart. The new series is added as another column chart series.

When we change the second set of columns to an area type, the area is drawn behind the original columns.

We will need to put the column and area series on different axes in order to make the area stretch to the edges of the chart while leaving the columns where they are. When the area series is plotted on the secondary axis, it still stays behind the bars. This is fortunate, because it keeps the protocol from becoming too complicated.

We’ll modify the area chart data and just use endpoints 0 and 1. We’ll use a transparent fill and colored border on the area series. We’ll remove the secondary vertical axis but add a secondary horizontal axis above the chart, and we’ll adjust the tick positions of this secondary horizontal axis. I know it’s a lot of steps, and I’ll do them individually later when I show how to construct the chart. Note that the area’s border lies behind the bars, just as the filled area was behind the bars.

But we see the other borders of this area chart, on the sides and bottom of the chart. How do we get rid of them?

If we strip away the original columns for clarity, we see that a single area chart series gives us the outlines on all four sides of the area.

Let’s instead use two area chart series: Blank, which will eventually be blank instead of semitransparent as in the chart below, and Target stacked on Blank.

Then we make the Target area transparent and give it the orange border we’ve been using for our target line.

Change the height of the Target area to zero, so the top and bottom edges of the resulting rectangle coincide.

This was the magical step of Liu’s approach: making the Target area series zero units tall. I didn’t even think of it, perhaps because old versions of Excel (2003 and earlier) treated the area chart shape differently. If it was zero height, either the line didn’t show up at all, or it showed up but was double the thickness, I don’t recall exactly which. Probably this was changed in Excel 2007, and I never noticed.

And finally, make the Blank series blank (transparent, i.e., no fill).

That’s exactly what we need: a single horizontal line at the desired Y position. Now let’s build the chart from scratch, step by step.

Build Chart with Horizontal Line Behind Bars

Once again, we’ll begin with this simple data and column chart.

Let’s add two series to the chart, “Blank” which will be a hidden area series and “Target” which will provide the horizontal line with its outline. These will be stacked areas. We want our horizontal line at Y=2.5; we use 2.5 for the Blank values, and 0 for Target. (I’m using a value of 1 for Target for now, just so you can see it as we proceed.)

Copy the data range, select the chart, and on the Home tab, click the Paste dropdown and select Paste Special. In the dialog, choose New Series, in Columns, Series Names in First Row and Categories in First Column.

Blank and Target are added as more clustered columns.

Right click on one of the added series, choose Change Series Chart Type from the pop up menu, change both to stacked areas, and place them on the secondary axis.

Change New Series to Secondary Stacked Areas

Format the Blank series to have no fill so it is transparent. Make Target also transparent, but give it a border.

Now you can change the height of Target to zero. When you get good at this, you can just start out with zero.

Make Target a horizontal line by giving it a height of zero

Excel added the secondary vertical axis but not the secondary horizontal axis. Click the floating plus icon next to the chart, click the arrow beside Axes, and check the secondary horizontal axis.

Delete the secondary vertical axis (right edge of chart). The area series are now plotted along the primary vertical axis, so they are in sync with the columns.

Adjust the secondary horizontal axis, choosing the On Tick Marks option for Axis Position.

Format Secondary Axis Position to On Tick Marks

Now hide the secondary horizontal axis: format it to use no line and to have no labels.

The final result, which is a bit more intricate to create than the chart with a horizontal line in front of the bars, is a chart with a horizontal line that lies behind the plotted data.

Example Workbook

You can download an Excel workbook with a few methods for creating a horizontal line across your chart here: Horizontal-Line-Worksheet-2.xlsx.

Add a Horizontal Line to an Excel Chart

Tuesday, September 11, 2018 by Jon Peltier 19 Comments

Tuesday, September 11, 2018 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

A common task is to add a horizontal line to an Excel chart. The horizontal line may reference some target value or limit, and adding the horizontal line makes it easy to see where values are above and below this reference value. Seems easy enough, but often the result is less than ideal. This tutorial shows how to add horizontal lines to several common types of Excel chart.

We won’t even talk about trying to draw lines using the items on the Shapes menu. Since they are drawn freehand (or free-mouse), they aren’t positioned accurately. Since they are independent of the chart’s data, they may not move when the data changes. And sometimes they just seem to move whenever they feel like it.

The examples below show how to make combination charts, where an XY-Scatter-type series is added as a horizontal line to another type of chart.

Add a Horizontal Line to an XY Scatter Chart

An XY Scatter chart is the easiest case. Here is a simple XY chart.

XY Scatter Chart Without Horizontal Line

Let’s say we want a horizontal line at Y = 2.5. It should span the chart, starting at X = 0 and ending at X = 6.

This is easy, a line simply connects two points, right?

We set up a dummy range with our initial and final X and Y values (below, to the left of the top chart), copy the range, select the chart, and use Paste Special to add the data to the chart (see below for details on Paste Special).

XY Scatter Chart With Ugly Horizontal Line

When the data is first added, the autoscaled X axis changes its maximum from 6 to 8, so the line doesn’t span the entire chart. We have to format the axis and type 6 into the box for Maximum. We probably also want to remove the markers from our horizontal line.

Paste Special

If you don’t use Paste Special often, it might be hard to find. If you copy a range and use the right click menu on a chart, the only option is a regular Paste, and Excel doesn’t always correctly guess how it should paste the data. So I always use Paste Special.

To find Paste Special, click on the down arrow on the Paste button on the Home tab of Excel’s ribbon. Paste Special is at the bottom of the pop-up menu.

You can also use the Excel 97-2003 menu-based shortcut, which is Alt + E + S (for Edit menu > Paste Special).

The tooltip below Paste Special in the menu indicates that you could also use Ctrl + Alt + V, but this shortcut doesn’t do anything for charts.

When the Paste Special dialog appears, make sure you select these options: Add Cells as a New Series, Y Values in Columns, Series Names in First Row, Categories (X Values) in First Column.

Click OK and the new series will appear in the chart.

Add a Horizontal Line to a Column or Line Chart

When you add a horizontal line to a chart that is not an XY Scatter chart type, it gets a bit more complicated. Partly it’s complicated because we will be making a combination chart, with columns, lines, or areas for our data along with an XY Scatter type series for the horizontal line. Partly it’s complicated because the category (X) axis of most Excel charts is not a value axis.

As with the XY Scatter chart in the first example, we need to figure out what to use for X and Y values for the line we’re going to add. The Y values are easy, but the X values require a little understanding of how Excel’s category axes work. Since the category axes of column and line charts work the same way, let’s do them together, starting with the following simple column and line charts.

Column and Line Charts Without Horizontal Lines

Note in the charts above that the first and last category labels aren’t positioned at the corners of the plot area, but are moved inwards slightly. This is because column and line charts use a default setting of Between Tick Marks for the Axis Position property. We can change the Axis Position to On Tick Marks, below, and the first and last category labels line up with the ends of the category axis. The line chart looks okay, but we have cut off the outer halves of the first and last columns.

Let’s focus on a column chart (the line chart works identically), and use category labels of 1 through 5 instead of A through E. Excel doesn’t recognize these categories as numerical values, but we can think of them as labeling the categories with numbers.

Now let’s label the points between the categories. Not only do we have halfway points between the categories (1.5, 2.5, 3.5, 4.5), we also have a half category label below the first category (0.5) and another after the last category (5.5).

Column Chart With Categories 1-5 and Mid Categories

If the Axis Position property were set to On Tick Marks, our horizontal line starts at 1 (the first category number of 1) and ends at 5 (the last category number of 5). This would be wrong for a column chart, but might be acceptable for a line chart.

Column Chart With Desired Horizontal Line

Here is our desired horizontal line, stretching from 0.5 to 5.5

So let’s use this data and the same approach that we used for the scatter chart, at the beginning of this tutorial.

Copy the range, and paste special as new series. We’ve added another set of columns or another line to the chart.

Column and Line Chart With Horizontal Line

Right click on the added series, and choose Change Series Chart Type from the pop-up menu.

In the Change Chart Type dialog, select the XY Scatter With Straight Lines And Markers chart type. We’re using markers to temporarily mark the ends of the line, and we’ll remove the markers later; in general we will change directly to XY Scatter With Straight Lines.

The new series don’t line up at all, though, because Excel decided we should plot the scatter series on the secondary axes. We could rescale the secondary axes, then hide them, but that makes a complicated situation even more complicated.

So we need to uncheck the Secondary Axis box next to the Scatter series in the Change Chart Type dialog.

Change Series Chart Type And Axis Dialog

And now everything lines up as expected: the markers on the horizontal lines are at the edges of the plot area.

We should remove those markers now, and in the future select the chart type without markers.

“Lazy” Horizontal Line

You may ask why not make a combination column-line chart, since column charts and line charts use the same axis type. And many charts with horizontal lines use exactly this approach. I call it the “lazy” approach, because it’s easier, but it provides a line that doesn’t extend beyond all the data to the sides of the chart.

Start with your chart data, and add a column of values for the horizontal line. You get a column chart with a second set of columns, or a line chart with a second line.

Column and Line Charts With Lazy Horizontal Lines

Change the chart type of the added series to a line chart without markers. Doesn’t look very good for the column chart (left) since the horizontal line ends at the centerlines of the first and last column. You could probably get away with it for the line chart, even though the horizontal line doesn’t extend to the sides of the chart.

If we change the Axis Position so the vertical axis crosses On Tick Marks, the horizontal lines for both charts span the entire chart width. In the column chart, this comes at the expense of the outer halves of the first and last columns. The line chart looks okay, though.

Add a Horizontal Line to an Area Chart

As with the previous examples, we need to figure out what to use for X and Y values for the line we’re going to add. The category axis of an area chart works the same as the category axis of a column or line chart, but the default settings are different. Let’s start with the following simple area chart.

Notice that the first and last category labels are aligned with the corners of the plot area and the filled area series extends to the sides of the plot area. This is because the default setting of the Axis Position property is On Tick Marks. We can change it to Between Tick Marks, which makes the area chart look a bit strange.

Below is the data for our horizontal line, which will start at 1 (the first category number of 1) and end at 5 (the last category number of 5), without the half-category cushion at either end. Copy the data, select the chart, and Paste Special to add the data as a new series.

Right click on the added series, and change its chart type to XY Scatter With Straight Lines And Markers (again, the markers are temporary). The resulting line extends to the edges of the plotted area, but Excel changed the Axis Position to Between Tick Marks.

Change the Axis Position setting back to On Tick Marks, and remove the markers from the line.

“Lazy” Horizontal Line

In the column chart, and perhaps for the line chart, the “lazy” approach did not give a suitable horizontal line, since the line did not extend to the edges of the plot area. Let’s see how it works for an area chart.

Make a chart with the actual data and the horizontal line data.

Right click on the second series, and change its chart type to a line. Excel changed the Axis Position property to Between Tick Marks, like it did when we changed the added series above to XY Scatter.

Change the Axis Position back to On Tick Marks, and the chart is finished.

For the area chart, the appearance of the lazy horizontal line is identical to the more complicated line that uses an XY Scatter series. Since it’s easier and just as good, it’s probably better to use the lazy approach.

Follow-Up

An alert reader noted in the comments that the line produced by this method is placed in front of the bars, and it might be better to place such reference lines behind the data. I have written a new post describing an approach that does just this: Horizontal Line Behind Columns in an Excel Chart.

Historical

I showed similar approaches in an old post, Add a Target Line.

Histogram With Normal Curve Overlay

Monday, November 6, 2017 by Jon Peltier 6 Comments

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

Combination Charts

Precision Positioning of XY Data Points

Clustered Column Chart

Column Chart Axis Measurements

The Algebra

Adding the XY Series

Adjusting Gap Width

More Combination Chart Articles on the Peltier Tech Blog

Gantt Chart with Nice Date Axis

Simple Gantt Charts

Gantt Chart with Wrong Monthly Date Scale

Line Chart Date Axes

Gantt Chart with Nice Monthly Date Scale

The Data

Create and Format the Chart

Format the Date Axes

Finish with a Little Clean-Up

Articles Related to Gantt Charts on this Blog

More Axis Scale Articles

Horizontal Line Behind Columns in an Excel Chart

Easy Horizontal Line Across Chart

Let’s Try an Area Chart

Build Chart with Horizontal Line Behind Bars

Example Workbook

More Combination Chart Articles on the Peltier Tech Blog

Add a Horizontal Line to an Excel Chart

Add a Horizontal Line to an XY Scatter Chart

Paste Special

Add a Horizontal Line to a Column or Line Chart

“Lazy” Horizontal Line

Add a Horizontal Line to an Area Chart

“Lazy” Horizontal Line

Follow-Up

Historical

More Combination Chart Articles on the Peltier Tech Blog

Histogram With Normal Curve Overlay

The Problem

The Normal Curve

The Histogram

Combination Chart with Normal Curve and Histogram

Combination Charts

Clustered Column Chart

Column Chart Axis Measurements

The Algebra

Adding the XY Series

Adjusting Gap Width

More Combination Chart Articles on the Peltier Tech Blog

Simple Gantt Charts

Gantt Chart with Wrong Monthly Date Scale

Line Chart Date Axes

Gantt Chart with Nice Monthly Date Scale

The Data

Create and Format the Chart

Format the Date Axes

Finish with a Little Clean-Up

Articles Related to Gantt Charts on this Blog

More Axis Scale Articles

Easy Horizontal Line Across Chart

Let’s Try an Area Chart

Build Chart with Horizontal Line Behind Bars

Example Workbook

More Combination Chart Articles on the Peltier Tech Blog

Add a Horizontal Line to an XY Scatter Chart

Paste Special

Add a Horizontal Line to a Column or Line Chart

“Lazy” Horizontal Line

Add a Horizontal Line to an Area Chart

“Lazy” Horizontal Line

Follow-Up

Historical

More Combination Chart Articles on the Peltier Tech Blog

The Problem

The Normal Curve

The Histogram

Combination Chart with Normal Curve and Histogram

Related Topics