Dynamic Array & Old Array Formulas to Create Student Classes Report - Excel Magic Trick 1608

Learn how to create a report that shows each student by quarter with a list of classes in a single cell. See many of the new Excel 365 Functions like: SORTBY, UNIQUE, FILTER, TEXTJOIN and the ROWS Function. See how we can combine Spilled Arrays formulas and Old School Arrays Formulas together to make a report that automatically updates when new data arrives.

[MalinaC]

I'm so happy that i eventually have those New functions !!!

[wayneedmondson]

Super Mike! The fun never ends :)) Thumbs up!!

[sachinrv]

Dynamic Array is most amazing innovation of Excel in its 20+ years of its evolution.. Cheers :)

[GeertDelmulle]

SORTBY()? That’s yet another super-useful function us O365 Outsiders don’t have... :-(
BTW: super great video, Mike: it is one continuous string of gold nuggets from the beginning to the end, awesome!

[Bindhyeshful]

You are awesome. Thanks for this video

[johnborg]

That's my The best of the Best. I was playing about with the previous data using the unique and the xlookup. Thanks Mike. :) :)

[vida]

Great instant update with formulas. However, I wish dynamic arrays had automatic expansion feature as Excel table, so that formulas don't need to be dragged

[drsteele]

Well done, Mike! But it’s immensely frustrating that trying to use dynamic array on a spilled array generates an error. This fails =FILTER(SD[Classes], SD[Student]=F4#) even though we all think it shouldn’t. Maybe this is one reason MSExcelTeam is taking so long to roll out dynamic arrays. On another note, when I create a formula I know is correct except for the final parenthesis, I usually just quickly press Enter and then Enter again to accept the suggested correction – beats typing Shift9 to get er done.

[richardhay]

Great solution but I would rather "drag it down"!!

[andyanand]

Hi Mike, How would you do it in excel 2019 Dynamically. Please help! Thank you in advance

[MyWatchP]

I am currently working on a similar software but I am facing a challenge. All students are reading the same core subjects but with different electives subjects. how do I import the class mark, exams mark and position from the master sheet onto the report? Can I get ur email address so I can share files with u for help?