Excel Dynamic Arrays or Mixed Cell References for Cross Tabulated Reports? EMT 1518

In this video we embark into the New World of Spilled Arrays. Which will require us to re-think how we use Excel. In this video learn about The New Office 365 Dynamic Array Formulas and the Excel Calculation engine which will allow us to create a Budgeted Income Statement either with using Standard Formulas and Mixed Cell References or with a Dynamic Spilled Array Formula.

[chrism]

The Dynamic Array capability is unbelievable! Nice video Mike!

[wayneedmondson]

Hey Mike.. just re-watched this one. Now I can follow along with the Insider version on my laptop. It is awesome to put the DAFs to use. So much faster in some cases vs. mixed cell references and copy down / copy over and F2 to verify. One formula, upper left cell of the range, CTRL+ENTER and done. Fantastic! Thanks for this great series on DAFs. Double thumbs up!!

[iulianionescu]

As the first person to view this, I give it two thumbs up :)

[simoiyahector-morales]

Love mixed cell but love the option of dynamic array too. It really is preference and convenience

[IlaPatel]

Spilled arrays for sure. Love this new engine in excel!

[SyedMuzammilMahasanShahi]

wow. Another EXCELlent video and some new to try. Love your videos Mike and utmost respect for you. Thank you so much.

[mattschoular]

Thanks Mike. I haven't used the new calc engine yet but I am looking forward to it. I will probably stick with mixed cell references.. the reason for this is that I share files with other users who just don't know that "Excel Is Fun". Go figure....

[ismailismaili]

Hi Mr. Mike thank you so much for the video of course the Spilled array formula is way better and saving time.

[Lu_Ca]

definitely faster the second, new way. I'll use it all the time. thanks

[drsteele]

Another super vid! The spilled arrays are superior to the mixed-refs, I think. Very large grids will calculate much more quickly; there is no need to copy across and down; and they are more intuitive.

[mohamedchakroun]

Thanks Mike for dynamic arrays calculation, maybe in this case I use the mixed cells references. We could select all the range E10:J22, put the function and hit ctrl+enter it will deliver the result for the whole range selected like a dynamic arrays :-)

[entertainmentgalaxy]

excel magic tricks level upgraded and made easy. Thanks for sharing...

[douglaszulu]

Great insight. This new feature is exciting

[MalinaC]

When I have the latest update - I will deffinately use spilled arrays! But still I will be teaching mixed cell referenses as well ;). Thanks for sharing!

[JonathanExcels]

I assume that for those of us that are comfortable with mixed cell references that we will continue to use them. However, for those in school learning Excel they will probably learn it the new way.

[johnborg]

Amazing!!!! I may sound the odd one out here. The new Calc engine is great to go faster, especially when using the Sort and the Unique functions to solve a specific problem, but the fun was the building of the array formula. I don't know if I am clear enough.

[peterbartholomew]

Mike. Great material!
Could also demonstrate the use-case so that we can see how the dynamic array functionality plays with defined Names. I can't help thinking that 'Months' and 'ItemPercentages' would be more readable within a formula than directly referenced multi-cell ranges which always were something of a mess in terms of their notation.

[wmfexcel]

I think I have the answer for this question in workplace.... Mixed reference will be dominating for a long time simply because most of the corporations (big or small) are not using Office 365. Indeed, I have a gut feeling that the adoption rate of Office 365 in small business is higher than all those BIG companies. The bigger the organisations, the slower they move.
For the new learners of Excel, they are super lucky as they are "Born" with these awesome features. However, they may not be exciting as we do because they didn't know the "hard" way we used to use. In my opinion, we should teach both the "classic" and "modern" Excel in parallel for 2 reasons:
1) So that they appreciate the "modern" Excel more;
2) When they graduate and enter the real workplace (where many organisations are still using 2013, if not 2010 or 2007), they can still be able to work with Excel.
Honestly speaking, for those who learn Excel starting from Office 365, they may have no idea how to work with Excel 2003 (or even Excel 2007). Don't you think so? :P

[shaver]

Hi Mike, wow exellent Video!
If I remenber right, array formulars were slower than usually formulars. So I'm thinking, I use the mixed cell references for now.
(Sorry, for my bad english :-S )

[wayneedmondson]

Hi Mike.. I've been using mixed cell references for so long, it is second nature. I guess it will take some experimentation to determine which choice is best and under what circumstances. Either way, nice to have the option. Thanks again for all of your insights with these new features. Thumbs up!