MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Replace a Pivot Table with 3 Dynamic Array Formulas

October 02, 2018 - by Bill Jelen

Replace a Pivot Table with 3 Dynamic Array Formulas

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.

One dynamic array formula to create customers down the side of the report
One dynamic array formula to create customers down the side of the report

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

For the columns area, use TRANSPOSE
For the columns area, use TRANSPOSE

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.

This is a sweet formula
This is a sweet formula

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

Change some cells in the original data
Change some cells in the original data

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

Your cross tab report automatically expands with the new data
Your cross tab report automatically expands with the new data

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

Download Excel File

To download the excel file: replace-a-pivot-table-with-3-dynamic-array-formulas.xlsx

Excel Thought Of the Day

I've asked my Excel Master friends for their advice about Excel. Today's thought to ponder:

"Keep your data close and your spreadsheets closer"

Title Photo: Lukasz Szmigiel on Unsplash

Bill Jelen is the author / co-author of
Microsoft Excel 2019 Pivot Table Data Crunching

Use Excel 2019 pivot tables and pivot charts to produce powerful, dynamic reports in minutes instead of hours, to take control of your data and your business. Even if you’ve never created a pivot table before, this book will help you leverage all their remarkable flexibility and analytical power–including valuable improvements in Excel 2019 and Excel in Office 365.