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:
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.
In cell E8, the final formula calculates the amount for Other, by subtracting the top 3 amounts from the Grand Total.
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.