# Advanced Excel: Dynamic Arrays, LET, LAMBDA, and Power Query

> Go beyond lookups and pivots: formulas that spill whole tables (FILTER, SORT, UNIQUE, XMATCH), readable and reusable formulas with LET and LAMBDA, repeatable cleanup with Power Query, guardrails with validation and conditional formatting, and a clear sense of when Excel is the wrong tool.


---

# Advanced Excel: Dynamic Arrays, LET, LAMBDA, and Power Query

You already trust Excel with real work. You write lookups, you build pivot tables, and you know the quiet dread of a report that has to be rebuilt by hand every month because the export changed shape again. This guide is for that moment. Modern Excel has a second layer on top of the grid: formulas that return whole tables, a way to name the pieces of a formula, and a cleanup tool that remembers every step so next month costs one click.

Everything runs on one thread: a messy real-world export that gets cleaned, reshaped, and analysed. You meet the same four sales reps and the same three regions in every phase, so each new tool fixes a problem you have already felt.

## Prerequisite

You should be comfortable with lookups, conditional sums, and pivot tables. If any of that is shaky, start with [Excel From Zero](/guides/excel-from-zero), then [Excel Formulas That Do Real Work](/guides/excel-formulas-that-do-real-work) and [Excel Pivot Tables and Charts](/guides/excel-pivot-tables-and-charts).

Version note, checked against Microsoft's documentation in October 2026: FILTER, SORT, SORTBY, UNIQUE, SEQUENCE, XMATCH, and LET work in Excel for Microsoft 365, Excel 2024, and Excel 2021. LAMBDA, BYROW, and MAP are documented for Microsoft 365 and Excel 2024 (not 2021). Power Query ships as Get & Transform on the Data tab in the same recent versions. Each phase repeats the version note where it matters.

## How to read this

- **Fighting a `#SPILL!` or a stray `@` right now?** Jump to [Phase 1](01-dynamic-arrays-and-spilling.md).
- **Rebuilding the same cleanup every month?** Go straight to [Phase 3](03-power-query-repeatable-cleanup.md).
- **Want it to finally make sense?** Read in order. Each phase builds on the last.

## The phases

1. **[Dynamic Arrays and Spilling](01-dynamic-arrays-and-spilling.md)** - formulas that return whole tables, the spill range, `#SPILL!`, the `#` and `@` operators, and FILTER, SORT, SORTBY, UNIQUE, SEQUENCE, and XMATCH.
2. **[LET and LAMBDA: Readable and Reusable Formulas](02-let-and-lambda.md)** - name the pieces of a formula, then package a formula as your own function.
3. **[Power Query: Repeatable Cleanup](03-power-query-repeatable-cleanup.md)** - import a messy CSV, unpivot it, merge in a lookup table, and refresh with one click.
4. **[Guardrails: Data Validation and Conditional Formatting with Formulas](04-validation-and-conditional-formatting.md)** - stop bad data at the door and make problems visible.
5. **[Knowing When to Leave Excel](05-when-to-leave-excel.md)** - the row limit, the Data Model, and when Power BI, SQL, or Python is the better tool.

> Deferred on purpose: VBA and Office Scripts, Power Pivot measures in depth (see [Power BI DAX Deep Dive](/guides/power-bi-dax-deep-dive) for the language they share), and newer array helpers that are not yet in every version.


---

# Dynamic Arrays and Spilling

For years, one Excel formula meant one answer in one cell. If you wanted a list of every East region order, you reached for a pivot table or a helper column and a filter. Dynamic arrays change the rule: a formula can return many values, and Excel pours them into the cells below and beside it. After this phase you will pull filtered, sorted, de-duplicated tables out of raw data with a single formula that updates itself.

Version note: the functions in this phase work in Excel for Microsoft 365, Excel 2024, and Excel 2021, per Microsoft's documentation.

## The data we will use

Paste this into a sheet at `A1`. It is the cleaned version of the export you will build properly in [Phase 3](03-power-query-repeatable-cleanup.md).

| | A | B | C | D |
|---|---|---|---|---|
| 1 | Region | Rep | Product | Amount |
| 2 | East | Ana | Desk | 400 |
| 3 | West | Ben | Chair | 150 |
| 4 | East | Cy | Chair | 120 |
| 5 | West | Ben | Desk | 380 |
| 6 | East | Ana | Lamp | 60 |
| 7 | North | Dee | Lamp | 95 |
| 8 | East | Ana | Chair | 175 |
| 9 | North | Dee | Desk | 310 |

The amounts add up to 1,690. Keep that number in mind. It is how you will check later results.

## What a spill actually is

A **spill** happens when a formula produces several values and Excel places them in the neighboring cells. The cell where you typed the formula is the anchor. The block of cells the results fill is the **spill range**. Click any cell in it and Excel draws a thin blue border around the whole block.

Type this in `F2` and press Enter (not Ctrl+Shift+Enter):

```excel
=FILTER(A2:D9, A2:A9="East", "none")
```

FILTER keeps the rows of its first argument where the second argument is TRUE. The `A2:A9="East"` test produces eight TRUE or FALSE values, one per row, and FILTER keeps the rows that line up with TRUE. The third argument is what to show if nothing matches.

| F | G | H | I |
|---|---|---|---|
| East | Ana | Desk | 400 |
| East | Cy | Chair | 120 |
| East | Ana | Lamp | 60 |
| East | Ana | Chair | 175 |

*What just happened:* one formula, typed once in `F2`, filled `F2:I5`. Only `F2` contains the formula. The other cells show a ghosted copy in the formula bar, and you can edit only the anchor. Change a value in the source data and the spill redraws itself, growing or shrinking to fit.

> 💡 **Key point**
> Older Ctrl+Shift+Enter array formulas returned a fixed-size block. A dynamic array formula resizes itself every time the data changes.

## When it breaks: #SPILL!

If something sits in the cells the result needs, Excel refuses to overwrite it and shows `#SPILL!`. Excel will not destroy your data to make room.

| Symptom | Calm fix |
|---|---|
| `#SPILL!`, a cell in the spill range has content | Clear or move the obstructing cell. Selecting the error cell shows the intended spill range as a dashed border. |
| `#SPILL!`, merged cells in the range | Unmerge them, or move the formula elsewhere. |
| `#SPILL!`, formula is inside an Excel Table | Spilled formulas are not supported inside Tables. Put the formula on the grid outside the Table. |
| `#SPILL!`, size cannot be determined | The result's size changes between calculation passes, which RAND, RANDARRAY, and RANDBETWEEN cause. Avoid them in sizing arguments. |
| `#SPILL!`, out of memory | Point the formula at a smaller range. |
| `#CALC!` from FILTER | Nothing matched and you gave no third argument. Add one, such as `"none"`. |

Source: Microsoft's article [How to correct a #SPILL! error](https://support.microsoft.com/en-US/Excel/how-to-correct-a-spill-error).

The most common real cause is boring: you typed a formula, then someone typed a note a few rows below it, and the spill hit that note. Selecting the formula cell shows the dashed border, and the error tells you which cell is in the way.

## Referring to a spill: the # operator

A spill range changes size, so you cannot point at it with a fixed range like `F2:I5`. Instead, put `#` after the anchor cell. It means "the whole spill range of the formula in that cell."

| Formula | Result | Why |
|---|---|---|
| `=ROWS(F2#)` | 4 | The East filter spilled four rows. |
| `=SUM(INDEX(F2#,,4))` | 755 | `INDEX` with an empty row argument returns column 4 of the spill: 400 + 120 + 60 + 175. |

If a new East order appears in the data, both formulas change with no editing.

## The @ operator and old files

Before dynamic arrays, Excel silently squashed a range down to one value when a formula expected a single value. This was called **implicit intersection**: for a formula in row 3, `=A1:A10` quietly returned the value from `A3`, the cell in the same row.

Dynamic-array Excel no longer does that silently, because the formula might now be meant to spill. When you open a workbook created in an older version, Excel adds `@` to formulas that could return more than one value, such as `=@INDEX(...)`, so they keep behaving exactly as before. Microsoft's wording is that nothing changes about how your formula behaves; you can now see the previously invisible intersection.

Two practical rules:

- If an old formula shows an `@` you did not type and you want it to spill, delete the `@`.
- If you want a single value from a range or array, type `@` yourself.

Source: Microsoft's article [Implicit intersection operator: @](https://support.microsoft.com/en-us/office/implicit-intersection-operator-ce3be07b-0101-4450-a24e-c1c999be2b34).

## SORT and SORTBY

`SORT` has the signature `SORT(array, [sort_index], [sort_order], [by_col])`. The `sort_index` is a column number inside the array, defaulting to 1. The `sort_order` is `1` for ascending (the default) or `-1` for descending.

Filter East orders, then sort them by amount (column 4), largest first:

```excel
=SORT(FILTER(A2:D9, A2:A9="East", "none"), 4, -1)
```

| Region | Rep | Product | Amount |
|---|---|---|---|
| East | Ana | Desk | 400 |
| East | Ana | Chair | 175 |
| East | Cy | Chair | 120 |
| East | Ana | Lamp | 60 |

`SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2], ...)` sorts by a separate range instead of a column number. Microsoft recommends it for grids because it keeps working if you insert a column, while `SORT` breaks when index numbers shift.

```excel
=SORTBY(A2:D9, D2:D9, -1)
```

| Region | Rep | Product | Amount |
|---|---|---|---|
| East | Ana | Desk | 400 |
| West | Ben | Desk | 380 |
| North | Dee | Desk | 310 |
| East | Ana | Chair | 175 |
| West | Ben | Chair | 150 |
| East | Cy | Chair | 120 |
| North | Dee | Lamp | 95 |
| East | Ana | Lamp | 60 |

You can add tie-breakers: `=SORTBY(A2:D9, A2:A9, 1, D2:D9, -1)` sorts by region A to Z, and within each region by amount high to low. All the arrays must have the same number of rows as the data.

## Combining conditions in FILTER

FILTER's second argument is a TRUE/FALSE array, so you build conditions with arithmetic. Multiply for AND. Add for OR.

```excel
=FILTER(A2:D9, (A2:A9="East")*(D2:D9>100), "none")
```

East AND amount over 100 keeps three orders: Ana Desk 400, Cy Chair 120, and Ana Chair 175. (Ana Lamp 60 is East but too small.)

```excel
=SORT(FILTER(A2:D9, (A2:A9="West")+(A2:A9="North"), "none"), 4, -1)
```

West OR North, sorted by amount descending:

| Region | Rep | Product | Amount |
|---|---|---|---|
| West | Ben | Desk | 380 |
| North | Dee | Desk | 310 |
| West | Ben | Chair | 150 |
| North | Dee | Lamp | 95 |

## UNIQUE

`UNIQUE(array, [by_col], [exactly_once])` returns the distinct rows of a range. `by_col` compares columns instead of rows. `exactly_once` set to TRUE returns only items that occur a single time.

| Formula | Spilled result | Why |
|---|---|---|
| `=UNIQUE(B2:B9)` | Ana, Ben, Cy, Dee | Four distinct reps, in order of first appearance. |
| `=UNIQUE(B2:B9,,TRUE)` | Cy | Ana appears 3 times, Ben 2, Dee 2, Cy once. |
| `=SORT(UNIQUE(C2:C9))` | Chair, Desk, Lamp | Distinct products, A to Z. |

The double comma in the second row skips `by_col` and leaves it at its default of FALSE.

## SEQUENCE

`SEQUENCE(rows, [columns], [start], [step])` generates numbers. Omitted optional arguments default to 1.

| Formula | Spilled result |
|---|---|
| `=SEQUENCE(3)` | 1, 2, 3 down a column |
| `=SEQUENCE(3,1,100,50)` | 100, 150, 200 down a column |
| `=SEQUENCE(2,3)` | Row 1: 1, 2, 3. Row 2: 4, 5, 6. |

Use it for row numbers, invoice-number series, or as the engine inside bigger formulas. For example, `=DATE(2026, SEQUENCE(1,12), 1)` returns the first day of each month of 2026 across twelve columns (format the cells as dates).

## XMATCH

`XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])` returns the position of an item in a range. It improves on `MATCH` with explicit modes:

| match_mode | Meaning |
|---|---|
| 0 (default) | Exact match |
| -1 | Exact match, or the next smaller item |
| 1 | Exact match, or the next larger item |
| 2 | Wildcard match (`*`, `?`, `~` are special) |

| search_mode | Meaning |
|---|---|
| 1 (default) | Search first to last |
| -1 | Search last to first |
| 2 / -2 | Binary search, ascending / descending (the data must already be sorted) |

On the reps in `B2:B9` (Ana, Ben, Cy, Ben, Ana, Dee, Ana, Dee):

| Formula | Result | Why |
|---|---|---|
| `=XMATCH("Ana", B2:B9)` | 1 | The first Ana is the first item. |
| `=XMATCH("Ana", B2:B9, 0, -1)` | 7 | Searching from the bottom finds the last Ana. |
| `=XMATCH("D*", B2:B9, 2)` | 6 | Wildcard match finds the first name starting with D. |
| `=INDEX(D2:D9, XMATCH("Dee", B2:B9, 0, -1))` | 310 | Position 8 is Dee's last order; `INDEX` fetches its amount. |

A classic use of match mode `-1` is bands. Put the thresholds 0, 100, 250, 500 in `F2:F5` (sorted ascending).

| Formula | Result | Why |
|---|---|---|
| `=XMATCH(175, F2:F5, -1)` | 2 | 175 is not listed, so the next smaller item, 100, is used: position 2. |
| `=XMATCH(60, F2:F5, -1)` | 1 | The next smaller item is 0. |
| `=XMATCH(400, F2:F5, -1)` | 3 | The next smaller item is 250. |

Feed that position into `INDEX` over a list of labels to turn amounts into "Small", "Medium", "Large".

## Your turn: spot the spill problem

You type `=UNIQUE(B2:B9)` in `K2`. It returns `#SPILL!`. You select `K2` and see a dashed border running from `K2` to `K5`. Cell `K4` holds the text "check later". What do you do, and what should the spill show once fixed?

Clear or move the contents of `K4`. The formula then returns Ana, Ben, Cy, and Dee in `K2:K5`.

Check yourself before moving on:

```quiz
[
  {"q": "A FILTER formula returns #CALC! when nothing matches. What is the standard fix?", "choices": ["Wrap the whole data range in an Excel Table", "Supply the third argument, if_empty, such as \"none\"", "Press Ctrl+Shift+Enter to enter it as an array formula", "Add the @ operator before the range"], "answer": 1, "explain": "FILTER returns #CALC! when it finds no rows and no if_empty value was given. The third argument gives it a fallback to display."},
  {"q": "A FILTER formula in F2 currently spills into F2:I5. Which formula always refers to the whole spilled result, even if it later grows to 10 rows?", "choices": ["F2:I5", "F2#", "@F2", "F2:F2"], "answer": 1, "explain": "The # spill-reference operator after the anchor cell means the entire current spill range. A fixed range like F2:I5 would not grow."},
  {"q": "Using the sample data, what does =UNIQUE(B2:B9,,TRUE) return?", "choices": ["Ana, Ben, Cy, Dee", "Cy", "Ana", "Ben, Dee"], "answer": 1, "explain": "The third argument exactly_once returns only values appearing a single time. Ana appears 3 times, Ben 2, Dee 2, and Cy once."}
]
```

## Recap

1. A dynamic array formula returns many values and spills them into a spill range anchored at the cell you typed.
2. `#SPILL!` means something is in the way: non-empty or merged cells, a Table, an unsizable result, or too much data. Excel never overwrites your data.
3. `F2#` refers to the whole spill range, and an `@` in an old formula marks implicit intersection that Excel used to apply silently.
4. FILTER takes conditions you combine with `*` (AND) and `+` (OR), and needs `if_empty` to avoid `#CALC!`.
5. SORT sorts by a column number, SORTBY by a separate range. UNIQUE de-duplicates, SEQUENCE generates numbers, XMATCH finds positions with explicit match and search modes.

Next up, [LET and LAMBDA](02-let-and-lambda.md): once formulas get this long, you need to name the pieces.


---

# LET and LAMBDA: Readable and Reusable Formulas

Once you stack FILTER inside SORT inside INDEX, a formula stops being readable. Three weeks later you open it and cannot tell which `D2:D9` is which. Worse, the same chunk appears three times, so Excel works it out three times and one fix has to be made in three places. LET gives the pieces names. LAMBDA lets you give the whole formula a name and call it like `SUM`.

Version note, per Microsoft's documentation: LET works in Microsoft 365, Excel 2024, and Excel 2021. LAMBDA, BYROW, and MAP are documented for Microsoft 365 and Excel 2024, not for Excel 2021. If you share a workbook, the other person's version decides whether your LAMBDA works.

## LET: variables inside a formula

`LET(name1, value1, [name2, value2, ...], calculation)` assigns names to values, then evaluates a final calculation that uses them. The names exist only inside that one formula. Think of it as a scratch pad written at the top of the formula.

Rules from Microsoft: the last argument must be the calculation that returns the result, you can define up to 126 name and value pairs, and names must start with a letter and cannot look like a cell reference (so `x1` is out, `east` is fine).

Using the sales data from [Phase 1](01-dynamic-arrays-and-spilling.md) (`A1:D9`, total 1,690), the East share of all sales:

```excel
=LET(
    east,  SUMIFS(D2:D9, A2:A9, "East"),
    total, SUM(D2:D9),
    east / total
)
```

| Step | Value |
|---|---|
| `east` | 400 + 120 + 60 + 175 = 755 |
| `total` | 1,690 |
| result | 755 / 1,690 = 0.4467, shown as 44.7% once formatted as a percentage |

Line breaks and spaces inside a formula are allowed: press Alt+Enter in the formula bar to add a line break. Formatting like this is half the benefit.

The other half is speed and safety. Microsoft notes that a repeated expression is calculated each time it appears, while a LET name is calculated once and reused. Change the threshold in one place and every use updates.

## LET with arrays

A name can hold a whole array. Task: list the reps whose total sales are at least 500, biggest first.

```excel
=LET(
    reps,   UNIQUE(B2:B9),
    totals, SUMIF(B2:B9, reps, D2:D9),
    keep,   totals >= 500,
    SORTBY(FILTER(reps, keep), FILTER(totals, keep), -1)
)
```

Walk through it by hand:

| Name | Value |
|---|---|
| `reps` | Ana, Ben, Cy, Dee |
| `totals` | 635, 530, 120, 405 (Ana: 400 + 60 + 175, Ben: 150 + 380, Cy: 120, Dee: 95 + 310) |
| `keep` | TRUE, TRUE, FALSE, FALSE |

`FILTER(reps, keep)` gives Ana and Ben, `FILTER(totals, keep)` gives 635 and 530, and `SORTBY(..., -1)` orders by those totals descending. Spilled result: **Ana, Ben**.

Without LET you would write `UNIQUE(B2:B9)` and `SUMIF(...)` twice each, nested six deep. With LET, each line reads like a step in a recipe.

## LAMBDA: your own function

`LAMBDA([parameter1, parameter2, ...], calculation)` creates a function with parameters. You can test one directly by appending arguments in parentheses:

```excel
=LAMBDA(number, number + 1)(1)
```

That returns 2. Microsoft documents this call-in-place pattern for testing.

The gotcha everybody hits: put a bare `=LAMBDA(number, number + 1)` in a cell without calling it and Excel returns `#CALC!`. A LAMBDA has to be called, either with parentheses and arguments as above or by a name.

Limits from Microsoft: up to 253 parameters, and a parameter name follows normal name rules with one exception, no period.

## Name it in Name Manager

The real power is saving a LAMBDA under a name, so the whole workbook can call it.

1. On the Formulas tab, select **Name Manager**, then **New**.
2. **Name**: `SharePct`.
3. **Scope**: Workbook.
4. **Comment**: up to 255 characters. Say what it does and what the argument means: `Share of all sales for one region. Argument: region name`. The comment shows as a tooltip when someone calls it.
5. **Refers to**:

```excel
=LAMBDA(region, SUMIFS($D$2:$D$9, $A$2:$A$9, region) / SUM($D$2:$D$9))
```

6. Select **OK**.

Now it behaves like a built-in function:

| Formula | Result |
|---|---|
| `=SharePct("East")` | 755 / 1,690 = 44.7% |
| `=SharePct("West")` | 530 / 1,690 = 31.4% |
| `=SharePct("North")` | 405 / 1,690 = 24.0% |

(West is 150 + 380, North is 95 + 310.)

If the share definition ever changes, you edit one name in Name Manager and every cell that calls `SharePct` updates. That is the case for LAMBDA: one definition, many callers, no copy-paste drift.

You can also keep a LAMBDA private to one formula by defining it inside LET and calling it by that local name, for example `=LET(double, LAMBDA(x, x * 2), double(5))`, which returns 10.

> ⚠️ **Gotcha**
> A named LAMBDA is only as portable as the Excel version of whoever opens the file. Nothing you write with LAMBDA can be used in versions that do not include it. For anything you send to many people, check their versions first, or keep the logic in plain formulas.

## MAP and BYROW: apply a LAMBDA across a list

On their own, LAMBDAs work on one thing at a time. Two helpers apply them across arrays.

`MAP(array1, lambda)` calls the LAMBDA once per element and returns an array of the same shape. With more than one array, the LAMBDA needs one parameter per array, and the LAMBDA must be the last argument.

```excel
=MAP(UNIQUE(A2:A9), LAMBDA(r, SUMIFS(D2:D9, A2:A9, r)))
```

`UNIQUE(A2:A9)` is East, West, North (order of first appearance), so the spill is the region totals: **755, 530, 405**.

`BYROW(array, lambda(row))` passes each row of an array to the LAMBDA as a single parameter, which must return one value, and returns one result per row. Say `G2:I5` holds the monthly amounts per rep (rows Ana, Ben, Cy, Dee; columns Jan, Feb, Mar), the same layout as the export you clean in the next phase:

| | Jan | Feb | Mar |
|---|---|---|---|
| Ana | 400 | 380 | 0 |
| Ben | 150 | 0 | 210 |
| Cy | 120 | 90 | 60 |
| Dee | 95 | 310 | 175 |

```excel
=BYROW(G2:I5, LAMBDA(r, SUM(r)))
```

Result: **780, 360, 270, 580**, one total per rep. They add to 1,990, the grand total of the grid. If the LAMBDA returns more than one value, BYROW returns `#CALC!`.

When a single built-in function already does the job, use it. MAP and BYROW earn their place when the per-item logic is custom.

## Your turn: name the pain

You find `=SUMIFS(D2:D9,A2:A9,"East")/SUM(D2:D9)` pasted into forty cells, and the boss wants the denominator to exclude Dee's orders. Which tool fits: LET inside each cell, or a named LAMBDA? A named LAMBDA, because there is one definition to fix instead of forty copies. LET alone would still leave forty formulas to edit.

Check yourself before moving on:

```quiz
[
  {"q": "You type =LAMBDA(x, x+x) into a cell and press Enter. What do you get, and why?", "choices": ["20, because x defaults to 10", "#CALC!, because the LAMBDA was created but never called", "#NAME?, because LAMBDA must be saved first", "0, because no argument was supplied"], "answer": 1, "explain": "Microsoft documents #CALC! for a LAMBDA created in a cell without being called. Call it as =LAMBDA(x, x+x)(5), or save it as a name and call that."},
  {"q": "What is one real benefit of LET besides readability?", "choices": ["It lets formulas run in Excel versions older than 2021", "A named value is calculated once and reused instead of being recomputed each time it appears", "It converts formulas to values permanently", "It removes the 8,192-character formula limit"], "answer": 1, "explain": "LET calculates a named expression once and reuses it. It also makes the formula shorter and easier to read, but it does not change version support or the formula length limit."},
  {"q": "In Name Manager, which field holds the LAMBDA definition itself?", "choices": ["Name", "Scope", "Comment", "Refers to"], "answer": 3, "explain": "Name is the function name, Scope is workbook or sheet, Comment is the tooltip description (up to 255 characters), and Refers to holds the =LAMBDA(...) definition."}
]
```

## Recap

1. LET names values and arrays inside one formula, calculates each once, and ends with the calculation that returns the result.
2. LAMBDA turns a calculation with parameters into a function. Test it by calling it in place with parentheses, because an uncalled LAMBDA returns `#CALC!`.
3. Save a LAMBDA under a name through Formulas, Name Manager, New, with the definition in Refers to, and edit it in one place to change every caller.
4. MAP applies a LAMBDA to each element of an array, and BYROW applies one to each row, returning one value per row.
5. LAMBDA, MAP, and BYROW need Microsoft 365 or Excel 2024, so check who will open the file.

Next up, [Power Query: Repeatable Cleanup](03-power-query-repeatable-cleanup.md): formulas analyse data, but a messy export needs reshaping first.


---

# Power Query: Repeatable Cleanup

Every month the export arrives looking slightly different from what your formulas expect: junk rows on top, a stray space in "East ", months spread across columns instead of rows. You fix it by hand, it takes an hour, and next month you do it again from memory. Power Query turns that hour into a recorded recipe you replay with one click. After this phase you can import a messy file, reshape it, join it to a lookup table, and refresh the whole thing the day the next export lands.

Version note: Power Query appears as Get & Transform on the Data tab in Excel for Microsoft 365, Excel 2024, and Excel 2021 on Windows. Microsoft says Excel for Microsoft 365 for Mac offers some support, so menus and connectors can differ there.

## The mental model: a recorded recipe, not an edited sheet

Do not think of Power Query as editing a spreadsheet. Think of it as writing a recipe that Excel follows each time. You click through a sample of the data and Excel records each transformation as a **step**. The data source stays untouched: Power Query reads it, applies the steps, and delivers a clean table. Microsoft's own description is that it defines a repeatable process you can refresh later, and records transformations as query steps rather than modifying the source.

```mermaid
flowchart LR
  A["Messy CSV"] --> B["Power Query steps"]
  C["Reps table"] --> B
  B --> D["Clean table in Excel"]
  D --> E["Pivot, charts, formulas"]
```

Two pieces to know:

- The **Power Query Editor** is the window where you click transformations and see a preview. Its **Query Settings** pane on the right lists the **Applied Steps**. Click any step to see the data as it looked at that moment. If the pane is closed, open it from the View tab, then Query Settings.
- Behind every click, Power Query writes code in a language called **M**. You rarely need to touch it, but reading it makes the recipe concrete, as you will see below.

## The messy export

Your system exports `sales_export.csv`. This is what it contains, as plain text:

```text
Quarterly order export - generated 2026-09-30,,,,
,,,,
Region,Rep,Jan,Feb,Mar
East ,Ana,400,380,
east,Ben,150,,210
West,Cy,120,90,60
West,Dee ,95,310,175
Total,,765,780,445
```

Count the problems: two junk rows above the real header, a total row at the bottom that is not an order, inconsistent text ("East " with a trailing space, "east" in lowercase, "Dee " with a trailing space), blank cells where there were no sales, and months laid out across columns, which pivot tables and formulas dislike.

## Step 1: import it

On the Data tab, choose **Get Data**, then **From File**, then **From Text/CSV**, and pick the file. A preview window opens. Choose **Transform Data** (rather than Load) to open the Power Query Editor.

Power Query may add steps on its own for text and CSV files, typically promoting the first row to headers and guessing column types from the first 200 rows. Here that guess is wrong, because the first row is a title. In the Applied Steps list, delete any auto-added "Promoted Headers" and "Changed Type" steps by selecting the step and clicking the X beside it. You will do them properly.

## Step 2: clean it, one recorded step at a time

| # | Command (Power Query Editor) | What it does | Rows left |
|---|---|---|---|
| 1 | Home, Remove Rows, Remove Top Rows, enter 2 | Drops the title row and the empty comma row | 6 (header + 4 orders + Total) |
| 2 | Home, Use First Row as Headers | The row with Region, Rep, Jan, Feb, Mar becomes the header | 5 |
| 3 | Home, Remove Rows, Remove Bottom Rows, enter 1 | Drops the Total row | 4 |
| 4 | Select Region and Rep, then Transform, Format, Trim | Removes leading and trailing spaces ("East " becomes "East", "Dee " becomes "Dee") | 4 |
| 5 | Select Region, then Transform, Format, Capitalize Each Word | "east" becomes "East" | 4 |
| 6 | Select Jan, Feb, Mar, then Home, Data Type, Whole Number | Columns become numbers. Empty cells show as `null`. | 4 |
| 7 | Select Jan, Feb, Mar, then Transform, Replace Values, find `null`, replace with `0` | A blank sale becomes an explicit zero | 4 |

Check your work as you go by clicking each step in the Applied Steps list and looking at the preview. After step 7 the table is:

| Region | Rep | Jan | Feb | Mar |
|---|---|---|---|---|
| East | Ana | 400 | 380 | 0 |
| East | Ben | 150 | 0 | 210 |
| West | Cy | 120 | 90 | 60 |
| West | Dee | 95 | 310 | 175 |

> 🪖 **War story**
> The Total row is a trap for pivot tables: leave it in and every figure is double-counted, with no error to warn you. Always remove totals and notes from data before analysing it. Later, you will check that the cleaned numbers add up to the same totals the export claimed.

If a CSV from another region has dates like 22/09/2026 and Power Query reads them with US month-first rules, the conversion errors. Microsoft's fix is to right-click the column, choose Change Type, then **Using Locale**, and pick the locale the file was written in.

## Step 3: unpivot

The table is **wide**: one column per month. Analysis wants it **long**: one row per rep, month, and amount, so adding April means adding rows, not a new column.

Select the Region and Rep columns, then choose Transform, **Unpivot Columns** dropdown, **Unpivot Other Columns**. Power Query turns the headers into values under a column named **Attribute** and the cell values into a column named **Value**. Rename them to Month and Amount by double-clicking the headers.

Microsoft lists three variants:

| Command | Unpivots | Picks up a new column added later? |
|---|---|---|
| Unpivot Columns | The columns you selected | Yes. Power Query internally builds this as Unpivot Other Columns |
| Unpivot Other Columns | Every column except the ones you selected | Yes, which is why you choose it when months will be added |
| Unpivot Only Selected Columns | Only the columns you selected | No, new columns stay as they are, which is what Microsoft says it is for |

Result: 4 reps times 3 months is 12 rows.

| Region | Rep | Month | Amount |
|---|---|---|---|
| East | Ana | Jan | 400 |
| East | Ana | Feb | 380 |
| East | Ana | Mar | 0 |
| East | Ben | Jan | 150 |
| East | Ben | Feb | 0 |
| East | Ben | Mar | 210 |
| West | Cy | Jan | 120 |
| West | Cy | Feb | 90 |
| West | Cy | Mar | 60 |
| West | Dee | Jan | 95 |
| West | Dee | Feb | 310 |
| West | Dee | Mar | 175 |

Reconcile it: the Amount column sums to 400+380+0+150+0+210+120+90+60+95+310+175 = 1,990. The export's Total row said 765 + 780 + 445 = 1,990. They match, so nothing was lost.

## Step 4: merge in a lookup table

Reps belong to teams, but that lives in another table. On a separate sheet, list it and use Data, **From Table/Range** to load it as a second query named `Reps`:

| Rep | Team |
|---|---|
| Ana | Alpha |
| Ben | Alpha |
| Cy | Beta |
| Dee | Beta |
| Eli | Beta |

Back in the sales query, choose Home, **Merge Queries**. Pick the sales query as the first (left) table and `Reps` as the second (right), click the `Rep` column in each, and choose a join kind. Left outer keeps every row of the left table and brings in matches from the right. Microsoft's join kinds are:

| Join kind | Keeps |
|---|---|
| Left outer | All rows from the left table, matching rows from the right |
| Right outer | All rows from the right table, matching rows from the left |
| Full outer | All rows from both tables |
| Inner | Only rows that match in both |
| Left anti | Only left rows with no match on the right |
| Right anti | Only right rows with no match on the left |

The merge adds a column named `Reps` holding a nested table per row. Click the expand icon in its header and tick only `Team`. Choose left outer here: all 12 rows stay, each gaining a Team. Merged on `Rep`, the Alpha rows are Ana's and Ben's six rows, and Beta's are Cy's and Dee's six.

The dialog shows how many rows matched before you confirm, so read it. The columns you join on must have the same data type, and text comparison is exact (spaces and capitalization both count), so "Dee " with a trailing space would not have matched "Dee", and neither would "dee". That is why the Trim step came first.

Switch the join kind and the same two tables answer a different question. Reps as the left table and sales as the right with **Left anti** returns only reps with no orders: **Eli**. This is a clean way to find the gaps.

A team summary from the merged table, via a pivot table or Group By, is Alpha = 400+380+0+150+0+210 = 1,140 and Beta = 120+90+60+95+310+175 = 850. They add to 1,990 again.

## What the clicks recorded

Open Home, **Advanced Editor** to read the M that Power Query wrote. It looks roughly like this (step names in your file will be the longer names the interface generates, such as "Removed Top Rows"):

```powerquery
let
    Source = Csv.Document(File.Contents("C:\data\sales_export.csv"),
        [Delimiter=",", Columns=5, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    NoTitle   = Table.Skip(Source, 2),
    Promoted  = Table.PromoteHeaders(NoTitle, [PromoteAllScalars=true]),
    NoTotal   = Table.RemoveLastN(Promoted, 1),
    Trimmed   = Table.TransformColumns(NoTotal,
        {{"Region", each Text.Proper(Text.Trim(_)), type text},
         {"Rep", Text.Trim, type text}}),
    Typed     = Table.TransformColumnTypes(Trimmed,
        {{"Jan", Int64.Type}, {"Feb", Int64.Type}, {"Mar", Int64.Type}}),
    NoBlanks  = Table.ReplaceValue(Typed, null, 0, Replacer.ReplaceValue,
        {"Jan", "Feb", "Mar"}),
    Long      = Table.UnpivotOtherColumns(NoBlanks, {"Region", "Rep"}, "Month", "Amount")
in
    Long
```

Each line is one step, and each step takes the previous step's result as input. That is the whole trick: a pipeline of small, named transformations. Power Query is the same idea as the extract-transform-load work described in [ETL and ELT Pipelines](/guides/etl-elt-pipelines), shrunk to fit inside a workbook.

## Load and refresh

Choose Home, **Close & Load** to send the result to a worksheet as a Table. The **Close & Load To** option lets you load as a connection only, which is the right choice for a query that only feeds another one (such as `Reps`), or add the data to the Data Model (see [Phase 5](05-when-to-leave-excel.md)).

Next month, replace `sales_export.csv` with the new file at the same path, then on the Data tab choose **Refresh All**. Every step replays. Because you used Unpivot Other Columns, the unpivot step itself adapts when the export gains an Apr column. The earlier steps do not: the Source step records `Columns=5`, and the type and null-replacement steps name only Jan, Feb, and Mar, so a new month means editing those steps too.

Power Query can load up to 1,048,576 rows to a worksheet, the sheet's own limit.

Recipes break when their assumptions break:

| Symptom | Cause | Fix |
|---|---|---|
| Refresh fails at Source | The file moved or was renamed | Edit the Source step's file path |
| A step shows an error about a column | A header was renamed in the new export | Edit the step to use the new name |
| Rows missing at the top or bottom | The export added or removed junk rows | Adjust the Remove Top and Bottom Rows counts |
| Error cells in a number column | A text value such as "n/a" appears where a number should be | Replace or filter those values with a step before the type change |

> ⚠️ **Gotcha**
> Remove Top Rows with a fixed count is the most fragile step in this recipe. A more robust recipe filters by content, for example removing every row where Region is "Total" or empty, but that depends on what your export does, so test it against two or three months of real files.

## Your turn: pick the join

You have an Orders query and a Customers query. Management asks for "customers who have never ordered." Which join kind, and which table goes on the left? Customers on the left, Orders on the right, **Left anti**: it returns only left rows with no match on the right.

Check yourself before moving on:

```quiz
[
  {"q": "What does Power Query do to your source CSV file when you clean the data?", "choices": ["It overwrites the file with the cleaned version", "Nothing; it records steps and applies them to the data it reads, leaving the source untouched", "It converts the file to .xlsx", "It deletes rows it removes from the CSV"], "answer": 1, "explain": "Power Query reads the source and applies recorded steps. The steps are replayed on each refresh, and the original file is not modified."},
  {"q": "The export will gain a new month column every quarter. Which unpivot command picks up new columns automatically while keeping Region and Rep fixed?", "choices": ["Unpivot Only Selected Columns, selecting the month columns", "Unpivot Other Columns, selecting Region and Rep", "Remove Columns", "Transpose"], "answer": 1, "explain": "Unpivot Other Columns unpivots everything except the columns you selected, so a new column added in the source is unpivoted on the next refresh. Unpivot Only Selected Columns leaves new columns alone."},
  {"q": "A left outer merge returns null Team values for a rep who definitely exists in the lookup table. What is the most likely cause?", "choices": ["Power Query cannot join on text columns", "The left table must be smaller than the right table", "The key values differ slightly, such as a trailing space, because matching is exact", "Left outer joins only keep the first row"], "answer": 2, "explain": "Text matching is exact, so Dee with a trailing space does not match Dee. Trim the key columns before merging, and read the match count in the Merge dialog."}
]
```

## Recap

1. Power Query records your cleanup as ordered steps, leaves the source untouched, and replays the steps on every refresh.
2. Open it with Data, Get Data, From File, From Text/CSV, then Transform Data. The Applied Steps list on the right shows and lets you edit every step.
3. Remove junk rows, trim and fix text, set data types, and handle blanks before you reshape.
4. Unpivot Other Columns turns wide months into long rows, and keeps working when new columns appear.
5. Merge Queries joins tables on key columns. The join kind decides which rows survive, and left anti finds what is missing.
6. Refresh All replays everything, so fragile steps such as fixed row counts are the first thing to check when a refresh fails.

Next up, [Guardrails: Data Validation and Conditional Formatting with Formulas](04-validation-and-conditional-formatting.md): cleaning is easier when bad data never gets in.


---

# Guardrails: Data Validation and Conditional Formatting with Formulas

Cleaning a messy export is one fight. Stopping the mess from getting into your own workbook in the first place is a better one. Data Validation refuses bad entries as they are typed. Conditional Formatting colors the cells that look wrong so nobody misses them. Both can be driven by a formula, which turns them from toys into real guardrails.

## One rule for both: write the formula for the first cell

Both tools take a formula that returns TRUE or FALSE, and you write it as if for the **active cell**: the first cell of your selection. Excel then applies the same formula to every other selected cell, shifting the references as it goes, the way copying a formula does. Microsoft's example: if you select B2 through B10, B2 is the active cell, and you write the formula for B2.

So the dollar signs decide everything:

| Reference | Meaning when applied across the selection |
|---|---|
| `D2` | Shifts in both directions: each cell tests a different cell |
| `$D2` | Column stays D, row follows each cell: each row tests its own D |
| `$D$2` | Always tests D2: all or nothing |
| `$D$2:$D$9` | A fixed range, used as the thing counted or searched |

Keep this table in mind. Nearly every "my conditional formatting is wrong" question is a missing or extra dollar sign. (Mixed references are taught in [Excel From Zero](/guides/excel-from-zero).)

## Data Validation with a custom formula

Select the cells to protect, then on the Data tab choose **Data Validation**. The dialog has three tabs: Settings, Input Message, and Error Alert. On Settings, the Allow list includes Whole Number, Decimal, List, Date, Time, Text Length, and **Custom**. Choose Custom and type a formula that must return TRUE for an entry to be accepted.

**Example 1: amounts must be positive numbers.** Select `D2:D100` (active cell `D2`) and enter:

```excel
=AND(ISNUMBER(D2), D2>0)
```

| Entry | `ISNUMBER(D2)` | `D2>0` | Result |
|---|---|---|---|
| 400 | TRUE | TRUE | Accepted |
| -5 | TRUE | FALSE | Rejected |
| abc | FALSE | TRUE (text compares greater than any number) | Rejected, because `AND` needs both |

**Example 2: no duplicate order IDs.** Say the IDs are in `A2:A100`, with 1001, 1002, and 1003 already there. Select `A2:A100` (active cell `A2`) and enter:

```excel
=COUNTIF($A$2:$A$100, A2)=1
```

You type 1002 into a new row. `COUNTIF` now counts two 1002s (the old one plus the one you are entering), so the test is `2=1`, which is FALSE, and the entry is rejected. A fresh ID such as 1004 counts once and passes.

**Example 3: a dropdown.** Choose Allow, **List**, and in Source type `East,West,North`, or point at a range by starting with an equals sign, such as `=$J$2:$J$5`. Without the `=`, Excel treats the text as a literal item. The cell now offers those choices.

### Choose the strength of the alert

On the Error Alert tab, pick a **Style**:

| Style | Behavior |
|---|---|
| Stop | Blocks the entry. The user can Retry or Cancel. |
| Warning | Warns, but the user may accept the invalid entry. |
| Information | Informs, and the user may accept or reject. |

Write a message that says how to fix it, such as "Order IDs must be unique. Check column A for this number." Use Stop for rules that must never break, and Warning where an exception can be legitimate.

> ⚠️ **Gotcha**
> Validation checks what people type. Pasting a value into a validated cell is not checked, and pasting cells copied from elsewhere can replace the validation rule itself. Rows loaded by a Power Query refresh never pass through it either. Test your own sheet: paste a bad value in and see. Treat validation as a typing guardrail, and use **Data Validation, Circle Invalid Data** on the Data tab to circle entries that already break the rules.

## Conditional Formatting with a formula

Select the range, then Home, **Conditional Formatting**, **New Rule**, **Use a formula to determine which cells to format**. Enter a formula that returns TRUE for the cells to format, choose **Format**, and select a fill or font.

**Example 1: highlight the whole row when the amount is over 300.** On the Phase 1 data, select `A2:D9` (active cell `A2`) and enter:

```excel
=$D2>300
```

The `$D` pins the test to column D. The row number is free, so each row tests its own amount. Rows 2 (400), 5 (380), and 9 (310) light up across all four columns: Ana's Desk, Ben's Desk, and Dee's Desk. Rows 3, 4, 6, 7, and 8 stay plain.

What goes wrong without the `$`: with `=D2>300` applied across `A2:D9`, each column tests a different cell, so the formatting scatters. With `=$D$2>300` every cell tests only D2, so everything lights up or nothing does.

**Example 2: flag repeated reps.** Select `B2:B9` (active cell `B2`) and enter:

```excel
=COUNTIF($B$2:$B$9, $B2)>1
```

The fixed range is what is searched. `$B2` is each cell's own name. Ana appears 3 times, Ben 2, Dee 2, Cy 1, so every cell except Cy's is highlighted. That pattern is how you spot duplicates in a column of IDs.

**Example 3: dates.** `=$E2<TODAY()` applied across a row flags anything whose due date in column E has passed, and `=$E2>TODAY()` flags dates in the future.

**Example 4: banding.** `=MOD(ROW(),2)=0` shades every second row, whatever filters or sorting you apply.

Manage rules from Home, Conditional Formatting, **Manage Rules**, where the order of rules matters and you can see which range each applies to.

## Your turn: fix the highlighter

A colleague selected `A9:D2` from the bottom up, so the active cell is `A9`, and wrote `=$D2>300`. The wrong rows are highlighting. Why, and how do you fix it?

The formula is relative to the active cell, which is `A9`, but it was written for row 2. Excel shifts the row reference accordingly, so each cell tests the amount seven rows above its own instead of its own. Delete the rule, select from `A2` down to `D9` so the active cell is `A2`, and recreate it.

Check yourself before moving on:

```quiz
[
  {"q": "You select A2:D9 with A2 active and want each row highlighted when its own Amount (column D) is over 300. Which formula is right?", "choices": ["=D2>300", "=$D$2>300", "=$D2>300", "=D$2>300"], "answer": 2, "explain": "The $ before D pins the column to D, while the row stays relative so each row tests its own amount. =D2>300 shifts across columns, and =$D$2>300 tests only D2 for every cell."},
  {"q": "A validation rule uses Custom with =COUNTIF($A$2:$A$100, A2)=1. The sheet already holds order ID 1002 and you type 1002 again. What happens?", "choices": ["It is accepted, because COUNTIF counts only existing cells", "It is rejected, because COUNTIF now finds two 1002s so the test is 2=1, which is FALSE", "It is accepted with a warning only", "It is rejected, because COUNTIF cannot count text"], "answer": 1, "explain": "The cell being entered counts too. Two matches make the test 2=1, which is FALSE, so a Stop alert rejects the entry."},
  {"q": "Which error alert style lets the user override the validation and keep the entry?", "choices": ["Stop", "Warning", "None of them; validation always blocks", "Only a macro can override"], "answer": 1, "explain": "Stop blocks. Warning and Information both allow the user to accept the entry. Warning asks whether to continue anyway."}
]
```

## Recap

1. Write the formula for the active cell, the first cell of the selection. Excel shifts references for the rest.
2. Dollar signs decide what each cell tests: `$D2` follows the row, `$D$2` is fixed, and `D2` shifts both ways.
3. Data Validation, Custom, accepts an entry only when the formula returns TRUE. Stop, Warning, and Information set how strict the alert is.
4. Conditional Formatting, Use a formula, colors cells where the formula returns TRUE. `=$D2>300` highlights whole rows.
5. Validation guards typing, not pasting or refreshing, so pair it with Circle Invalid Data and with formatting that makes problems visible.

Next up, [Knowing When to Leave Excel](05-when-to-leave-excel.md): the limits of the grid and the tools beyond it.


---

# 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).