Function Problems

How to Create a Dynamic Top 10 Customer Sales Report in Excel

Anum Mushtaq Oct 22, 2026 869 views

Question details

The user needs to construct a dynamic report that groups sales order data by customer, calculates the total outstanding value and quantity, ranks the top 10 customers, and identifies the best-selling product for each of those customers.

How to Create a Dynamic Top 10 Customer Sales Report in Excel

Product: Excel
Device & OS: not provided
Scenario: Analyzing a detailed sales-order dataset with multiple rows per order line to extract actionable customer and product performance insights.
Observed behavior: The target report must automatically rank the top 10 customers based on total outstanding value while simultaneously displaying the specific top product's name, value, and quantity for each ranked customer.

Before you start

Ensure your raw sales data is formatted as an official Excel Table (Ctrl+T) with clear, distinct headers for Customer Name, Product Reference, Outstanding Value, and Quantity.

Solution 1Recommended

Use Power Pivot and DAX Measures (Recommended)

Power Pivot handles complex groupings and nested dynamic rankings much more efficiently than standard formulas, making it the ideal tool for extracting top customers and their respective top products.

By utilizing the Data Model and DAX (Data Analysis Expressions), you can create specific measures that calculate outstanding values and dynamically generate top 10 rankings without altering your raw data structure.

Load data into the Data Model

Select your data table, navigate to the Power Pivot tab, and click 'Add to Data Model'.

Create base summary measures

In the Power Pivot window, create the foundational measures for value and quantity. For example: VO = SUM(Data[Value outstanding]) and QO = SUM(Data[Qty Outstanding]).

Create a customer ranking measure

To rank customers by value, write a RANKX measure: Rank = RANKX(GENERATE(ALL(Data[Account]), ALL(Data[Account Name])), [VO]).

Identify the top-selling product dynamically

Create a measure to return the name of the top product per customer using TOPN and CONCATENATEX: Top selling product = CONCATENATEX(TOPN(1, VALUES(Data[Stock Reference]), [VO]), Data[Stock Reference], ", ").

Build the final PivotTable

Insert a PivotTable from the Data Model. Place the Customer Name in the Rows area, and add your new measures (Rank, VO, QO, Top selling product) to the Values area. Filter the Rank column to show items less than or equal to 10.

Use Power Pivot and DAX Measures (Recommended)

Understanding DAX Filtering: The ALL() function inside the RANKX measure is crucial because it removes any implicit filters from the PivotTable, ensuring the rank is calculated against the entire list of customers rather than just the current row.

Solution 2

Use Excel Dynamic Array Formulas

If you do not have access to Power Pivot, you can use modern Excel dynamic array functions to extract and calculate the top 10 customers and their best products.

Advanced Data Analysis in WPS Office

Create Dynamic Sales Reports Effortlessly with WPS Office

WPS Office Spreadsheet offers robust data analysis tools, including fully compatible dynamic array functions and advanced PivotTables, allowing you to build complex top 10 sales reports quickly.

1. Open your sales data in WPS Spreadsheet: Launch WPS Office and open your raw sales order dataset.
2. Insert a PivotTable: Select your entire data range, navigate to the 'Insert' tab, and click 'PivotTable'.
3. Organize fields: In the PivotTable fields pane, drag the Customer field to the Rows area and Outstanding Value to the Values area.
4. Apply Top 10 filter: Click the filter dropdown on the Row Labels cell in the PivotTable, select 'Value Filters', and choose 'Top 10...' to automatically filter the list.
5. Analyze product breakdowns: Drag the Product field under the Customer field in the Rows area, and sort it by Outstanding Value descending to clearly see the leading product for each top customer.

Seamlessly compatible with Microsoft Excel formulas and PivotTable structures.Built-in top 10 value filtering for rapid PivotTable insights.Lightweight architecture ensures smooth performance on large datasets.Free to download with an intuitive, familiar interface.

microsoft office alternative - wps office

Frequently Asked Questions

How do I filter a standard PivotTable to show only the top 10 customers?

Click the dropdown arrow next to 'Row Labels' in your PivotTable, hover over 'Value Filters', and select 'Top 10...'. Choose the data field you want to base the ranking on (e.g., Sum of Outstanding Value) and click OK.

Why is my RANKX DAX formula returning 1 for every row?

This happens when the row context filters the dataset down to just that specific customer. You must wrap the table or column reference inside the RANKX function with ALL() (e.g., ALL(Data[Account])) to clear the context and evaluate the rank against the full dataset.

Can I use XLOOKUP with multiple criteria to find the top product?

Yes. You can concatenate criteria using an ampersand (&) in the lookup_value argument (e.g., CustomerName & MaxProductValue) and do the same for the lookup_array argument (e.g., CustomerRange & ValueRange) inside XLOOKUP to pinpoint the exact product.

Excel & Data Problems