Pivot Filter by Values - Top or Bottom % | MyExcelOnline

Pivot Table filtering allows getting you the values that make the top or bottom percentage. For example, you get the top records that makeup 25% of the total sum. Let us see this in action!

 

Download this Excel Workbook and follow the step-by-step tutorial below on how to pivot filter by value – %:

DOWNLOAD EXCEL WORKBOOK


This is our current Pivot Table setup. We will be filtering the Top 25% Sum of Sales.

Pivot Filter by Values - Top or Bottom % | MyExcelOnline

 

Example 1: Filter by Values – Top %

STEP 1: Click on the Row Label filter button in the Pivot Table.

Pivot Filter by Values - Top or Bottom % | MyExcelOnline

STEP 2: Select Value Filters.

You will see that we have a lot of filtering options. Let us try out –  Top 10

Pivot Filter by Values - Top or Bottom % | MyExcelOnline

STEP 3: Since, you want the list of Top channel partners that makes 25% of total sales – select the Top in the first field.

Pivot Filter by Values - Top or Bottom % | MyExcelOnline

STEP 4: In the second field, mention the percentage required – type 25.

Pivot Filter by Values - Top or Bottom % | MyExcelOnline

STEP 5: In the third field, select Percentage and click OK.

Pivot Filter by Values - Top or Bottom % | MyExcelOnline

 

You now have the top channel partners that makeup 25% of the total sum!

Pivot Filter by Values - Top or Bottom % | MyExcelOnline

 

Example 2: Filter by Values – Bottom %

STEP 1: Click on the Row Label filter button in the Pivot Table.

Pivot Filter by Values - Top or Bottom % | MyExcelOnline

STEP 2: Select Value Filters and Top 10.

Pivot Filter by Values - Top or Bottom % | MyExcelOnline

STEP 3: Select the Bottom in the first field, 25 in the second field, and Percentage in the third field.

Click OK.

pivot filter by values

Now you have the bottom channel partners that when combined, will give you the bottom 25% of sales.

Pivot Filter by Values - Top or Bottom % | MyExcelOnline

 

Following the steps mentioned above, you can easily filter the Pivot Table by values and list the Top or Bottom items based on the percentage of total sales. You can also use Pivot Table to filter Top/Bottom items by Sum, filter items based on Value, etc.

Click here to learn all about Pivot Tables.

 

HELPFUL RESOURCE:

Make sure to download our FREE PDF on the 333 Excel keyboard Shortcuts here:

Microsoft Excel Training

You can learn more about how to use Excel by viewing our FREE Excel webinar training on FormulasPivot TablesPower Query, and Macros & VBA!

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Share on LinkedIn