I am doing an excel sheet for work but I am completely stumped on a problem.
I have a sheet with about 950 rows and a few columns showing features and about 10 columns showing a trend in data. I need to filter the data by the features and have the filtered data automatically show in a line graph. The problem is excel will only allow 255 series per graph (so it won't allow me to select all of the data before I filter it.) Right now I have the first 255 rows set in a graph. The problem is when the data is filtered the data stays in its original assigned cell so it will only graph the data that was originially in the first 255 rows. So basically if after the filter there were 2 series and one was originally in row 100 and one was in row 300, only the the one in row 100 shows on the graph. I need the graph to show all of the filtered data. Hopefully that makes sense. Any ideas?

 

Pivot table and pivot chart?

 

Select the filtered rows

edit>go to>special>visible cells only. Should work. If not do the same but copy and paste into a new sheet.

 

Nope doesn't do anything. The copy and paste would probably work but I am trying to make it graph automatically without having to make a new graph each time you filter the data.

 

I am not familiar with pivot stuff but I have been trying for a while this morning with them and can't seem to get them to do what I need. They seem to be more for summarizing data.

 

Here is a better idea of what I have:

Area | Zone | Location | Substation | Circuit # | Jan | Feb | Mar | Apr | May | June.....
X......Y.........Z............A................1.. ...........0......4.......5.......1......2
Z......A.........C............B................2.. ...........1......0.......1.......2......0
. . . . . . . . . .
. . . . . . . . . .

There are 950 rows. I need to be able to filter the circuits by area, zone, substation etc and have the filtered data show in the graph.

 

Is this using the automatic filtering where the first row becomes a series of dropdowns?

 

A pivot table should still work.

 

use a pivot table to organize your data, then just copy and paste it into a separate tab that contains your charting information. you'll have to do it each time you want a new graph though.

 

http://chandoo.org/wp/2009/02/12/mak...-data-filters/

 

That is exactly what I want to do, but I have 950 rows and you can only graph 255 in a single graph. It will only let me select the first 255 of my data.

 

What version of Office do you have?

 

2007

 

After filtering will you ever have > 255 data series?

 

No.

 

After a quick search I don't think you have the option to do what you want.. you may have to rearrange your data so you only have 255 maximum series.

 

There may be a work around with a macro or by referencing a range, but I don't have Excel on this computer... Your best bet for right now is to reference a range of 255 rows and once you filter just copy and paste the results to this range and your chart should update.

 

I messed around with the pivot tables and charts some and I have it almost all figured out. The only thing I am stuck on now is the chart showing sums.
For example on this graph:
http://img18.imageshack.us/img18/1378/grapht.png
The series shown is one of the ones on the very top of the chart in the 700-800 range, but the value that I want it to actually graph is only 6? How can I get it to do this?

Here are my pivot table and chart lists:

 

sorry here are the pivot table lists:

 

@ fiendlist

Sorry, no help from me though =(.

 

Still can't figure this out....

 

do you have a field that sums the values you want to sum inside the chart? rather than having the chart try to sum several fields, you just pick the field that IS the sum.

 

I'm not quite understanding what you're trying to accomplish, so bear that in mind if I'm totally off track.

If you're just trying to get a single sum for each month, take the circuit number out of the pivot table. This will give you a summation for all circuits.

 

Nope, not trying to do anything with sums. Want to essentially do this:http://chandoo.org/wp/2009/02/12/mak...-data-filters/
except I have 950 series and excel only allows 255. I have been told the way to get around that is to use a pivot table and chart

 

Can you use Access instead?

 

4?

 

Can you describe what you want the chart to look like (what's on each axis, what type of chart, do the series stack up)?

 

I found out the problem. I was using a stacked line graph instead of a normal line graph

 

So problem solved by pivot table/chart?