Excel - Plotting different y-values on top of same x-values

I'm trying to figure out a way to plot my data so that values in the same category are stacked on top of each other like this:

But whenever I go to plot it I end up with this:

I've been searching for similar questions but end up finding much more complicated problems I don't understand.

Solution 1:

There are probably a few ways to do this. Here's one. I didn't have ready access to Excel, so I did this in LO Calc. The menus are a little different, but I'll describe it. Without a lot of customizing, I produced the basic chart:

Here are the key steps.

• A chart type that treats X values as categories will see apples and bananas as labels, not category names. Even though you have multiples of the same name, each entry will be plotted as a separate sequential value on the X axis, corresponding to item 1, item 2, etc.

  So I used a scatter chart. I added a column of X values, using 1 for apple and 2 for banana to force the values to the right position on the X axis.

• I selected columns A and C of the data to create the graph, and selected the scatter chart (XY chart) type, with just points. That gets you the points aligned the way you want.

• Now you need to deal with getting the X axis to look the way you want. Select the X axis and go to Format Axis (you can right-click on the axis and it's a context menu item).

• Use the scale settings to control the min, max, and grid intervals. For this example, I used min = 0, max = 2, major interval = 1, minor interval = 0. You can play with these values to position the data on the graph.

• For the labels, I just selected no axis labels and added an axis title. Then I entered Apples and Bananas, and played with the leading spacing and the spacing between them to get the alignment. You could also use text boxes for the labels.