Just a simple line chart from 2 columns of data...

[dazed_and_confused]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I can't believe that Excel for MS is more intuitive than Excel for Mac but...
would someone tell me how to generate a simple line chart for this data?
Melt Time Surface Area
12.5 96
7.5 160
4.5 280
I expect 3 data points connected by a single line with the column headers being the X and Y labels - but I keep getting two lines, two legend values and some rather odd axis.

Is a Excel for Mac chart wizard in the works?

Help & TIA.

 

[Bob Greenblatt]

Melt Time Surface Area
12.5 96
7.5 160
4.5 280

I think what you are seeing is that if you highlight all 8 cells, Excel
thinks you wish to plot 2 series. If instead, you are trying to plot melt
time against surface area (or vice versa) you really want an XY scatter
chart. Highlight the 6 cells and pick one of the scatter charts.

 

[CyberTaz]

Just a footnote to Bob G's reply: Apparently your experience with Win Excel
precedes Office 2007... The "Chart Wizard" as you know it is a thing of the
past

 

[dazed_and_confused]

OK. That looks a lot more familiar. Is there a short-cut to getting my column headings to be my axis labels?

 

[CyberTaz]

Yes - Before generating the chart type an apostrophe before each of the
numbers you want used as labels [just formatting the cells as "Text" won't
suffice.]. In fact, you can then select the entire range & pick a Line Chart
like you were trying to do before. You'll get the results you expected.

The problem you were having is that the data is stored as values so Excel
picks them up and plots them as such. BTW: the same thing would have
happened in a PC version using the wizard

 

[dazed_and_confused]

I did as you suggested but ended up with the original line chart results. (?)

I also tried the text-marked headers with a scatter chart. The second header "Surface Area" appeared as the chart title - no axis labels. (?)

 

[CyberTaz]

Works fine here, but I should have asked for clarification since it wasn't
actually specified in any of your posts: which column of data are you trying
to plot? The numbers you want used for labels have to be in the left column,
the values to be plotted need to be to the right of them.

 

[dazed_and_confused]

I tried to copy the columns into my original post but they got "compressed". Two columns of data. The first row cells contain the headers (soon to be axis labels). The remaining rows contain values for both headers. Consider column one as the X axis and column two as the Y axis.

Sample:
R1C1 = Melt Time; R1C2 = Surface Area
R2C1 = 12.5; R2C2 = 96
Etc., etc.

 

[CyberTaz]

Well I just tried it again on my home system. Here's what I did:

B8 contains Melt C8 contains Surface
B9 contains '12.5 C9 contains 96
B10 contains '7.5 C10 contains 160
B11 contains '4.5 C11 contains 280

I selected all 8 cells, clicked the Charts button in the Elements Gallery &
chose Line, then clicked on the first icon - Line. The result gave me an X
Axis (vertical) scale ranging from 0-300, a Y Axis (horizontal) series of
12.5, 7.5, 4.5 along with a single blue Line connecting the data points of
96, 160 & 280 plus a Legend identifying the blue line as Surface. Surface is
also used as the chart title, but that can be changed.

Keep in mind that I'm not saying this is a *better* option than the XY
Scatter suggested by Bob G, just that it "works"

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[Laroche J]

I also tried the text-marked headers with a scatter chart. The second header
"Surface Area" appeared as the chart title - no axis labels. (?)

The axis titles cannot be defined by cell references, they are fixed text
strings defined in the Formatting palette's Chart Options, in the Title
section. You can also click on the titles and type directly in the boxes.

JL
Mac OS X 10.4.11
Office v.X 10.1.9, Office 2008 trial

 

[CyberTaz]

Not exactly

You also can select the Title or Axis label then enter the explicit
reference in the Formula Bar [such as =Sheet1!$B$7] in order to have the
content of that cell displayed as the label. It will update as does any
other formula if the cell content changes.

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[dazed_and_confused]

I have to go with the scatter diagram. It plots the X/Y values as I need. By selecting the "straight marked" style it is exactly what I need displayed. Axis labeling is still an issue.

It doesn't sound like I'm going to find a short-cut to getting cells in the data range to become axis labels (i.e. marking the cell values as text didn't work for me). So I went back to the tried and true method of editing the chart with labels once it's drafted - BUT I'm still having trouble.

Here's what I did: The data table is constructed as previously described (i.e. column headings with data in following rows). I selected the non-text values as my data range. Chosen a straight marked scatter chart. (The closest thing to a label is in the legend "series 1". I'll probably delete this as this is not a multiple series chart.) To edit the chart I've opened "select data source" (by highlighting different parts of the chart). From this menu I can see that I can readily type a chart title (or reference text in a cell). I skipped that for now. I see that there is a similar opportunity to add Category (X) axis labels (by typing or referencing). BUT I cannot click into the edit box OR select the "cell reference" icon. ? I toyed with everything else on this menu thinking I must not understand how to add an axis label (X or Y) - with no success. I also tried highlighting the chart axis directly to edit them but find no option for typing text or referencing header cells - at least that I can click on/into. (It seems to me that axis labeling was a step in what are now passe chart wizards.)

So the question is a sequel to the original - but it is still simple:
How do I add axis labels to a straight marked scatter chart?

 

[Laroche J]

Yes, exactly :-(

I agree that axis LABELS can be defined by =Sheet1!$B$7 (although this would
yield a one-value axis, so =Sheet1!$B$7:$B$10 would be more appropriate),
but not the axis TITLES, that must be defined as text strings. If I enter
=Sheet1!$B$7 in the title box definition, I literally get =Sheet1!$B$7 on
the chart as the axis title. If you can achieve otherwise please post
specific instructions.

JL
Mac OS X 10.4.11
Office v.X 10.1.9, Office 2008 trial



CyberTaz wrote on 2009-04-30 06:27:

 

[Laroche J]

I have to go with the scatter diagram. It plots the X/Y values as I need. By
selecting the "straight marked" style it is exactly what I need displayed.
Axis labeling is still an issue.

It doesn't sound like I'm going to find a short-cut to getting cells in the
data range to become axis labels (i.e. marking the cell values as text didn't
work for me). So I went back to the tried and true method of editing the chart
with labels once it's drafted - BUT I'm still having trouble.

Here's what I did: The data table is constructed as previously described (i.e.
column headings with data in following rows). I selected the non-text values
as my data range. Chosen a straight marked scatter chart. (The closest thing
to a label is in the legend "series 1". I'll probably delete this as this is
not a multiple series chart.) To edit the chart I've opened "select data
source" (by highlighting different parts of the chart). From this menu I can
see that I can readily type a chart title (or reference text in a cell). I
skipped that for now. I see that there is a similar opportunity to add
Category (X) axis labels (by typing or referencing). BUT I cannot click into
the edit box OR select the "cell reference" icon. ? I toyed with everything
else on this menu thinking I must not understand how to add an axis label (X
or Y) - with no success. I also tried highlighting the chart axis directly to
edit them but find no option for typing text or referencing header cells - at
least that I can click on/into. (It seems to me that axis labeling was a step
in what are now passe chart wizards.)

So the question is a sequel to the original - but it is still simple:
How do I add axis labels to a straight marked scatter chart?

OK, what you haven't understood is that in a XY-scatter chart the X-values
ARE the X-axis labels. So the famous "series 1" you want to delete is
exactly what you need to plot your chart. In the X-values box enter the
range of you first column data, and in the Y-values box enter the range of
you second column data. That's all.

Note that you could have many lines on the same XY-scatter chart, each with
its own set of Y-values, all using the same or their own set of X-values.
The X-axis labels will then encompass the whole range of X-values from all
data sets.

JL
Mac OS X 10.4.11
Office v.X 10.1.9, Office 2008 trial

 

[Bob Greenblatt]

Pardon my 2 cents, but pertaining to the following exchange:

As suggested above I believe you may be entering the formula in the wrong
place. If by "title box definition" you mean the actual text box where the
Axis Label displays in the Chart Area, that isn't what I wrote.

If you can achieve otherwise please post
specific instructions.

Were I not able to do so I would not have posted that I could nor given the
directions on how to accomplish it. In 2004 or 2008 the specific
instructions are no more involved than what I posted previously but I'll
enumerate them more explicitly...

Once Axis Titles are added to the chart:

1- Click the Axis Title box to select it,
2- Click in the Formula Bar Edit Field,
3- Type the formula that refers to the cell whose content you want displayed
in the Axis Title box. The example I gave assumes the cell reference to be
cell B7 on a sheet named Sheet1, thus the formula would simply be =Sheet1!B7
[Alternatively you can click the intended cell after typing the =]
4- Press return

Yes, any text box, whether in a chart as a title or just a notation, or
anywhere on a work sheet can be linked to cell contents. Just select the
text box, and enter a formula reference (like =sheet1A1) into the formula
bar. Press enter, and that text box will now be in the recalculation chain
and update whenever the cell contents change.

 

[Laroche J]

CyberTaz wrote on 2009-04-30 18:57:

Are you using v.X or 2008? I'm not sure whether v.X offers the same ability
although I would expect that it does. I'm also not sure what you mean by
"title box definition" but my instruction specifically referred to entering
the formula in the Formula Bar which I've never heard called by that term.
Please see the in line response below:

Al right, all right, my bad. I was not using the formula bar, I was using
the box labeled "Value (X) axis", under Titles in Chart Options. My
apologies. For the first time in a long while something new to try and use.

And yes it also works in v.X.

Thanks
JL

 

[CyberTaz]

Are you using v.X or 2008? I'm not sure whether v.X offers the same ability
although I would expect that it does. I'm also not sure what you mean by
"title box definition" but my instruction specifically referred to entering
the formula in the Formula Bar which I've never heard called by that term.
Please see the in line response below:

Yes, exactly :-(

My point was not that there was anything wrong about your 2 suggestions,
just to the statement that Axis Titles are "fixed text strings" which can't
be defined by cell references.

I agree that axis LABELS can be defined by =Sheet1!$B$7 (although this
would
yield a one-value axis, so =Sheet1!$B$7:$B$10 would be more appropriate),

That's true, but I'm not talking about labeling the data points - those are
Data Labels. I'm referring to the X & Y Axis Labels.

but not the axis TITLES, that must be defined as text strings.

As originally stated, not true.

If I enter =Sheet1!$B$7 in the title box definition, I literally get
=Sheet1!$B$7 on
the chart as the axis title.

As suggested above I believe you may be entering the formula in the wrong
place. If by "title box definition" you mean the actual text box where the
Axis Label displays in the Chart Area, that isn't what I wrote.

If you can achieve otherwise please post
specific instructions.

Were I not able to do so I would not have posted that I could nor given the
directions on how to accomplish it. In 2004 or 2008 the specific
instructions are no more involved than what I posted previously but I'll
enumerate them more explicitly...

Once Axis Titles are added to the chart:

1- Click the Axis Title box to select it,
2- Click in the Formula Bar Edit Field,
3- Type the formula that refers to the cell whose content you want displayed
in the Axis Title box. The example I gave assumes the cell reference to be
cell B7 on a sheet named Sheet1, thus the formula would simply be =Sheet1!B7
[Alternatively you can click the intended cell after typing the =]
4- Press return

JL
Mac OS X 10.4.11
Office v.X 10.1.9, Office 2008 trial

--
Regards |:>)
Bob Jones
Office:Mac MVP

CyberTaz wrote on 2009-04-30 06:27:

Not exactly

You also can select the Title or Axis label then enter the explicit
reference in the Formula Bar [such as =Sheet1!$B$7] in order to have the
content of that cell displayed as the label. It will update as does any
other formula if the cell content changes.

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[dazed_and_confused]

I'm responding to this post because it has been the most useful so far.

Scatter chart? Yes! Thank you. Now, can you give me more step-by-step instructions telling me exactly how to add axis labels? (At this point I don't care if they come from referenced "header" cells or Crayola crayons.)

TAI

 

[Laroche J]

1- Select the eight cells
2- Create an XY-Scatter chart, where your three data points should now be
plotted
3- This should create a grid where the X-axis values run from 0 to something
in the range 14 to 20 depending on the width of your chart. The Y-axis
values should run from 0 to 300.
4- The labels of the X-axis might be 0 - 5 - 10 - 15 , or 0 - 2 - 4 - 6 etc,
or 0 - 10 - 20 depending on the width of your chart. If you're not happy
with the automatically selected values, select the X-axis and from the
Format menu (or the Formatting palette) edit values in the Scale section.
There is Minimum (the first value at the left), Maximum (the last value at
the right), Major unit (the increment between each displayed value), Minor
unit (the increment between each unlabeled tick mark). Look also in the
Patterns section for showing or hiding the tick marks and the labels.
5- Repeat for the Y-axis

Are you looking for something else than your Melt times as X-axis labels?

JL
Mac OS X 10.4.11
Office v.X 10.1.9, Office 2008 trial

 

[CyberTaz]

I'm taking it at face value that you do actually mean "axis labels", so:

Click the chart. [If the Toolbox> Formatting Palette isn't displayed go to
View> Formatting Palette].

In the Chart Options - Titles section of the Formatting Palette open the
list which reads "Chart Title" & Select "Horizontal (Category) Axis" then
type the title into the box directly below the list.

Repeat for "Vertical (Value) Axis.

HTH |:>)
Bob Jones
[MVP] Office:Mac