In Excel pivot table the drill down is limited to 1,000 rows
Discover how to manage drill-down limits in pivot tables, enabling efficient data exploration beyond the 1,000-row constraint.
Written by Ian Oldrey
Updated at February 27th, 2026
Table of Contents
In Excel the default setting on the drill down of a pivot table is a thousand rows. This is when you are looking at a cell with a total value in it in the pivot table and double click to open a list of the items (orders, customers, etc.) that make up that figure.
How do I expand the number of rows on pivot table drill downs?
In the excel workbook you are using follow these steps.
Step 1 - Data Tab
Click on the ‘Data’ tab for the workbook/spreadsheet and click on ‘Queries & Connections’
Step 2 - Queries & Connections
On the right of the page you will see the Queries & Connections pane.
- Click on the ‘Connections’ header
- Right Click ‘ThisWorkBookDataModel’
- Click ‘Properties’
Step 3 - Connection Properties
In the ‘Maximum number of records to retrieve:’ enter the number you would like up to 1,048,576.
Be aware if you set a very high number and you request a drill down with many thousands of items it could take some time to present it to screen.
