Excel: Top 5 Rows and Combine the Rest
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)...
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).
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.
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.
Elena Rostova holds a Master's degree in Public Health Journalism. She covers groundbreaking medical research, holistic wellness trends, mental health awareness, and nutritional science.
