Google Answers Logo
View Question
Q: Excel Chart ( Answered 5 out of 5 stars,   0 Comments )
Subject: Excel Chart
Category: Computers
Asked by: fdic-ga
List Price: $2.00
Posted: 27 Feb 2005 08:51 PST
Expires: 29 Mar 2005 08:51 PST
Question ID: 481782
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.
Subject: Re: Excel Chart
Answered By: maniac-ga on 27 Feb 2005 09:26 PST
Rated:5 out of 5 stars
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

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
in the formula bar and the value
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

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.


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.
fdic-ga rated this answer:5 out of 5 stars
That worked perfectly!  Thanks so much.

There are no comments at this time.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  

Google Home - Answers FAQ - Terms of Service - Privacy Policy