This example is in the download file, on the Top10Filter sheet. Read the sections below, for details on how the workbook is set up, and to see the code that runs the filter. To see only the Bottom 10 order dates, follow these steps.In addition to filtering for the top or bottom items, you can use a Value Filter to show a specific portion of the grand total amount.To filter the pivot table, so it shows only the Top 10 order dates, follow these steps.The results change, to show only the 5 order dates with the highest sales amounts.In the example shown below, there are 24 months of Order dates in the Row Labels area.In the Values area, you can see the total sales for the first few order dates.
Cell E1 is named Type Sel, and cell E2 is named Num Sel. If any other cell was changed, the filter code does not run.
As you can see in the pivot table shown below, the bottom 5 order dates only total 98,165, so the 6th lowest order date is also included in the Value Filter results, achieve our 100,000 minimum.
The selection boxes in the Top 10 Filter dialog cannot be linked to worksheet cells, to get a value.
However, you can use an Excel macro to get the values that have been entered on a worksheet.
In the screen shot below, when you select from the drop down lists in the yellow cells, a Top Ten filter is applies to the Order Mth field, with the settings from cells E1 and E2. To see only the top selling order dates that contribute to 10% of the total sales amount, follow these steps.