All you need to know about Excel's new Spill feature

by | Feb 21, 2019 | Excel Tips & Tricks

spill

The term spill in Excel's literature is defined as populating multiple cells with a single formula. With the announcement of the dynamic arrays in September 2018, Excel formulas can now return (spill) an array of values into neighboring cells by starting with the cell containing the formula.

At the time of writing this article, Microsoft announced that this feature is currently only available to a number of select insider users. When it’s ready, the feature is planned for release for Office 365 users.

 

How it works

A simple demonstration of this feature would be entering a formula like =B2:B6

Spill

You might also see spilling when using the new dynamic array functions like SORT.

Spill

Existing functions now support spilling as well.

Spill

 

Basics

  1. You do not need to select the range where the results are to be populated. Excel will determine the size of the output range automatically.
  2. The output range starts from the formula cell, and "spills" the results by populating the cells through right and bottom, based on the return array.
  3. When you select a cell within the spill area, the range will be highlighted with a border.
  4. Use the first cell (top-left) in the spill range to edit the formula. Other cells in the spill range will show the formula as well, but the formula will be "ghosted" and cannot be updated.
  5. A spilled range can be referenced using the dash character (#) after the top-left cell. For example, you can use B2# instead of B2:B6. This is called spilled range operator.
  6. If there are any non-empty cells in the spill area, the formula returns a #SPILL! error. Excel will automatically populate the cells when the occupied cells are cleared.
  7. Spill feature is not supported in Excel Tables.
  8. Excel continues to support Array formulas (Ctrl+Shift+Enter formulas) for backward compatibility reasons. However, Microsoft encourages users to not use the array formulas. You can easily convert an array formulas into dynamic arrays by deleting the entire range, and re-entering the formula with the Enter key.
  9. Excel only supports dynamic arrays between workbooks when both workbook are open. Otherwise, you will see a #REF! error.