XY Scatter Plot - Change Labels From Numeric To Text Programmatica

R

Refresher

Hello,
Let's say I have data like this:

A 1
A 2
A 3
B 4
B 2
C 1
C 5
C 0

I want to create an XY scatter plot where the values in the first column are
the X-Axis and the values in the second column are values for the Y-Axis.

Right now, I can only get numeric labels for the X-Axis. I want the labels
to be text, namely A, B and C

How can I do this *programmatically* in VB.NET or C#? I know there are Excel
tool add-ins that allow this to be done manually, but I am doing some
automation.

My only 2, non-optimal, solutions thus far are:
1. To hide the numeric labels and manually try to place the text labels via
text boxes using the size of the chart and other funky things to create an
algorithm. Very ugly
2. To use a line chart, not show the lines and treat each row as a series.
This works well until I get over 255 data points then I run into the 255
series limit in Excel 2003 (and not fixed in 2007). Then I get into the
situation where I have to create multiple worksheets and charts. Again,
better than (1), but still ugly.

Any help would be appreciated.

Thanks.
 
B

Bernard Liengme

Assuming your data is JUST as you show it, there is no way to make an XY
chart of this,
an XY chart needs pairs of numeric data (like the graphs you made in Grade 9
algebra - if you are North American) You have what is called "category data"
for you x-values.
I think you need a Line chart
best wishes
 
R

Refresher

You can create a XY Scatter with that data; it just treats the text values as
a series and assumes a 1, 2, 3, 4, 5... type of X-Axis.

What I want is to have the text actually be the labels of the X-Axis.

Say I had

1 1
1 2
1 3
2 4
2 1
3 0
3 5

You can easily create an XY Scatter out of that. Now pretend 1 = A, 2 = B, 3
= C....that's what I want.
 
R

Refresher

Right. That is exactly how I do it now. See my solution (2) in my original
post.
But I run into the Series limit problem because in order to get a scatter
look from a line chart, you have to treat each row as a series. That's
non-optimal. I know there has to be a way to do it programmatically with a
scatter plot -- I mean add-ins have been made to allow you to do it manually.
 
J

Jon Peltier

Insert a column which provides numerical equivalents (e.g., 1, 2, 3 for A,
B, C). Start with a column chart with this data:

A 0
B 0
C 0

This gives you the labels A, B, C across the X axis. Now add your data

1 1
1 2
1 3
2 4
2 1
3 0
3 5

as a new series, and change it to an XY chart. Then force it back onto the
primary axis. 1 will align with A, 2 with B, etc.

- Jon
 
R

Refresher

Jon,
Thanks for the reply.
Could you go into a little more detail? I am trying to do this manually in
Excel. I easily created the column chart and got the A, B, C on the X-Axis,
but when I tried to add a new series (Series 2), and select the "real" data
to be part of that, I got an error that said "The reference is not valid.
References for titles, values, or sizes must be single cell, row or column".

What am I missing?

Thanks a lot.
 
J

Jon Peltier

Start with the column chart. Copy the data for the XY series data (both
columns), select the chart, go to Edit menu > Paste Special, and add the
data as a new series, categories in first column. This will temporarily mess
up the chart, but don't worry. Select the added series, go to Chart menu >
Chart Type, and select an XY chart type. Excel puts the XY series onto the
secondary axis; double click the XY series, and on the Axis tab, select
Primary.

- Jon
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top