9 Exciting NEW Excel Functions for Shaping Arrays - Incredible!
HTML-код
- Опубликовано: 20 июн 2024
- ⬇️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
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
That's wonderful to hear 🙏 keep working hard and learning.
It was a clear explanation. Thanks Mynda!
Thanks, Luis 🙏
wow lo máximo!, muchas gracias Mynda!
My pleasure, Miguel 😊
Another - very useful video! Thank you!
My pleasure 😊
I just want. to say thank you so much for the content and explanation.
You're very welcome!
Very much helpful Maam
Much appreciated video. Concise!
🙏 Glad you liked it, Mark.
Thanks Mynda! I have to spend some time using these functions, they look really powerful
They open up a whole raft of possibilities. Have fun with them, Chris.
Great Mynda! Thanks for the demo. Thumbs up!!
Cheers, Wayne!
Excellent and very complete explanation Mynda! Thank you very much.
Glad you enjoyed it, Ivan!
Amazing tutorial, thank you!!
Glad you enjoyed it!
Amazing as usual. Thank you!
Thanks so much!
Hi Mynda!These New Functions Certainly Look Really Interesting...Thank You :)
Great to hear, Darryl 🙏
Excellent functions! Thank you.
Glad you like them!
Amazing!!😍
Glad you liked it 🙏
super useful..thank you so much mynda for your efforts of creating and sharing with us
My pleasure, Venkat 🙏
your videos are just as incredible! 👏
Thanks so much 🙏
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).
Excel❤lent video. Straight forward, to the point, helpful examples.
Glad you liked it!
And so we learn every day ,tks
Glad you liked it, Nigel 😊
Excellent. Thank you.
Glad you enjoyed it!
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)))
Nice! Thanks for sharing.
man i couldn't wrap my brain around the versatility of all these new functions
Hope the video helped 😊
another AMAZING video Excel Guru
Thanks so much 🙏
Love your videos!
Thanks so much 🙏
I hope these get pushed to all users soon, much easier than the workarounds!
They’re generally available on the 365 current channel.
Quite helpful 🎉... Thanks 😊
My pleasure 😊
Very interesting. Newer heard before about these functions
Glad you liked it!
That's very interesting, thank you for sharing it :)
Glad you like them, Malika 😊
Well explained thank u for ur hard work
Thank you 😊
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
Excellent
Thank you so much 😀
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!!
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
This vid made my life so much simpler. Thank you
So pleased to hear that, Nina! I'm using the FORMULATEXT function to display the formula.
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. 🤗
What an amazing way to add the headers. And now add all these functions into LET to make it even better.
Nice, Jose! Great idea. One of the reasons I love VSTACK is it solves the lack of headers provided by FILTER.
I would have used the SEQUENCE Function instead of typing 5 times 1 in the chooserow Function
@@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. 👎
@@gurupradeep9648 This is exactly what I was looking for! That makes it possible to make a dynamic unpivot formula.
Fantastic
Thank you so much 😀
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.
Awesome to hear 🙏
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?
'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
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?
Thank you! No, the array formulas don't/can't apply to cell formatting. You'd have to use Conditional Formatting.
@@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.
Dynamic array function is more powerful for VBA user and Ctrl shift array old excel version guy😀
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! 🤗
You'd have to copy and paste the formula as values to allow for individual values to be edited or columns inserted etc.
@@MyOnlineTrainingHub Thank you so much, Mynda! 🥰
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
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))))
@@MyOnlineTrainingHub Mynda, you are a magician! Thank you so much.
👌
👍
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.
Glad I’m not the only one 😆
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!
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
Great, it's time to invest in a newer version of Office, currently using 2016 ;(
Indeed, Hans. 365 is the way to go to get the latest Excel features each month.
It would be very helpful if you would reference the version of Excel you are using in your videos, please.
Right now these functions are only available in 365 or the web version.
At the very beginning of the video I say they’re available to 365 users.
The new dynamic array functions (including the one released since 2019) can replace almost every combination of old functions.
Just about 😁
Does anyone know a formula to make the wrap count dynamic, to wrap at different intervals
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
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?
Hi Gary, no, perpetual license products like 2021 do not get new features. New features only come with Microsoft 365 licenses.
The EXPAND function might be needed when you want to combine tables with different sizes using VSTACK or HSTACK.
Mmm, but you can already do that and just use IFNA for handling errors.
👍
🙏
Too much for me....
I recommend you download the Excel file and try these functions if you have 365. They're not as complicated as they first appear 😉
@@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.
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
)
);
😉
Yikes! I haven't used MAKEARRAY yet. Thanks for sharing, Sachin.