Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Lets imagine I manage 100 pizza stores. I want to plot a chart each day, of how long deliveries are taking.

My data might look like this:

London      23
London      22
London      44
London      18
Paris       34
Paris       14
New York    45
etc

I would like a scatter plot; numbers (minutes) on one axis, and cities on the other axis. I would also like each city to appear in the order I choose.

I can only get this to work if I substitute each city name for a number (city number), but that has the effect of the axis labels being the city numbers (rather than city name). How can I substitute the number for the real city name? I do not want to label each data point, only the city axis! I've tried a secondary axis, but again am unable to get the city name as the label.

I'd like to use 'simple' Excel to solve this, but if there's another solution (e.g. VBA, Python), that'd interest me too...

share|improve this question
3  
A scatter plot isn't used for this. A bar/column plot could be better suited. After reading on Excel, maybe a line plot would be suited as well, assuming you hid the lines. In Python I would direct you to the use of Matplotlib. With enough coding you could do whatever you wanted. –  Doktoro Reichard Sep 17 '13 at 22:27
    
What about using SPSS. That gets the job done: postimg.org/image/4njm99udh –  Vincent Sep 17 '13 at 22:28
    
@Vincent Isn't SPSS paid? Besides, how would one do that in there? Seems too similar to Excel to be a realistic solution. –  Doktoro Reichard Sep 17 '13 at 22:30
    
there is a walk-around that by it's way too broad for the question. –  me how Sep 18 '13 at 13:51
    
Yes SPSS is commercial software. There is a freeware equivalent (PSPP: gnu.org/software/pspp) that has similar options, although I am not sure if it is able to create graphs like SPSS. In SPSS I selected simple scatter (under graphs). It automatically groups the variables on the x-axis. –  Vincent Sep 18 '13 at 14:13

2 Answers 2

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:

Line Chart Example

share|improve this answer

I ended up using PyPlot, with the excellent guidance of http://matplotlib.org/gallery.html, to see the features that I was interested in using. Not for the non-programmer, but I think PyPlot can do absolutely anything.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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