chartsmicrosoft excel
Apples 1
Apples 5
Apples 2
Bananas 1
Bananas 4
Bananas 6
I want two bars on the X axis (Apples, Bananas), but with crosses marking the points on the Y axis.
Here's a mockup:
I'd want 1 and 2 to be labelled "Apples" and "Bananas" respectively.
Excel scatter plots cannot take names instead of values on their x-axis. They assume a number series for the x-axis. If you want to replicate the effect of a scatter plot but use named x-axis values, you can use a line chart instead.
First, though, you are going to need to re-order your data. Each data point for a city must be in a separate column on the same row. For your example data set:
A B C D E
1 London 23 22 44 18
2 Paris 34 14
3 New York 45
Select the data and choose a line chart style with data points marked. Double-click a line to go into the Format Data Series dialog. Under Line Color choose No Line, and click OK. You can repeat the process quickly with the rest of the data set by clicking on a line and pressing CTRL+Y.
The end result will look something like this:
The quickest (and I think easiest) way to do this is:
Here's a sample:
Best Answer
Based on your description, I think you could rearrange your data into a format like this.
Then, each row of the data could be considered as a single dataset with the same labels 'apples' and 'bananas'. You could then make the chart you want directly in Excel by trying with inserting a line chart.
But here I can give you another solution which directly draws the dot chart you want. What I did is using the Funfun Excel add-in, this add-in basically enables you to use JavaScript directly in Excel so you are able to use powerful JavaScript libraries like D3.js or Chart.js.
I made a chart with Funfun using Chart.js according to your problem, you could check the chart on the link below
https://www.funfun.io/1/edit/5a281afbcf76561801b7335d
You could also link the URL directly to your Excel. Here is how it looks in Excel.
Disclose: I'm a developer of Funfun