Graphed LogoGraphed
Login

How to Find Top 10 Values in Excel Pivot Table

Cody Schneider•

Excel Pivot Tables are one of the most powerful tools for summarizing large datasets, but staring at a giant table of numbers can feel overwhelming. To get straight to the insights, you often need to find the top performers - your top 10 products, top 10 customers, or top 10 sales regions. This article will show you exactly how to use the built-in "Top 10" filter in your Pivot Table to instantly surface your most important data.

First, A Quick Pivot Table Refresher

Before we can filter, we need a Pivot Table. If you’re already a pro, feel free to skip to the next section. If you need a quick setup reminder, here’s how to get started.

Let's use a common example: a simple sales report. Imagine your data looks something like this, with columns for Region, Category, Product, and Sales Amount:

To turn this data into a Pivot Table:

  1. Click anywhere inside your dataset.

  2. Go to the Insert tab on the Excel ribbon.

  3. Click on PivotTable. Excel will automatically select your data range and suggest placing the Pivot Table in a new worksheet. Just click OK.

You'll now see the PivotTable Fields pane on the right side of your screen. This is where you build your report. For this tutorial, let’s find our top-selling products. We'll set it up like this:

  • Drag the Product field into the Rows area.

  • Drag the Sales Amount field into the Values area. Excel will automatically default to "Sum of Sales Amount."

Your basic Pivot Table is now ready, showing a list of every product and its total sales. While useful, it’s still just a long list. It's time to find the best-sellers.

How to Apply the Top 10 Filter in Your Pivot Table

This is where the magic happens. Excel has a built-in filter designed specifically for this purpose. Follow these simple steps to isolate your top 10 products.

Step 1: Open the Filter Options

In your Pivot Table, find the heading for your rows - in our case, it's labeled "Row Labels" (or "Product," depending on your Excel version and view settings). Click the small dropdown filter icon next to it.

Step 2: Navigate to Value Filters

From the dropdown menu, hover over Value Filters. This will open a secondary menu with several options like "Greater Than," "Less Than," and "Between." We want the last one on the list.

Step 3: Select "Top 10..."

Click on Top 10.... This will open the "Top 10 Filter" dialog box, where you can configure your filter settings.

Step 4: Configure the Filter Settings

The "Top 10 Filter" dialog box gives you a surprising amount of flexibility. Here’s a breakdown of the options:

  • Show (Top/Bottom): The first dropdown defaults to "Top," which is what we want. You can easily switch this to "Bottom" to find your worst-performing items (more on that later).

  • The Count (10): The middle box defaults to 10, but you can change this to any number you need. Want the Top 5? Just type "5." Need the Top 20? No problem.

  • The Unit (Items/Percent/Sum): This dropdown lets you choose how you define your top list.

    • Items: This is the most common option. It will show the top 10 individual products based on their sales value.

    • Percent: This identifies the top items that collectively make up a certain percentage of the total. For example, "top 80 percent" would show you the products that contribute to 80% of your total sales (a great way to apply the Pareto principle).

    • Sum: This lets you find the top items that add up to a specific value. You could ask for the top products whose sales total up to $1,000,000.

  • By (Value Field): This dropdown tells Excel which values to use for the ranking. Since we only have one field ("Sum of Sales Amount") in our Values area, it's selected by default. If you had multiple fields (e.g., Sales Amount and Quantity), you could choose which one to base the filter on.

For our initial goal, the default settings work perfectly: Show Top 10 Items by Sum of Sales Amount.

Step 5: Click OK and See Your Results

Once you click OK, your Pivot Table will instantly refresh to display only the top 10 products based on sales. All the other items will be hidden, leaving you with a clean, focused list of your best-sellers.

And that’s it! With just a few clicks, you’ve gone from a massive data list to a focused, actionable report.

Going Beyond the Basics: Advanced Filtering Tips

The Top 10 filter is much more versatile than its name suggests. Here are a few ways to leverage its full potential to answer different business questions.

Find the Bottom Performers

Sometimes you need to know what's not working. To find your 10 worst-selling products, simply follow the same steps as above, but in the "Top 10 Filter" dialog box, change the first dropdown from Top to Bottom.

This is invaluable for identifying products to discontinue, marketing campaigns that failed, or sales reps who may need additional support.

Filter by Percentage to See Your Biggest Drivers

The "80/20 rule" (or Pareto Principle) often applies in business: roughly 80% of the results come from 20% of the efforts. You can use the Top 10 filter to test this with your own data.

Re-open the filter dialog box and change the settings to:

  • Show Top

  • 80

  • Percent

Excel will show you the top products that, when added together, account for 80% of your total sales. This helps you focus your resources on the products that truly drive your business.

Combine Top 10 with Other Filters and Slicers

One of the best features of Pivot Tables is their dynamic nature. The Top 10 filter can work alongside other filters, like Slicers.

For example, you could add a "Region" slicer (PivotTable Analyze > Insert Slicer > Check "Region"). Now, when you click on a region in the slicer like "North," your Top 10 product list will automatically update to show the top 10 products sold specifically in the North region.

This lets you drill down and get highly specific insights without ever creating a new report. You can ask questions like:

  • What are our top 10 products this quarter? (Add a Date filter).

  • Who are our bottom 5 sales reps in a specific territory? (Filter by Bottom 5 and slice by territory).

  • Which products are in our Top 10 for online sales but not for in-store sales? (Use two pivot tables, one filtered for each sales channel).

Visualize a Pivot Table Top 10 Data in Charts

Top value lists are clear but you'll get more impact and tell a better data story by including a visualization like a bar chart in your reports and dashboards. You can easily visualize your Pivot Table top 10 results by doing the following:

  1. Navigate to PivotTable Analyze in the ribbon toolbar

  2. Click PivotChart

  3. Select your chart type and then click okay

Congratulations, your pivot table based on the top 10 valued list will transform into a visual format.

Final Thoughts

Filtering a Pivot Table to show the top 10 values is an essential technique that transforms a sea of data into a prioritized list of what truly matters. By mastering the Value Filters menu, you can quickly identify your top products, customers, or campaigns, allowing you to move beyond data wrangling and straight to making informed decisions that grow your business.

While Excel is great, the process of setting up pivot tables, applying filters, and refreshing reports still takes manual effort. At Graphed, we've automated this entire flow. Instead of clicking through menus, you can just ask a question like, "Show me my top 10 selling products this quarter as a bar chart" and get an auto-updating dashboard in seconds. We connect to your data sources like Shopify, Google Analytics, and ad platforms, so the insights are always live and you don't spend half of your day in spreadsheets. Give Graphed a try and see how easy real-time data analysis can be.