Excel Pivot Table Row Limit: What You Need to Know

In Excel for Microsoft 365 on Windows, a PivotTable displayed on a worksheet is limited by the worksheet grid: 1,048,576 rows in total. That is not a separate limit on how many records a PivotTable can summarize; the source, the number of distinct row labels, and the PivotTable’s displayed layout all affect what you can work with.

The short answer: the worksheet sets the display ceiling

Excel worksheets have a maximum of 1,048,576 rows and 16,384 columns. A PivotTable placed on a worksheet must fit inside those boundaries. Its headers, labels, subtotals, and other displayed rows occupy worksheet cells too, so the number of detail rows available is less than the full grid limit whenever the report uses any rows above or below its data.

For example, a report with one header row and 1,048,575 detail rows would use the worksheet’s entire row capacity. If it also has a grand total row, it cannot fit all those detail rows on that sheet. In practice, a PivotTable with that many displayed rows is unusual: most reports group many source records into a much smaller set of categories.

So the useful distinction is this: the worksheet limits how much of a PivotTable can be displayed there, while the amount of source data a PivotTable can summarize depends on how that data is stored and on available system resources.

Source records are not the same as PivotTable rows

A source record is one row in the underlying data. A PivotTable row is a displayed group or item created from the fields you place in the Rows area. If 500,000 transactions belong to 12 regions, a PivotTable grouped by region can show 12 region rows, not half a million transaction rows. Adding a date or product field can create more groups, but the PivotTable still summarizes repeated combinations rather than automatically listing every transaction.

For a conventional source range stored on a worksheet, the source itself is subject to the worksheet limit. A header row leaves room for at most 1,048,575 data rows on that sheet. A PivotTable based on that source may show far fewer rows if it groups the data; if you add a field with a unique value for nearly every record, the output can approach the sheet’s capacity.

There can also be a difference between the number of groups and the number of visible lines. Nested row fields, repeated labels, subtotals, and grand totals can add lines to a report. Expand and collapse controls change which parts of a hierarchy are currently displayed, but do not change the underlying source records.

How to tell what is reaching the limit

First identify whether the problem is with the source or the report. If your data is on a worksheet and you cannot add another record below the last available row, you have reached the worksheet limit for that source. If the PivotTable is cut off or cannot expand farther down, it may be running out of rows on its output sheet. If it refreshes slowly or fails during calculation, the issue may instead be memory, complexity, or another resource constraint.

Check the source and layout before changing the workbook:

Count source records, including whether a header row uses one of the worksheet’s rows.
Look at the fields in the PivotTable Rows area. A field containing a distinct ID for every record can create a very long report.
Check whether subtotals, grand totals, or multiple nested fields add displayed lines.
Confirm that the PivotTable has enough empty space below and to the right. Existing content can prevent it from expanding even when the sheet has unused rows elsewhere.
Consider whether a slow refresh is being mistaken for a row-limit error. A large or complex report can take time without having reached the grid boundary.

Working with more source data than fits on a sheet

If your source has more records than a worksheet can hold, you cannot place all of those records directly into one ordinary worksheet range. You can, however, use Power Query and Excel’s Data Model to work with data without loading every record onto a sheet. In Excel for Microsoft 365 on Windows, import or connect to the source, shape it in Power Query if needed, and load it to the Data Model rather than to a worksheet table. You can then create a PivotTable using that model.

The Data Model does not give a workbook an unlimited capacity. Its practical size depends on factors such as available memory, the size and structure of the data, the number of calculations, and the workbook environment. Highly repetitive columns may compress efficiently, while large amounts of distinct text can require substantially more storage. A model can therefore handle more rows than a worksheet source in some situations, but there is no single practical row count that is safe to promise for every computer and dataset.

There is also an important boundary: even if the model can hold and summarize a large source, a PivotTable displayed on a worksheet still has to fit within that worksheet’s 1,048,576-row grid. Use grouped or filtered summaries when possible. If you need a result with more displayed rows than the grid allows, you will need to divide the output or use a tool designed to work with larger result sets.

Why a PivotTable may produce so many rows

The fields you place in Rows determine the report’s grouping. A field such as Department may produce a few dozen items. An Invoice ID or timestamp may be unique for nearly every record and produce a much longer list. Adding multiple fields creates nested groups: for example, Region, then Store, then Product. The resulting report may contain many combinations, especially if there are numerous distinct values at each level.

To make a report smaller, remove fields that identify individual records unless you truly need a record-level listing. Use a date grouping such as month or year instead of placing every date or timestamp in Rows. Filter to a relevant period or category, or group numerical values into useful ranges. These choices change the level of detail in the report; they do not delete or alter the source data.

Layout options can improve readability but do not bypass the row ceiling. In Excel for Microsoft 365 on Windows, use Design > Report Layout > Show in Tabular Form to display row fields in separate columns. This can make a hierarchy easier to scan, but it does not reduce the number of distinct groups. Similarly, hiding subtotals may remove some displayed lines, but it cannot make an over-limit detail list fit if the list itself is too long.

Refreshing, filtering, and changing the source

A PivotTable does not necessarily update its results the moment source data changes. Select a cell in the PivotTable and use PivotTable Analyze > Refresh, or refresh all workbook connections when appropriate. If the source is a fixed cell range and new records were added outside it, the PivotTable may not include them. Converting the source to an Excel table can make it easier for the source to grow: select the data and use Insert > Table, then use that table as the PivotTable source and refresh after adding records.

Filters help reduce visible output when you do not need every category at once. A report filter, a field filter, or a slicer can focus the PivotTable on a subset. Filtering is useful for analysis, but it does not necessarily reduce the size of the underlying source or Data Model. If the PivotTable must present every distinct item simultaneously, filtering is not a way around the worksheet’s maximum row count.

When a refresh reports that the PivotTable cannot expand, inspect the cells around its output for existing values, formulas, or another PivotTable. Excel needs room to place the refreshed result. Moving the PivotTable to a clear sheet or removing blocking content may solve an expansion problem that looks like a row-limit issue.

Choosing between a PivotTable and other tools

For an ordinary summary of sales, counts, dates, or categories, a PivotTable is often the right choice because it lets you rearrange fields and inspect grouped totals. For a source too large to fit in a worksheet, a Data Model PivotTable can preserve that interactive summary approach without placing every source record on a sheet.

If the goal is to export or inspect every record, rather than summarize it, a PivotTable may be a poor fit. A filtered query, a database, or a reporting tool can be more appropriate for a result larger than a worksheet. Power Query can filter, combine, and transform data before loading a manageable result. If the final output must be an Excel worksheet, split it into logically defined sections or files when it exceeds the grid, and make the division clear so that users do not mistake a partial extract for the full dataset.

There is no general PivotTable setting that raises the worksheet row limit. Changing Compact Form to Tabular Form, moving the PivotTable, or using a different worksheet does not create a larger grid. Moving the report can give it more available space, but each worksheet has the same maximum number of rows.

Frequently Asked Questions

Can an Excel PivotTable have more than 1,048,576 rows?
A PivotTable displayed on a worksheet cannot occupy more than the worksheet’s 1,048,576 rows. Its source or Data Model may contain more records than fit on a sheet, but the displayed report must still fit within the grid.

Does the 1,048,576 limit count the header row?
Yes. It is the maximum number of worksheet rows, not a count reserved only for PivotTable data. Headers, labels, subtotals, and totals use rows too. A header in row 1 leaves at most 1,048,575 remaining rows on that sheet.

Can Excel summarize more rows than it can store in a worksheet?
Yes, if you bring data into the Data Model or connect to an external source rather than loading every record onto a worksheet. How much the model can handle depends on memory, data characteristics, and workbook resources; it is not unlimited.

Why does my PivotTable show fewer rows than my source data?
PivotTables group records according to the fields in their layout. Repeated values are combined into groups, so thousands of transactions might become a short list of regions or products. Add a more detailed row field if you need to see finer groupings.

Will changing the PivotTable layout let it exceed the row limit?
No. Tabular Form and other layout choices affect presentation, not the worksheet’s capacity. Removing unnecessary row fields or filtering categories can reduce the displayed result, but no layout setting expands the grid.

Does Excel for Mac have a different worksheet row limit?
The worksheet grid limit is the same: 1,048,576 rows. Ribbon locations and some data-import or Data Model capabilities can differ by Excel version and platform, so Windows-specific steps may not appear identically on Mac.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.