Excel – Aligning Stacked Bar Chart w/ Scatter Plot Data

Excel scatter plots cannot take names instead of values on their x-axis. They assume a number series for the x-axis. If you want to replicate the effect of a scatter plot but use named x-axis values, you can use a line chart instead.

First, though, you are going to need to re-order your data. Each data point for a city must be in a separate column on the same row. For your example data set:

   A         B   C   D   E    
1  London    23  22  44  18
2  Paris     34  14
3  New York  45

Select the data and choose a line chart style with data points marked. Double-click a line to go into the Format Data Series dialog. Under Line Color choose No Line, and click OK. You can repeat the process quickly with the rest of the data set by clicking on a line and pressing CTRL+Y.

The end result will look something like this:

You need to assign one series to a secondary axis in order to make this type of change. The trick to get a single-axis chart is to hide the secondary axis, so your chart looks like it has only one.

To get a secondary axis: Right click on your series and select Format Data Series. Under Select Options check Plot Series on Secondary Axis.

Now you can change the chart type, etc for each series.

The next step is to hide the axis. But first make sure that the axis are actually identically scaled. If they are not, right click on the right handside scale and select Format Axis. Under Axis Options you can change the scaling using fix values. Once you made sure the axes are identical, select None for Major tick mark type and Axis labels