Pinterest Pixel

Pivot Filter by Values – Top or Bottom %

Bryan
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!

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!

Key Takeaways

  • Filter by Performance: Pivot tables allow you to filter data based on the top or bottom percentage of values, making it easy to analyze the best or worst performers.
  • Dynamic Filtering: The filter updates dynamically when data changes, ensuring that insights remain relevant as new data is added.
  • Customizable Thresholds: You can set any percentage (e.g., top 10% or bottom 20%) to refine the data view according to your needs.
  • Works with Aggregated Data: The filter applies to summary values (e.g., total sales, average score), not individual raw data points.
  • Enhances Decision-Making: Helps identify trends, outliers, and key contributors, making it valuable for business intelligence and reporting.

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

Pivot Filter by Values - Top or Bottom %

 

Example 1: Filter by Values – Top %

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

Filter by Values - Top or Bottom Sum

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 %

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

Filter by Values - Top or Bottom Sum

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

Pivot Filter by Values - Top or Bottom %

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

Pivot Filter by Values - Top or Bottom %

 

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

Pivot Filter by Values - Top or Bottom %

 

Example 2: Filter by Values – Bottom %

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

Filter by Values - Top or Bottom Sum

STEP 2: Select Value Filters and Top 10.

Pivot Filter by Values - Top or Bottom %

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 - Top or Bottom %

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 %

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.

Frequently Asked Questions

How do I apply a Top or Bottom % filter in a Pivot Table?

Click on the dropdown arrow in the Pivot Table’s value field, select “Value Filters,” then choose “Top 10.” In the filter window, change “Items” to “%” and specify the percentage you want.

Can I filter by Top/Bottom % for multiple value fields at once?

No, the filter applies to a single value field at a time. If you need multiple filters, you must apply them separately or use calculated fields.

Does the filter automatically update when new data is added?

Yes, but you need to refresh the Pivot Table to ensure the filter applies to the latest data.

Can I use Top/Bottom % filtering with non-numeric fields?

No, this filter works only on numeric value fields, as it ranks and selects data based on calculations.

What happens if the percentage doesn’t result in a whole number of items?

Excel rounds up to the nearest whole number, ensuring at least one record is displayed even if the percentage calculation is fractional.

If you like this Excel tip, please share it



Pivot Filter by Values - Top or Bottom % | MyExcelOnline


Bryan

Bryan Hong is an IT Software Developer for more than 10 years and has the following certifications: Microsoft Certified Professional Developer (MCPD): Web Developer, Microsoft Certified Technology Specialist (MCTS): Windows Applications, Microsoft Certified Systems Engineer (MCSE) and Microsoft Certified Systems Administrator (MCSA).

He is also an Amazon #1 bestselling author of 4 Microsoft Excel books and a teacher of Microsoft Excel & Office at the MyExecelOnline Academy Online Course.

See also  Drill Down To Audit Using a Pivot Table

Star 30 Days - Full Access Star

One Dollar Trial

$1 Trial for 30 days!

Access for $1

Cancel Anytime

One Dollar Trial
  • Get FULL ACCESS to all our Excel & Office courses, bonuses, and support for just USD $1 today! Enjoy 30 days of learning and expert help.
  • You can CANCEL ANYTIME — no strings attached! Even if it’s on day 29, you won’t be charged again.
  • You'll get to keep all our downloadable Excel E-Books, Workbooks, Templates, and Cheat Sheets - yours to enjoy FOREVER!
  • Practice Workbooks
  • Certificates of Completion
  • 5 Amazing Bonuses
Satisfaction Guaranteed
Accepted paymend methods
Secure checkout

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...