Graph with two y axis acess to same x value

[Lukas69]

Hi guys,

I´m trying to create a graph. I have several Products sold in several locations. I want to display in one graph the turnover and also the change in percent point compared to the month before.

What I'm doing is:

Select the cells and insert clustered columns

Switch Row/Column

Change chart type -> Combo

For turnover I select cluster column

For change in percent I select Scatter am tick the box for secondary Axis

You can see the result in the first picture. How I actually want it to be in the second.

What do I have to do, to have the scatter on the associated pillar?

Any help would be appreciated. Thank you very much

excel sheetTarget graph

[#2]

@Lukas69 

This is the best I can do. It uses a separate range for the scatter chart series. It is plotted on the secondary x-axis and y-axis. The secondary x-axis labels have been set to None.

See the attached sample workbook.

[#3]

@Hans Vogelaar
Thank you very much. That is exatcly what I'm looking for.

Unfortunately I can not reconstruct how you did it.


in your file:
I select A5:D8 and insert a Clustered Column
Than I go on chart filters Select Data Source -> Add series name ="changes" series Y values =B11:B21 and series x values is empty.
Afterwards I change the chart typ to Combo, everything cluster but changes as Scatter and as secondary axis.
As result I get the same chart as before.

Where is my mistake or what additional step is necessary?

[#4 · Accepted solution]

@Lukas69 

On the Chart Design tab of the ribbon, select Add Chart Element > Axes > Secondary Horizontal.

Double-click the secondary horizontal axis (it is displayed at the top).

Under Axis Options, expand Labels, and set Label Position to None.

[#5]

@Hans Vogelaar
thank you very much. That does the trick.