Advanced Combination Chart Help

[CzechCzar]

Hi all


My manager has asked me to make a pretty complex chart. Apologies for the crappy handwriting, but the drawing is necessary.

I work in HR, and we are dealing with mapping employee's salaries to market salary surveys. My manager wants a floating bar representing the 25th %ile, 50th %ile, 75th %ile, and average, of the survey position. I need the same for internal position to which the survey position matches - for these I want the maximum, mean, median, and minimum, again displayed as a floating bar. (Internal employees are called incumbents.)


Survey data is not given on an individual basis, but only aggregated in 25th %ile, 50th %ile, 75th %ile, and average. Internal incumbent data is individual, obviously, and I calculate these statistics myself.


Further down the rabbit hole: next to these floating bars, I need to display the individual employee (incumbent) data, in scatterplot form. And, I need to be able to separate the data, by gender, by age, etc.


I have no idea how to get this to work properly in Excel. Is this even possible?

 

[Jon Peltier]

Not too complicated, if I can explain it clearly.

This is a combination chart. I'll use a floating bar chart to show the mid 50th (25th to 75th) and/or min to max, plus two line chart series with markers but no lines for mean and median, and an XY Scatter series for the individual data points.

Here's the data. There's one set for the bars and lines and another for the individual XY data.

Select the first set of data, and make a stacked bar chart (top left). Excel guesses incorrectly that you're plotting by columns, so switch rows and columns (top right).

Select and delete the 75th series (middle left). Format the 25th series so it uses No Fill so the other series float on it (middle right).

Right click the Mean series, select Change Series Chart Type, and select the Line with markers type (NOT Stacked Line with Markers). Repeat for the Median series (bottom left). Format the Mean and Median series so they use markers but no lines (bottom right).

Copy the data for the individual XY points, select the chart, and choose Paste Special from the Home tab (click the down arrow on the Paste button). Select the options shown in the dialog below left. The data is added as a new line series with markers and no lines, because Excel helpfully remembers the changes made to the Mean and Median series (below right).

Right click the new series, choose Change Series Chart Type, and select XY, Markers only (top left, below). Select the XY series and format it, choose Primary Axis (top right).

Format format format. Colors and markers that work. Data labels for the last point of the Mean and Median series. Individuals names for the Individuals XY points. In Excel 2013, one option for data labels is to use labels from cells. In other versions of Excel, use Rob Bovey's Chart Labeler from Welcome to Application Professionals.

 

[CzechCzar]

This is incredibly helpful. Thanks so much. One question if I may:

I am trying to do this step (regarding individuals):

"Copy the data for the individual XY points, select the chart, and choose Paste Special from the Home tab (click the down arrow on the Paste button). Select the options shown in the dialog below left. The data is added as a new line series with markers and no lines, because Excel helpfully remembers the changes made to the Mean and Median series (below right)."

Yet, when I select my second column of data, copy, click my chart, choose paste special, and click the same options that you did, nothing gets added to the chart. Do you know why this might be happening?

 

[CzechCzar]

Here is where I am getting hung up. Using some dummy data:

I can get around all the moving/hiding/removing various fields.

Everything looks good, right?

Well, when I then try and copy/paste special my individual data points into the chart, it gets messed up.

If I copy only the salary data, not the employee ID, it does nothing whatsoever.

If I copy in both salary and employee ID, it looks like this:

Excel is trying to distribute the employees horizontally, instead of putting them on top of each other. Do you happen to know if this is fixable?

This is incredibly helpful. Thanks so much. One question if I may:

I am trying to do this step (regarding individuals):

"Copy the data for the individual XY points, select the chart, and choose Paste Special from the Home tab (click the down arrow on the Paste button). Select the options shown in the dialog below left. The data is added as a new line series with markers and no lines, because Excel helpfully remembers the changes made to the Mean and Median series (below right)."

Yet, when I select my second column of data, copy, click my chart, choose paste special, and click the same options that you did, nothing gets added to the chart. Do you know why this might be happening?

 

[CzechCzar]

bump?

 

[Jon Peltier]

My bad. I wrote the protocol, then adjusted the data. The data you need to copy for the XY individuals points is the first two columns (the column with {3, 3, 3,...} and the column with {81, 86, 68,...} in my data), only the numeric data, not the labels.

 

[CzechCzar]

Thanks a ton, worked like a charm. You have helped me with something I would not have been able to do alone!!

My bad. I wrote the protocol, then adjusted the data. The data you need to copy for the XY individuals points is the first two columns (the column with {3, 3, 3,...} and the column with {81, 86, 68,...} in my data), only the numeric data, not the labels.

 

[CzechCzar]

Am I correct in assuming that the [{3,3,3...} is the series number? I ask because when I try to put in my second series of individual data (in my hand drawn diagram, the dots next to the x's), nothing is happening when I try to paste special the data into the chart. So, if I wanted separate columns for males and females (dots vs x's), how would I set up the data differently? I recognize that I am probably taking up way more of your time than you originally hoped for, and am deeply grateful. I just want to figure out a way to get this to work.

My bad. I wrote the protocol, then adjusted the data. The data you need to copy for the XY individuals points is the first two columns (the column with {3, 3, 3,...} and the column with {81, 86, 68,...} in my data), only the numeric data, not the labels.

 

[CzechCzar]

Never mind, I figured it out myself!!

 

[CzechCzar]

I hate to resurrect a long-dead problem. This is still solved in Excel 2010. However, my office just switched to Excel 2013, and I now have to do this again. The problem is that the process is completely different, and I am now back to square one. Would anyone be able to offer a TL;DR for how to do this in Excel 2013?

Many thanks!