Include New Items without a PivotTable

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 πŸ™‚

Objective

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.

Narrative

We’ll build the report using these three steps:

  • Basic Summary
  • Sort
  • 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.

Basic Summary

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:

=UNIQUE(Table1[AcctName])

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.

Sort

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:

=SORT(UNIQUE(Table1[AcctName]))

Bam:

What about a total? Sure, let’s do that next.

Total Above

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:

=SUM(C6#)

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

 

Get a quick email notice when a new Excel article is available

  • This field is for validation purposes and should be left unchanged.

This article was written by Jeff Lenning

3 comments:

  1. DRSteele
    Reply

    Terrific post, Jeff. The spilled array and dynamic function technologies are sea-changes in Excel, to be sure. But you will note one drawback. The formatting of the cell that contains the spiller formula does not carry on through the spilled array, forcing the user to fiddle with formatting of the spilled cells. Microsoft knows about it the drawback, but more voices in their ears couldn’t hurt.

    1. Jeff Lenning Post author
      Reply

      Thanks Donald! Yes, I totally agree about the formatting issue, and am hoping it is addressed at some point. I know we are still early-on with these capabilities, but it is really interesting to see how they can be applied. So fun πŸ™‚

  2. Jerry Cooper
    Reply

    Thanks for this, Jeff. Another thing we LOVE about PivotTables (at least I do) is that when new data is added to the source, and new items exist (e.g. a new Customer) in that new data, the PivotTable shows those new items as Expanded by default, even if that field is Collapsed. This is a handy way to highlight new Customers and see all the details before deciding to re-Collapse that key field.

Leave a Reply

Your email address will not be published. Required fields are marked *

For security, use of Google's reCAPTCHA service is required which is subject to the Google Privacy Policy and Terms of Use.

I agree to these terms.