Trouble adding scatter plot markers (total) to a vertical stacked column chart (components)

Question

Is it possible in Excel 2010 to add a series of scatter plot markers (XY chart) to a vertical clustered/stacked column chart. I can see examples of how to do it when the clustered chart is horizontal, but nothing on vertical.

My markers would show the total amount. My stacked bar is showing the components, one of which is a negative (See images below).

The total series goes onto the secondary axis, but then it’s not at all aligned with the other data. I'm unable to place it on the primary series. I'm sort of stuck.

Does anyone have an idea of how to work around this?

Any links or ideas greatly appreciated.

Thanks Michael.

Answer 1

I believe the answer is "No". You can't do that with a vertical stacked column chart. The reason is that while Excel supports a second vertical (Y) axis that a series can be plotted on, it does not support a second horizontal (X) axis.

Answer 2

Some versions of Excel allow a secondary horizontal axis, but you don't need to add one for what you're trying to do.

Just plot all four data series as a stacked column chart. Then click on the "total" series to select it and change that one series to a Line (not Scatter) graph by choosing "Marked Line" from the Graph menu or tab.

Here's what you'll get:

Answer 3

The answer is Yes. You need to remember that in the bar chart (it's not a column chart if the bars are horizontal) the Y values are plotted on the horizontal axis, but the scatter series you're adding still uses the vertical axis for Y values.

So here's your data:

I put the labels and the three stacked series first, then the X and Y for the markers later. The Marker X values are what you already had but plotted as Y values, and the Marker Y values are the vertical position of the markers (the vertical scale is 0 to 10, so Item1 is at 9.5, Item2 at 8.5, etc.).

1. So first make a stacked bar chart (top left chart).

2. Copy the two columns of Marker data, select the chart, and use Paste Special to plot the data as a new series, with categories (labels) in the first column, and series name in the first row. It is plotted as another series of stacked bars (top right chart).

3. Right click on this set of bars, choose Change Series Chart Type from the pop-up menu, and change to a scatter chart type. Excel plots the markers on the (new) secondary vertical axis, but uses the existing primary horizontal axis, which is very convenient (bottom left chart).

4. Select the secondary vertical axis (right edge of the chart), and press Ctrl+1 to format it. Change the axis label position to None (bottom right chart).