charting non-adjacent data cells

[BK]

Using Excel 2003. It seems that there is a limit of 5 or 6 non-adjacent
cells that can be selected to display on a single chart. Am I missing
something, or is that true?

The user has put the data labels in alphabetical order and wants to chart
different groups of those items together. I seem to run into trouble when
the group he wants to chart has more than 6 components.

 

[Andy Pope]

Hi,

It's not so much the number of non contiguous ranges but rather the
length of the address it creates in conjunction with the 1024 limit to
the series formula.

Series formula for a chart based on data in A1:B6, where B1 contains
series name, A2:A6 contains labels and B2:B6 contains data.
=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$6,Sheet1!$B$2:$B$6,1)

Same formula but only plotting even row cells.
=SERIES(Sheet1!$B$1,(Sheet1!$A$2,Sheet1!$A$4,Sheet1!$A$6),(Sheet1!$B$2,Sheet1!$B$4,Sheet1!$B$6),1)

The second chart plots half the information but the series formula is
nearly twice as long. If you change the sheet name to something
descriptive you can image the effect that will have on the series.

Cheers
Andy

 

[BK]

Thanks. It's probably the sheet name that is pushing me over rather than
the non-adjacent ranges I'm selecting. My user tried to name the sheets
with very clear labels, so I'll be able to save lots of characters there.
<grin>

 

[Jon Peltier]

The best approach is to clean up the worksheet. If you're always taking
every other cell, put the links into an adjacent column, and plot this
column. There's no real performance or size penalty for having data in two
places, as long as one is linked to the other or both are linked to the
original data, and the usability benefits are substantial.

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

Thanks. It's probably the sheet name that is pushing me over rather than
the non-adjacent ranges I'm selecting. My user tried to name the sheets
with very clear labels, so I'll be able to save lots of characters there.
<grin>