Is there any way of showing the top 20 entries in a pivot table, then
grouping the remaining entries as one, i.e. 'Other'.

Yes, you can show the top 20 entries in a pivot table and group the remaining entries as 'Other'. Here's how you can do it:

1. Create a pivot table with the data you want to analyze.
2. Drag the field that you want to show the top 20 entries for into the Rows or Columns area of the pivot table.
3. Right-click on one of the entries in the field and select 'Filter' 'Top 10' (or 'Top 20' depending on how many entries you want to show).
4. In the 'Top 10 AutoFilter' dialog box, select the 'Top' tab and enter the number of entries you want to show (e.g. 20).
5. Click 'OK' to apply the filter and show only the top 20 entries.
6. Right-click on one of the entries in the field again and select 'Group'.
7. In the 'Grouping' dialog box, enter 'Other' as the name for the new group.
8. Select the entries that you want to group together (i.e. all the entries that are not in the top 20).
9. Click 'OK' to group the entries together as 'Other'.
10. Your pivot table should now show the top 20 entries and a group called 'Other' that contains all the remaining entries.

That's it! You have successfully shown the top 20 entries in a pivot table and grouped the remaining entries as 'Other'.

You need to provide more information about the fields in your PivotTable. The
Top 10 filter (which can be anything like Top 5, Top 20) can only be applied
a single field in your Values report area.

To find the Top 20 in all of the Values report area of your PivotTable,
apply Conditional Formatting to highlight the cells.

To get rid of the "Other" data depends if it is in one field or many fields
in your PivotTable. You can use a calculated field to add them, count them
etc. and name it Other and then remove the "Other" fields from the PivotTable.

Again, without informaton about what fields and their location in a
PivotTable report area, it is difficult to answer your question completely.
--
Gnothi se auton.


"Sarah (OGI)" wrote:

Is there any way of showing the top 20 entries in a pivot table, then
grouping the remaining entries as one, i.e. 'Other'.

To do what you are asking will require you to add a field to yoru source
data. By using array, sumproduct and the rank/large functions you should be
able to identify the top 20 and the others...
--
HTH...

Jim Thomlinson


"xrelanon" wrote:

You need to provide more information about the fields in your PivotTable. The
Top 10 filter (which can be anything like Top 5, Top 20) can only be applied
a single field in your Values report area.

To find the Top 20 in all of the Values report area of your PivotTable,
apply Conditional Formatting to highlight the cells.

To get rid of the "Other" data depends if it is in one field or many fields
in your PivotTable. You can use a calculated field to add them, count them
etc. and name it Other and then remove the "Other" fields from the PivotTable.

Again, without informaton about what fields and their location in a
PivotTable report area, it is difficult to answer your question completely.
--
Gnothi se auton.


"Sarah (OGI)" wrote:

Is there any way of showing the top 20 entries in a pivot table, then
grouping the remaining entries as one, i.e. 'Other'.

The left hand pane of my pivot table shows a list of company names. The
corresponding column shows the associated amounts, by company. 'Sum of
Amount' is in cell A9.

I'd like to be able to show the Top x number of rows, say for this example,
10 rows (so up to and including Company J). I'd like to group the remaining
rows (Company K to Company T inclusive) as 'Other' and sum the amount for
that group. As a result, I'd like to create a graph that identifies the
market share by the top 10 companies, but also including one chunk which
represents the amount of business placed by 'other'.

The data for this pivot table will change on a monthly basis, therefore the
top 10 companies may differ each time, therefore I don't think its just a
case selecting the top 10 companies and grouping that list.

Sum of Amount pt_class_code
Motor
company_name New Business
Company A £100,000
Company B £90,000
Company C £80,000
Company D £70,000
Company E £60,000
Company F £50,000
Company G £40,000
Company H £30,000
Company I £20,000
Company J £19,000
Company K £18,000
Company L £17,000
Company M £16,000
Company N £15,000
Company O £14,000
Company P £13,000
Company Q £12,000
Company R £11,000
Company S £10,000
Company T £9,000
Grand Total £694,000

I hope that makes sense. I didn't want to makes things too complicated.


"xrelanon" wrote:

You need to provide more information about the fields in your PivotTable. The
Top 10 filter (which can be anything like Top 5, Top 20) can only be applied
a single field in your Values report area.

To find the Top 20 in all of the Values report area of your PivotTable,
apply Conditional Formatting to highlight the cells.

To get rid of the "Other" data depends if it is in one field or many fields
in your PivotTable. You can use a calculated field to add them, count them
etc. and name it Other and then remove the "Other" fields from the PivotTable.

Again, without informaton about what fields and their location in a
PivotTable report area, it is difficult to answer your question completely.
--
Gnothi se auton.


"Sarah (OGI)" wrote:

Is there any way of showing the top 20 entries in a pivot table, then
grouping the remaining entries as one, i.e. 'Other'.

Hi Sarah

You can't do it totally automatically, but there would be very little to do
each month to give your required result.
To get the top 20
double click on the Company fieldAdvancedAutosortDescendingUsing
fieldSum of valueOKOK

This will give your companies in descending order of value

You can highlight any company names below the top 20right clickGroup and
Show DetailGroup
This will allocate the name Group1 which you can overtype with whatever name
you wish.
Double click the Group title, and the rows will be filtered to show the
total value for the Group below the lowest of your top 20 companies.

If you want to Ungroup them, repeat the procedure but choose Ungroup.

You will need to Ungroup each Month, do a Refresh, then Group those values
below the top 20 once again.
--
Regards
Roger Govier



"Sarah (OGI)" wrote in message
...
The left hand pane of my pivot table shows a list of company names. The
corresponding column shows the associated amounts, by company. 'Sum of
Amount' is in cell A9.

I'd like to be able to show the Top x number of rows, say for this
example,
10 rows (so up to and including Company J). I'd like to group the
remaining
rows (Company K to Company T inclusive) as 'Other' and sum the amount for
that group. As a result, I'd like to create a graph that identifies the
market share by the top 10 companies, but also including one chunk which
represents the amount of business placed by 'other'.

The data for this pivot table will change on a monthly basis, therefore
the
top 10 companies may differ each time, therefore I don't think its just a
case selecting the top 10 companies and grouping that list.

Sum of Amount pt_class_code
Motor
company_name New Business
Company A £100,000
Company B £90,000
Company C £80,000
Company D £70,000
Company E £60,000
Company F £50,000
Company G £40,000
Company H £30,000
Company I £20,000
Company J £19,000
Company K £18,000
Company L £17,000
Company M £16,000
Company N £15,000
Company O £14,000
Company P £13,000
Company Q £12,000
Company R £11,000
Company S £10,000
Company T £9,000
Grand Total £694,000

I hope that makes sense. I didn't want to makes things too complicated.


"xrelanon" wrote:

You need to provide more information about the fields in your PivotTable.
The
Top 10 filter (which can be anything like Top 5, Top 20) can only be
applied
a single field in your Values report area.

To find the Top 20 in all of the Values report area of your PivotTable,
apply Conditional Formatting to highlight the cells.

To get rid of the "Other" data depends if it is in one field or many
fields
in your PivotTable. You can use a calculated field to add them, count
them
etc. and name it Other and then remove the "Other" fields from the
PivotTable.

Again, without informaton about what fields and their location in a
PivotTable report area, it is difficult to answer your question
completely.
--
Gnothi se auton.


"Sarah (OGI)" wrote:

Is there any way of showing the top 20 entries in a pivot table, then
grouping the remaining entries as one, i.e. 'Other'.

I know this is old, but it came up when I was searching for the same answer - so figured it a good place to post the automatic solution I just figured out..

1. Don't have any filters or groups on the pivot table

2. Sort the pivot table on what you want you want at the top

2. Hide row grand totals, you will create this yourself if needed

3. Somewhere under your pivot table, much further down than where you ever expect the data to extend to, say row 1000, make an "Others" row as you'd like it to appear, with a =sum() formula extending from the previous row (999), up to the first row you wish to group into 'others'. ie. for a top 20 report, from 24 (or wherever the 21st entry appears) through to 999

4. Create a Grand Total row under this if you want, same formula, expect extend it to the first row in your pivot table

5. Hide the rows from 24 through to 999 (in this example)

DONE !!

Refreshing the pivot table may move your data around and give you a different top 20, but the hidden rows will remain hidden and the formulas will continue to capture their values

Happy to provide an example if needed

On Thursday, November 22, 2007 10:22 AM sara wrote:

Is there any way of showing the top 20 entries in a pivot table, then
grouping the remaining entries as one, i.e. 'Other'.


On Thursday, November 22, 2007 10:42 AM xrelano wrote:

You need to provide more information about the fields in your PivotTable. The
Top 10 filter (which can be anything like Top 5, Top 20) can only be applied
a single field in your Values report area.

To find the Top 20 in all of the Values report area of your PivotTable,
apply Conditional Formatting to highlight the cells.

To get rid of the "Other" data depends if it is in one field or many fields
in your PivotTable. You can use a calculated field to add them, count them
etc. and name it Other and then remove the "Other" fields from the PivotTable.

Again, without informaton about what fields and their location in a
PivotTable report area, it is difficult to answer your question completely.
--
Gnothi se auton.


"Sarah (OGI)" wrote:


On Thursday, November 22, 2007 11:01 AM James_Thomlinso wrote:

To do what you are asking will require you to add a field to yoru source
data. By using array, sumproduct and the rank/large functions you should be
able to identify the top 20 and the others...
--
HTH...

Jim Thomlinson


"xrelanon" wrote:


On Thursday, November 22, 2007 11:15 AM sara wrote:

The left hand pane of my pivot table shows a list of company names. The
corresponding column shows the associated amounts, by company. 'Sum of
Amount' is in cell A9.

I'd like to be able to show the Top x number of rows, say for this example,
10 rows (so up to and including Company J). I'd like to group the remaining
rows (Company K to Company T inclusive) as 'Other' and sum the amount for
that group. As a result, I'd like to create a graph that identifies the
market share by the top 10 companies, but also including one chunk which
represents the amount of business placed by 'other'.

The data for this pivot table will change on a monthly basis, therefore the
top 10 companies may differ each time, therefore I don't think its just a
case selecting the top 10 companies and grouping that list.

Sum of Amount pt_class_code
Motor
company_name New Business
Company A £100,000
Company B £90,000
Company C £80,000
Company D £70,000
Company E £60,000
Company F £50,000
Company G £40,000
Company H £30,000
Company I £20,000
Company J £19,000
Company K £18,000
Company L £17,000
Company M £16,000
Company N £15,000
Company O £14,000
Company P £13,000
Company Q £12,000
Company R £11,000
Company S £10,000
Company T £9,000
Grand Total £694,000

I hope that makes sense. I didn't want to makes things too complicated.


"xrelanon" wrote:


On Thursday, November 22, 2007 12:49 PM Roger Govier wrote:

Hi Sarah

You can't do it totally automatically, but there would be very little to do
each month to give your required result.
To get the top 20
double click on the Company fieldAdvancedAutosortDescendingUsing
fieldSum of valueOKOK

This will give your companies in descending order of value

You can highlight any company names below the top 20right clickGroup and
Show DetailGroup
This will allocate the name Group1 which you can overtype with whatever name
you wish.
Double click the Group title, and the rows will be filtered to show the
total value for the Group below the lowest of your top 20 companies.

If you want to Ungroup them, repeat the procedure but choose Ungroup.

You will need to Ungroup each Month, do a Refresh, then Group those values
below the top 20 once again.
--
Regards
Roger Govier



"Sarah (OGI)" wrote in message
...

I know this is old, but it came up when I was searching for the same answer - so figured it a good place to post the automatic solution I just figured out..

1. Don't have any filters or groups on the pivot table

2. Sort the pivot table on what you want you want at the top

2. Hide row grand totals, you will create this yourself if needed

3. Somewhere under your pivot table, much further down than where you ever expect the data to extend to, say row 1000, make an "Others" row as you'd like it to appear, with a =sum() formula extending from the previous row (999), up to the first row you wish to group into 'others'. ie. for a top 20 report, from 24 (or wherever the 21st entry appears) through to 999

4. Create a Grand Total row under this if you want, same formula, expect extend it to the first row in your pivot table

5. Hide the rows from 24 through to 999 (in this example)

DONE !!

Refreshing the pivot table may move your data around and give you a different top 20, but the hidden rows will remain hidden and the formulas will continue to capture their values

Happy to provide an example if needed

On Thursday, November 22, 2007 10:22 AM sara wrote:

Is there any way of showing the top 20 entries in a pivot table, then
grouping the remaining entries as one, i.e. 'Other'.


On Thursday, November 22, 2007 10:42 AM xrelano wrote:

You need to provide more information about the fields in your PivotTable. The
Top 10 filter (which can be anything like Top 5, Top 20) can only be applied
a single field in your Values report area.

To find the Top 20 in all of the Values report area of your PivotTable,
apply Conditional Formatting to highlight the cells.

To get rid of the "Other" data depends if it is in one field or many fields
in your PivotTable. You can use a calculated field to add them, count them
etc. and name it Other and then remove the "Other" fields from the PivotTable.

Again, without informaton about what fields and their location in a
PivotTable report area, it is difficult to answer your question completely.
--
Gnothi se auton.


"Sarah (OGI)" wrote:


On Thursday, November 22, 2007 11:01 AM James_Thomlinso wrote:

To do what you are asking will require you to add a field to yoru source
data. By using array, sumproduct and the rank/large functions you should be
able to identify the top 20 and the others...
--
HTH...

Jim Thomlinson


"xrelanon" wrote:


On Thursday, November 22, 2007 11:15 AM sara wrote:

The left hand pane of my pivot table shows a list of company names. The
corresponding column shows the associated amounts, by company. 'Sum of
Amount' is in cell A9.

I'd like to be able to show the Top x number of rows, say for this example,
10 rows (so up to and including Company J). I'd like to group the remaining
rows (Company K to Company T inclusive) as 'Other' and sum the amount for
that group. As a result, I'd like to create a graph that identifies the
market share by the top 10 companies, but also including one chunk which
represents the amount of business placed by 'other'.

The data for this pivot table will change on a monthly basis, therefore the
top 10 companies may differ each time, therefore I don't think its just a
case selecting the top 10 companies and grouping that list.

Sum of Amount pt_class_code
Motor
company_name New Business
Company A £100,000
Company B £90,000
Company C £80,000
Company D £70,000
Company E £60,000
Company F £50,000
Company G £40,000
Company H £30,000
Company I £20,000
Company J £19,000
Company K £18,000
Company L £17,000
Company M £16,000
Company N £15,000
Company O £14,000
Company P £13,000
Company Q £12,000
Company R £11,000
Company S £10,000
Company T £9,000
Grand Total £694,000

I hope that makes sense. I didn't want to makes things too complicated.


"xrelanon" wrote:


On Thursday, November 22, 2007 12:49 PM Roger Govier wrote:

Hi Sarah

You can't do it totally automatically, but there would be very little to do
each month to give your required result.
To get the top 20
double click on the Company fieldAdvancedAutosortDescendingUsing
fieldSum of valueOKOK

This will give your companies in descending order of value

You can highlight any company names below the top 20right clickGroup and
Show DetailGroup
This will allocate the name Group1 which you can overtype with whatever name
you wish.
Double click the Group title, and the rows will be filtered to show the
total value for the Group below the lowest of your top 20 companies.

If you want to Ungroup them, repeat the procedure but choose Ungroup.

You will need to Ungroup each Month, do a Refresh, then Group those values
below the top 20 once again.
--
Regards
Roger Govier



"Sarah (OGI)" wrote in message
...


On Thursday, April 28, 2011 6:32 PM Rob H wrote:

I know this is old, but it came up when I was searching for the same answer - so figured it a good place to post the automatic solution I just figured out..



1. Don't have any filters or groups on the pivot table



2. Sort the pivot table on what you want you want at the top



2. Hide row grand totals, you will create this yourself if needed



3. Somewhere under your pivot table, much further down than where you ever expect the data to extend to, say row 1000, make an "Others" row as you'd like it to appear, with a =sum() formula extending from the previous row (999), up to the first row you wish to group into 'others'. ie. for a top 20 report, from 24 (or wherever the 21st entry appears) through to 999



4. Create a Grand Total row under this if you want, same formula, expect extend it to the first row in your pivot table



5. Hide the rows from 24 through to 999 (in this example)



DONE !!



Refreshing the pivot table may move your data around and give you a different top 20, but the hidden rows will remain hidden and the formulas will continue to capture their values



Happy to provide an example if needed