Replace a Pivot Table with 3 Dynamic Array Formulas

October 02, 2018 - by Bill Jelen

It has been eight days since dynamic array formulas were announced at the Ignite 2018 conference in Orlando. Here is what I've learned:

1. Modern Arrays were announced at Ignite on September 24, 2018 and officially called Dynamic Arrays.
2. I've written a 60-page e-book with 30 examples of how to use them, and I am offering it free until the end of 2018.
3. The roll-out is going to be a lot slower than anyone wants, which is frustrating. Why so slow? The Excel team has made changes to Calc Engine code that has been stable for 30 years. Of particular concern: with add-ins that inject formulas into Excel that inadvertently used implicit intersection. Those add-ins will break if Excel now returns a Spill range.
4. There is a new way to refer to the range returned by an array: =E3# but it does not have a name yet. The # is called the Spilled Formula Operator. What do you think of a name like Spill Ref (suggested by Excel MVP Jon Acampora) or The Spiller (suggested by MVP Ingeborg Hawighorst)?

As the co-author of Pivot Table Data Crunching, I love a good pivot table. But what if you need your pivot tables to update and you can't trust your manager's manager to click Refresh? The technique described today offers a series of three formulas to replace a pivot table.

To get a sorted list of unique customers, use =SORT(UNIQUE(E2:E564)) in I2.

To put product across the top, use =TRANSPOSE(SORT(UNIQUE(B2:B564))) in J1.

Here is a problem: you don't know how tall the customers list will be. You don't know how wide the product list will be. If you refer to I2#, the Spiller will automatically refer to the current size of the returned array.

The formula to return the values area of the pivot table is a single array formula in J2: =SUMIFS(G2:G564,E2:E564,I2#,B2:B564,J1#).

In English, this says that you want to add the revenues from G2:G564 where the Customers in E match the current row's customer from the I2 array formula and the products in B match the current column of the array formula in J1.

What if the underlying data changes? I added a new customer and a new product by changing these two cells in the source.

The report updates with new rows and new columns. The Array-Range Reference of I2# and J1# handles the extra row and column.

Why does the SUMIFS work? This is a concept in Excel called Broadcasting. If you have a formula that refers to two arrays:

• Array one is [27 rows] x [1 column]
• Array two is [1 row] x [3 columns]
• Excel will return a resultant array that is as tall and wide as the tallest and widest portion of the referenced arrays:
• The result will be [27 rows] x [3 columns].
• This is called Broadcasting arrays.

Watch Video

Title Photo: Lukasz Szmigiel on Unsplash