Pivot Table Issues

How to Find the Best-Selling Product Using Pivot Tables in Excel

Amos Gikunda Oct 9, 2026 869 views

Question details

The user needs to identify the highest-selling products categorized by department and subcategory using a dataset of sales records.

How to Find the Best-Selling Product Using Pivot Tables in Excel

Product: Excel
Device & OS: not provided
Scenario: Analyzing business sales data to determine the top-performing products across different categories or departments.
Observed behavior: The user needs an efficient method to summarize and filter a large dataset to extract the top-selling items by quantity.

Before you start

Ensure your sales data is organized in a tabular format with clear column headers (e.g., Department, Subcategory, Quantity Sold) and contains no empty rows or columns before creating your Pivot Table.

Solution 1Recommended

Use a Pivot Table with a Top Items Filter

The most efficient way to isolate the absolute best-selling product within each department or subcategory.

By applying a Value Filter in a Pivot Table, you can instruct Excel to dynamically display only the highest-performing items, hiding the rest of the data.

Insert a Pivot Table

Click anywhere inside your sales dataset, go to the 'Insert' tab on the ribbon, and click 'PivotTable'. Choose to place it on a New Worksheet and click 'OK'.

Configure Rows and Values

In the PivotTable Fields pane, drag 'Department' and 'Subcategory' (or Product) into the Rows area. Drag 'Quantity' into the Values area to calculate the total sold.

Apply a Value Filter

Click the filter dropdown arrow next to the 'Row Labels' heading in your Pivot Table. Hover over 'Value Filters', then select 'Top 10...' from the menu.

Set Filter to Top 1

In the Top 10 Filter dialog box, change the number from '10' to '1' to show only the top item. Ensure it is set to filter by 'Sum of Quantity', then click 'OK'.

Use a Pivot Table with a Top Items Filter

Quick Tip: If you want to see the top 3 or 5 products instead, simply change the number in the Top 10 Filter dialog box accordingly.

Solution 2

Sort Pivot Table Values from Largest to Smallest

A quick alternative if you want to see the full ranked list of products rather than isolating just the single top item.

Data Analysis with WPS Office

Find Top-Selling Products Effortlessly with WPS Spreadsheet

WPS Spreadsheet provides powerful Pivot Table features that make it incredibly easy to summarize large sales datasets, filter top items, and extract actionable business insights. Best of all, it is fully compatible with Microsoft Excel files.

1. Open Your Data: Launch WPS Spreadsheet and open your existing sales data workbook.
2. Insert Pivot Table: Navigate to the Insert tab and click PivotTable to summarize your data layout.
3. Filter Top Item: Drag your product categories to Rows and quantities to Values, then apply a Value Filter to show the Top 1 item.

Create dynamic Pivot Tables in secondsSeamlessly open and edit Microsoft Excel (.xlsx) files without formatting lossBuilt-in Top 10 Value Filters for quick data extractionFree, lightweight, and easy-to-use interface

microsoft office alternative - wps office

Frequently Asked Questions

Can I find the top 5 best-selling products instead of just one?

Yes. When using the 'Top 10' Value Filter in your Pivot Table, simply change the number in the dialog box from 10 to 5. This will display the top 5 items based on the quantity sold.

Why is my Pivot Table not updating when I add new sales data?

Pivot Tables do not update automatically. To include new data, click anywhere inside the Pivot Table, go to the PivotTable Analyze (or Options) tab on the ribbon, and click 'Refresh'. If you added new rows outside the original selection range, you may need to click 'Change Data Source' first.

How can I find the highest revenue product instead of quantity sold?

Instead of dragging 'Quantity' into the Values area of your Pivot Table, drag your 'Sales Revenue' or 'Total Price' column into the Values area. Then, apply the sort or Top 10 filter based on that new financial field.

Excel & Data Problems