Pay Scale bands and plotting employees - HELP

[beachley]

Hi - So I currently have a pivot chart with floating bars of the pay scale for each position. The position (job title) is listed on the x-axis. I would then like to have an xy scatter plot with a point within each of those bands of where the current employees in those positions fall. I believe because the position is listed on the x-axis is a text it will not let me create a combined pivot chart.

Is there any way to get around this? Or has anyone else created a similar chart?

 

[sijpie]

I don't think it is an XY Scatter plot you need, but a line chart (just plot the points, not the lines in between). A XY scatter chart is where you have two sets of figures, for instance temperature vs time. Line chart is where you have figures for entities, for instance salary and employee.

So you should be able to combine these in a graph. I would refer you to the website of Jon Pellier where you can find just about anything on combining graphs. Jon's Excel Charts and Tutorials - Index

 

[beachley]

I believe I need an XY Scatter plot because we have numerous people with the same job title and by using the line chart I cannot have numerous points within one of my floating bars. I have used Jon's tutorial on something similar (Salary Chart: Plot Markers on Floating Bars - Peltier Tech Blog) however, when I try to paste his points like he does it does not work. I am using Excel 2016. My end goal is something similar to what Jon created in this post.

 

[Jon Peltier]

"It does not work" is a broad and vague symptom. Could you describe in more detail how the tutorial does not work in your case?

I will suggest however that if you're plotting data from a pivot table, by default Excel gives you a pivot chart, and a pivot chart is limited to plotting all the data in the pivot table, and no data from outside the pivot table is allowed. It is possible, however, to build a regular Excel chart from pivot table data.