Pinterest Pixel

Pivot Table Filter: Top 5 Customers

You can easily filter your Pivot Table to show your Excel Pivot Top 5 customers or Top... read more

Download Excel Workbook
John Michaloudis
Posted on

Overview

You can easily filter your Pivot Table to show your Excel Pivot Top 5 customers or Top X Customers.

There are lots of different Value Filters to choose from

Let’s look at each of these options one-by-one!

 

Filter Items by Value

You can easily create Pivot Table in Excel to summarize your data and use the filter option to get the Top/Bottom X values from the data.

Here is an example of sales data of a company:

Pivot Table Filter: Top 5 Customers

You can use the in-built filter to display the pivot table top 5 by sales value.

Pivot Table Filter: Top 5 Customers

Let’s see how you can create a pivot table and display Excel top 5 values and names!

 

Watch it on YouTube and give it a thumbs-up!

Pivot Table Filter: Top 5 Customers | MyExcelOnline

Follow the step-by-step tutorial on Excel Pivot Top 5 and download this Excel workbook to practice along:

download excel workbookFilter-Top-5-Values-1.xlsx

 

STEP 1: Select all Cells in the Data Table.Pivot Table Filter: Top 5 Customers

STEP 2: Go to Insert > Pivot Table.

Pivot Table Filter: Top 5 Customers

STEP 3: In the Create PivotTable dialog box, Click OK.

You don’t have to change any default setting.

Pivot Table Filter: Top 5 Customers

STEP 4: In the PivotTable Fields dialog box, drag and down Customer Label in Row Area.

Pivot Table Filter: Top 5 Customers

STEP 5: Now, drag and down Sales Label in Column Area.

Pivot Table Filter: Top 5 Customers

Now, you have an entire list of total sales by different customers. To find the Excel Pivot Top 5 customer by sales, follow the steps below:

STEP 6: Click on the filter button next to Row Labels.

Pivot Table Filter: Top 5 Customers

STEP 7: Go to Value Filters > Top 10.

Pivot Table Filter: Top 5 Customers

STEP 8: In the Top 10 Filter (CUSTOMER) dialog box, type 5 and Click OK.Pivot Table Filter: Top 5 Customers

 

Your pivot table is now filtered!

Pivot Table Filter: Top 5 Customers

 

Now, that you have a list of Excel Pivot Top 5 customers by sales. Let’s move forward and understand what else can be done using this Top 10 filter in Excel.

The Top 10 filter is super flexible and you can easily filter the data to get Bottom 2, Top 80%, etc.

 

Bottom 2 Customer

In the Top 10 Filter dialog box, select Bottom from the dropdown and type 2.

Pivot Table Filter: Top 5 Customers

The result will be as shown below:

Pivot Table Filter: Top 5 Customers

 

Filter Items that make up a Specific Percentage of Value

Instead of displaying Top or Bottom X values, you can also show a specific portion of the grand total amount.

To display the top-selling customers contributing to 2% of the total sales amount:

STEP 1: Click on the Filter buttons in the Pivot Table.

Pivot Table Filter: Top 5 Customers

STEP 2: Go to Value Filter > Top 10.

Pivot Table Filter: Top 5 Customers

STEP 3: In the Top 10 Filter dialog box:

  • In the first field, select Top.
  • In the second field, type 25.
  • In the third field, select Percent.
  • In the fourth field, select Sum of SALES.

Pivot Table Filter: Top 5 Customers

STEP 4: Click OK.

Pivot Table Filter: Top 5 Customers

This will provide you with a list of top customers that represent 25% of the sales.

Pivot Table Filter: Top 5 Customers

Filter Items that make up a Specific Value

You can use this filter to get a list of top customers that accounts for a specific value say $300,000.

To display the top customers that account for $300,000:

STEP 1: Click on the Filter buttons in the Pivot Table.

Pivot Table Filter: Top 5 Customers

STEP 2: Go to Value Filter > Top 10.

Pivot Table Filter: Top 5 Customers

STEP 3: In the Top 10 Filter dialog box:

  • In the first field, select Top.
  • In the second field, type 300000.
  • In the third field, select Sum.
  • In the fourth field, select Sum of SALES.

Pivot Table Filter: Top 5 Customers

Here, is the result!

Pivot Table Filter: Top 5 Customers

 

Conclusion

You can use the filter button in the Pivot Table to the top-selling customers based on Item, Percentage, or Sum. Simply select the option from the dropdown and Excel will provide you with a filtered list!

There is a lot more you can do using Excel Pivot Table, Click here to know them all!

Further Learning:

Helpful Resource:

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

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

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Founder & Chief Inspirational Officer at MyExcelOnline.com

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.

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!