9 Exciting NEW Excel Functions for Shaping Arrays - Incredible!

[@ignatiussilveira4257]

Your presentations are clear & well organized 👏 I'm fortunate to bump into the right channel.

[@MyOnlineTrainingHub]

Glad you're finding the content helpful! 😊

[@israrahmad9521]

I just want. to say thank you so much for the content and explanation.

[@MyOnlineTrainingHub]

You're very welcome!

[@jihadalbustanji3760]

What you are doing is a great assistance to all .. please accept my true respects and best of luck wishes ... you are great person!

[@MyOnlineTrainingHub]

Thanks, that's kind of you!

[@alexanderbaranov5418]

Very interesting. Newer heard before about these functions

[@MyOnlineTrainingHub]

Glad you liked it!

[@TangogarajTangoKursu]

You know what, i have been addicted to your videos. You are doing great job and i keep growing myself thanks to your instructional videos. Warm regards

[@MyOnlineTrainingHub]

That's wonderful to hear 🙏 keep working hard and learning.

[@LuisCarlosChavez717]

It was a clear explanation. Thanks Mynda!

[@MyOnlineTrainingHub]

Thanks, Luis 🙏

[@darrylmorgan]

Hi Mynda!These New Functions Certainly Look Really Interesting...Thank You :)

[@MyOnlineTrainingHub]

Great to hear, Darryl 🙏

[@gt3726b]

I hope these get pushed to all users soon, much easier than the workarounds!

[@MyOnlineTrainingHub]

They’re generally available on the 365 current channel.

[@wayneedmondson1065]

Great Mynda! Thanks for the demo. Thumbs up!!

[@MyOnlineTrainingHub]

Cheers, Wayne!

[@Excelvenky]

super useful..thank you so much mynda for your efforts of creating and sharing with us

[@MyOnlineTrainingHub]

My pleasure, Venkat 🙏

[@MlguelM]

wow lo máximo!, muchas gracias Mynda!

[@MyOnlineTrainingHub]

My pleasure, Miguel 😊

[@IvanCortinas_ES]

Excellent and very complete explanation Mynda! Thank you very much.

[@MyOnlineTrainingHub]

Glad you enjoyed it, Ivan!

[@Excelambda]

Great video!! For last example, an alternative, dynamic solution if will get more data to the right: =HSTACK(TOCOL(IF(C32:G35"",C31:G31,NA()),2),TOCOL(IF(C32:G35"",C32:G35,NA()),2)) -or with single variable the entire array/table "t": =LET(t,C31:G35,a,DROP(t,1),HSTACK(TOCOL(IF(a"",TAKE(t,1),NA()),2),TOCOL(IF(a"",a,NA()),2)))

[@MyOnlineTrainingHub]

Nice! Thanks for sharing.

[@jorgeegoavilnunez6385]

Excellent functions! Thank you.

[@MyOnlineTrainingHub]

Glad you like them!

[@MuhtashimKukda]

man i couldn't wrap my brain around the versatility of all these new functions

[@MyOnlineTrainingHub]

Hope the video helped 😊

[@chrism9037]

Thanks Mynda! I have to spend some time using these functions, they look really powerful

[@MyOnlineTrainingHub]

They open up a whole raft of possibilities. Have fun with them, Chris.

[@wojwoj06]

Another - very useful video! Thank you!

[@MyOnlineTrainingHub]

My pleasure 😊

[@JoseAntonioMorato]

In your last advanced example, I changed the formula to be even more advanced: =VSTACK({"Post Code","Suburbs"},SORT(FILTER(HSTACK(TOCOL(CHOOSEROWS(C31:G31,1,1,1,1)),TOCOL(C32:G35)),TOCOL(C32:G35)""))) 🤗 Dear Mynda, I was impolite not to mention that I loved the video and that it inspired me a lot, as I hadn't thought about nesting the formulas as they were nested in your advanced examples. Thanks for the great ideas. 🤗

[@B1897forzajuve]

What an amazing way to add the headers. And now add all these functions into LET to make it even better.

[@MyOnlineTrainingHub]

Nice, Jose! Great idea. One of the reasons I love VSTACK is it solves the lack of headers provided by FILTER.

[@gurupradeep9648]

I would have used the SEQUENCE Function instead of typing 5 times 1 in the chooserow Function

[@JoseAntonioMorato]

@@gurupradeep9648 1st: the number 1 is entered 4 times and not 5; 2nd: insert the SEQUENCE function, to repeat the number 1 four times, in this case, it would be to insert one more function to the formula and still continue to use the CHOOSEROWS function: CHOOSEROWS(C31:G31,SEQUENCE(4,,1, 0) Totally unnecessary. 👎

[@KajTallungs]

@@gurupradeep9648 This is exactly what I was looking for! That makes it possible to make a dynamic unpivot formula.

[@KV21A]

another AMAZING video Excel Guru

[@MyOnlineTrainingHub]

Thanks so much 🙏

[@AshwaniSharma-fc2nz]

Very much helpful Maam

[@mugalismailb.1209]

Now I have learned some cool new Ninja techniques to be applied to my work. 😎 I'm going to feel like a ninja when I'll be applying this formulas. 😎😂 Just love your videos. Love from India.

[@MyOnlineTrainingHub]

Awesome to hear 🙏

[@willm7994]

Please do a video for wrapcols & wraprows where the wrap count is dynamic, it will be awesome. I have a problem that needs that solution

[@TheMarkGross]

Much appreciated video. Concise!

[@MyOnlineTrainingHub]

🙏 Glad you liked it, Mark.

[@ninakonecka6925]

This vid made my life so much simpler. Thank you

[@MyOnlineTrainingHub]

So pleased to hear that, Nina! I'm using the FORMULATEXT function to display the formula.

[@sergegagne874]

Amazing as usual. Thank you!

[@MyOnlineTrainingHub]

Thanks so much!

[@ExcelTutorials1]

Amazing tutorial, thank you!!

[@MyOnlineTrainingHub]

Glad you enjoyed it!

[@database_tips_tricks]

And so we learn every day ,tks

[@MyOnlineTrainingHub]

Glad you liked it, Nigel 😊

[@hamidsh4789]

Excellent. Thank you.

[@MyOnlineTrainingHub]

Glad you enjoyed it!

[@nadermounir8228]

Well explained thank u for ur hard work

[@MyOnlineTrainingHub]

Thank you 😊

[@arbazahmad7177]

Quite helpful 🎉... Thanks 😊

[@MyOnlineTrainingHub]

My pleasure 😊

[@staceythienel3193]

Love your videos!

[@MyOnlineTrainingHub]

Thanks so much 🙏

[@k0023382]

Very helpful indeed. Extending on this video, I wonder how you would manipulate an ICS file (icalendar file=one very large column) to transform each VEVENT to a row that contains the values of 3 columns (SUMMARY, DTSTART, DTEND).

[@gustavoruiz6625]

Amazing!!😍

[@MyOnlineTrainingHub]

Glad you liked it 🙏

[@schubertpeter79]

your videos are just as incredible! 👏

[@MyOnlineTrainingHub]

Thanks so much 🙏

[@malikastar9265]

That's very interesting, thank you for sharing it :)

[@MyOnlineTrainingHub]

Glad you like them, Malika 😊

[@Fabi_terra]

Hi Mynda, I wanted to thank you for the great tutorial! However, I have a query about the functions you explained. I got an idea for a particular spreadsheet using those functions, but I am facing an issue due to the spilled array result of those formulas. I want to be able to change a number, or text, or even add new columns, which I can't do when the formulas return a spilled array. Is there a way to get the results as an array of values instead of a spilled array? Please let me know if you have any suggestions. Thank you! 🤗

[@MyOnlineTrainingHub]

You'd have to copy and paste the formula as values to allow for individual values to be edited or columns inserted etc.

[@Fabi_terra]

@@MyOnlineTrainingHub Thank you so much, Mynda! 🥰

[@chakralamurali]

Excellent

[@MyOnlineTrainingHub]

Thank you so much 😀

[@isiwan4853]

Hey! You have some of the best Excel content I have come across. I'm planning on becoming a financial analyst and I was wondering if you could recommend some areas of Excel I should master? For eg: Pivot tables, Xlookup, CountIF etc.. Your help is much appreciated!!

[@MyOnlineTrainingHub]

Thanks for your kind words, Ian! Definitely master all the functions you can, PivotTables, Power Query and Power Pivot. You might also want to look at Power BI. I have courses covering all those topics here: www.myonlinetraininghub.com/ Happy to help further if you want to reach out via email: website at MyOnlineTrainingHub.com

[@ignatiussilveira4257]

I'd appreciate if you use a highlighted pointer.

[@MyOnlineTrainingHub]

Thanks for the suggestion.

[@jamesdickens1394]

Great Video as always and looks like I am about 5 months behind! Is there a way to pull cell formating along with the data your moving. A simple example would be if you have column and or row headers in your data that are a light gray backgound and you want that same formating applied to the Shaped Arrays. How can that be done without using conditional formating?

[@MyOnlineTrainingHub]

Thank you! No, the array formulas don't/can't apply to cell formatting. You'd have to use Conditional Formatting.

[@jamesdickens1394]

@@MyOnlineTrainingHub Is there a way to use text join to create comma seperated values that would be inserted at the end of the Choosecols or Chooserows function? I have been trying to make this work with no success.

[@yousrymaarouf2931]

Fantastic

[@MyOnlineTrainingHub]

Thank you so much 😀

[@jordanray3509]

This is a great review of the new functions, thank you! One thing I am trying to figure out however, is how to use these new functions to transpose a table from (any thoughts would be great!): Name Jan-10 Feb-10 Mar10 Name Dates John 12 20 30 => To => John 12 Jill 18 20 25 John 20 John 30 Jill 18 Jill 20 Jill 25

[@MyOnlineTrainingHub]

Here you go with headings included: =VSTACK({"Name","Value","Date"},HSTACK(TOCOL(CHOOSECOLS(A2:A3,1,1,1)),TOCOL(B2:D3,3,FALSE),TOCOL(CHOOSEROWS(B1:D1,1,1))))

[@jordanray3509]

@@MyOnlineTrainingHub Mynda, you are a magician! Thank you so much.

[@tmb8807]

I remember initially thinking EXPAND sounded almost like a DAX ADDCOLUMNS-style function, but alas the pad_with argument doesn’t accept expressions. I can’t really see any uses for it either but I’m sure there are some.

[@MyOnlineTrainingHub]

Glad I’m not the only one 😆

[@luigibru8677]

For CHOOSEROWS I would like to choose the 1st row with the Headers and the rows "Marketing" only. How is it possible to do? Thanks!

[@MyOnlineTrainingHub]

You can specify the rows in an array, e.g. =CHOOSEROWS(C31:D34,{3,2}) therefore you can use a function that returns an array of the row numbers. If you get stuck you can post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@mbrown6240]

Great video! I am curious, what if you have 1000 rows of data, how would you use the "chooserows" to get the correct header next to the data?

[@MyOnlineTrainingHub]

'header next to the data?' You mean row label? If so, you're probably better to use INDEX & MATCH. If you have further questions: Maybe the grouped status is still present in the Pivot Cache. More on the Pivot Cache here: www.myonlinetraininghub.com/excel-pivot-cache

[@lkumar2727]

Dynamic array function is more powerful for VBA user and Ctrl shift array old excel version guy😀

[@alancannon8868]

It would be very helpful if you would reference the version of Excel you are using in your videos, please.

[@tmb8807]

Right now these functions are only available in 365 or the web version.

[@MyOnlineTrainingHub]

At the very beginning of the video I say they’re available to 365 users.

[@willm7994]

Does anyone know a formula to make the wrap count dynamic, to wrap at different intervals

[@MyOnlineTrainingHub]

Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@B1897forzajuve]

The new dynamic array functions (including the one released since 2019) can replace almost every combination of old functions.

[@MyOnlineTrainingHub]

Just about 😁

[@hcandts]

Great, it's time to invest in a newer version of Office, currently using 2016 ;(

[@MyOnlineTrainingHub]

Indeed, Hans. 365 is the way to go to get the latest Excel features each month.

[@garyanderson6273]

note in the email to me regarding these functions say that they are only available in Microsoft 365. do the Microsoft 365 functions for Excel eventually make their way into updates for MS Office Professional Plus 2021 for Excel?

[@MyOnlineTrainingHub]

Hi Gary, no, perpetual license products like 2021 do not get new features. New features only come with Microsoft 365 licenses.

[@IkMag]

The EXPAND function might be needed when you want to combine tables with different sizes using VSTACK or HSTACK.

[@MyOnlineTrainingHub]

Mmm, but you can already do that and just use IFNA for handling errors.

[@gurkiclk]

👌

[@MyOnlineTrainingHub]

👍

[@ymn76ymn]

👍

[@MyOnlineTrainingHub]

🙏

[@cshahed]

Too much for me....

[@MyOnlineTrainingHub]

I recommend you download the Excel file and try these functions if you have 365. They're not as complicated as they first appear 😉

[@cshahed]

@@MyOnlineTrainingHub yes, I have 365, thanks I'll give it a shot 😎 What I find difficult is how and where to use it, I can see it's a great tool, but what's the utilization.

[@sachin.tandon]

Some of the newish functions, like MAKEARRAY now mean you can author Rolling Calculations like the one below: Inputs: x : a single-column numerical variable, sorted in the order the user expects to calculate rolling calculations on window : an integer specifying the window length/width. For example, if window is 3, then the aggregate will be applied over the set of 3 rows ending in the current row agg : a text string specifying which aggregate function should be applied over each window Outputs: An array with ROWS(x) rows and 1 column containing the result of the aggregation over each window. For the first window-1 rows, the output array will show NA(). Reference: Flexyourdata PD.ROLLING.AGGREGATE = LAMBDA(x, window, agg, LET( _x, x, _w, window, _agg, agg, _aggs, { "average"; "count"; "counta"; "max"; "min"; "product"; "stdev.s"; "stdev.p"; "sum"; "var.s"; "var.p"; "median"; "mode.sngl"; "kurt"; "skew"; "sem" }, _thk, LAMBDA(x, LAMBDA(x)), _fn_aggs, MAKEARRAY( ROWS(_aggs), 1, LAMBDA(r, c, CHOOSE( r, _thk(LAMBDA(x, AVERAGE(x))), _thk(LAMBDA(x, COUNT(x))), _thk(LAMBDA(x, COUNTA(x))), _thk(LAMBDA(x, MAX(x))), _thk(LAMBDA(x, MIN(x))), _thk(LAMBDA(x, PRODUCT(x))), _thk(LAMBDA(x, STDEV.S(x))), _thk(LAMBDA(x, STDEV.P(x))), _thk(LAMBDA(x, SUM(x))), _thk(LAMBDA(x, VAR.S(x))), _thk(LAMBDA(x, VAR.P(x))), _thk(LAMBDA(x, MEDIAN(x))), _thk(LAMBDA(x, MODE.SNGL(x))), _thk(LAMBDA(x, KURT(x))), _thk(LAMBDA(x, SKEW(x))), _thk(LAMBDA(x, STDEV.S(x) / SQRT(_w))) ) ) ), _fn, XLOOKUP(_agg, _aggs, _fn_aggs), _i, SEQUENCE(ROWS(x)), _s, SCAN( 0, _i, LAMBDA(a, b, IF(b < _w, NA(), _thk(MAKEARRAY(_w, 1, LAMBDA(r, c, INDEX(_x, b - _w + r)))))) ), _out, SCAN(0, _i, LAMBDA(a, b, _fn()(INDEX(_s, b, 1)()))), _out ) ); 😉

[@MyOnlineTrainingHub]

Yikes! I haven't used MAKEARRAY yet. Thanks for sharing, Sachin.

[@SuperArnie]

Excel❤lent video. Straight forward, to the point, helpful examples.

[@MyOnlineTrainingHub]

Glad you liked it!