text label in x-axis (scatter chart)

[andy650]

I'd like to create a scatter chart but force all the data points into
one column for each series (as poorly illustrated below if viewed using
fixed font).

x x
x
x x

x
x x


x


Series A Series B


There are a few issues I'm trying to work around. First, scatter
plots seem to require a numeric value. If I have text in the first
column of data, then the chart will spread the data points
horizontally. If I create a numeric "label" in the first column, I
at least get the visual effect I'm looking for but then I have to
manually create a text box with the series name below each series.
This is tedious since I'd like to plot lots of series and would like
to eventually automate chart creation via a macro. I can't find any
way to have a text label in the x-axis that references a cell from the
worksheet.

Stock charts get me pretty close but I really want to display all data
points as discrete points on the chart.

Any ideas? Any help is much appreciated,
Andy

 

[Andy Pope]

Hi,

You could tackle this in two ways, you will have to decide which best
suits you.

Use the XY scatter, plotting just the x and y values, so you get the
layout required. Then use either of these free add-ins to link data
labels with the text in the cells.

Rob Bovey's Chart Labeler
http://appspro.com

Or John Walkenbach's Chart Tools
http://j-walk.com

Or with your data laid out like so,
B1: =Series1 C1: =Series2
A2: =Label1 B2: =3 C2: =4
A3: =Label2 B3: =5 C3: =2
A4: =Label3 B4: =2 C4: =6

Create a line chart with data plotted by Rows. This will give you the 2
columns with each series consisting of 2 points. You will need to format
each series to have a Line of none. You can change 1 series and then use
the F4 button to repeat the changes to the currently selected item.

Cheers
Andy

 

[Andy]

The second method gets me closer to what I'd like to do. Thanks! Now
all I have to do is write a simple macro to format all the series
identically and I'll be even closer.