Excel Name Manager Spill Range: Create and Use One

How-to basics · Updated November 18, 2026

To name a spill range in Excel for Microsoft 365, open Formulas > Name Manager > New, give the range a name, and enter a reference to the spill formula’s top-left cell followed by # in Refers to—for example, =Products!$A$2#. The name then refers to the full spill range and expands or contracts as the formula’s results change.

What a Name Manager spill range means

A spill range is the group of cells populated by a dynamic array formula. The cell containing that formula is its anchor; the results spill into neighboring cells as needed. Excel’s spill reference operator, #, means “the entire spill range beginning at this cell.” It is attached to the anchor reference, not to the last visible result cell.

A defined name gives that reference a readable label. For example, a name such as ProductList can stand for the results beginning at Products!A2. The name does not copy the values or create a separate list. It points to the cells returned by the formula, so formulas using the name can use the current results as they resize.

This is different from selecting a fixed range such as A2:A10. A fixed reference continues to point to those cells even if the formula later returns more or fewer results. A spill reference follows the array’s current size, which is why it is useful for reports, formula inputs, and drop-down lists.

Create a named spill range step by step

In Excel for Microsoft 365 on Windows, use this workflow:

  1. Put a dynamic array formula in a cell and confirm that it spills into the cells you expect.
  2. Select Formulas > Name Manager, then choose New.
  3. Enter a concise name, such as ProductList. Choose Workbook for Scope if you want the name available throughout the workbook.
  4. In Refers to, enter the anchor cell reference followed by #. For an anchor in A2 on a sheet named Products, use =Products!$A$2#.
  5. Choose OK, then close Name Manager. Test the name in a formula or another feature that accepts defined names.

The example assumes the spill formula is in Products!A2; its name definition refers to every result in that spill, not just A2. If the sheet name contains spaces, enclose it in apostrophes: for an anchor in A2 on a sheet named Product Catalog, the Refers to entry is ='Product Catalog'!$A$2#. Keep the leading equals sign.

You can open Name Manager with Ctrl+F3 on Windows. The ribbon route is easier to remember and also works if that shortcut is intercepted by another program or keyboard setting. On Mac, use the Formulas tab and the available name-management command; labels and dialog layout can vary by Excel release. Excel for the web may present a different interface, so use the desktop app if you cannot find the required name-management controls.

See the reference work with a real example

Suppose the Products sheet has five product entries in B2:B6: Pear, Apple, Pear, Plum, and Apple. With those entries in B2:B6 and A2 clear for the results, enter this formula in A2:

=SORT(UNIQUE(B2:B6))

It returns three sorted values in A2:A4: Apple, Pear, and Plum. The example uses UNIQUE and SORT, which are available in Microsoft 365 and Excel 2021 or later. Create ProductList with Refers to set to =Products!$A$2#. ProductList now refers to those three results. If a new distinct product is added to B2:B6, the spilled results and the named reference can grow accordingly, as long as the spill area is unobstructed.

With ProductList defined as above, enter this formula in an empty worksheet cell to count the current items:

=COUNTA(ProductList)

For the five sample entries, the result is 3 because there are three distinct values in the spill. This illustrates the practical benefit of the name: the formula describes the list’s purpose rather than repeating its sheet and cell coordinates.

Use the name in formulas and drop-down lists

A defined spill name can be used anywhere Excel accepts a range or array reference, though the destination feature determines how the results appear. In a worksheet formula, the name can feed a calculation, lookup, or another dynamic array formula. If a formula returns multiple results, Excel may spill those results into neighboring cells too. Make sure the destination area is clear.

Names are particularly helpful for Data Validation lists. To create a drop-down based on ProductList, select the input cell or cells, choose Data > Data Validation, set Allow to List, and enter =ProductList in Source. The setup assumes the workbook-level name ProductList already refers to the spill range on Products. The menu’s labels can differ slightly across builds, but the validation rule should use the name, not a manually typed sequence of items. When the source spill changes, the list can reflect its current contents.

Using a workbook-scoped name is usually the simplest option for a validation list used on another worksheet. If a list does not appear, first confirm that the name exists, that its Refers to entry ends with #, and that the anchor formula currently produces values. Also check that the validation Source begins with an equals sign and uses the exact name spelling.

Choose the right scope and name

Name Manager’s Scope setting controls where a defined name can be used. A workbook-scoped name is available across worksheets. A worksheet-scoped name is associated with one sheet and can be useful when the same label has a different meaning on different sheets, but it can make cross-sheet formulas and validation setup harder to understand. For a shared list, a workbook-scoped name is generally clearer.

Choose a name that describes the data rather than its current position: ProductList is more useful than RangeA2. Excel names cannot contain spaces, and they must follow Excel’s naming rules; underscores are a common substitute, such as Active_Products. Avoid names that look like cell references, and be consistent about singular or plural wording.

Names can refer to more than a direct cell reference. Name Manager’s Refers to field accepts formulas, so a name can represent a calculated result as well as a spill reference. However, a direct anchor-and-hash reference is often the easiest choice when the goal is simply to give an existing spill a clear label. Keep the definition easy to inspect so another workbook user can trace the name back to its source.

Common problems and how to fix them

  • The name refers only to one cell: Check that the Refers to entry includes the anchor cell and the trailing #. Without the operator, the definition points to the anchor alone.
  • The formula returns #SPILL!: Excel cannot place one or more results in the required cells. Clear obstructing contents or merged cells in the spill area, then check whether the spill can expand. The name cannot make a blocked spill work.
  • The name evaluates to an error: Check the anchor formula first. An error in the underlying formula, or a reference to a cell that is not a valid spill anchor, can flow through to the name.
  • The name is not recognized: Verify spelling and scope in Name Manager. A worksheet-scoped name may not be available in the context where you are using it; create a workbook-scoped version if appropriate.
  • The list does not update: Confirm that the source formula actually recalculates to a different spill, that the name still points to its anchor with #, and that the consuming feature uses the name rather than a fixed cell range.
  • The spill has fewer values than expected: Check the formula’s input range and logic. A spill reference includes the formula’s actual returned array; it does not add missing source records or remove duplicates unless the formula does so.

One frequent misconception is that the hash sign belongs in the defined name itself. It belongs in the Refers to expression. Name the reference ProductList, then use the anchor-and-hash reference in the definition. Another point worth checking is whether blank-looking cells are truly blank: a formula returning an empty text string can still occupy a cell and affect downstream operations, even though it looks empty on screen.

Compatibility and alternatives

Spill references depend on Excel’s dynamic array behavior. They are suitable for Microsoft 365 and versions with dynamic array support, including Excel 2021 and Excel 2024. A workbook opened in an older Excel release that does not support dynamic arrays may not calculate the spill formula or interpret the spill operator as intended. If a workbook is shared with people on older versions, test it in the target version and consider a fixed range or another compatible design.

For a fixed list that rarely changes, a normal defined range may be simpler than a dynamic spill name. For data stored as rows in an Excel Table, structured references can be a good alternative because the table expands as rows are added. A spill-based name is most useful when the list is generated by a dynamic array formula—for example, a sorted unique list—and the range should follow the formula’s changing output.

These choices solve different problems. A table is a structured data store, a fixed defined name is a label for a specific reference, and a spill name follows a formula’s returned array. Choose based on how the source data is maintained and whether the output size changes, not just on which reference looks shortest.

Frequently Asked Questions

What does the # symbol mean in an Excel range reference?
The spill operator refers to the complete dynamic array output starting at the cell immediately before it. For example, a reference ending in A2# follows the spill anchored at A2 as its height or width changes.

Can I put the # symbol in the Name Manager name box?
No. Give the defined name a normal valid name, such as ProductList. Put the spill operator in the Refers to expression, after the anchor cell reference.

Can I use a named spill range for Data Validation?
Yes. Define a workbook-scoped name that refers to the spill, then select Data > Data Validation, choose List, and use the name with a leading equals sign in Source. If it fails, check scope and spelling.

Why does my named spill range show #SPILL!?
The name reflects the result of its underlying formula; it does not clear space for the array. Remove contents or merged cells blocking the spill, then confirm the anchor formula can return its results.

Will a spill range name work in older Excel?
Not reliably in releases without dynamic array support. The hash operator and spill formulas require a compatible Excel version. If recipients use older software, consider a fixed range or a table-based approach.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.