Pivot Table Issues

How to Show All Tied Records in an Excel PivotTable Top 10 Filter

Elise Williams Oct 21, 2026 869 views

Question details

The user needs to display all records that share the cutoff value when applying a Top 10 filter to an Excel PivotTable.

How to Show All Tied Records in an Excel PivotTable Top 10 Filter

Product: Spreadsheet
Device & OS: not provided
Scenario: Filtering a PivotTable to show the top N results where multiple items share the exact same value at the cutoff boundary.
Observed behavior: The standard PivotTable Top 10 filter restricts the display to exactly the requested number of items, hiding any additional records that are tied at the cutoff value.

Before you start

Ensure your source data contains no duplicate header names and verify that the numeric columns used for ranking are properly formatted as numbers.

Solution 1Recommended

Add a Helper Column to the Source Data

Use a ranking formula in your source data to manually assign a rank to each record. This method naturally accounts for ties before the data enters the PivotTable.

The most reliable way to include all ties in a basic PivotTable is to calculate the rank directly in the source data. By using the RANK.EQ function, tied values receive the exact same rank number.

Insert a Rank Column

Open your source data worksheet and add a new column header named 'Rank' next to your values.

Apply the RANK.EQ Formula

In the first cell of the new column, enter the formula =RANK.EQ(B2, $B$2:$B$100), adjusting the cell references to match your specific value column.

Update PivotTable Source Data

Go to your PivotTable, click on 'PivotTable Analyze' (or 'Options'), select 'Change Data Source', and expand the range to include your new 'Rank' column.

Filter by Rank

Add the 'Rank' field to the Filters area of your PivotTable. Click the filter dropdown, select 'Value Filters' -> 'Less Than or Equal To', and enter 10.

Dynamic Updates: As long as your source data is formatted as an Excel Table, the rank formula will automatically apply to new rows, keeping your PivotTable accurate upon refresh.

Solution 2

Use a DAX Measure in Power Pivot

If you are using the Data Model in Excel, creating a tie-aware DAX measure using the RANKX function allows you to dynamically filter the Top 10 including ties.

Easily Analyze Top Records with WPS Spreadsheet

WPS Spreadsheet provides powerful PivotTable functionalities and over 400 built-in functions. You can seamlessly use helper columns with RANK.EQ to manage tied data and create precise Top 10 reports.

1. Open your dataset: Launch WPS Spreadsheet and open the file containing your raw data.
2. Create a Rank helper column: Add a new column using the =RANK.EQ() function to assign rank values that recognize ties.
3. Insert a PivotTable: Highlight your expanded dataset, navigate to the Insert tab, and click PivotTable.
4. Apply your custom filter: Drag the Rank column into the Filters pane and set the criteria to display values less than or equal to 10.

Fully compatible with Microsoft Excel .xlsx files and PivotTablesBuilt-in RANK.EQ function for flawless tie-handlingIntuitive interface for creating and managing complex data modelsFree, lightweight, and fast alternative for data analysis

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't the default Top 10 filter show ties in Excel PivotTables?

The built-in Top 10 Value filter is programmed to strictly limit the maximum number of rows returned. Once it hits exactly 10 rows, it cuts off the display, intentionally excluding any remaining items even if they have the exact same cutoff value.

Does RANK.EQ handle ties differently than RANK.AVG?

Yes. The RANK.EQ function assigns the identical top rank to tied values (e.g., 1, 2, 2, 4), making it perfect for finding the top N items. Conversely, RANK.AVG assigns the average rank to tied values (e.g., 1, 2.5, 2.5, 4).

Can I use Conditional Formatting instead of filtering to highlight the Top 10?

Yes. If you apply a Top/Bottom Rules conditional format directly to your data range or PivotTable values, the rule naturally evaluates the threshold value. Because it highlights based on value rather than strict row count, it will automatically highlight all tied records at the cutoff limit.

Excel & Data Problems