scatter chart x axis problem

#1 05-27-2005, 06:05 PM

The data is in three columns. My client wants the values in column 1 to also
be the x-axis values. Is this possible? Nothing that I have tried has worked.
Thanks in advance.

Suzan

#2 05-28-2005, 03:05 AM

Suzan -

If you're making an XY Scatter chart, Excel automatically uses the first
column as the X values, and uses the rest of the columns for Y values
for one less series than you've selected columns.

If you're making a line chart, Excel usually uses each column of data as
Y values for another series, and uses counting numbers (1, 2, 3,..) for
the category values.

To help Excel realize what you want for a Line chart, put series names
in the top row, and leave the cell above the category labels blank:

Ser.1 Ser.2 Ser.3
10 120 130 145
20 125 140 135
30 135 145 140

I'm in the habit of doing this even for scatter charts. One of my few
good habits.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Suzan wrote:
> The data is in three columns. My client wants the values in column 1 to also
> be the x-axis values. Is this possible? Nothing that I have tried has worked.
> Thanks in advance.
>
> Suzan

#3 05-31-2005, 09:05 PM

Jon - I see where you went with this and I tried it by making a copy of
column A and removing the header. It then worked with a line chart but not
with the scatter chart. The scatter chart gave me values and I could change
the scale but I couldn't get it to give each value as listed in the data of
the new column A. (The data is not evenly dispersed, i.e., 31.5, 64, 192,
etc.)

Suzan

"Jon Peltier" wrote:

> Suzan -
>
> If you're making an XY Scatter chart, Excel automatically uses the first
> column as the X values, and uses the rest of the columns for Y values
> for one less series than you've selected columns.
>
> If you're making a line chart, Excel usually uses each column of data as
> Y values for another series, and uses counting numbers (1, 2, 3,..) for
> the category values.
>
> To help Excel realize what you want for a Line chart, put series names
> in the top row, and leave the cell above the category labels blank:
>
> Ser.1 Ser.2 Ser.3
> 10 120 130 145
> 20 125 140 135
> 30 135 145 140
>
> I'm in the habit of doing this even for scatter charts. One of my few
> good habits.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
>
> Suzan wrote:
> > The data is in three columns. My client wants the values in column 1 to also
> > be the x-axis values. Is this possible? Nothing that I have tried has worked.
> > Thanks in advance.
> >
> > Suzan
>

#4 06-03-2005, 09:05 AM

Suzan -

Scatter charts treat each X "value" as a value, while Line charts treat
them as nonnnumeric category labels (or date values). If you want evenly
spaced labels regardless of value (distance from 31.5 to 64 same as from
64 to 192), use a line chart, and format the series to remove the lines
connecting the points.

If you want the axis to represent the numerical values of the X data,
but want a tick only where you have a point, add a dummy series to the
chart. This series uses the same X values, but zero (or whatever is the
Y axis minimum) as Y. This series then has a set of points along the X
axis of the chart. Hide the regular axis ticks and labels (double click,
Patterns tab, option None where appropriate), format the new series as
black crosses, to mimic tick marks, and apply data labels (the Show
Values option) to put pseudo tick labels beneath these pesudo tick marks.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Suzan wrote:
> Jon - I see where you went with this and I tried it by making a copy of
> column A and removing the header. It then worked with a line chart but not
> with the scatter chart. The scatter chart gave me values and I could change
> the scale but I couldn't get it to give each value as listed in the data of
> the new column A. (The data is not evenly dispersed, i.e., 31.5, 64, 192,
> etc.)
>
> Suzan
>
> "Jon Peltier" wrote:
>
>
>>Suzan -
>>
>>If you're making an XY Scatter chart, Excel automatically uses the first
>>column as the X values, and uses the rest of the columns for Y values
>>for one less series than you've selected columns.
>>
>>If you're making a line chart, Excel usually uses each column of data as
>>Y values for another series, and uses counting numbers (1, 2, 3,..) for
>>the category values.
>>
>>To help Excel realize what you want for a Line chart, put series names
>>in the top row, and leave the cell above the category labels blank:
>>
>> Ser.1 Ser.2 Ser.3
>>10 120 130 145
>>20 125 140 135
>>30 135 145 140
>>
>>I'm in the habit of doing this even for scatter charts. One of my few
>>good habits.
>>
>>- Jon
>>-------
>>Jon Peltier, Microsoft Excel MVP
>>Peltier Technical Services
>>Tutorials and Custom Solutions
>>http://PeltierTech.com/
>>_______
>>
>>
>>Suzan wrote:
>>
>>>The data is in three columns. My client wants the values in column 1 to also
>>>be the x-axis values. Is this possible? Nothing that I have tried has worked.
>>>Thanks in advance.
>>>
>>>Suzan
>>

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)