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