Pivot Table Issues

How to Increase the Number of Rows Returned from Excel PivotTable Details

Kushani NimanthikaKushani Nimanthika Nov 13, 2026 868 views

Question details

The user wants to increase the number of detail rows returned when double-clicking a PivotTable value in Excel, particularly when the PivotTable uses the Data Model.

How to Increase the Number of Rows Returned from Excel PivotTable Details
Product
Microsoft Excel
Device & OS
not provided
Scenario
Double-clicking a PivotTable value in Excel creates a detail sheet, but the user needs more returned rows than the default Data Model limit allows.
Observed behavior
Excel returns a limited number of rows from the detail drill-down when the PivotTable is based on the Data Model.
Before you start

Open the workbook in Excel and check that the Data tab and Queries & Connections pane are available. If the Data Model properties dialog does not show a detail-row limit, plan to rebuild the PivotTable from a regular table or range.

Solution 1Recommended

Increase the Data Model Detail-Row Limit in Excel

Use this solution when your Excel version exposes the Data Model properties; it changes the row limit for the PivotTable detail drill-down.

The Data Model detail-row limit is controlled in the workbook's Data Model connection rather than in the PivotTable field settings. This option appears in Excel versions that include the Data Model or Power Pivot.

1
Create the PivotTable with the Data Model

In Excel, select the source data and click Insert > PivotTable. In the Create PivotTable dialog, check “Add this data to the Data Model” and click OK.

2
Open Queries & Connections

Click the Data tab on the ribbon, then click Queries & Connections to open the Queries & Connections pane.

3
Open Data Model properties

In the Queries & Connections pane, right-click the Data Model connection and select Properties.

4
Increase the detail-row limit

In the Data Model Properties dialog, locate the detail-row or row limit option, increase the value, and click OK.

5
Verify the detail sheet

Refresh the PivotTable, double-click a value, and confirm the new detail sheet returns more rows.

Increase the Data Model Detail-Row Limit in Excel
Option availability: If the option is not available, your Excel version may not support changing this limit; use the alternative solution below.
Free Microsoft Office alternative

Use WPS Office Spreadsheets for PivotTable Detail Work Without Data Model Limits

WPS Office is a free, lightweight Office alternative that opens .xlsx files and supports PivotTable creation and detail drill-down in a familiar ribbon interface. It helps you avoid Excel Data Model configuration while keeping your spreadsheet workflow intact.

Create PivotTables and double-click values to view detail rows without Excel Data Model settingsOpens, edits, and saves .xlsx files with formulas, formatting, and PivotTables preservedFamiliar Office-style ribbon interface makes it easy for Excel users to switchFree to download and lightweight compared with Microsoft Office
microsoft office alternative - wps office

Frequently Asked Questions

Why does double-clicking a PivotTable value return a limited number of rows in Excel?

The PivotTable is likely using the Data Model. Data Model drill-down has a default row limit. Go to Data > Queries & Connections, right-click the Data Model, choose Properties, and increase the limit if available.

Where do I find the Data Model row limit option in Excel?

Open the Data tab, click Queries & Connections, right-click the Data Model connection in the pane, and select Properties. The row-limit option appears there in supported Excel versions.

What can I do if the Data Model Properties dialog does not show a detail-row limit?

Rebuild the PivotTable from a normal Excel table or range without checking Add this data to the Data Model, then double-click a value to extract detail rows directly from the source.

Does WPS Office support PivotTable detail drill-down?

Yes. WPS Office Spreadsheets can create PivotTables and double-click a value to create a detail sheet, and it opens and saves .xlsx files without requiring the Excel Data Model.

Will changing the Data Model row limit affect my source data?

No. It only changes how many rows Excel returns when you drill into PivotTable details; the source data remains unchanged.