I'm using pivot tables and I need to get the top 5 rows and then combine the rest in a single row, after the 5th and before the grand total row (shown below).

Table Example

Is there a way of doing this?

At the moment the "Other" and "Total" rows are manually added but are overwritten or orphaned if the size of the pivot table's data set changes and needs too much maintenance every time it's refreshed.

EDIT: I should have mentioned, the pivot tables are generated from a single data source from a separate, which is used on multiple other tabs.

4

1 Answer

You could have a separate pivot which lists the top 5 classes by rank.

Then you need a formula in the main table to see if that class appears in the top 5. If it does, return the class name, if it doesn't, then return "Other". Use that as the grouping in the main pivot.

1

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password

Post as a guest

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy