How to Get Top 5 Values in Excel Pivot Table
Excel Pivot Tables are fantastic for summarizing mountains of data, but raw summaries can still feel overwhelming. Your goal isn't just to see all your sales data but to quickly spot your top performers - the products, regions, or campaigns that are driving real results. This guide will show you exactly how to filter a Pivot Table to display only the top 5 values, so you can stop scrolling and start making smarter decisions.
Why Is a "Top 5" Report Even Useful?
Before jumping into the "how," it's helpful to understand the "why." Focusing on your top performers isn't just a reporting trick, it's a strategic move. A "Top 5" report helps you:
Identify Your Winners: Quickly see which products generate the most revenue, which marketing channels have the best ROI, or which sales reps are closing the most deals.
Allocate Resources Effectively: When you know what's working best, you can double down on it. Should you increase the budget for a specific ad campaign? Or order more stock of a best-selling product? The data will tell you.
Spot Emerging Trends: Monitoring your Top 5 over time can reveal new products gaining momentum or veteran products that are starting to slip.
Simplify Communication: Instead of presenting a report with 100 rows, you can present a concise summary of the 5 most important items. This makes it easier for stakeholders to grasp the key takeaways immediately.
The Starting Point: Create Your Basic Pivot Table
To find your top 5, you first need a Pivot Table. Let’s assume you have some familiar-looking sales data in an Excel sheet, with columns like Product Name, Category, Region, and Sales Amount.
If you don't have a Pivot Table yet, here's a lightning-fast refresher:
Click anywhere inside your data range.
Go to the Insert tab on the Excel ribbon and click PivotTable.
Excel will typically auto-select your data range. Just click OK to create the Pivot Table in a new worksheet.
From the "PivotTable Fields" panel (usually on the right side of the screen), drag and drop the fields you need. For our example, we'll drag Product Name to the Rows area and Sales Amount to the Values area.
You should now have a simple Pivot Table that lists all your products and their total sales. Now, let's filter it down to the top 5.
Finding Your Top 5 Products: The Step-by-Step Guide
Here’s the core process for filtering your Pivot Table to show only the top 5 values. It’s easier than it sounds!
Step 1: Open the Filter Options
In your Pivot Table, find the column header for your rows. In our example, it's titled "Row Labels" or "Product Name." Click the small dropdown arrow right next to that header. This opens up the sorting and filtering menu.
Step 2: Navigate to Value Filters
From the dropdown menu that appears, hover your mouse over Value Filters. This will reveal a secondary menu with several options like "Greater Than," "Less Than," and the one we're looking for, "Top 10..."
Step 3: Select the "Top 10" Filter
Click on Top 10... from the menu.
"Wait, I want the Top 5, not the Top 10!"
Don't worry! This is just the name of the menu item. This is where you can define any "Top N" filter, whether it's the top 3, top 5, top 8, or any other number you need.
Step 4: Configure the Top 5 Filter Settings
A dialog box named "Top 10 Filter" will pop up. This is where you tell Excel exactly what you want to see. Let's break down the fields:
First dropdown (Show): This should already be set to Top. You can also change this to "Bottom" if you want to find your worst-performing items.
Text Box: This is where the magic happens. Change the default number 10 to 5.
Second dropdown (Filter Type): You have three choices here - Items, Percent, or Sum. For finding your top 5 products, you want to leave this set to Items.
Third dropdown (By Value): This field tells Excel which numerical value to use for the ranking. Since we dragged "Sales Amount" into the Values area, it should automatically show "Sum of Sales Amount." Confirm this is correct.
Your configured box should now say: Show Top 5 Items by Sum of Sales Amount.
Step 5: Apply the Filter
Click OK. Your Pivot Table will instantly refresh to show only the 5 products with the highest sales amounts. That's it! You've successfully filtered your report to focus on your winners.
Taking It Further: Advanced "Top N" Reporting Techniques
Now that you've mastered the basics, you can apply this skill in more powerful ways. Here are a few common scenarios and practical tips.
Finding the Top 5% of Performers
Sometimes you don't want a fixed number of items, but rather the items that contribute to a certain percentage of your total sales. For example, which products make up the top 20% of all revenue?
The process is nearly identical. In the "Top 10 Filter" dialog box (Step 4), simply change the filter type dropdown from Items to Percent and enter your desired percentage (e.g., 20).
This is particularly useful when you have a massive product list and want to apply the 80/20 rule - identifying the 20% of products that drive 80% of your revenue.
Switching to the Bottom 5 Underperformers
Knowing what isn't working is just as important as knowing what is. To find your bottom 5 performing products, follow the same steps, but in the filter dialog box, change the first dropdown from Top to Bottom.
This is your go-to report for identifying products that may need to be discontinued, are facing new competition, or have marketing support that isn't paying off.
Using Slicers for Interactive Top 5 Reports
What if you want to see the Top 5 products, but be able to switch between regions or time periods on the fly? This is where Slicers come in.
A Slicer is a user-friendly filter button that allows you to dynamically segment your Pivot Table. Let's add one for Region:
Click anywhere on your Pivot Table to bring up the PivotTable Analyze tab in the ribbon.
In that tab, click Insert Slicer.
A box will appear with all your data fields. Check the box for the field you want to filter with, such as Region, and click OK.
A Slicer menu will appear on your worksheet showing your different regions. Now, when you click on a region (e.g., "North"), your Pivot Table will instantly update to show the Top 5 selling products within that specific region. Click another region, and it updates again. This turns your static report into a dynamic mini-dashboard.
Common Issues and Quick Fixes
"The 'Value Filters' Option is Grayed Out!"
This is a common frustration and it almost always has a simple solution. The Value Filters option will be disabled if your Pivot Table doesn't have a field in the Values area. Remember, you can't find the "Top 5 products by sales" if "Sales" isn't in your table! Double-check that you've dragged a numerical field (like Sales Amount, Quantity, or Profit) into the Values area of your "PivotTable Fields" panel.
"My Report is Showing 'Top X' based on the wrong value."
If you have more than one metric in your Values area (e.g., Sum of Sales and Sum of Quantity), make sure you select the correct one in the "Top 10 Filter" setup box. In the final dropdown ("By Value"), you'll be able to choose which metric your ranking is based on. Always double-check this field to ensure your filter is using the right data.
Final Thoughts
Filtering your Pivot Table to show the top 5 values is a simple but incredibly powerful way to cut through the noise and get straight to the most important insights in your data. By mastering the Value Filter menu and combining it with tools like Slicers, you can quickly move beyond just summarizing data and start using it to drive smart, strategic business decisions.
Manually building, filtering, and updating these reports in Excel every week is exactly the kind of repetitive work we wanted to eliminate. At Graphed, we connect directly to your data sources like Google Analytics, Shopify, or Salesforce a single time. From there, you can just ask in plain English, "Show me my top 5 products by sales last month," and get an interactive, real-time dashboard instantly, with no pivot tables required. It’s all about getting the insights without the busywork.