You can alternatively use the range A2:A18. In this Regexmatch formula, the Product is the field label (column name). =regexmatch(Product,'^Product 1$|^Product 10$|^Product 11$') In the Pivot Table editor custom formula field, we can use a formula as below to filter these values. Assume, the extracted top 3 items by their values (Qty) are “Product 1”, “Product 10”, and “Product 11”. We can later easily convert this top 3 values to top 10.įor this, we can use the same data shown in the above example. What we are going to do is extracting the top 10 item names (not quantity) based on their aggregated (grouped) quantities.įor our example purpose, I am going to filter the top 3 pivot table items by their values. Since we are going to use a custom formula to filter top 10 Pivot Table items based on their values, we must know how to group and aggregate the source data. The Formula Logic Behind Filtering Top 10 Pivot Table Items in Google Sheets

But in our case, I mean to filter top 10 values in Pivot Table, use the range A1:B.įrom my own testing, the Pivot Range E1:F won’t work correctly with custom formulas. That doesn’t mean you can’t use a custom formula in the Pivot Table based on the pivot range. Write a custom formula based on the source range (A1:B) not on the pivot range (E1:F) (please see the screenshot below for the range reference). In that, we are going to apply the filter, right? But there is one important point to note! What’s that? Filtering Top 10 Items by Values in Pivot Table in Google SheetsĪ Pivot Table contains aggregated data. In this post, you can learn how to code a custom formula to filter by bottom/top 10 or you can say bottom/top ‘n’ items in the Pivot Table in Google Sheets. Instead, in Google Sheets, at present, you can use custom formulas for this type of filtering.įor this, the Pivot Table editor in Google Sheets has a conditional filter option. Google Sheets Pivot Table lacks this Value Filters by Top 10 or Bottom 10 (to see Bottom 10, you should first click Top 10 in Excel). See the Excel Pivot Table filter option first even though I am not going to elaborate on it. Let’s hope that Sheets’ development team will include such a feature in Google Sheets Pivot Table in the near future. That Excel feature helps us to filter bottom 10 values too. Using that we can filter top ‘n’ values in the Pivot Table. In Excel, I could see a Top 10 filter option under the ‘Value filters’ in Pivot Table reports. You may require to code a custom formula for this. I couldn’t find any built-in option to Filter Top 10 Items in Google Sheets Pivot Table.

Excel pivot chart show filter values