# How to use spill formulas in Excel on Mac: steps and fixes for Microsoft 365

> Learn how to use spill formulas in Excel on Mac: enter one formula, reference whole spills with #, and fix spill errors step by step.

URL: https://www.lazur.app/how-to/use-spill-formulas-in-excel
Updated: 2026-11-09

To use a spill formula in Excel on Mac, type one formula in the top-left cell of the area you want, then press Return. Excel fills the cells below and to the right by itself, and those cells must be empty or Excel shows a #SPILL error.

## Quick answer

- Type the formula in the top-left cell, press Return, and Excel fills the cells below and to the right.
- Add # to the anchor cell, like =SUM(A2#), to reference the whole spill.
- Put @ in front of a range when you want one value per row instead of a spill.
- If you see a #SPILL error, click the error-checking alert, choose Select Obstructing Cells, then clear the blocking entries.
- Spill formulas do not work inside Excel Tables, so convert the table to a range first.

Applies to: Excel for Microsoft 365, Excel 2024 and Excel 2021 for Mac. Time: 15 min. Difficulty: Medium.

## What you need

- Excel for Microsoft 365, Excel 2024 or Excel 2021 for Mac
- A worksheet with a list of values, for example in column A
- Empty cells below and to the right of the formula's first cell
- Excel for the web is not confirmed for spill support

## How to Use spill formulas in Excel on Mac in Excel on a Mac

1. **Check the cells the spill needs.** Choose the top-left cell of the result area. This is the anchor. Look at the cells below and to the right, and confirm they are empty. A spill cannot land on a cell that already holds a value.
2. **Type a formula that returns several values.** Shortcut: Return. Click the anchor cell and type =SEQUENCE(10). This formula returns the numbers 1 to 10, so it fills ten cells. Any formula that returns more than one value works the same way.
3. **Press Return to commit.** Shortcut: Return. Press Return. Excel fills the cells below and to the right of the anchor. The filled area is the spill range, and only the anchor holds the formula. Tip: If you see a spill error instead of numbers, skip to the error steps below.
4. **Reference the whole spill with #.** Click a free cell, such as C2. Type =SUM(, click A2, type #, then close the bracket. The formula reads =SUM(A2#) and returns 55 for the ten-number example. Tip: Type # with Shift and 3 on a US keyboard.
5. **Use @ for one value per row.** Type a formula with @ in front of the range, such as =VLOOKUP(@A:A,A:C,2,FALSE). Press Return, then copy the formula down the column. Each row gets one value, and nothing spills.
6. **Open the error-checking alert.** If a cell shows a #SPILL error, click that cell. Look for the error-checking alert icon next to it, and click the icon to open the menu.
7. **Select the obstructing cells.** (Error-checking alert > Select Obstructing Cells) Choose Select Obstructing Cells from the alert menu. Excel highlights the entries blocking the spill. Delete or move those entries. The spill fills in once the range is clear. Tip: Microsoft's pages use this wording. I could not confirm it on a Mac screen, so look for the same words.
8. **Convert a table to a range.** (Table Design > Tools > Convert to Range) If the formula sits inside an Excel Table, click inside the table. Open the Table Design tab and choose Convert to Range in the Tools group. Then move the spill formula into the new range. Tip: These labels come from Microsoft's Windows-style path. Check the ribbon on your Mac if a name differs.
9. **Test the spill after an edit.** Change one value in your source list, such as the first number in column A. The spill should update on its own. If the spill error returns, repeat the alert steps.
10. **Keep both workbooks open for cross-file spills.** If the spill reads from another workbook, open both files before you recalculate. Cross-workbook spills need both files open, or the reference can return #REF error values.

## What a spill formula does

A spill formula returns more than one value. Instead of filling one cell, it spills its results into the cells around it. You write the formula once, and the result area updates by itself when your data changes.

Excel calls that filled area the spill range. The cell holding the formula is the anchor. Every spill formula has one anchor, and the spill grows out from it.

Microsoft calls this behavior dynamic arrays. Its support pages list it for Excel for Microsoft 365 for Mac, Excel 2024 for Mac and Excel 2021 for Mac. Older Mac versions needed a legacy array formula, entered with a special key combination, or a formula copied down a column.

![Person at a desk working on a spreadsheet, the kind of Excel on Mac work where spill formulas save time](https://images.lazur.app/content-images/howto/use-spill-formulas-in-excel/how-to-use-spill-formulas-in-excel-1-92822e.jpg)

## Which Mac versions support spill formulas

| Version | Spill support | What the source says |
| --- | --- | --- |
| Microsoft 365 for Mac | Yes | Listed in Microsoft's dynamic array article |
| Excel 2024 for Mac | Yes | Listed in the same article |
| Excel 2021 for Mac | Yes | Listed in the same article |
| Excel 2019 and 2016 | Verify first | Listed there too, but other sources say 2021 or Microsoft 365 is required |
| Excel for the web | Not confirmed | Not in the article's Applies To list |

_Spill support by version, from Microsoft's dynamic array article (checked 2026-11-09)_

Microsoft's list is not fully consistent. The same page names Excel 2019 and 2016, while other sources say you need Excel 2021 or Microsoft 365. If you are on an older build, test one formula before you rely on it.

To check your build, open Excel and read the version in the About box. The latest Mac release I could open is version 16.114.3, build 26102720, dated October 27, 2026. Its release notes do not mention dynamic arrays or spill.

The only spill note in the Mac release notes I checked is from July 14, 2026, for version 16.111. It says PivotTables can collapse into one cell showing a #SPILL error when blocked. Spill errors can show up outside plain formulas.

## Your first spill formula, with a worked example

Use a simple example to test the idea. Type =SEQUENCE(10) in cell A2. Excel fills A2 down to A11 with the numbers 1 to 10. Nothing else is needed, but the cells below A11 must stay empty.

Once that works, change the 10 to 5. The spill shrinks to A2 through A6 on its own. That automatic resizing is what makes spill formulas useful compared with copied formulas.

Only the anchor cell holds the formula. The other cells in the spill show values, and you cannot edit one of them by itself. To change the result, edit the anchor.

## Refer to a whole spill with the # operator

Add # to the anchor reference to point at the whole spill. With =SEQUENCE(10) in A2, the formula =SUM(A2#) in C2 returns 55. That matches =SUM(A2:A11), the fixed range you would type without a spill.

The difference shows up when the data changes. A2# follows the spill and updates if its size changes. A fixed range such as A2:A11 stays the same, so you have to edit it by hand.

Try it with the change from before. Set the anchor to =SEQUENCE(5), and =SUM(A2#) in C2 drops to 15, the total of 1 through 5. The fixed range =SUM(A2:A11) still returns 55 and now adds blank cells, so it gives the wrong picture of your data.

> **Typing the # sign:** On a US keyboard, # is Shift and 3. Some Mac keyboard layouts put it elsewhere, so check the symbol before you type.

- **1,048,576** rows in an Excel worksheet, which whole-column spills can run past ([source](https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3))
- **16,384** columns in an Excel worksheet ([source](https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3))
- **32,767** characters allowed in a single cell ([source](https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3))

## Stop a formula from spilling with @

Sometimes you want one result per row, not a full spill. Put @ in front of the range. The formula =VLOOKUP(@A:A,A:C,2,FALSE), copied down a column, returns one value per row and does not spill.

The @ asks Excel for the single value that lines up with the same row. Microsoft calls this implicit intersection. Use it when a whole-column formula would otherwise run past the bottom of the sheet.

## Fix a #SPILL error on Mac

A #SPILL error means Excel found a result but could not place it. Start by clicking the error cell. Excel shows a dashed border around the range it wanted to fill.

Next, click the error-checking alert beside the cell and choose Select Obstructing Cells. Excel highlights what is in the way. Delete or move those entries, and the spill returns.

> **Keep spill ranges clear:** Do not type into a cell inside a spill range. Any entry there blocks the spill, and the anchor shows the #SPILL error.

| Cause | What you see | Fix |
| --- | --- | --- |
| Spill range is not blank | Something sits in the cells the result needs | Select Obstructing Cells, then delete or move the entry |
| Formula inside an Excel Table | The table blocks the spill | Move the formula outside the table, or convert the table to a range |
| Merged cells in the way | A merged cell sits inside the spill range | Unmerge the cells, or move the formula |
| Indeterminate size | The result size changes on every calculation | Replace RAND, RANDARRAY or RANDBETWEEN in the size argument with a fixed number |
| Out of memory | The result is too large | Reference a smaller range |
| Beyond the worksheet edge | A whole-column formula runs off the grid | Use a bounded range such as A2:A7, or add @ |

_Common #SPILL causes and fixes, from Microsoft's spill error pages_

Find your cause in the table and apply the matching fix. Work through them in order if you are not sure which one applies. Most people find the cause in the first row.

![Analyst checking spreadsheet numbers on a screen before sharing a summary](https://images.lazur.app/content-images/howto/use-spill-formulas-in-excel/how-to-use-spill-formulas-in-excel-3-7b3fd8.jpg)

## Whole columns and the 1,048,576 row limit

A worksheet has 1,048,576 rows and 16,384 columns. A whole-column reference covers all 1,048,576 rows. In dynamic-array Excel, a formula like =VLOOKUP(A:A,A:C,2,FALSE) tries to return about that many results, so the spill runs off the grid.

Use a bounded range instead. =VLOOKUP(A2:A7,A:C,2,FALSE) spills only the rows you name, but it does not work inside a table. =VLOOKUP(A2,A:C,2,FALSE), copied down, works inside tables and does not spill.

Each cell can hold up to 32,767 characters. That limit matters when a formula builds long text in every row.

## Spill formulas and Excel Tables

Spilled array formulas are not supported in Excel Tables. Place the formula outside the table. If you want the table, convert it to a range first.

A Microsoft Q&A thread from April 2021 gives the same answer. Its accepted answer is to convert the table to a range first. The thread names no Mac build, so treat it as general guidance.

Convert the table from the Table Design tab. Microsoft's label for the command is Table Design > Tools > Convert to Range. I could not confirm those exact labels on a Mac screen, so check the ribbon if they differ.

Before you convert, make sure you do not need the table features. Converting turns the table into a plain range.

## Cross-workbook spills and closed files

A cross-workbook dynamic array works only when both workbooks are open. Microsoft's spilled range operator page says it does not support references to closed workbooks, and those return #REF error values.

Open the source workbook again and the reference resolves. If a file looks broken after a restart, open both workbooks before you change any formula.

## Mistakes people make with spill formulas

- Typing into a cell inside the spill area after the formula is in place.
- Using a whole-column reference without checking the 1,048,576 row limit.
- Putting a spill formula inside an Excel Table and expecting it to work.
- Using RAND, RANDARRAY or RANDBETWEEN to set the size of a spill.
- Merging cells in the rows below a formula.
- Using a fixed range like A2:A11 when the spill grows, so totals stay wrong.

## Does spill work in Excel for the web on Mac?

I could not confirm spill support in Excel for the web. Microsoft's dynamic array article does not list it in the Applies To section, so treat it as unknown. The steps on this page are for the desktop app on a Mac.

If you switch between the desktop app and the browser, test one formula in each before you rely on it.

## Keyboard shortcuts for spill work

Microsoft's common Mac shortcut list covers the basics for working around spills. It does not list the spill commands themselves, so the menu and alert steps above do the real work.

![Man at a MacBook reviewing data, learning how to use spill formulas in Excel on Mac](https://images.lazur.app/content-images/howto/use-spill-formulas-in-excel/how-to-use-spill-formulas-in-excel-2-515efe.jpg)

Undo is the one to remember. If a spill lands in the wrong place, press ⌘Z before you change anything else.

## What to do next

A spill formula gives you a live list. If that list needs grouping, totals or a summary by category, a pivot table may fit better. The [pivot table guide](https://www.lazur.app/how-to/make-a-pivot-table-in-excel) walks through that on Mac.

Keep your spill ranges clear and label your anchor cells. A short note above each spill helps you find it again when a spill error appears later.

## Shortcuts

- Commit a formula and let it spill: Return
- Undo a spill you did not mean to make: ⌘Z
- Redo or repeat the last action: ⌘Y
- Copy a spill result: ⌘C
- Paste copied cells: ⌘V
- Open Paste Special: ⌘⌃V
- Find a value or formula: ⌘F
- Close a dialog: Esc

## If it does not work

- **Spill error saying the spill range is not blank** Click the error-checking alert, choose Select Obstructing Cells, then delete or move the blocking entry.
- **Spill error inside an Excel Table** Move the formula outside the table, or convert the table to a range first from the Table Design tab.
- **Spill error from a formula that uses RAND, RANDARRAY or RANDBETWEEN** The size changes on each calculation. Use a fixed number, such as =SEQUENCE(1000), or reference a range instead.
- **Spill error that extends beyond the worksheet edge** Replace a whole-column reference with a bounded range such as A2:A7, or add @ and copy the formula down.
- **#REF error after reopening a file with a cross-workbook spill** Open both workbooks together. Cross-workbook spills need both files open to resolve.
- **Spill error caused by merged cells** Unmerge the cells in the spill area, or move the formula to a clear area.

## With Lazur

Lazur is a voice assistant for the Mac that lives in the notch. Hold ⌥ ⌘ and ask; it sees your screen, answers out loud, points at buttons and walks you through apps. Hold fn to dictate. Free for 3 days, then $18/month: https://www.lazur.app/download

- “Where is the Convert to Range button?”: Lazur circles the button on your screen and labels it, so you find it without hunting through the ribbon. You still do the click yourself. (Points at it)

## FAQ

### Does spill work in Excel 2019 for Mac?

Microsoft's dynamic array article lists Excel 2019 and 2016, but other sources say spill needs Excel 2021 or Microsoft 365. Treat 2019 as unconfirmed and test one formula before you rely on it.

### Why does my spill formula show a #SPILL error on a Mac?

The most common cause is an entry in the cells the result needs. Click the error-checking alert and choose Select Obstructing Cells. Clear the blocking entries, and the spill fills in.

### Is the # operator the same as a colon range?

In Microsoft's example, =SUM(A2#) and =SUM(A2:A11) both return 55. The difference is that A2# follows the spill when its size changes, while a fixed range does not.

### Can I use spill formulas inside an Excel Table on Mac?

No. Spilled array formulas are not supported in Excel Tables. Put the formula outside the table, or convert the table to a range first.

### Do spill formulas work the same on Windows?

The steps match the Windows version, with ⌘ in place of Ctrl. Microsoft's pages do not give a separate Mac path for spill actions, so check the ribbon if a label differs.

### Why does a RAND formula cause a spill error every time I recalculate?

RAND-type functions can change the size of the result on each calculation pass. Excel then cannot settle on a size and reports a spill error with an indeterminate size cause. Use a fixed size or a range instead.

## Sources

1. [Dynamic array formulas and spilled array behavior](https://support.microsoft.com/en-us/excel/dynamic-array-formulas-and-spilled-array-behavior), Microsoft Support, checked 2026-11-09
2. [How to correct a #SPILL! error](https://support.microsoft.com/en-us/excel/how-to-correct-a-spill-error), Microsoft Support, checked 2026-11-09
3. [#SPILL! error - Extends beyond the worksheet's edge](https://support.microsoft.com/en-us/office/-spill-error-extends-beyond-the-worksheet-s-edge-b3406d00-be0b-4a68-9976-40143ec7b55f), Microsoft Support, checked 2026-11-09
4. [Spilled range operator](https://support.microsoft.com/en-us/excel/spilled-range-operator), Microsoft Support, checked 2026-11-09
5. [Common Office for Mac keyboard shortcuts](https://support.microsoft.com/en-us/office/common-office-for-mac-keyboard-shortcuts-307f469a-1a05-4263-9b55-49df19a8b8ff), Microsoft Support, checked 2026-11-09
6. [Release notes for Office for Mac](https://learn.microsoft.com/en-us/officeupdates/release-notes-office-for-mac), Microsoft Learn, checked 2026-11-09
7. [Excel specifications and limits](https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3), Microsoft Support, checked 2026-11-09
8. [Spill error in Excel for Mac Tables - Microsoft Q&A](https://learn.microsoft.com/en-us/answers/questions/5094066/spill-error-in-excel-for-mac-tables), Microsoft Q&A, checked 2026-11-09