Making a Pivot Table From Data Larger Than Excel Allows

Opening a CSV with two million rows in Excel gives a warning that the data was truncated, and the missing rows are gone from every total you build on it. A pivot table is the natural tool for the summary you want, so the question is how to feed it more than a sheet can hold. There are three routes: load past the sheet into the Data Model, shrink the data before it arrives, or sample it. This post goes through each, with the limits that decide which to use. It is a spoke of pivot tables and their SQL equivalents.

The limit and what it applies to

An Excel worksheet has 1,048,576 rows and 16,384 columns (Excel row and column limits). Opening a bigger CSV directly fills the sheet to the limit and drops the rest without any later reminder. The limit is a property of the grid. It is not a property of the pivot, and not of Power Query. A query can read ten million rows. Where it puts them decides whether the rows survive.

Route 1: load to the Data Model

The Data Model is an in-memory store (the xVelocity engine, the same one behind Power Pivot) that keeps columns compressed. A PivotTable can read from it with no sheet in between.

1. Data, Get Data, From File, From Text/CSV, and choose the file.

2. Click the arrow beside Load, choose Load To, then Only Create Connection and tick Add this data to the Data Model.

3. Insert, PivotTable, From Data Model.

The pivot now summarizes every row the model received, past a million. The workbook file stores the model compressed, so a file with five million rows may be tens of megabytes where a sheet would be unmanageable.

What the model does well depends on the columns. It compresses by dictionary: a column with few distinct values, like region or status, costs almost nothing, while a column of unique ids or long free text costs a lot. Two habits cut size and load time:

• Remove columns the pivot does not use. A fifty-column export that feeds a four-field pivot should be cut to those four plus keys before loading. The model keeps every column, used or not.

• Split date and time. A timestamp with seconds has millions of distinct values. Keep a date column and an hour column instead, and drop the full timestamp if you do not need it.

In Power Query this is two steps: Table.SelectColumns and a transform that derives the date.

The model has its own measures language, DAX, for calculations a plain pivot cannot do. A simple sum needs none. For ratios across tables you write measures, which is a different skill from formulas on a sheet.

The memory ceiling

The model lives in RAM while the workbook is open. 64-bit Excel can use as much as the machine has; 32-bit Excel is limited to about 2 GB for the process and fails at loads that 64-bit handles. The symptom of running out is an error such as "There isn't enough memory to complete this action" or a refresh that ends partway.

Check which you have under File, Account, About Excel. If it says 32-bit, a large load needs the 64-bit build or a smaller input. A rule of thumb: model size is a small fraction of a CSV's size when columns repeat values, and close to it when they do not. If a 2 GB CSV of mostly unique text will not load, no setting fixes it, because the data is the problem (reading a 500 MB Excel file without running out of memory covers the sheet side).

Route 2: aggregate before Excel sees it

Most pivots do not need the detail. A pivot of revenue by region, category and month needs rows at that grain, which is a few thousand, not a few hundred million. Do the grouping where the data lives and bring in the result.

In a database:

SELECT region,
       category,
       date_trunc('month', order_date) AS month,
       SUM(quantity * unit_price)      AS revenue,
       COUNT(*)                        AS order_lines
FROM orders
GROUP BY region, category, date_trunc('month', order_date);

On a file, DuckDB reads the CSV directly and does the same without a server:

COPY (
  SELECT region, category,
         date_trunc('month', order_date) AS month,
         SUM(quantity * unit_price) AS revenue,
         COUNT(*) AS order_lines
  FROM read_csv('orders.csv')
  GROUP BY ALL
) TO 'orders_by_month.csv' (HEADER, DELIMITER ',');

The output file is small enough to open directly or load into a pivot. Keep a count column like order_lines so the pivot can still produce averages by dividing sums, and keep sums rather than averages, because averages of averages are wrong. See querying CSV files with DuckDB instead of Excel.

The cost of this route is flexibility. A pivot on pre-aggregated rows can slice only by the columns you kept. To look by customer you need the grouping again. For most reports, that is acceptable, and a refresh takes seconds.

Route 3: sample

For exploring structure, not for reporting numbers, take a sample. A random tenth of the rows shows the shape of the data, which fields matter and what a pivot layout should be. In DuckDB, SELECT * FROM read_csv('orders.csv') USING SAMPLE 1% returns a random one percent. Build the layout on the sample, then point the real pivot at the aggregated full data. Never publish totals from a sample as if they were totals.

Choosing between the routes

Checks

Count rows twice: once in the source (wc -l for a CSV, SELECT COUNT(*) for a database) and once in the model. In the pivot, put the count of any field in Values with no filters, and compare with the source. A difference means rows were dropped by a filter, a parse error or the old sheet limit. Compare the grand total of a numeric field the same way.

If you use a sheet anyway, check the last row of the table: when it is row 1,048,576 you have hit the limit and the data is truncated.

When a pivot is the wrong way to summarize big data

You need the detail. A pivot summarizes. To find individual rows that break a rule, use a query.

The data changes by the minute. A model refresh reloads everything. For streaming data, summarize in the database and chart the result.

Many people will use it. Each person needs a copy of the file and the memory to load it. A shared dashboard or published dataset serves them better.

The shape is relational. Several large tables joined by keys belong in a database with indexes, not in a workbook.

Microsoft documents the size limits in Excel specifications and limits and the model in its page on data model specification and limits. For the file readers on the Python side, see analyzing more than a million rows without Excel. To convert a small summary to JSON, use the CSV to JSON converter.

For the engine behind the model and the file-based alternative, see the DuckDB documentation.