Use the Top 10 filter feature in an Excel pivot table, to see the Top or Bottom Items, or find items that make up a specific Percent or items that total a set Sum. You can summarize your data by creating an Excel Pivot Table, and then use Value Filters to focus on the top 10, bottom 10 or a specific portion of the total values in your data.For example, insteading of showing the total sales for all products, use a filter to show just the top 10 products, or narrow it down to the top 2.On the Lists worksheet, there is a list of numbers, named Num List, in cells A2: A10. Screen Updating = True Exit Sub err Handler: Msg Box "Could not apply filter" Resume exit Handler End Sub When a change is made on the worksheet, the Worksheet_Change code is triggered. In cells C2: C3, there is a list of filter types, named Type List . It checks which cell has been changed (Target), and compares that cell's address to the addresses of the Type Sel and Num Sel ranges. Right-click the Top10Filter sheet tab, and click View Code. The following code is pasted onto that code module. On the Top10Filter sheet, the yellow cells (E1: E2) contain data validation drop down lists, based on those lists. If one of those cells is a match for the Target address, the filter code runs.
It will be easier to send the "Top" or "Bottom" selection to our macro, if we use the numeric values of those constants.
In the screen shot below, the City field has been filtered to show only the top 2 cities, with the highest sales amounts.
Or, if you want to focus on the poor performers, you can use a value filter to find the bottom 5 products or sales regions.
In the screenshot below, you can see that the grand total sales amount is 663,732.
Ten percent of the grand total is 66,373, and you can use a Top 10 filter to find the top or bottom dates combine to total at least that amount.
To get the numeric values for a constant, type a question mark in the Immediate window, followed by the constant, and then press Enter .