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.

pivottopten01

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)

pivottopten02

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

=IF(D5=””,””,B5)

pivottopten03

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”)

pivottopten03a

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))

pivottopten03c

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

pivottopten04

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.

pivottopten05

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

pivottopten06

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.

________________

You may also like...

2 Responses

  1. Shafali says:

    when I do the total for others it is coming out as #REF!

  2. alanj47 says:

    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 *