Automatically Copy Transactions into Spill Ranges

Let’s say we have a data-entry table, where we type or paste new transactions. We would like a copy of each transaction to be directed to a specific worksheet, depending on the transaction type. For example, sales transactions should flow from the data-entry table into another worksheet that displays all sales transactions. Returns should flow into a dedicated returns worksheet, and so on.

As with anything in Excel, there are several approaches to this. For example, we could apply a filter and then copy/paste. We could use Power Query. We could even use a PivotTable in several ways (drill-down to create detail pages, filter, or use the show report filter pages command). Well, now there is another option … Spill ranges. A notable advantage of using a spill range is that they are literally hands-free … no manual filters, no manual copy/paste, not even a need to click a refresh button or any other command icon. So, depending on the workbook, users, and objectives, this may be a nice option. Let’s dig in.


Before we get too far, let’s step back and look at our objective. We have a table where we enter (or paste) transactions. Perhaps something like this:

We would like sales transactions to automatically flow to another worksheet. Perhaps something like this:

Also, we would like all returns to flow to another worksheet, like this:

Accomplishing this is fairly easy with dynamic arrays and spill ranges. And, the best part is that as you enter new transactions into the data table, they will automatically flow to the corresponding sheets.


Here are the steps we’ll use:

  • Create the data-entry table
  • Write a formula to retrieve sales transactions
  • Write a formula to retrieve returns

Let’s get to it.

Note: dynamic arrays and spill ranges are available on a limited basis at the time Iā€™m writing this post. So, your version of Excel may not have these capabilities. At the time Iā€™m writing this, they are available to O365 subscribers with the Insiders Fast channel. If you are an O365 subscriber, you may be able to switch to the Insiders Fast update channel. See the Microsoft website for more info.

Create the data-entry table

First, we’ll want to create a table for users to type or paste transactions. Generally, we’ll want to store this data in a Table. To convert an ordinary range into a Table, select any cell in the range and use the Insert > Table command.

If the data is stored in a Table, we can use the Table’s name and other structured table references in the formulas. For example, Table1 to reference the entire table, and Table1[Type] to reference the table’s type column.

If the data is stored in an ordinary range instead of a Table, then we can use column-only references to ensure new data is included. For example, A:C to reference all rows in three columns of data, or B:B to reference all rows in a single column.

In this example, I used a Table to store the data, and it’s named Table1. The table is shown below.

Now, it is time to write the formulas. We’ll start by retrieving the sales transactions.

Write a formula to retrieve sales transactions

On a new worksheet, we want to write a formula that retrieves transactions where the Type column value is equal to “Sale.” To accomplish this, we’ll use a dynamic array function called FILTER. The FILTER function allows us to return a range, but only include rows that meet one or more conditions.

To retrieve transactions from Table1, where the Type column value is equal to “Sale,” we could use this formula:

=FILTER(Table1, Table1[Type]="Sale")

We hit enter and … wow! All columns for all Sale transactions flow into the sheet:

We don’t even need to fill the formula down or right. This is because the formula returns multiple values … and the results spill into the adjacent cells (rows and columns) as needed.

Let’s test it out to ensure it works. We go back to the data table and add a new sales transaction, like 1006 below:

We flip back to the sales worksheet and … OH MY GOSH … it is there:

No manual filtering, no manual copy/paste, no manual refresh, no manual steps at all. It is fully automatic.

Retrieving the returns is just as easy.

Write a formula to retrieve returns

On another new worksheet, we just write a single formula:


We hit enter and bam … it is done:

That single formula returns multiple values, spilling down and right as needed to accommodate them. If we add a new return transaction, the formula automatically retrieves it as well.

Well, that is another way we can apply dynamic array formulas and spill ranges. These capabilities are certainly opening up new ways to accomplish tasks.

If you have other spill range tips, please share by posting a comment below…thanks!

Sample file: CopyToSpillRange.xlsx





Posted in ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Our training programs start at $29 and will help you learn Excel quickly.


  1. Cathy on March 14, 2019 at 10:04 am

    WHOA, love this simple formula…..but would it be efficient and useful to have a macro that allowed users to set the TYPE? or is there another way that better suits this flexibility

    • Jeff Lenning on March 14, 2019 at 10:08 am

      Hi Cathy … one easy way to allow the users to select the type is just store that in a cell, for example, store “Sale” in A1. Then, modify the formula to reference that cell, like this:
      =FILTER(Table1, Table1[Type]=A1)
      That way a user can just type in any type and the formula will retrieve them auto-magically šŸ™‚

  2. Nate on March 15, 2019 at 5:42 pm

    Love this. Now I just need to convince the boss to upgrade us to Office 365!

  3. Richard on March 21, 2019 at 10:11 am

    Great little nugget of informastion this, just a shame my version of Office365 does like this formula šŸ™

    I get the below, which I thin k means i dont have the insider version.

Leave a Comment