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:

excel pivot top 5

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

Pivot Table Filter: Top 5 Customers | MyExcelOnline

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 WORKBOOK

 

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

STEP 2: Go to Insert > Pivot Table.

Pivot Table Filter: Top 5 Customers | MyExcelOnline

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

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

Pivot Table Filter: Top 5 Customers | MyExcelOnline

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

Pivot Table Filter: Top 5 Customers | MyExcelOnline

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

STEP 7: Go to Value Filters > Top 10.

Pivot Table Filter: Top 5 Customers | MyExcelOnline

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

 

Your pivot table is now filtered!

Pivot Table Filter: Top 5 Customers | MyExcelOnline

 

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

The result will be as shown below:

Pivot Table Filter: Top 5 Customers | MyExcelOnline

 

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

STEP 2: Go to Value Filter > Top 10.

Pivot Table Filter: Top 5 Customers | MyExcelOnline

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

STEP 4: Click OK.

Pivot Table Filter: Top 5 Customers | MyExcelOnline

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

Pivot Table Filter: Top 5 Customers | MyExcelOnline

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

STEP 2: Go to Value Filter > Top 10.

Pivot Table Filter: Top 5 Customers | MyExcelOnline

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

Here, is the result!

Pivot Table Filter: Top 5 Customers | MyExcelOnline

 

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!

 

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!

728x90

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

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Pivot Table Filter: Top 5 Customers | MyExcelOnline