One reason we love PivotTables is that they automatically expand to include new items. For example, we have a PivotTable that summarizes transactions by account. Then next month, we paste new transactions into the data table and there is a new account. To get this new account included in our report, all we do is refresh the PivotTable. Bam, the new account automatically appears. And we absolutely LOVE that about PivotTables!
But, now we have another option: dynamic arrays and spill ranges. We can now write a formula that causes any new accounts to be included in the spill range. And, we can use a function like SUMIFS to aggregate the amount.
Although I’d probably still use a PivotTable for this in practice, I am having a lot of fun exploring new and interesting possibilities that dynamic arrays and spill ranges offer 🙂
Before we get too far, let’s step back and look at our objective. We have a bunch of data, like this:
We want to summarize it by account, like this:
When we add new transactions to the data table, if there is a new account, such as Trade shows, we want it automatically included in the summary, like this:
Historically, this is exactly why we LOVED PivotTables. Because new items are automatically included in the summary. Dynamic arrays and spill ranges offer another option, so, let’s dig in.
We’ll build the report using these three steps:
- Basic Summary
- Total Above
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.
Before we get the basic summary working, let’s take another look at the data table.
The data is stored in a Table named Table1, and we can refer to the AcctName column by using a structured table reference like this: Table1[AcctName]. Next, the AcctName column has duplicates. Which makes sense because there can be many transactions for any given account. Our summary should contain one line for each account.
So, to build our summary, we’d like to start by getting a unique list of the account names. Next, we need a way to aggregate the amount column by account.
First, let’s create a column of the accounts. We can accomplish this by using the UNIQUE function, like this:
The formula returns multiple results and they spill out of the formula cell (B6), as shown below.
Next, we can use SUMIFS to create the summary column. We use the spill reference (B6#) as shown below:
=SUMIFS(Table1[Amount], Table1[AcctName], B6#)
When we hit enter … bam:
And at this point, we have accomplished our objective because when we add a new transaction to the data table, it is included in the summary … even if the transaction includes a new account.
For example, we add a new transaction for Trade shows to the data table, like this:
And the new account is automatically included in the summary, like this:
There are a couple of optional enhancements we can make if desired.
Let’s say we would like to sort the summary by account name. We can accomplish this by wrapping the SORT function around the UNIQUE function, like this:
What about a total? Sure, let’s do that next.
Since we want to leave plenty of empty cells under the spill range to accommodate future accounts, we’ll just put the total above the detail. Rowdy, I know. A simple SUM function that references the spill range would work. Something like this:
Since we used the spill reference, C6#, any new values in the spill range will be included in the total.
And, I think we got it … a formula-based summary that automatically includes new items!
If you have any other fun dynamic array or spill range tips, please share by posting a comment below…thanks!
Sample file: NewItemsSpill.xlsx