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

6 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.

  3. Jeffrey Pfouts
    Reply

    This looks so promising. My struggle has many facets.

    First off is that my company will not have and up to date copy of Excel for a while as we just updated and licensed everyone to MS Office 2013. So… I’m still only using pivot tables and frankly this is a whole new area to me.

    Secondly I am in metrology – measurement of parts. I am struggling to compile my data. I have a variety of different parts, each with its own unique features to be measured. Some parts can have over 200 features or data points to be analyzed. We do short runs and extreme flexibility is required while trying to expedite our delivery process. I spend so much time creating unique reports when I really think a flexible spreadsheet would save me so much time.

    For example: I am measuring a cylinder with 20 individual points. I need to report the min, max, and average of the diameter. I also need to report the radial position of each point for form and tolerancing to determine where our process is working and/or not working correctly. This is considered one feature. Now imagine a part with 200 of these features…

    So my questions :
    Does anyone work with data that is not accounting based?
    How do you go about organizing data for analysis in a pivot table? Is there a decision making chart?

    Thank you!!

    1. Jerry Cooper CMA
      Reply

      Jeffrey, I would agree with your opening line, that your struggle has “many facets” … especially when you’re measuring at 20 different points on a cylinder! Reminds me of when I used to work for a forging/machining operation checking parts with a CMM.

      To answer your first question, YES, Excel is widely used by non-accountants for just this type of analysis. The fact that your background is not in accounting could actually prove to be an advantage in your application of Excel.

      As far as how to organize your data for PivotTable analysis, think of it as a database, and use Excel Table functionality. First make sure you have no blank rows or columns in your data range, then select any cell within that range and do Insert / Table from the menu. Next, use that Table to create a PivotTable by doing Insert / PivotTable. After that just start exploring the options for data layout and analysis.

      1. Jeffrey Pfouts
        Reply

        Thanks Jerry. We’re a investment casting house with machining capability. I am an experienced PC-DMIS programmer trying to streamline my data analysis outside of DataPage plus.

        Experimenting outside of my priorities is frowned upon so I was hoping to get beyond the experimenting stage faster so it wouldn’t be noticed. I spent a couple of hours yesterday trying to organize my data and now I am behind schedule.

        I’m almost to the point that I need to start exploring other options. At least now, I know how to use tables and that they adjust to data volume. That has been the worst part of my data compilation. One day it is a spreadsheet for 15 parts and the next week I am adjusting that spreadsheet for 45 parts. And since we are in the AS9100 world, there are a lot of reports showing individual data per part that are dependent of the AQL lot size.

        Again… Thanks for your input.

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.