chart series

[Novice]

Hi, I would like to create a single chart using multiple columns as one series

error message states that title, values, sizes must be single cell, row or
column...

x axis A2:A12 y axis D2:F12 (legend name)

Thanking you in advance.

 

[Jon Peltier]

The easy way:

Duplicate A2:A12 in A13:A23 and A24:A34. Copy E2:E12 to D1323 and F2:F12
to D2434 and make your chart from columns A and D.

Hard way:

Make the chart using A2:A12 and D212. Go to Chart menu > Source Data >
Series. Select the series, change X values from

=Sheet1!$A$2:$A$12

to

=Sheet1!$A$2:$A$12,Sheet1!$A$2:$A$12,Sheet1!$A$2:$A$12

and change Y values from

=Sheet1!$D$2:$D$12

to

=Sheet1!$D$2:$D$12,Sheet1!$E$2:$E$12,Sheet1!$F$2:$F$12

and click OK.

- Jon

 

[Novice]

Thanks. Is there a way to maneuver around the 'series' box without changing
the formula unintentionally? Long formulas are hard to edit

 

[Jon Peltier]

You could edit the series formula (which is actually how I prefer to do
this). Select the series and look in the formula bar. Change this:

=SERIES(????,Sheet1!$A$2:$A$12,Sheet1!$D$2:$D$12,1)

to this:

=SERIES(????,(Sheet1!$A$2:$A$12,Sheet1!$A$2:$A$12,Sheet1!$A$2:$A$12),(Sheet1!$D$2:$D$12,Sheet1!$E$2:$E$12,Sheet1!$F$2:$F$12),1)

where ???? is either blank, a text string in quotes, or a range address,
used for the series name.

- Jon

 

[Novice]

Thanks again. That works great on charts that need to edited. The formula
bar doesn't show a long series formula for new charts. I've looked to see if
I have that turned off but can't find where that would be.

 

[Jon Peltier]

I don't understand this:

The formula bar doesn't show a long series formula for new charts.

If you select a series, you should get a SERIES formula in the formula bar.

- Jon

 

[Novice]

When creating a new chart and 'selecting data' the 'chart data range' window
opens. As I type a long formula; I can't backspace to edit something that is
beyond what appears in the window. (selecting data with the mouse works fine
except when selecting more than one column) The formula does not appear in
the fx box like it does when you click on a data point in the chart.

If you're still confused....disregard

A different question:

After I copy a number of charts to a different workbook can I edit the
charts formulas without changing each chart manually? Change the worksheet
the formula refers to? Cell references will be the same.

Chart 1
=SERIES("xyz",'2007 xyz'!$A$2:$A$50,'2007 xyz'!$B$2:$B$50,1)

Chart 2
=SERIES("xyz",'2008 xyz'!$A$2:$A$50,'2008 xyz'!$B$2:$B$50,1)

Thank again for your help!

 

[Jon Peltier]

When creating a new chart and 'selecting data' the 'chart data range'
window
opens. As I type a long formula; I can't backspace to edit something that
is
beyond what appears in the window. (selecting data with the mouse works
fine
except when selecting more than one column) The formula does not appear in
the fx box like it does when you click on a data point in the chart.

If you're still confused....disregard

I still don't understand the issue. It's not easy to work in the box, but
you can get around. Press F2, then use the arrow keys. To select multiple
ranges, select the first range, then hold Ctrl while selecting additional
ranges.

A different question:

After I copy a number of charts to a different workbook can I edit the
charts formulas without changing each chart manually? Change the
worksheet
the formula refers to? Cell references will be the same.

Chart 1
=SERIES("xyz",'2007 xyz'!$A$2:$A$50,'2007 xyz'!$B$2:$B$50,1)

Chart 2
=SERIES("xyz",'2008 xyz'!$A$2:$A$50,'2008 xyz'!$B$2:$B$50,1)

Ask new questions in a new thread.

I have an add-in that can be used for this (in most cases, it's not
perfect).

http://peltiertech.com/Excel/Charts/ChgSrsFmla.html

- Jon

 

[Novice]

Thanks. F2 works. Without it...

I still don't understand the issue. It's not easy to work in the box, but
you can get around. Press F2, then use the arrow keys. To select multiple
ranges, select the first range, then hold Ctrl while selecting additional
ranges.


Ask new questions in a new thread.

I have an add-in that can be used for this (in most cases, it's not
perfect).

http://peltiertech.com/Excel/Charts/ChgSrsFmla.html

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