How to Filter top / bottom items that create a specified value in Pivot Table

  Excel Interview Q&A

Suppose you want to find the top retailers that contribute 20 million in sales.

How to Filter top / bottom items that create a specified value in Pivot Table

You can do this by using the top 10 filters in the pivot table.

  • Go to Row Label filter – Value Filters – Top 10.

Filter-Data-in-a-Pivot-Table-in-Excel-Dataset-Top-10-

  • In the Top 10 Filters dialog box, there are four options that you need to specify:
    • Top / Bottom: In this case since we are looking for the top retailers that make 20 million in total sales, select the top.

Filter-Data-in-a-Pivot-Table-in-Excel-Top-10-field-1

  • In the second area, you need to specify a price that should be for the top retailers. In this case, since we want to get top retailers who make up 20 million in sales, it would be 20000000.

Filter-Data-in-a-Pivot-Table-in-Excel-Sum-20Mil-2

  • Now select Sum.

Filter-Data-in-a-Pivot-Table-in-Excel-Sum-20Mil-3

  • The last field lists all the various values listed in the Price field. In this case, since we only have sales totals, it shows only ‘sales totals’.

Filter-Data-in-a-Pivot-Table-in-Excel-Sum-20Mil-4

This will appear – filtered list of top retailers that make up 20 million of total sales.

Filter-Data-in-a-Pivot-Table-in-Excel-Sum-20Mil-result

LEAVE A COMMENT