How do I filter a pivot chart without modifying the data

jsimonuchc
jsimonuchc used Ask the Experts™
on
Hi,

I am trying to find a way to make a pivot chart only show me the top 10 results without changing the data. For example, I have the following data set

42      19.09%
36      16.36%
17      7.73%
16      7.27%
14      6.36%
11      5.00%
9      4.09%
9      4.09%
8      3.64%
6      2.73%
5      2.27%
4      1.82%
4      1.82%
4      1.82%
4      1.82%
3      1.36%
3      1.36%
2      0.91%
2      0.91%
2      0.91%
2      0.91%
2      0.91%
1      0.45%
1      0.45%
1      0.45%
1      0.45%
1      0.45%
1      0.45%
1      0.45%
1      0.45%
1      0.45%
1      0.45%
1      0.45%
1      0.45%
1      0.45%
1      0.45%
1      0.45%
220      100.00%

When I make a chart out of this, the chart has a TON of data points. I am really only interested in the results that are over 1% (the percent is the count out of the total). However, if I make use of the excel Autoshow top 10 feature, it recalculates the percentage to make the 10 items i show add up to 100%. I basically want to simply truncate any data <1%

Any ideas on how to do this? Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
see this link:
                    http://office.microsoft.com/en-us/excel/HP051993841033.aspx

Author

Commented:
The first link doesn't work for me, unfortunately, as I am never sure exactly what the data is going to be, so I can't pick up choose specific values. I need a way to always just show the top 10.

The second link is better, but do you know if there is a way to do those thinks in Excel 2003?
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Commented:
Take a look at this demo.

The easiest approach is to add a new column to your dataset. I am using a simple True/false evaluation, this works well with pivot pagefields, like I use in the demo.  You can adjust the cutoff of 1%, and the pivot will reflect the change as soon as you refresh.

The "Other" category is a bit tricky, only because it is in the last row. Depending on how real data gets into the range, this may be an inconvenient location. I thought about putting it into the first row, but this is a demo so I kept it simple.

If you go with a bar chart, then simply hide the rowitem "Other" and the bars will not add up to 100%.

An alternative solution is to not use a pivotchart.  I hate pivotcharts due to their tendency to clutter the axis labels, reformat on every refresh (including restoring default colors), and other problems. With a regular chart, you can gain control over the range the chart uses for series1 data, and use a formula to decide how many cells are in the range. That way the chart calculates the number of points/values to plot depending on the data, not on the construction of the pivottable.

Tom.

EE-25092893-chart-filter.xls
all links shown in this link appeared in the Excel 2003 search topic "filter pivot tables and pivot charts":
                 http://office.microsoft.com/en-us/excel/results.aspx?qu=filter+Pivot+tables+and+pivot+charts&sc=9&av=ZXL110
Commented:
Back to your requirement that the data is not changed:
I'm guessing your pivot does not look much like my demo, because a top 10 doesn't make a whole lot of sense with the data I'm using. First, if I choose top ten for the first column, I don't get 100%, I get 76% so I don't quite understand your comment. 2nd, the nature of pivottables is to aggregate data, so the results of "counts" and "sums" can have unexpected results in top ten filtering.

It's possible to change the "Chart" column formula to select the top 10, if that's preferable.  
Look at this demo.
I don't see a way to do it simply by manipulating the pivottable settings.

Tom.
EE-25092893-chart-filter.xls

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial