Excel 2007 Pivot Chart Filtering Help

[tennisjon2002]

I have a pivot chart and table set up with some data I am working on for work. I when I filter data on the chart I would like to make it able to "filter down" if possible.

Here is a quick basic example of what I would like to be able to do.

If this was the data

Column 1 | Column 2 | Column 3
Male Tall Joe
Female Short Jane
Female Tall Ann


If I filtered the data in column one and set it for "Male" and then went to filter Column 2 I would like the only options to filter in coulumn 2 to know be "Tall", since there were no short males in the data. Then if I went to filter column 3 I would like the only option to be "Joe" since he is the only one who meets the previous filters for "Male" and "Tall". Right now my pivot chart shows all options when you filter, even if they are no longer applicable because of other filters. Is this possible to do?

 

[SkipVought]

Hi,

Not in the PT filter.

You would need to construct your own drill down, using a series of ComboBoxes, the result of each defines a criteria value for a query to 'filter' the next list, to only contain valid entries base on previous selection(s).

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JPaules]

Pivot tables work well for money or for counting items. That's really about the extent of it. What you want are filters.

--
JP

 

[SkipVought]

Yes, the PivotTable is an AGGREGATION REPORT tool.

You should also check out the AutoFilter on your data. As you select filter items, only relevant data is displayed in other Filter Drop Downs.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tennisjon2002]

I am using a pivot table because I have ~950 series of data and I can not use a normal chart with that much data. I am doing a spreadsheet involving some power distribution reliability numbers. I have the data and would like to filter it by region, area, substation etc and have that "filter down" or "drill down

 

[SkipVought]

What is a CHART going to add to the understanding of your data, at least the data thar you have described? NOTHING!!!

Check out the AutoFilter. It will do very close to what you have described.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tennisjon2002]

Skip that was just a simple example to show what I wanted it to be able to do.

My actual data goes something like

Area Zone Location Substation Circuit # Jan Feb Mar..
N W City xyz 1234 1 2 3
S E Town abc 9876 0 3 4
.
Except I have about 950 circuits.

I need to the chart to show the change in circuits over time. I can't use a normal chart because excel will only chart the first 255 or so circuits, and when you filter down it just hides the ones that are no longer applicable, therefore only charts the filtered data that was in the original 255. For example, if I have it filtered down to 5 circuits, it will only show the ones that were originally in the first 255, because it just hides the other cells. I was told by others on here a while ago that a pivot chart and pivot table were the only way to graph the data so that when I have it filtered it will graph all of the filtered data (as long as the filtered data is <255.

So.. yes the auto filter does exactly what I want to do, but it won't work in this case with my graph because I have ~950 series of data and excel will only graph the first prefiltered 255.

 

[SkipVought]

So what data are you charting: x & y axis?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tennisjon2002]

X axis contains the months, y axis is the numbers for the data each month.

 

[SkipVought]

I'm not understanding why the AutoFilter will not perform what you need. I assume, from you original post, that you want to "drill down" using area, zone, location, substation.

You CAN reference the WHOLE TABLE in the chart source data. Of course, viewing all the data is meaningless! Buts as you "drill down" you will see a meaningful 'picture' emerge.

I just took your structure and expanded it to over 1000 rows.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tennisjon2002]

Yes your assumption is right.
You can filter the data, but I don't see a way to make the data that is in row 255+ to display. It will only display the data that is in the original unfiltered first 255 rows.

 

[SkipVought]

Playing with it more and seeing that my chart was probably 'rotated', missing the 255 limit.

What I've done under these kinds of circumstances (and I work with charting data from databases where you can have millions of data points), you can query the source data (your sheet) in accordance with your selection(s) in an MS Forms Drop Down. It will involve using a bit of VBA code, if you are up to it. Otherwise, you are stuck with the PicotChart approch, with the inherent problem with the dependent filtering.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}