Yes, Excel can handle 1.5 million rows. A worksheet stops at 1,048,576 rows, but Power Query and the Power Pivot data model are not bound by that limit. They hold data in memory, outside the grid. I ran a property transaction model on roughly 1.5 million rows and got a full refresh down to three minutes, from fifteen. That is slow by database standards, and it was fine for the job.
This surprises people, because most advice on the topic stops at "Excel maxes out at a million rows, use a database." That advice is half right, and being half right costs money. Teams either migrate a year early and pay for infrastructure they don't need, or they trust the grid limit, hit it, and panic.
Here is what a project at that scale looked like, and the signals that eventually pushed me out of Excel. None of them were row count.
The project
A real estate investor had roughly 1.4 to 1.6 million property transaction records covering about a decade, sitting in a folder of CSV files. He wanted two things out of it: rent gap analysis, comparing what new tenants pay against what sitting tenants pay, and capital appreciation at building level, quarter over quarter and year over year.
Neither of those is a lookup. They need a real model, with relationships, time intelligence, and measures that recalculate as you filter by area and date range. The question was whether Excel could model that, not whether it could store it.
It could, for a long while.
Where the 1,048,576-row limit applies
Most advice on this collapses three separate things into one number.
| Component | Row limit | What it's for |
|---|---|---|
| Worksheet grid | 1,048,576 hard stop | Where you look at data |
| Power Query | No practical row limit | Where you clean and shape data |
| Power Pivot data model | Millions of rows, memory-bound | Where data lives for analysis |
The million-row limit belongs to the grid, the sheet you scroll. Power Query never has to land data there. You can point it at a folder, transform it, and load the result straight to the data model, skipping the worksheet entirely. The data model is columnar and compressed, which is why it swallows row counts the grid can't.
The ceilings that do bite are different ones.
File size is the first. An .xlsx workbook stops at 2 GB. Compression in the data model is aggressive, so 1.5 million rows of transaction data lands well under that, but wide tables with a lot of high-cardinality text columns eat the budget fast.
Memory is the second. 64-bit Excel is bound by available RAM. 32-bit Excel is capped near 2 GB of address space and falls over much earlier. If you are doing this work on 32-bit, that is your first fix and it costs nothing.
Refresh time is the third, and it is the one that shapes your working day. Nobody warns you about it.
Fifteen minutes to three
My first working pipeline refreshed in about fifteen minutes. It was correct and it was unusable.
Fifteen minutes sounds survivable until you are building. During development you change an ingestion step, refresh, check the result, change it again. Four iterations is an hour. You stop experimenting and the model stops improving, because testing any idea costs a coffee break.
After the fixes below, plus filtering the set down to the records the analysis needed, a full refresh ran in about three minutes. Those two things aren't separable and I won't pretend they are: some of the gain came from doing less work, and some came from doing the same work better.
Once loaded, the model was fast. A dozen or so DAX measures covering rent gap comparisons, quarter-over-quarter and year-over-year appreciation, CAGR, and currency switching all recalculated quickly enough to click around in. That is the columnar engine doing its job. At this scale the bottleneck is refresh, not analysis.
Six things that made Power Query slow
These are the bottlenecks I hit. In my experience they account for most "Power Query is unusably slow" complaints at this row count.
1. Automatic type detection. Power Query inserts a Changed Type step by default, guessing column types from a sample. On a folder of CSVs that guess gets re-applied per file and can force extra passes over the data. Turn automatic type detection off in Query Options and set your types once, deliberately, at the end of the transform chain.
2. The Combine Files template is doing more than you think. Point Power Query at a folder, click Combine, and it generates a sample file query, a helper function, and a parameter. Four moving parts you didn't write and can't easily reason about. That is convenient for ten small files. On a decade of CSVs it is opaque, and it made diagnosing performance nearly impossible. I replaced it with a manual ingestion: read the folder, filter to the files I want, apply one explicit transform function, append. More lines to write, but I could see what it was doing, and it ran faster.
3. OneDrive turns local files into network reads. Source files in a synced OneDrive folder can get read over the network rather than off the disk, which turns a fast local operation into a slow remote one. Pointing the query at a genuinely local path was one of the bigger single wins here, and it took two minutes to test.
4. Referenced queries get re-evaluated. Power Query does not cache aggressively. If three queries reference the same upstream query, that upstream work can run three times. Look for a staging query that half your model depends on. It is often doing its job over and over.
5. Table.Buffer is not a speed setting. Buffering forces a table into memory so it stops being re-read, and in specific spots that genuinely helps. Applied broadly it does the opposite: it pins large intermediate tables in memory and can make things worse, especially at this row count. Reach for it after you have measured something.
6. Transforming before filtering. Obvious in principle, easy to violate in practice. Every row you drop early is a row you don't type, sort, merge, or transform later. This was a real chunk of the fifteen-to-three gain.
Two data problems worth naming separately
Performance was the loud problem. These two were the dangerous ones, because neither throws an error.
Mixed date formats. The CSVs came from sources using both US convention (MM/DD/YYYY) and the local convention (DD/MM/YYYY), and there is no way to look at 03/04/2021 and know whether it means March 4th or April 3rd. Power Query will happily parse both and hand you a number. What you get is a model that runs clean and reports the wrong quarter for a chunk of your transactions. The fix was an explicit locale-aware parsing rule per source instead of letting Power Query guess.
Junk records that look like data. I removed properties with no ID, and records carrying rent values that couldn't be real. Both would have passed straight through any type check. They are structurally valid, just false. On appreciation and rent gap measures, a handful of impossible rent figures can visibly bend a trend line, and nothing in the pipeline would have told me.
At this scale the errors that hurt you are the quiet ones. A refresh that breaks tells you it broke. A fake rent figure just sits there looking like data. Budget review time for the data itself, not only for the pipeline.
Have a workbook that is getting slow, or a folder of exports nobody quite trusts? That is the work I do: cleaning up the data layer underneath reporting, and moving it somewhere faster when it has earned the move. Book a 15-min fit call and bring the file.
When to leave Excel
Row count is a bad trigger. These are better ones. If two or more are true, start planning the move.
| Signal | Why it matters |
|---|---|
| Refresh time stops you iterating | You stop improving the model, and it quietly ages |
| More than one person needs the same numbers | Workbooks get copied, copies drift, now you have two truths |
| Data arrives faster than you can refresh | You are reporting on a snapshot and calling it current |
| You are approaching the 2 GB workbook limit | There is no graceful degradation, it just stops |
| You need to join to something Excel can't reach | An API, an app database, another team's warehouse |
| The workbook is the only place the logic exists | Business rules trapped in M and DAX with no version history |
On this project it was the first and third. The dataset kept growing, refresh kept lengthening, and the gap between "the data exists" and "the model reflects it" kept widening. The workbook had started costing me more attention than the analysis was worth.
What leaving looked like
I moved the pipeline to BigQuery. Concretely:
- The Power Query transform logic was rebuilt as SQL.
- CSV ingestion was automated with Google Apps Script instead of a manual folder drop.
- The DAX gap-analysis measures were translated into SQL window functions.
LAG,LEAD, and partitioned aggregates cover most of what the time intelligence measures were doing.
Query times went from minutes to seconds at the same roughly 1.5 million rows.
None of that migration would have gone smoothly without the Excel phase first. The Excel model is where the business logic got worked out: what counts as a comparable unit, which records to exclude, how to handle the date ambiguity. Rebuilding that in SQL was translation. Discovering it in SQL would have been slower and more expensive.
So should you move?
If you are under a few million rows, a refresh measured in minutes is acceptable, and one person owns the workbook, then stay. Load to the data model instead of the grid, work through the six bottlenecks above, and you will probably buy yourself another year.
Move when refresh time is changing your behaviour, or more than one person depends on the numbers, or data arrives faster than you can process it. The row count is not what beat you. A single file on a single machine just stopped being enough for what you are doing with it.
Frequently asked questions
Can Excel handle more than 1 million rows?
Yes. The 1,048,576-row limit applies to the worksheet grid, not to Power Query or the Power Pivot data model. Loading data to the data model instead of a sheet lets Excel work with several million rows, bounded by available memory and the 2 GB workbook file size rather than by a row count.
How slow is Power Query with 1.5 million rows?
On my project the first working pipeline refreshed in about fifteen minutes. After optimization, and after filtering the set to what the analysis needed, a full refresh ran in about three minutes. Queries against the loaded data model were fast. Refresh is the bottleneck at this scale, not analysis.
Why is my Power Query refresh so slow?
The most common causes are automatic type detection, the auto-generated Combine Files template, source files being read over a network path such as synced OneDrive, referenced queries being re-evaluated multiple times, overuse of Table.Buffer, and transforming rows before filtering them out.
When should I move from Excel to BigQuery or a database?
Move when refresh time stops you iterating, when more than one person needs the same numbers, when data arrives faster than you refresh, when you approach the 2 GB workbook limit, or when you need to join to a source Excel can't reach. Row count alone is a poor trigger.
Should I build the model in Excel first, then migrate?
Often yes. Excel is a fast place to discover business logic: what to include, what to exclude, how to handle edge cases. Migrating a working model is translation. Discovering the logic directly in SQL is usually slower and more expensive.
If your workbook is running out of room
Most of the workbooks I get handed are not broken. They are slow, a bit fragile, and holding more business logic than anyone realises. Sometimes the fix is a week of pipeline work and the file lives another two years. Sometimes it really is time to move, and the job is doing that without losing the logic already baked in.
Book a 15-min fit call, bring the workbook, and I will tell you which of the two you are looking at.