Pivot Table Issues

How to Find the Top 10 Clients by Count in Excel

Rana Garcia Oct 9, 2026 871 views

Question details

The user needs to quickly extract and display the top ten clients based on their total occurrences in a dataset containing over 50,000 records.

How to Find the Top 10 Clients by Count in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Analyzing a large dataset to identify the most frequent clients without experiencing severe performance lags.
Observed behavior: Needs an efficient data analysis method, as traditional row-by-row counting formulas can cause performance issues on 50,000+ records.

Before you start

Ensure your dataset has clear column headers and no empty rows, and consider formatting your data as an Excel Table (Ctrl+T) to improve formula performance and automatic expansion.

Solution 1Recommended

Use a PivotTable to Find the Top 10 Clients

This is the fastest and most efficient way to analyze large datasets and display the top 10 highest totals without writing complex formulas.

PivotTables are optimized for performance and can process hundreds of thousands of rows almost instantly. This method is highly recommended for datasets larger than 50,000 records.

Insert a PivotTable

Select your entire data range or table. Navigate to the Insert tab on the ribbon and click PivotTable. Choose to place it on a New Worksheet and click OK.

Configure the PivotTable Fields

In the PivotTable Fields pane, drag the 'Client' field into the 'Rows' area. Then, drag the same 'Client' field into the 'Values' area. Ensure it is set to 'Count of Client'.

Apply the Top 10 Filter

Click the filter dropdown arrow next to 'Row Labels' in the PivotTable. Hover over 'Value Filters' and select 'Top 10...'. In the dialog box, leave the settings as 'Top', '10', 'Items', and click OK.

Use a PivotTable to Find the Top 10 Clients

Data Updating: If you add new clients to your source table later, simply right-click anywhere inside your PivotTable and select 'Refresh' to instantly update the Top 10 list.

Solution 2

Use Dynamic Array Formulas (SORT and UNIQUE)

An alternative formula-based approach using modern Excel functions to create an automatic, sorted client list.

Solution 3

Highlight the Top 10 Using Conditional Formatting

Use conditional formatting to visually highlight the top 10 client counts within a larger summary list.

Data Analysis in WPS

Find Top 10 Data Easily with WPS Spreadsheet

WPS Spreadsheet fully supports PivotTables, dynamic array formulas, and advanced conditional formatting. It handles datasets with tens of thousands of rows effortlessly, making it the perfect tool for fast data extraction.

1. Open Your Dataset: Launch WPS Spreadsheet and open your .xlsx file containing the client records.
2. Create a PivotTable: Select the data, go to the Insert tab, and choose PivotTable.
3. Organize the Fields: Drag your client column header into both the Rows area and the Values area (set to Count).
4. Apply the Filter: Click the Row Labels filter icon, choose Value Filters > Top 10, and click OK.

Lightning-fast PivotTable processing for large datasets100% compatibility with Microsoft Excel formats (.xlsx)Familiar user interface requiring no new learning curveFree, lightweight, and available on Windows, Mac, and Linux

microsoft office alternative - wps office

Frequently Asked Questions

How do I update my Top 10 PivotTable when new data is added?

If your source data is formatted as a Table, you only need to right-click anywhere inside the PivotTable and select 'Refresh'. The Top 10 list will automatically recalculate to include the new records.

Can I find the bottom 10 clients instead of the top 10?

Yes. When you open the Top 10 Value Filter dialog box in your PivotTable, simply click the first dropdown menu and change the selection from 'Top' to 'Bottom'.

Why is Excel freezing when I use COUNTIF on my data?

Using COUNTIF across full column references (like A:A) on datasets exceeding 50,000 rows requires massive computational power. To prevent lagging, use bounded ranges (e.g., A2:A55000) or switch to the PivotTable method, which is much faster.

Excel & Data Problems