Excel Totals for Top 3 Plus Other

Excel Totals for Top 3 Plus Other

In a pivot table, you can use the built-in Top 10 command, to show the Top 10 or Bottom 3, or almost any other top/bottom summary. In this post, we'll see how to show top 3, plus "other", to show the remaining amounts.

In this screen shot, the Top 10 is selected as a Value Filter.


After you select this command, the pivot table shows the selected results, and hides everything else.

Top 3 Plus Other

Recently, I had to make charts that showed the top 3 amounts in a category, and the remaining amounts lumped together as "Other". The pivot table summarized the data very efficiently, but wouldn't create an "Other" grouping.So, to solve the problem, I used formulas adjacent to the pivot table.

In this example, we want to see the three dates with the highest amounts, and a total for the remaining dates.

The first formula, in column D, pulls the top three dates. An IF formula is used, in case there are fewer than 3 dates in the results. The formula is entered in cell D5, and copied down to cell D7:

=IF(OR(A5="",A5="Grand Total"),"",A5)


The next formula is entered in cell E5 and copied down to E7:



Calculate the Other Amount

In cell D8, a different formula shows the result of "Other", if there are 3 or more top amounts.

=IF(OR(A8="",A8="Grand Total"),"","Other")


In cell E8, the final formula calculates the amount for Other, by subtracting the top 3 amounts from the Grand Total.

=IF(D8="","", GETPIVOTDATA("Total",$A$4)-SUM(E5:E7))


When you change the pivot table Report Filter selections, the Top 3 and Other amounts change automatically.


Create a Top 3 Chart

To show the results on an Excel dashboard, you can create a chart, and move it to a Summary sheet.


If you're using Excel 2010, you can add Slicers, so users can change the filter selections, without messing up your pivot table layout.


Download the Sample File

To see the pivot table and formulas, you can download the Pivot Table Top 3 Other sample file. The file is in Excel 2010 format, and is zipped.

There are no macros in the file, and the Slicers are only visible in Excel 2010.

0 thoughts on “Excel Totals for Top 3 Plus Other”

  1. Hello,
    Great article.
    What about showing the top 3 most occurring values (text values)?
    Say I have a column "Sales" - with a few items - "Bits","Bolts","Nuts","Beer".
    And another column "Dates".
    What I want is to have three cells, showing the top 3 items, based on year.
    It would say:
    #1 in 2010: Nuts
    #2 in 2010: Bolts
    #3 in 2010: Beer
    Is this possible? Any thoughts?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.