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 wrote:
> 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, Microsoft MVP - Excel
http://www.andypope.info

[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>


"Andy Pope" <[email protected]> wrote in message
news:[email protected]...
> 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 wrote:
>> 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, Microsoft MVP - Excel
> http://www.andypope.info

[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/
_______


"BK" <[email protected]> wrote in message
news:[email protected]...
> 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>
>
>
> "Andy Pope" <[email protected]> wrote in message
> news:[email protected]...
>> 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 wrote:
>>> 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, Microsoft MVP - Excel
>> http://www.andypope.info
>
>