# Knowing When to Leave Excel

Excel is so capable that people keep forcing it past the point where it helps: the file takes four minutes to open, three people overwrite each other's copies, and nobody can say where a number came from. Skill is not only using a tool well. It is also seeing that the problem has outgrown it. This phase gives you the hard limits, the middle path inside Excel, and a way to choose the next tool.

## The hard limits

These come from Microsoft's specifications page for Excel:

| Limit | Value |
|---|---|
| Rows per worksheet | 1,048,576 |
| Columns per worksheet | 16,384 |
| Characters in one cell | 32,767 |
| Characters in a formula | 8,192 |
| Numeric precision | 15 digits |

Source: Microsoft's [Worksheet and workbook specifications and limits](https://support.microsoft.com/en-us/office/1672b34d-7043-467e-8e27-269d656771c3).

A worksheet row limit is a wall, not a slowdown. Power Query documents the same ceiling: it can fill at most 1,048,576 rows to a worksheet. A file with more rows than that cannot be loaded in full onto a sheet, whatever tool imports it.

The limits you hit before the wall are softer, and mostly about memory. Microsoft notes that in 32-bit Excel, the process shares about 2 GB of virtual address space, with a data model using maybe 500 to 700 MB of it. The 64-bit version has no hard file-size limit and is bound by system resources. If you regularly handle big files, check which one you have under File, Account, About Excel.

## The middle path: the Data Model and Power Pivot

Excel has a second storage area behind the grid: the **Data Model**. It holds tables inside the workbook with **relationships** between them, and PivotTables can read from it. **Power Pivot** is the add-in and window for managing that model and writing measures in DAX, the same formula language Power BI uses.

You load data into it from Power Query: in the Close & Load To options, choose to add the data to the Data Model instead of (or as well as) a worksheet. Rows stored there do not occupy worksheet rows, so the 1,048,576 limit on the grid stops applying to them. Microsoft's Power Pivot overview describes importing millions of rows into a workbook this way.

In the running example, this is how the sales export could grow past a sheet: keep the monthly CSVs, load them with Power Query to the Data Model, relate them to `Reps`, and pivot on the model.

Be aware of two things:

- Power Pivot availability depends on your Office edition. Check Microsoft's [Power Pivot overview](https://support.microsoft.com/en-us/office/where-is-power-pivot-aa64e217-4b6e-410b-8337-20b87e1c2a4b) for the versions it applies to, and confirm that your own edition has it.
- The Data Model fixes the row-count wall, not the sharing, version-control, or refresh-scheduling problems.

## When Excel is the wrong tool

Here is a judgment-based guide, not a law. Look for the signal, not the size.

| Signal | What is really wrong | Better tool |
|---|---|---|
| More rows than a sheet can hold, or a file that crawls and crashes | Memory and the grid wall | Data Model for a stopgap, then a database or Python |
| The same report refreshed by hand every week and emailed as a file | A manual delivery process | Power BI: a published report that refreshes on a schedule. See [Power BI From Zero](/guides/power-bi-from-zero) |
| Many readers need to slice the same numbers and trust one version | One source of truth, with permissions | Power BI, and see [Power BI DAX Deep Dive](/guides/power-bi-dax-deep-dive) for measures |
| The data really lives in a database and you keep exporting it, then pasting | Copying, and the copy goes stale | SQL, querying the source directly. See [Spreadsheets to SQL to Pipelines](/guides/spreadsheets-to-sql-to-pipelines) |
| Joins across many tables, or the same query is needed again and again | Excel joins are fragile and slow | SQL |
| Statistics, modeling, text processing, or logic that needs loops and tests | Formulas are the wrong shape for the logic | Python with pandas. See [Pandas From Zero](/guides/pandas-from-zero) |
| Data moves between systems on a schedule | You need an automated pipeline, not a workbook | See [ETL and ELT Pipelines](/guides/etl-elt-pipelines) |

A useful test is to ask "who else must trust this?" A workbook built by one person for that person scales fine. A workbook that feeds decisions for twenty people needs a version history, access control, and a way to test changes, which spreadsheets do not give you. That is a judgment call, and where it tips depends on your organization.

> 💡 **Key point**
> You do not need to abandon Excel. Power Query, the Data Model, and the dynamic-array formulas you have learned are the same ideas those tools use: recorded transformations, relationships between tables, and formulas that return tables. Moving on later costs far less because the concepts carry over.

## Your turn: choose the tool

Your monthly sales CSV has grown to 3 million rows. Eight managers want a dashboard they can filter themselves, refreshed every Monday. Which of the following fits best: keep one Excel workbook and email it, load into the Data Model and keep emailing, or publish a Power BI report fed by the same Power Query cleanup?

The third. The rows outgrow a sheet, the audience is wide, and the refresh is recurring, which are exactly the signals for Power BI. The Power Query steps you built carry over, since Power BI uses the same engine.

Check yourself before moving on:

```quiz
[
  {"q": "What is the maximum number of rows on one Excel worksheet?", "choices": ["65,536", "1,000,000", "1,048,576", "16,384"], "answer": 2, "explain": "Current Excel worksheets hold 1,048,576 rows by 16,384 columns. 65,536 was the limit of the very old .xls format, and 16,384 is the column limit."},
  {"q": "You load 3 million rows with Power Query. Where can they go so you can still analyze them in Excel?", "choices": ["Onto a single worksheet, which Excel will extend automatically", "Into the Data Model, which is separate from the worksheet grid", "Into one cell as text", "Nowhere; Excel cannot use more than 1,048,576 rows in any form"], "answer": 1, "explain": "Rows loaded to the Data Model do not occupy worksheet rows, so PivotTables built on the model can work with more rows than a sheet can show. A worksheet itself still stops at 1,048,576 rows."},
  {"q": "A team emails a workbook every Monday and eight managers each edit their own copy, so the numbers disagree. Which fix addresses the actual problem?", "choices": ["Use a bigger monitor", "Convert every formula to LAMBDA", "Move to a shared, scheduled report such as Power BI so there is one trusted version", "Add more conditional formatting"], "answer": 2, "explain": "The problem is delivery and a single source of truth, not formulas or row counts. A published report with scheduled refresh gives one version everyone sees."}
]
```

## Recap

1. A worksheet holds at most 1,048,576 rows and 16,384 columns, and a cell holds at most 32,767 characters.
2. The Data Model (with Power Pivot) stores related tables outside the grid, so it can handle more rows than a sheet. Availability depends on your Office edition.
3. Choose by signal: Power BI for shared, scheduled reports, SQL for data that lives in a database or needs many joins, Python for statistics and logic that does not fit formulas.
4. Pipelines, not workbooks, should move data between systems on a schedule.
5. Power Query, relationships, and table-returning formulas transfer directly to those tools.

You now have the modern Excel toolkit and a sense of its edges. For where the same ideas continue, read [Spreadsheets to SQL to Pipelines](/guides/spreadsheets-to-sql-to-pipelines).