Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Suzan
 
Posts: n/a
Default scatter chart x axis problem

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   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: scatter chart x axis problem

Hi Suzan,

Yes, it is definitely possible to use the values in column 1 as the x-axis values in a scatter chart. Here's how you can do it:
  1. Select the data range that you want to use for the chart, including the column 1 values that you want to use as the x-axis values.
  2. Click on the "Insert" tab in the ribbon at the top of the Excel window.
  3. Click on the "Scatter" chart type in the "Charts" section of the ribbon.
  4. Excel will automatically create a scatter chart with the default x-axis and y-axis values. To change the x-axis values to the values in column 1, right-click on the chart and select "Select Data" from the context menu.
  5. In the "Select Data Source" dialog box, click on the "Edit" button next to the "Horizontal (Category) Axis Labels" field.
  6. In the "Axis Labels" dialog box, select the range of values in column 1 that you want to use as the x-axis labels. You can either type in the range manually or use the mouse to select the range.
  7. Click "OK" to close the "Axis Labels" dialog box, and then click "OK" again to close the "Select Data Source" dialog box.

Your scatter chart should now have the values in column 1 as the x-axis values. If you need to make any further adjustments to the chart, you can use the various formatting and layout options in the "Chart Tools" section of the ribbon.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Jon Peltier
 
Posts: n/a
Default

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   Report Post  
Suzan
 
Posts: n/a
Default

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


  #5   Report Post  
Jon Peltier
 
Posts: n/a
Default

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Scatter Chart Axis as text in C# Web appl Pavr1 Charts and Charting in Excel 1 March 31st 05 05:28 AM
Scatter Chart Axis as text C# Pablo Villalobos via OfficeKB.com Charts and Charting in Excel 1 March 18th 05 11:10 PM
Second X axis at top of chart? Phil Hageman Charts and Charting in Excel 1 December 29th 04 02:48 PM
Scatter chart x axis tick mark lables so Charts and Charting in Excel 2 December 2nd 04 06:06 AM
the dates to label the x axis on the scatter chart won't print waterlady Charts and Charting in Excel 0 November 29th 04 07:44 PM


All times are GMT +1. The time now is 06:47 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"