BREAKING NEWS: Just moments ago at the Ignite Conference in Orlando, Microsoft unveiled a new calculation engine for Excel. A single formula can now spill into adjacent cells, essentially returning an array of values without anyone ever pressing Ctrl+Shift+Enter.
To go along with the new calc engine are six new functions: SORT, SORTBY, FILTER, UNIQUE, SEQUENCE, and RANDARRAY. I will cover all six functions in videos 2233 through 2237.
To download the workbook for today's video: https://wwww.mrexcel.com/download-c...odern-array-formulas-debut-in-office-365.xlsx
To go along with the new calc engine are six new functions: SORT, SORTBY, FILTER, UNIQUE, SEQUENCE, and RANDARRAY. I will cover all six functions in videos 2233 through 2237.
To download the workbook for today's video: https://wwww.mrexcel.com/download-c...odern-array-formulas-debut-in-office-365.xlsx
Transcript of the video:
Learn Excel from MrExcel podcast episode 2233: Modern Arrays and Single Hey, welcome back to the MrExcel netcast. I am Bill Jelen.
Amazing new set of functions just debuted in Excel now.
Hey, you have to be on the Insider Fast to get this. All right? It might be weeks or months until you get this or if you're on the semi-annual it might be January to get this. This is absolutely wild.
You know, I love Mike Girvin's Control Shift Enter book.
A lot of things in that book are to become a lot simpler, based on the next five podcast.
I'm going to start with just the most simple thing here. I have an array of data over there and A1 to C20.
I want to bring that somewhere else. So right here in a single cell =A1:C20, enter.
And they tell me, hey your formula spilled and return multiple values, so we spilled them into neighboring blank cells. Got it!
Do we have it? Alright, I wanted to show you this. I want you to look at the formula bar.
See right now the formula bar is grayed out.
That formula only exists in one cell. It's one cell, one formula.
When I go anywhere else, it's grayed out. Here, check this out. I come over here =FORMULATEXT.
Alright, so that formula does live in cell E3, but if I copy FORMULATEXT to E4.
No, there is no formula there. This is completely new, new, new stuff.
Now, couple of things, the original data was 20 rows and this is a table. So if I had something new...
Let's call it new, new, new, BAM! the results update wow, this is wild. This is wild, wild, wild. Ok. Now, we're going to remove this whole thing Couple of problems. Problem number one. What if there's other stuff?
What if there's not room for it to spill? So I have other stuff right there and I come here and I try and build one of those formulas =A1:C20 C21 now.
Enter.
There we are. Brand new error called #SPILL! and says that there is...
The spill range isn't blank. I can select obstructing cells and delete them. BAM, and everything comes back.
All right, this is freaking awesome. Now, here's the other problem.
This is only gonna be a problem to a small segment of the population and already just by watching this podcast you're part of that small segment of the population, but an even smaller segment of this small segment of the population are the people who know and love and use implicit intersection.
But an even smaller segment of this small segment of the population are the people who know and love and use implicit intersection. You gotta be saying hey, wait a second, that's not how implicit intersection works because if I would come here to this cell and enter =C2:C21 It should only return the 90 from the intersecting cell but C2:C20, I'm getting the whole thing. Implicit intersection? Gone!
No, it's not gone. When you want to do implicit intersection, the first of the new functions this week =SINGLE (C2:C 20) and that says give me just the item from this row and that'll work if I copy anywhere else just like implicit intersection. The backwards compatibility story?
Awesome. They will automatically convert your implicit intersection from an old workbook to the single then when I save this and then open in the old workbook, they will convert it back to implicit intersection. Beautiful job, Joe McDade at Microsoft.
This is the first of five podcasts this week. These are all amazing.
You want to tune in for every one of them. The new book Microsoft Excel 2019 comes out in November.
Click that i on the top right hand corner. To download the workbook from today's video, visit the URL in the YouTube description.
Hey, I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
Amazing new set of functions just debuted in Excel now.
Hey, you have to be on the Insider Fast to get this. All right? It might be weeks or months until you get this or if you're on the semi-annual it might be January to get this. This is absolutely wild.
You know, I love Mike Girvin's Control Shift Enter book.
A lot of things in that book are to become a lot simpler, based on the next five podcast.
I'm going to start with just the most simple thing here. I have an array of data over there and A1 to C20.
I want to bring that somewhere else. So right here in a single cell =A1:C20, enter.
And they tell me, hey your formula spilled and return multiple values, so we spilled them into neighboring blank cells. Got it!
Do we have it? Alright, I wanted to show you this. I want you to look at the formula bar.
See right now the formula bar is grayed out.
That formula only exists in one cell. It's one cell, one formula.
When I go anywhere else, it's grayed out. Here, check this out. I come over here =FORMULATEXT.
Alright, so that formula does live in cell E3, but if I copy FORMULATEXT to E4.
No, there is no formula there. This is completely new, new, new stuff.
Now, couple of things, the original data was 20 rows and this is a table. So if I had something new...
Let's call it new, new, new, BAM! the results update wow, this is wild. This is wild, wild, wild. Ok. Now, we're going to remove this whole thing Couple of problems. Problem number one. What if there's other stuff?
What if there's not room for it to spill? So I have other stuff right there and I come here and I try and build one of those formulas =A1:C20 C21 now.
Enter.
There we are. Brand new error called #SPILL! and says that there is...
The spill range isn't blank. I can select obstructing cells and delete them. BAM, and everything comes back.
All right, this is freaking awesome. Now, here's the other problem.
This is only gonna be a problem to a small segment of the population and already just by watching this podcast you're part of that small segment of the population, but an even smaller segment of this small segment of the population are the people who know and love and use implicit intersection.
But an even smaller segment of this small segment of the population are the people who know and love and use implicit intersection. You gotta be saying hey, wait a second, that's not how implicit intersection works because if I would come here to this cell and enter =C2:C21 It should only return the 90 from the intersecting cell but C2:C20, I'm getting the whole thing. Implicit intersection? Gone!
No, it's not gone. When you want to do implicit intersection, the first of the new functions this week =SINGLE (C2:C 20) and that says give me just the item from this row and that'll work if I copy anywhere else just like implicit intersection. The backwards compatibility story?
Awesome. They will automatically convert your implicit intersection from an old workbook to the single then when I save this and then open in the old workbook, they will convert it back to implicit intersection. Beautiful job, Joe McDade at Microsoft.
This is the first of five podcasts this week. These are all amazing.
You want to tune in for every one of them. The new book Microsoft Excel 2019 comes out in November.
Click that i on the top right hand corner. To download the workbook from today's video, visit the URL in the YouTube description.
Hey, I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
