9 Exciting NEW Excel Functions for Shaping Arrays - Incredible!

⬇️Download the Excel file here: www.myonlinetraininghub.com/n...
In this video I cover 9 new array shaping Excel functions including TOCOL, TOROW, WRAPCOLS, WRAPROWS, TAKE, DROP, CHOOSECOLS, CHOOSEROWS and EXPAND. I also use them in some advanced techniques, which is where their real power is realised. See the timestamps below to skip to the function you want to learn.
View my comprehensive courses: www.myonlinetraininghub.com/
Connect with me on LinkedIn: / myndatreacy
0:00 New Excel Array Functions
0:23 TOCOL & TOROW functions
2:14 WRAPCOLS & WRAPROWS functions
3:44 TAKE & DROP functions
5:49 CHOOSECOLS & CHOOSEROWS functions
8:08 EXPAND function
9:01 Advanced Examples

[@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 🙏

[@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.

[@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!

[@darrylmorgan]

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

[@MyOnlineTrainingHub]

Great to hear, Darryl 🙏

[@wojwoj06]

Another - very useful video! Thank you!

[@MyOnlineTrainingHub]

My pleasure 😊

[@Excelvenky]

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

[@MyOnlineTrainingHub]

My pleasure, Venkat 🙏

[@wayneedmondson1065]

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

[@MyOnlineTrainingHub]

Cheers, Wayne!

[@TheMarkGross]

Much appreciated video. Concise!

[@MyOnlineTrainingHub]

🙏 Glad you liked it, Mark.

[@sergegagne874]

Amazing as usual. Thank you!

[@MyOnlineTrainingHub]

Thanks so much!

[@SuperArnie]

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

[@MyOnlineTrainingHub]

Glad you liked it!

[@israrahmad9521]

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

[@MyOnlineTrainingHub]

You're very welcome!

[@schubertpeter79]

your videos are just as incredible! 👏

[@MyOnlineTrainingHub]

Thanks so much 🙏

[@database_tips_tricks]

And so we learn every day ,tks

[@MyOnlineTrainingHub]

Glad you liked it, Nigel 😊

[@ExcelTutorials1]

Amazing tutorial, thank you!!

[@MyOnlineTrainingHub]

Glad you enjoyed it!

[@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 😊

[@hamidsh4789]

Excellent. Thank you.

[@MyOnlineTrainingHub]

Glad you enjoyed it!

[@AshwaniSharma-fc2nz]

Very much helpful Maam

[@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.

[@KV21A]

another AMAZING video Excel Guru

[@MyOnlineTrainingHub]

Thanks so much 🙏

[@alexanderbaranov5418]

Very interesting. Newer heard before about these functions

[@MyOnlineTrainingHub]

Glad you liked it!

[@staceythienel3193]

Love your videos!

[@MyOnlineTrainingHub]

Thanks so much 🙏

[@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.

[@arbazahmad7177]

Quite helpful 🎉... Thanks 😊

[@MyOnlineTrainingHub]

My pleasure 😊

[@nadermounir8228]

Well explained thank u for ur hard work

[@MyOnlineTrainingHub]

Thank you 😊

[@malikastar9265]

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

[@MyOnlineTrainingHub]

Glad you like them, Malika 😊

[@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 🙏

[@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.

[@chakralamurali]

Excellent

[@MyOnlineTrainingHub]

Thank you so much 😀

[@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 🙏

[@yousrymaarouf2931]

Fantastic

[@MyOnlineTrainingHub]

Thank you so much 😀

[@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

[@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

[@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.

[@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.

[@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😀

[@gurkiclk]

👌

[@MyOnlineTrainingHub]

👍

[@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 😆

[@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.

[@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! 🥰

[@B1897forzajuve]

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

[@MyOnlineTrainingHub]

Just about 😁

[@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.

[@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.

[@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

[@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.

[@ymn76ymn]

👍

[@MyOnlineTrainingHub]

🙏

[@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

[@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.

[@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.