Excel Dynamic Arrays: Fully Dynamic Cross Tabulated Reports? Unbelievable! EMT 1520

In this video learn about how to create a Fully Dynamic Cross Tabulated Report that can update when formula inputs are changed for Row Header Conditions or Column Headers Conditions and will instantly update when new data is added to the source data. This is all done with Formulas, NOT PivotTables, so everything updates instantly when source data or formula inputs change. See the New Array Functions SORT and UNIQUE, and also the older Array Function TRANSPOSE, but most fundamentally, we will see that the INDEX and MATCH Function can lookup columns of data based on formula inputs, and will be the secret ingredient that we use inside the SUMIFS function and also inside the UNIQUE Function.

If you want totals for your dynamic Croos Tab report, check out this video:

[angeljeramaelmacip]

This will open the posibility of New most efficent reports. Thanks, you are ahead of any excel channel

[robertovelicaz]

What a great series of videos Mike! Most appreciated. As if Excel wasn't brilliant before, now this opens a whole new world of options.

[OzduSoleilDATA]

Beautiful stuff! You've gone all in. 🏆🏆🏆

[spilledgraphics]

Woah. on minute 7:03 DID NOT REALIZE that you can click on the arguments of the formula and Excel will automatically position there why editing or building it Mike !!! Excel is a never ending journey of learning !!!

[johnborg]

Watching repeatedly, and never seem to get enough.

[nsanch]

I'm so glad I finally got then new calc. engine so I can study this series. This trick especially was some magic! Thanks Mike.

[davidsargent]

Love watching your videos, you make the most complex seem so simple. Keep up the good work.

[larrycroce]

These tutorials are great. I can't wait for this to hit all version of Office 365. I have ProPlus through my company and it hasn't been introduced on this version yet. I've watched all the videos on Power Pivot and it has really made my job performance much better. Great job Mike, keep up the good work

[wmfexcel]

Unbelievable. I thought that is only applied to the new functions. Didn't expect it applies to "old" array functions. Really amazing!

[vijayarjunwadkar]

I can't thank you enough Mike! You have given me super ideas to create dynamic reports and I am just going to apply them right away! God bless you for this! 😊👍

[Granlund]

Very impressive - looking forward to this update being rolled out to the rest of us 👍

[pierrebodart]

Amazing and Magic... Excel revisited ;-)

[chrism]

Is there anything Excel can't do?? Well done Mike!!!!

[wayneedmondson]

Hi Mike.. getting a workout with this one. So cool to be able to follow along now. Thanks and thumbs up!

[BradleyDunlap]

This is just amazing. Truly mind-blowing. Thanks Mike!

[sonyset]

Excel on fire...gripping stuff...Ace of spades of functions

[knikl]

This is really awesome. Waw. Thank you. With power query and office 365, we can do everything

[davebowman]

Great stuff Mike, the new functions are huge.

[itamimii]

Fantastic! You still fascinate us with more and more brilliant videos, and you're saying it's just a step?!

[barttitulaerexcelbart]

Thanks Mike, every time I see this, it amazes me. Now transpose without CSE...where does this stop? As to the question to Geert Demulle : A suggestion (I don't have these new functions yet): You start with a total function (only for the row, so collumn total), and put it very near to the top. If you new add data to the "pivot" table, this row of totals will shift down, the row will not be overwritten. I have no idea how you can get this total row move upward if you have less data. At least a