Pinterest Pixel

Filter by Values – Top or Bottom Sum

Bryan
Pivot Table allows you to filter by values that make the top or bottom based on a specific sum.
For example, you get the top records that account for 1,000,000 of the total sales.

Let us see this in action!
Filter by Values - Top or Bottom Sum | MyExcelOnline Filter by Values - Top or Bottom Sum | MyExcelOnline

 

Download this Excel Workbook and follow the step-by-step tutorial below:

download excel workbookFilter-by-Values-Top-or-Bottom-Sum.xlsx


This is our current Pivot Table setup. We will be filtering the Top Channel Partners that account for 1,000,000 of total 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 1,000,000 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 value that the top channel partners should account for – type 1000000.

Filter by Values - Top or Bottom Sum

STEP 5: In the third field, select Sum.

Filter by Values - Top or Bottom Sum

STEP 6: The fourth field, lists all the values mentioned in the values area of the Pivot Table.

Select sum of values.

Filter by Values - Top or Bottom Sum

Now you have the top channel partners that when combined, will give you at least 1,000,000 for the sum of sales.

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, 1,000,000 in the second field, and Sum in the third field.

Select Sum of SALES in the fourth field and click OK.

Pivot Filter by Values - Top or Bottom %

Now you have the bottom channel partners that when combined, will give you at least 1,000,000 for the sum 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 sum of values. You can also use Pivot Table to filter Top/Bottom items by Percentage, filter items based on Value, etc.

Click here to learn all about Pivot Tables.

 

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

If you like this Excel tip, please share it
Filter by Values - Top or Bottom Sum | 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  Group Two Excel Pivot Tables Independently

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...