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.

Objective

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.

Narrative

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:

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

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