I am try to create a chart that shows the difference in pay between male and female. I want pay at the top/bottom, sector on the left, and a male/female icon and the pay points. I can not seem to achieve this. The closest I have come is a scatter chart that plots the pay points but then can not add the sector. I will need to add more sectors as well. Is there a way to achieve this.
I'm not quite sure why you don't think what I linked you to isn't what you are after. A scatter plot won't work with text labels as it expects numeric values. The information shown in the linked video is quite clear and I was able to re-create what you are after fairly simple:
With this data:
I added a column
Scatter_y for later use since scatterplots won't take textual values but numeric ones.
What were the steps I took:
A2:C5and insert a vertical grouped bar chart. Select one series (click on a bar) and overlap the two series a 100%.
- Right click the chart and add two new series. Name them e.g.:
Icon-Femaleand pick the appropriate data form column B or C.
- When added, right click the newly added bar chart, and choose to change graph type.
- On the right screen you can now for both new added series choose a scatter plot:
Icon-Maleseries, right click and edit series: X-values are
B2:B5and Y-values are
D2:D5. Do the same thing for
Icon-Femaleseries according to the appropriate ranges.
Now you'll notice the scatter plot used the Y-axis on the right side. Now click that axis and under axis options, fixe the minimum to
0,5and maximum to
- Next up is adding in the two icons on the appropriate series. I suppose you know how to add the two icons to your sheet, color and resize them. After you done doing that just copy the male icon and select the
Icon-Maleseries and paste (Ctrl+V for that matter). Do the same thing with the female icon.
- Now it's a matter of use no-fill on the bar chart, removing the y-axis on the right and format to your liking. Result is the picture on the top.