Pivot Table Issues

Work Around the 1,000-Row Power Pivot Drill-Down Limit in Excel 2016

Kushani NimanthikaKushani Nimanthika Nov 18, 2026 868 views

Question details

The user needs to retrieve more than 1,000 underlying records for an Excel 2016 PivotTable value.

Work Around the 1,000-Row Power Pivot Drill-Down Limit in Excel 2016
Product
Microsoft Excel 2016 Power Pivot
Device & OS
not provided
Scenario
The PivotTable uses worksheet data from another workbook, and the user wants to inspect all records behind a summarized value.
Observed behavior
Drilling through returns no more than 1,000 records, and the OLAP maximum-records setting is disabled.
Before you start

Locate the source workbook and identify the PivotTable value and filters you want to investigate. Check which source columns correspond to the PivotTable's row, column, and report filters.

Solution 1Recommended

Use Power Query to retrieve the matching source records

Use this workaround to load and filter the source workbook independently of the PivotTable drill-through limit.

The OLAP maximum-records setting is unavailable for the worksheet source described here. Filtering the source with Power Query does not change that setting; it creates a separate result.

Match the PivotTable filters as closely as possible. Calculated measures, relationships, and other data-model logic may require additional filters or transformations.

1
Connect to the workbook

In Excel 2016, select Data > New Query > From File > From Workbook, then choose the workbook containing the source data.

2
Open the source data

In Navigator, select the relevant worksheet or table and choose Edit to open Power Query Editor.

3
Apply matching filters

Use the filter buttons in the column headers to select the dates, categories, or other values represented by the PivotTable cell.

4
Load the results

Select Home > Close & Load to load the filtered records into Excel. Use Data > Refresh All when you need to update the result.

Use Power Query to retrieve the matching source records
Worksheet capacity: An Excel worksheet can contain up to 1,048,576 rows, including the header row. A larger result cannot fit on one worksheet.
Free Microsoft Office alternative

Analyze spreadsheet data with WPS Office

WPS Office is a free, lightweight alternative for working with spreadsheets in a familiar interface. Open compatible Excel files and continue everyday analysis as you move between office suites. It does not change Excel's Power Pivot drill-through setting.

Filter worksheet records to inspect source dataOpen and edit Microsoft Excel XLSX filesUse familiar spreadsheet menus and toolsMove existing compatible files into a lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why is the maximum number of records setting disabled?

The OLAP Drill Through maximum-records setting applies to OLAP data sources. It cannot be edited for the worksheet source described in this case.

Can Power Query return more than 1,000 matching records?

Yes. In Excel 2016, use Data > New Query > From File > From Workbook, filter the source data, and load the result. The number loaded is still subject to Excel's worksheet capacity.

Will filtering the source always reproduce the PivotTable drill-down?

Not necessarily. Apply the same visible PivotTable filters to the source, but also account for any data-model relationships, calculated measures, or other logic that affects the value.

Would moving the data to an OLAP source help?

An OLAP-compatible source can make the OLAP drill-through maximum-records setting available. The result also depends on the source and its configuration; moving data requires setting up an appropriate OLAP data source.