Forum Discussion

Anonymous29007's avatar
Anonymous29007
Copper Contributor
Jan 07, 2026

Top n vs. Others in Excel

Hi all,

I'm seeking some help because I'm kind of new to the more intermediate stuff in Excel. I have an Excel table with the following columns: Subcategory in column A, Brand in column B, Region in column C, Year in column D and Values Month in column E. I want to create a PivotTable and a Pivot line chart from this PivotTable that ranks the Top 5 Brands vs. Other Competitors by each region. For added context: There are 5 subcategories, 3 regions and 25 brands. 

Currently, I've tried grouping the remaining 20 brands as "Other Competitors" vs. the Top 5 brands within a selected region and possibly all regions (when no selection is made).

I'm seeking a solution similar to this...

Please mind the colours. I will sort those out later. But, the problem that I'm faced with is that upon selection of a region, the PivotTable won't update to the Top 5 brands of a selected region because they've already been grouped. How can I make this more dynamic so that I'm able to show The Top 5 brands vs. Others?

Please help.

EDIT: My operating system is Windows 10 (64-bit) and I use Excel 365 (Desktop version). For reference, I've attached a link to a sample file.

https://1drv.ms/x/c/b2d878e32a062614/IQC1wcnwLICcQasOfnGcwKn0ASjpXp9xQ6rjnOP10Jal5cc?e=HaXEWd

Thank you all once again.

excel
Formulas and Functions

3 Replies

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Jan 11, 2026

    Hi

    As I understand & assuming it's doable this would require Power Pivot - not sure & not my expertise 😒 However, I believe you should clarify how you expect to distinguish the TOP 5 (in green below) when several years display, ex.:

    - Based on the Average by column/Brand or something else?
    - What if the 6th, 7th... value equals the 5th?

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Jan 09, 2026

    Hi


    2 days with no reply probably means nobody's ready to re-create existing data (takes time), running the risk it doesn't reflect yours ==> Suggest you share your workbook (anonymized) with OneDrive, Google Drive or the like
    Also, please mention the version of Excel you run and on which platform (Windows, Mac, Web...)

    Hope this all makes sense

    • Anonymous29007's avatar
      Anonymous29007
      Copper Contributor
      Jan 09, 2026

      Hi Lorenzo​,

      Thank you so much for guiding me on this platform. I'm kind of new here and don't quite know how things operate on this forum. I'm not sure if my previous reply to you went through because it isn't reflecting on my end but, I've managed to update my original post to reflect what you've suggested.

      Thank you so much once again, I greatly appreciate it!

Resources