0

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.scatter chart Is there a way to achieve this.

enter image description here

  • 1
    It isn't clear what you want to do with sector. Can you mock up what the desired result would look like? You can just draw it by hand on this chart and snap a picture of it. – fixer1234 Aug 14 at 8:49
  • Maybe this is something you are after? – JvdV Aug 14 at 8:51
  • @JvdV Thanks but no – Naz Aug 14 at 8:52
  • @fixer1234 I have added the "What it should look like" image – Naz Aug 14 at 8:58
  • This made me think of another video by the same person as JvdV's link. She has a bunch of videos about creating info charts in Excel using symbols. Your example is much more straightforward than the elaborate stuff she does. I've never tried this, and don't have ready access to Excel, but I think it's just a question of using symbols for the markers instead of dots, and I think it's a built-in feature of recent versions of Excel. – fixer1234 Aug 14 at 9:10
2

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:

enter image description here

With this data:

enter image description here

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:

  • Select A2:C5 and insert a vertical grouped bar chart. Select one series (click on a bar) and overlap the two series a 100%.

enter image description here

  • Right click the chart and add two new series. Name them e.g.: Icon-Male and Icon-Female and 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:

enter image description here

  • Select Icon-Male series, right click and edit series: X-values are B2:B5 and Y-values are D2:D5. Do the same thing for Icon-Female series 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,5 and maximum to 4,5:

enter image description here

  • 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-Male series and paste (Ctrl+V for that matter). Do the same thing with the female icon.

enter image description here

  • 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.
  • The end result of the video didn't look like what I wanted as my end result, so maybe I overlooked it, but your answer is really helpful. Thanks. – Naz 2 days ago

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.