text label in x-axis (scatter chart)

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

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


[email protected] wrote:
> 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, Microsoft MVP - Excel
http://www.andypope.info

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.

Andy Pope wrote:
> 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
>
>
> [email protected] wrote:
> > 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, Microsoft MVP - Excel
> http://www.andypope.info