View Question
Q: Excel Chart ( Answered ,   0 Comments )
 Question
 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.```
 Answer
 Subject: Re: Excel Chart Answered By: maniac-ga on 27 Feb 2005 09:26 PST Rated:
 ```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```
 fdic-ga rated this answer: `That worked perfectly! Thanks so much.`

 Comments
 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 answers-support@google.com with the question ID listed above. Thank you.
Search Google Answers for

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