Comment from Jon Peltier
Time: Monday, August 11, 2008, 8:38 pm

Hi Rick -

Sounds like a topic for another entry. I haven’t codified my approach, there’s still a bit of art in it. I like to make the label text the same color as the markers or lines in a line or XY chart. I look for places in the chart where there is good separation between series, so labels are not cluttered and there is less chance for confusing which label goes with which points. Labeling the rightmost point in a series means usually there is room for the label to the right of the point.

I have a simple macro which automatically labels the last point of all series in a chart with the series name. I no longer use it myself, because it applies static text, and the label doesn’t change if I update the series name. It’s a simple fix which I may make someday (another blog topic!). Anyway, the simple macro can be found here:
Label Last Point

Comment from Jon Peltier
Time: Thursday, September 25, 2008, 12:18 am

Amy -

The rest of the paragraph explains how to do this:

“Format the secondary X axis (top) so that the Value Y Axis does not cross at the maximum category (uncheck the box). Then format the primary X axis (bottom) so that the Value Y Axis does cross at the maximum category (check the box).”

To format the scale of an axis, select the axis, press CTRL+1 (numeral one) to open the dialog, and on the Scale tab (Excel 2003 & earlier) or on the first tab of Excel 2007 (I forget its name), check or uncheck the appropriate box.

Comment from Jon Peltier
Time: Tuesday, November 25, 2008, 2:09 pm

Hi Bridget -

Most things are possible in Excel, you just need to know which preconceptions need to be broken.

However, there are a couple of issues with what you want to do:

First, the column clusters are positioned monthly with an equal space for each month. Plotting daily data on top of this results in an unequal space for each month because of the different numbers of days in each month.

Second, in general showing items on primary and secondary axes can lead to confusion, and showing the monthly columns and daily values on the same chart are likely to increase that confusion.

Because of these two issues, I’d think using two charts would be prudent.

Comment from Jon Peltier
Time: Tuesday, December 2, 2008, 5:36 pm

Ben -

Is the data for the lines contiguous as in my example of F1:I3, or sparse ike the column data in A7:whatever? If it is sparse, I suspect the problem is the setting for how Excel plots empty cells. By default, Excel doesn’t plot anything for a blank cell, so you’re seeing the gaps between populated cells. Select the chart, then go to Tools menu > Options > Charts and change the setting to Interpolate. Sometimes in a combination chart, you cannot set this property. Insetad you need to enter #N/A into the cells where you want the line to interpolate over.

Comment from Jon Peltier
Time: Tuesday, March 10, 2009, 8:19 pm

As difficult as primary and secondary axes with clustered and stacked series are to display clearly, they are even more difficult to read and interpret. Charts with simple data and primary plus secondary axes are hard to read and interpret correctly without drawing false conclusions because of the arbitrary placement of the axis scales with respect to each other.

This is a case in which panel charts (e.g., Marimekko Replacement – 2 by 2 Panel) are superior.

With that warning, here are the mechanics that do what you want.

Starting with a familiar example, with the single bar to the right of the stacked bars. These are all on the secondary axis despite the series names, because the hidden series with the month names as category labels is plotted on the primary axis.

Here the original primary axis series has been removed, which pushes the other series to the primary axis, and there is no longer a secondary axis.

Now let’s use vastly different values for the Secondary series, so the series towers over the stacked primary series.

Assign the Secondary series to the secondary axis. It’s thin, because the default gap width is 150%. Also note that the secondary Y axis starts at 480, not at zero. This is a grievous sin for a column or bar chart.

I’ve now fixed the axis scale, and given primary and secondary series a gap width of 20%. Hopefully the separation may prevent the user from becoming too befuddled about which axis goes with which bars.

Here’s the data we need to put XY data points at the appropriate axis categories along the category axis.

Copy the left two columns of this range, select that chart, and use Paste Special to add the data as a new series with all of the appropriate options.

It’s not visible, because a column chart with zero values has no thickness. Change the added series to an XY series. It goes onto the secondary axis, but primary and secondary axis groups use the primary category (X) axis, and the data points align where we want them.

Use Rob Bovey’s Chart Labeler to add the labels of the third column in our added data range to the XY series as data labels. They are colored red here for clarity.

Hide the secondary axis tick labels and tick marks. You’ll probably have to adjust the height of the plot area to give room for the added labels below the axis.

Finally hide the XY series by formatting it with no lines and no markers. Remove the legend entry for the Axis series: two single clicks to select the legend entry (the label not the marker) and press Delete. Finally, if you copied too closely and made the added labels red, make them black again.

Comment from Jon Peltier
Time: Friday, April 10, 2009, 4:47 pm

The primary series (that produces the visible labels along the bottom) must use a smaller range of category labels than the stacked columns. In my example, the stacked columns use B through K (10 columns), while the labels use G, H, and I (three columns). You may have to go to the source data dialog and adjust this, especially if you are using Excel 2007.

Comment from Jon Peltier
Time: Tuesday, April 14, 2009, 4:07 pm

Mona -

It sounds like you’re trying to plot two charts worth of different data in one chart. The line charts with monthly counts of large and small ice creams sold is easy enough (or would you want to show revenues?).

For the types of ice cream, if you are only plotting it annually, I would make a clustered column chart, one column per flavor, and the value would be the revenues of each. You could stack # of large and # of small, but then, the large servings count for more, so do you scale hte bars? That’s why I think revenue is easier to show.

If the number of each size and flavor is important, then you oculd show your initial charts as I’ve described, then create more charts showing numbers of each subcategory instead of revenues.

Comment from Jon Peltier
Time: Wednesday, April 15, 2009, 10:39 am

I’m not sure how you’re changing the links. The easiest way to handle this is to make an exact copy of the chart and its worksheet, and then replace the old data with the new data. This avoids the need to actually change any links.

This and related approaches are discussed in Make a Copied Chart Link to New Data.

Comment from Jon Peltier
Time: Monday, July 13, 2009, 9:28 pm

John -

The line series would use the same X values as the columns, not as the overall clusters. The line data must be staggered to match the column data, so the markers line up with the columns. Start with this data:

Start by creating a line chart, and use the option to interpolate over blank cells (otherwise there will be no lines connecting the markers).

Change A, B1, B2, and B3 to stacked column type series.

Move the line series to the secondary axis, and apply various formatting as in the chart above. Note: another XY series is used to provide the month labels along the bottom of the chart.

Here is an alternative arrangement for the chart:

Comment from Jon Peltier
Time: Wednesday, July 22, 2009, 9:24 am

When the series is added, it is added as another stacked column on top of the others. You can’t make it a line type series until after it is created as a stacked column.

Comment from Jon Peltier
Time: Thursday, October 22, 2009, 9:25 am

Looks like another entry for “You Asked For It”. Stay tuned.