Aligning X-Axis of scatter X-Y data to Clustered Axis Data

Question

Hi Everyone, I'm trying hard to figure out this puzzle with no satisfying result trough the web.

I was trying to combine a range of min-mid-max data of each salary grading level using standard clustered column chart and overlap the column to create "floating" view.

Second, I add data of "people" corresponding to the grade, so I expect that X axis will be "grade" and Y axis will be "value" all in primary axis. I did this by adding data and use combo chart for the chart type.

However, things happen not as expected and I'm lost (see picture). on the example, all formats have been matched using numbers, but it seems that X axis are just aligning to the Y values, despite my effort to insert the X value of the second data to "C" column data.

What I'm trying to do is to align the X axis of the scatter points to the primary, I would be grateful if anyone can help with some clue or detailed step on how to do this.

Answer 1

Hi SoparM,

Sorry for the delay, I was able to replicate this case with your desired result by following the video tutorial you have provided me.

The reason why you were not getting your desired result is because the chart counts the data series (Grade) from the primary table (with Min/Mid/Max) as 1 to 6, even if you are seeing it as 9 to 14. Then, when you add the Grade as data series from your second table, the chart counts it as it is (9-14). As a result, the dots (Scatter) appeared on the right side of the chart.

A workaround for this is to replace Grades 9 to 14 from the second table with 1 to 6, respectively. In that way, it forces the Grade data series to align accordingly with the primary table. Please see attached for your reference.

Please let me know if it worked out well for you. If not, please reply back to this forum to let me know and I will be glad to follow up and help you further.

Thank you and stay safe!

John

Answer 2

Hi SoparM,

I am John, an Independent Advisor and a Microsoft user like you. I'm here to work with you on this issue.

Please check the link below on how to properly combine two charts into one.

https://support.microsoft.com/en-us/office/add-...

I hope it worked out well for you. If not, please reply back to this forum to let me know. I will be glad to follow up and help you.

Regards,

John

Answer 3

Hi SoparM,

I am John, an Independent Advisor and a Microsoft user like you. I'm here to work with you on this issue.

Please check the link below on how to properly combine two charts into one.

https://support.microsoft.com/en-us/office/add-...

I hope it worked out well for you. If not, please reply back to this forum to let me know. I will be glad to follow up and help you.

Regards,

John

Hi Jhon,

Thank you for your prompt respond. 

Below is the link of reference I use (and intent to replicate) for this particular chart.

https://www.youtube.com/watch?v=n26KWRQzPf8&t=246s

using secondary axis doesn't solve my problem as still main X series and secondary X series didn't seems align.

I tried the reference you provided and below is the result

Answer 4

Dear John,

I am happy to tell you that you solved one of my life puzzle! :D.. as it bothers me already for few days

The workaround works perfectly and its enough for me (regardless a bit confusing on the logic though)

Thanks a heap for your kind support.

Cheers,

Sopar

Answer 5

Hi SoparM,

Great! I am happy that one of your problem has been resolved! 😊

Thank you and stay safe!

John