Pivot Table Issues

How to Fix Power Pivot Table Displaying Only 31,999 Records in Excel

Ayan Masood Sep 25, 2026 868 views

Question details

The user needs to resolve an issue where an Excel Power Pivot Table or Data Model result is capped at exactly 31,999 records, even though the original source dataset contains hundreds of thousands of values.

How to Fix Power Pivot Table Displaying Only 31,999 Records

Product: Microsoft Excel
Device & OS: not provided
Scenario: Analyzing and visualizing large datasets using Power Pivot and the Excel Data Model.
Observed behavior: The resulting Pivot Table output unexpectedly truncates and displays fewer records than expected, seemingly hitting a limit of 31,999 records despite there being no officially documented 31,999 row limit for PivotTables.

Before you start

Ensure your Microsoft Office application is fully updated, and verify the exact number of rows in your original data source before it is loaded into the Data Model.

Solution 1Recommended

Verify Data Model Specifications and Import Limits

Check if your dataset is hitting memory constraints or model limits within Excel, and confirm if the truncation occurs in the model or the export process.

There is no general PivotTable limit specifically set at 31,999 records. However, hardware memory limits (especially on 32-bit Excel) or specific data source connection constraints can cause unexpected behavior.

You need to isolate whether the data is missing from the underlying Data Model or if it is just failing to display in the PivotTable layout.

Open the Power Pivot Window

Navigate to the 'Power Pivot' tab on the Excel ribbon and click the 'Manage' button to open the Data Model.

Verify Loaded Row Count

Select your imported table in the Power Pivot window and check the record count at the bottom left of the screen. If it displays hundreds of thousands of rows, the Data Model is intact, and the issue lies in the PivotTable output.

Check Microsoft Data Model Specifications

Review Microsoft's official documentation for Data Model limits. Ensure you are using the 64-bit version of Microsoft Excel if you are handling very large datasets, as 32-bit versions are strictly limited to 2 GB of virtual memory.

Test with a Blank PivotTable

Create a brand new PivotTable from the Data Model on a blank worksheet to rule out corruption in the existing PivotTable layout.

Verify Data Model Specifications and Import Limits

Check the Export Process: If you are exporting data from Power Pivot to another tool, the 31,999 limit might be a restriction of the OLE DB provider or the destination software, not the PivotTable itself.

Solution 2

Inspect Filters, Relationships, and Calculated Measures

Ensure that hidden filters, incorrect table relationships, or specific DAX measures aren't artificially restricting the displayed records.

Free Microsoft Office alternative

Try WPS Office for Seamless Data Analysis

If you frequently encounter complex limitations, unexpected truncations, or heavy resource usage when using Microsoft Excel's Data Model, consider switching to WPS Office. It provides a lightweight, highly compatible, and user-friendly alternative for handling spreadsheets and standard Pivot Tables without the complexity.

1. Download and Install: Visit the official WPS Office website and download the free version for your operating system.
2. Open Your Spreadsheets: Launch WPS Spreadsheet and open your existing .xlsx files directly. The software will automatically recognize your standard Pivot Tables and data.
3. Analyze Without Constraints: Enjoy smooth, fast, and intuitive data summarization and visualization without worrying about bloated add-in limitations.

Highly compatible with Microsoft Excel (.xlsx, .xls, .csv) file formats.Lightweight architecture that processes standard Pivot Tables quickly and efficiently.Free to use with a familiar interface, requiring no steep learning curve.Seamless migration of your existing spreadsheet files without data loss.

microsoft office alternative - wps office

Frequently Asked Questions

Is there a hard limit of 31,999 rows in Excel Pivot Tables?

No, there is no general PivotTable limit specifically set at 31,999 records. Excel natively supports over 1 million rows per worksheet, and the Data Model can hold millions of rows. If you see this exact number, it is usually a result of memory constraints, export tool limitations, or specific DAX filter behavior.

Does using 32-bit Office affect Power Pivot data limits?

Yes. The 32-bit version of Microsoft Office has a strict 2 GB virtual address space limit. This can severely restrict the amount of data you can load and display in the Data Model and Power Pivot. Upgrading to 64-bit Office is highly recommended for analyzing large datasets.

How do I refresh the data model to ensure all rows are loaded?

Open the Power Pivot window, go to the Home tab, and click 'Refresh' or 'Refresh All'. This forces Excel to pull the latest data from your source connections. Check the status bar at the bottom to verify the total number of rows imported.

Can exporting data from Power Pivot cause truncated records?

Yes, querying or exporting data via certain connections (like older OLE DB providers) can sometimes hit specific row fetch limitations. Always verify if the data is truncated inside the Data Model itself, or if it only happens during the output or export process.

Excel & Data Problems