I have a spreadsheet that indicates the number of users for various products
from over 255 companies in rows. When I tried to create a pivot table and a
chart using those companies as row data I got a jumbled mess. I'd like to
filter the existing worksheet by the criteria of a certain number of users
per company and above that number.This would reduce the number of rows. Is
there no other way than using Advanced filter and having to alter my
spreadsheet or create a new one to include extra areas for criteria range
formulas. I want to include as many companies as possible without deleting
them arbitrarily from the rows field in the pivot table. I'm using Excel 2003.

Thank you in advance,

Paul

Paul,

Insert a column of formulas, with the formula

=UsersCutOff

like

=D225

and copy down to match. Then use that column as either a row field or page field, and select TRUE
as the value to display in your Pivot Table.

HTH,
Bernie
MS Excel MVP


"PayPaul" wrote in message
...
I have a spreadsheet that indicates the number of users for various products
from over 255 companies in rows. When I tried to create a pivot table and a
chart using those companies as row data I got a jumbled mess. I'd like to
filter the existing worksheet by the criteria of a certain number of users
per company and above that number.This would reduce the number of rows. Is
there no other way than using Advanced filter and having to alter my
spreadsheet or create a new one to include extra areas for criteria range
formulas. I want to include as many companies as possible without deleting
them arbitrarily from the rows field in the pivot table. I'm using Excel 2003.

Thank you in advance,

Paul

This is helpful to a degree. However, is it possible to create a chart with
all the company names in the row field without a jumbled mess. Should I just
use this same formula and increase the minimum number of users to reduce the
number of companies in the row field? I'm suspecting that pivot charts can't
handle large amounts of row data.

Thanks,

Paul

"Bernie Deitrick" wrote:

Paul,

Insert a column of formulas, with the formula

=UsersCutOff

like

=D225

and copy down to match. Then use that column as either a row field or page field, and select TRUE
as the value to display in your Pivot Table.

HTH,
Bernie
MS Excel MVP


"PayPaul" wrote in message
...
I have a spreadsheet that indicates the number of users for various products
from over 255 companies in rows. When I tried to create a pivot table and a
chart using those companies as row data I got a jumbled mess. I'd like to
filter the existing worksheet by the criteria of a certain number of users
per company and above that number.This would reduce the number of rows. Is
there no other way than using Advanced filter and having to alter my
spreadsheet or create a new one to include extra areas for criteria range
formulas. I want to include as many companies as possible without deleting
them arbitrarily from the rows field in the pivot table. I'm using Excel 2003.

Thank you in advance,

Paul

Paul,

You could increase the cut off to reduce the number of companies listed - remember that charts are
limited tools for display since they can only extend over one page visually. But then you lose
data.

You could cut your data further into segments, to decrease the number of companies included per
chart - maybe geographically, by business type, by size, by value, by whatever you can think of that
makes sense in the context of your data presentation, and then have multiple charts keyed to the
segment that you choose.

HTH,
Bernie
MS Excel MVP


"PayPaul" wrote in message
...
This is helpful to a degree. However, is it possible to create a chart with
all the company names in the row field without a jumbled mess. Should I just
use this same formula and increase the minimum number of users to reduce the
number of companies in the row field? I'm suspecting that pivot charts can't
handle large amounts of row data.

Thanks,

Paul

"Bernie Deitrick" wrote:

Paul,

Insert a column of formulas, with the formula

=UsersCutOff

like

=D225

and copy down to match. Then use that column as either a row field or page field, and select
TRUE
as the value to display in your Pivot Table.

HTH,
Bernie
MS Excel MVP


"PayPaul" wrote in message
...
I have a spreadsheet that indicates the number of users for various products
from over 255 companies in rows. When I tried to create a pivot table and a
chart using those companies as row data I got a jumbled mess. I'd like to
filter the existing worksheet by the criteria of a certain number of users
per company and above that number.This would reduce the number of rows. Is
there no other way than using Advanced filter and having to alter my
spreadsheet or create a new one to include extra areas for criteria range
formulas. I want to include as many companies as possible without deleting
them arbitrarily from the rows field in the pivot table. I'm using Excel 2003.

Thank you in advance,

Paul