max number of data ranges (bars) in a bar chart

[awaterworld]

I am trying to create a simple bar graph with 23 different data ranges, there
is all the room in the world for 23 bars in a single graph, yet when I start
selecting the data ranges (or labels for that fact) it gets to about 17 of
them, and then erases everything I have selected and starts over. Ending up
with a graph that only contains the last 4 or 5 data ranges????? I have also
search every help and internet and see addressing this???

 

[Andy Pope]

Hi,

Is your data in non-contiguous ranges? If so this will create a reference
that maybe too long.

Either move the data to a contiguous range or create the chart on a small
set of data and then add each series separately.

Cheers
Andy

 

[awaterworld]

I will try to use an example; Lets say I have 25 people that I want to
measure a paticular metric on the SAME graph, using a vertical bar chart. The
data sheet lists all 25 of the people's names at top of column, which will be
the Category (X) axis labels. The 25 columns have data stored in a range,
with a total at the bottom of each. The minute I try and even create the
graph by first adding the axis labels, it lets me select about 17 of them
before it clears out the data i selected and starts over,, like excel is
trying to tell me for some reason you can only measure (compare) 17 different
data values in a single graph. The problem with splitting them up into 2
graphs, is you do not get a true comparison amoung all the people, as it
scales to whatever the group in that graph happen to be. IS 17 the max??

 

[Andy Pope]

As I said the problem is due to formula length.

See the difference between selection of the Range A1:E6.
Selected as a contiguous range.
=Sheet1!$A$1:$E$6

Same range selected as single rows, non contiguous.
=Sheet1!$A$1:$E$1,Sheet1!$A$2:$E$2,Sheet1!$A$3:$E$3,Sheet1!$A$4:$E$4,Sheet1!$A$5:$E$5,Sheet1!$A$6:$E$6

Create your chart on a small number of rows.
Then use the Source Data dialog to Add further rows.

Cheers
Andy

 

[awaterworld]

I see what you are saying, however I get the same error when I try to create
with the first 15 columns, and then try to add a couple at a time, it erases
all of them and I get the "series formula too long". I read where excel has
a 1024 character max furmula length? and excel 2007 has over 8000 char
length,, which is bull because I just tried on one of our 2007 machines and
it only allowed 16 columns (or about 10 more characters). I even tried
renaming the "sheet" name to "1" so it would save repeating the long sheet
name each time and that only gained me an extra column or 2. I am basically
screwed,, unless I take a day and reference all these values on a different
sheet in a contiguous fashion,,, bummed.

 

[Andy Pope]

If you want to email the workbook I will take a look see.
You can change the text/values for confidentiality but keep the layout.

Cheers
Andy