Okay all you Excel gurus, I wish to build a scatter XY scatter chart
which details graphically a factor (say profit margin) per location. 
The problem is that the location numbers are all over the board i.e.
194, 794, 802 etc. so the wizard orders the data by this order.  What
I want it to do is just put the data in the order I specify in a
separate column so the factor is displayed in a logical manner. 
However I do want each data point to be labeled with the location
number and the margin factor.  The goal is to see all of our locations
on a chart and there overall profit margins so we can compare them to
each other and spot trends.

I have tried many configurations and the problem is they always end up
ordering the data by the location number which is hard to read.  The
data points need to be evenly displayed and labeled.

If anyone is interested I will email a screenshot of what I am able
get now and this may help you understand better.

Hello Fdic,

A screen shot is not necessary. What you describe is pretty stright
forward but not very easy to do automatically in Excel unless you do a
"trick".

For an example, I created a worksheet like this:

X     Location  Profit Margin
1         194           2%
2         820           3%
3         702           6%
4         210           3%

When I did 
  Insert -> Chart...
and selected a scatter chart, the default action was to create two
series - one labeled "Location" and the second labeled "Profit
Margin". I assume the second series is what you want with the first
series as the labels.

The "trick" is to change the location values to text. What I then did
was to enter a single quote "'" at the beginning of each location
number. So the first one will be displayed as
  '194
in the formula bar and the value
  194
will be displayed left justified in the spreadsheet (unless you change
the cell formatting). Change all the values and then select the two
columns (Location and Profit Margin) and now do
  Insert -> Chart...
the default scatter chart is in the order of values in the spreadsheet
cells and when you select "Show Value" as part of the formatting, the
value from the Location cell will be displayed next to each plotted
Profit Margin point.

Of course - maybe you need the locations to be a number (and not
text). In that case, I suggest a separate column for the location (as
a number). Just be sure to plot using the text value (and not the
number).

If this does not work for you - please make a clarification request
indicating other factors that must be addressed. I should be able to
come up with a suitable solution.

  --Maniac

---

Request for Answer Clarification by fdic-ga on 27 Feb 2005 10:22 PST

Seems to work but it shows the order i.e. 1, 2, 3, 4, 5, acroos the
bottom of the chart.  Can I make it show the location numbers so it is
easier to read?  Everything else looks perfect.

---

Clarification of Answer by maniac-ga on 27 Feb 2005 10:40 PST

Hello Fdic,

What you ask for is certainly "reasonable" but I am not aware of any
way to replace the "X" axis labels with a user specified range of
labels for a scatter chart. However there is an alternative:
  select Location and Profit Margin
  Insert -> Chart...
  choose a Line chart
  (format as you desire...)
At this point, it is possible to remove the line by doing something like:
  Select the line
  bring up the "format" dialog box (from the Chart toolbar or a right click menu)
  Change the line type from "Automatic" to "None"
which should give you the chart you are looking for.

[note - this method can also allow you to use numbers (and not text)
as the Locations but the wizard requires you to specify the X-axis
labels manually. If you try it you should see what I mean]

Good luck.
  --Maniac

That worked perfectly!  Thanks so much.