How to Increase the Number of Rows Returned from Excel PivotTable Details
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.

- 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.
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.
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.
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.
Click the Data tab on the ribbon, then click Queries & Connections to open the Queries & Connections pane.
In the Queries & Connections pane, right-click the Data Model connection and select Properties.
In the Data Model Properties dialog, locate the detail-row or row limit option, increase the value, and click OK.
Refresh the PivotTable, double-click a value, and confirm the new detail sheet returns more rows.

Create the PivotTable from a Normal Table or Range Instead of the Data Model
Use this if the Data Model row-limit option is missing, or if you want standard PivotTable detail behavior not governed by the Data Model.
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.

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.




