1

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

Improve this question
5
  • Maybe this is something you are after? Commented Aug 14, 2019 at 8:51
  • 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. Commented Aug 14, 2019 at 9:10
  • @fixer1234 I thought it would be something easy do to with markers as well, but trying it I kept on getting stuck. I must be missing something. Commented Aug 14, 2019 at 9:13
  • It looks like you've already got the markers. The issue is including the sectors on the Y axis? I think your problem is with how the data is structured. I don't have access to Excel, but I roughed something out in WPS Office, which sucks for charting: i.sstatic.net/vvEua.png. The data is structured with sectors in rows and gender in columns. I picked an XY chart from the limited choices. I had to add numerical values as placeholders for the Y axis. (cont'd) Commented Aug 14, 2019 at 9:42
  • Series are in columns, I had to manually reassign ranges in the series definitions to make the values the X axis and the category numbers the Y axis. I would substitute the sector names on the chart. Excel might have a chart type that allows a numeric variable for X and categories for Y. But this got me the basic structure you're looking for. Commented Aug 14, 2019 at 9:42

1 Answer 1

Reset to default
3

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.
Improve this answer
1
  • 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. Commented Aug 15, 2019 at 10:21

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.