# 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.

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:

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

### 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.

________________

### 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?