In today's video: replacing a pivot table with three dynamic array formulas.
(0:00) How to create a crosstab report in Excel from 3 array formulas
(0:13) The official name for the new arrays are Dynamic Arrays, not Modern Arrays
(0:23) I've written a 60-page e-book documenting 30 ways to use them. For more information: Excel Dynamic Arrays Straight to the Point
(0:36) The roll-out is going to be super-slow, as the Excel team tries to figure out if they will break anything.
(1:12) =E3# is an Array-Range Reference Notation
(1:44) Creating a cross-tab report to replace a pivot table with three formulas
(2:00) SORT/UNIQUE for ROWS
(2:22) TRANSPOSE/SORT/UNIQUE for COLUMNS
(2:39) SUMIFS in the Values area
(3:22) This is an example of Broadcasting arrays.
(4:12) Change the underlying data and the report updates
(0:00) How to create a crosstab report in Excel from 3 array formulas
(0:13) The official name for the new arrays are Dynamic Arrays, not Modern Arrays
(0:23) I've written a 60-page e-book documenting 30 ways to use them. For more information: Excel Dynamic Arrays Straight to the Point
(0:36) The roll-out is going to be super-slow, as the Excel team tries to figure out if they will break anything.
(1:12) =E3# is an Array-Range Reference Notation
(1:44) Creating a cross-tab report to replace a pivot table with three formulas
(2:00) SORT/UNIQUE for ROWS
(2:22) TRANSPOSE/SORT/UNIQUE for COLUMNS
(2:39) SUMIFS in the Values area
(3:22) This is an example of Broadcasting arrays.
(4:12) Change the underlying data and the report updates
Transcript of the video:
Learn Excel from MrExcel podcast Episode 2244. Replace a Pivot Table with Three Dynamic Array Formulas.
Hey, welcome back to the MrExcel netcast, I'm a Bill Jelen.
All right. Now, news.
Since episode 2237, last week I put out these five videos about these new arrays. And they were in announced at Ignite on September 24, 2018.
They're not called modern arrays. They're called dynamic arrays.
Second, I've written a 60-page eBook with 30 examples of how to use these, and for right now, in 2018, I'm giving this book away for free.
I reserve the right to start charging for it when 2019 comes.
There's the link up with a link in the YouTube description.
Third, the roll out is going to be a lot slower than anyone wants.
Even if you're on Insider Fast, there's a lot of people who don't have this. And why is it so slow?
The fact is, these are changes to CALC engine code that has been stable for 30 years.
They have particular concerns, that add-ins that inject formulas into Excel that inadvertently use implicit intersection will start returning a spill range, instead of a single cell and it'll break the add-in.
If they can't find the person who wrote the add-in, that's going to be a nightmare. All right?
So they're going slow to see what is happening.
And then, the thing we're going to cover today, and the thing I completely missed in those five videos from last week.
This new thing =E3# refers to the entire range that the array occupies. Right?
The hash tag itself is called the Spilled Formula Operator but Microsoft doesn't have a name for that reference. We've been bouncing around a lot of different names.
Array Range Reference, may be. Well, we're just... The Spiller. All right?
Let's get started.
Today, I want to take a data set and I want to create a pivot table from that data set but I know the data sets gonna be changing.
I can't trust my manager to update the pivot table.
I know it's just clicking refresh; that's a lot of work.
I can replace that pivot table with live formulas, and here's what we're gonna do.
We're gonna start out with =UNIQUE of the customer names over here and that gives me a list of customer names, but I want it sorted, =SORT(UNIQUE) will give me a sorted list of customers like that.
Perfect! Now, across the top, I want products or regions. Let's put products.
Now, the products, of course, starts out with UNIQUE of the products and I want to send that into the SORT, TRANSPOSE, of course, will turn it sideways. All right. So, now I have one formula here in I2.
It's returning a complete list of the customers, and another formula here in J1 that's returning a complete list of the products.
I have one more formula to finish the pivot table. =SUMIFS.
And the sum range is going to be my revenue over here, criteria range one, is go look through all of the customers.
Now, do I need to press F4? No, because this is a single formula.
We're not going to copy it anywhere and see if it's equal to the customers in I2.
And the way to do that is I2#; that says the complete range over there that makes up that array. Next argument, is the products and that we want to see if it's equal to J1#.
Again, I2# and J1# are the arranged array reference notations.
What's gonna happen here, and this is a term that I wrote about in Chapter 9 of the book; it's called Broadcasting.
We have an array here that's 27 rows x 1 column and another array here that's 3 columns x 1 row.
The results returned by this are going to be 27 rows x 3 columns.
They're going to make things large enough for, you know, all of the arrays reference.
Press ENTER and bam! One formula returns all of the answers that would have been in the pivot table.
And the beautiful, beautiful thing about this is if something changes the pivot table - it's not a pivot table it's a cross tab report - it's gonna update automatic.
So here's Cummins Engine with 371470 Let me just juice this up by $10,000,000 and it automatically updates.
Or let's add a new customer in and the report automatically updates. This is super cool and super powerful.
The array reference notation I2#, J1# automatically grows as those dynamic arrays in I2 and J1 expand.
If you like what you see in this video, please subscribe and ring that bell. All right, buy the book.
Right now, the book is free. Go down to that link down there in the YouTube comments.
They always say that a Computer book bestseller has sold 10,000 copies, so please share this, I want to make sure that I get 10,000 copies that make it a best-seller before I even start charging for it. Okay, wrap up.
Topics in this episode.
The official name for the new arrays are dynamic arrays not modern arrays. There's a 60 page eBook documenting 30 ways to use them.
The rollouts gonna be super slow as the Excel team tries to figure out if they'll break anything.
And then creating a cross tab report with three formulas SORT and UNIQUE for rows TRANSPOSE, SORT, UNIQUE for columns and then SUMIFS in the value area.
To download the workbook from today's busy video, visit the URL in the YouTube description. Hey, I wanna thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Hey, welcome back to the MrExcel netcast, I'm a Bill Jelen.
All right. Now, news.
Since episode 2237, last week I put out these five videos about these new arrays. And they were in announced at Ignite on September 24, 2018.
They're not called modern arrays. They're called dynamic arrays.
Second, I've written a 60-page eBook with 30 examples of how to use these, and for right now, in 2018, I'm giving this book away for free.
I reserve the right to start charging for it when 2019 comes.
There's the link up with a link in the YouTube description.
Third, the roll out is going to be a lot slower than anyone wants.
Even if you're on Insider Fast, there's a lot of people who don't have this. And why is it so slow?
The fact is, these are changes to CALC engine code that has been stable for 30 years.
They have particular concerns, that add-ins that inject formulas into Excel that inadvertently use implicit intersection will start returning a spill range, instead of a single cell and it'll break the add-in.
If they can't find the person who wrote the add-in, that's going to be a nightmare. All right?
So they're going slow to see what is happening.
And then, the thing we're going to cover today, and the thing I completely missed in those five videos from last week.
This new thing =E3# refers to the entire range that the array occupies. Right?
The hash tag itself is called the Spilled Formula Operator but Microsoft doesn't have a name for that reference. We've been bouncing around a lot of different names.
Array Range Reference, may be. Well, we're just... The Spiller. All right?
Let's get started.
Today, I want to take a data set and I want to create a pivot table from that data set but I know the data sets gonna be changing.
I can't trust my manager to update the pivot table.
I know it's just clicking refresh; that's a lot of work.
I can replace that pivot table with live formulas, and here's what we're gonna do.
We're gonna start out with =UNIQUE of the customer names over here and that gives me a list of customer names, but I want it sorted, =SORT(UNIQUE) will give me a sorted list of customers like that.
Perfect! Now, across the top, I want products or regions. Let's put products.
Now, the products, of course, starts out with UNIQUE of the products and I want to send that into the SORT, TRANSPOSE, of course, will turn it sideways. All right. So, now I have one formula here in I2.
It's returning a complete list of the customers, and another formula here in J1 that's returning a complete list of the products.
I have one more formula to finish the pivot table. =SUMIFS.
And the sum range is going to be my revenue over here, criteria range one, is go look through all of the customers.
Now, do I need to press F4? No, because this is a single formula.
We're not going to copy it anywhere and see if it's equal to the customers in I2.
And the way to do that is I2#; that says the complete range over there that makes up that array. Next argument, is the products and that we want to see if it's equal to J1#.
Again, I2# and J1# are the arranged array reference notations.
What's gonna happen here, and this is a term that I wrote about in Chapter 9 of the book; it's called Broadcasting.
We have an array here that's 27 rows x 1 column and another array here that's 3 columns x 1 row.
The results returned by this are going to be 27 rows x 3 columns.
They're going to make things large enough for, you know, all of the arrays reference.
Press ENTER and bam! One formula returns all of the answers that would have been in the pivot table.
And the beautiful, beautiful thing about this is if something changes the pivot table - it's not a pivot table it's a cross tab report - it's gonna update automatic.
So here's Cummins Engine with 371470 Let me just juice this up by $10,000,000 and it automatically updates.
Or let's add a new customer in and the report automatically updates. This is super cool and super powerful.
The array reference notation I2#, J1# automatically grows as those dynamic arrays in I2 and J1 expand.
If you like what you see in this video, please subscribe and ring that bell. All right, buy the book.
Right now, the book is free. Go down to that link down there in the YouTube comments.
They always say that a Computer book bestseller has sold 10,000 copies, so please share this, I want to make sure that I get 10,000 copies that make it a best-seller before I even start charging for it. Okay, wrap up.
Topics in this episode.
The official name for the new arrays are dynamic arrays not modern arrays. There's a 60 page eBook documenting 30 ways to use them.
The rollouts gonna be super slow as the Excel team tries to figure out if they'll break anything.
And then creating a cross tab report with three formulas SORT and UNIQUE for rows TRANSPOSE, SORT, UNIQUE for columns and then SUMIFS in the value area.
To download the workbook from today's busy video, visit the URL in the YouTube description. Hey, I wanna thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
