2 NEW Excel Functions ELIMINATE Copying Formulas

[@maxgolaz1]

Just want to thank you for your fantastic channel. Your explanations are so clear, production values so high, and loving the humour you have started to inject - not too much, nor forced so that it becomes facile but a touch that is genuinely fun. Hands down the best excel channel out there.

[@MyOnlineTrainingHub]

Aw, thanks so much for your encouraging words! Much appreciated 🥰

[@IvanCortinas_ES]

Clear, direct, pedagogical, to the point. Absolutely essential. Thank you Mynda!!

[@MyOnlineTrainingHub]

Thanks so much, Ivan! Glad you enjoyed it 🙏😊

[@777kiya]

This is excellent, thanks. These are 1. Easy to edit or debug as it's only in one formula 2. Clean, and more accurate with low probability of errors

[@MyOnlineTrainingHub]

Yes, great points. Thanks for sharing 🙏😊

[@syrophenikan]

Great video!!! Presentation Tip: When I train, I go into the Excel Options, and in the General section, I increase the Font Size to 22. Upon restarting Excel, the formula bar is much easier for my students to read from across the room. This also works well in videos. You may need to experiment with different sizes for what works best for you. Cheers. 👍👍👍👍

[@MyOnlineTrainingHub]

Great tip!

[@Seftehandle]

Indeed a great tip

[@peterbartholomew7409]

You ARE busy! I don't know how you keep it up. I tend to go one step further and define a named Lambda function for the averaging Averageλ = LAMBDA(x, AVERAGE(x)) so that the row and column averages now read = BYROW(array, Averageλ) = BYCOL(array, Averageλ) [the λ is just the letter of the Greek alphabet I tag on using the autocorrect sequence \lambda to remind myself that the name refers to a Lambda function]

[@MyOnlineTrainingHub]

I love how you do that, Peter. I was a little concerned that defining names might be a step too far for folks, but in hindsight, maybe with this scenario it would have made it appear simpler/easier.

[@jimfitch]

Another awesome tutorial, Mynda! I’ve been using BYROW (BYCOL to much lesser degree) with LAMBDA for quite a while. It’s a game changer. I did not know one-parameter limitation on LAMBDA within BYROW/BYCOL, so thanks for that. Your Top Scorer calculation is so cool! BTW, my academic weakness in sciences was Biology. Strong in MSE, which would be called STEM today (50+ years later). Even weaker in Art, except Mechanical Art where we used protractors, straight edges, & drawing templates. Aced that - too bad they made me do watercolors, oils, etc. Thank goodness the Internet wasn’t around back then. Those “works” were so bad that somebody would have posted them online, where nothing ever gets taken down! 😅

[@jimfitch]

Forgot to mention: Your tip for handling expanding range dimensions is neat, but FWIW I doubt I’ll use it. My general practice is to land source data on its own worksheet whether generated by Power Query, copied-&-pasted-as-values from Excel/Access, or entered by hand. I don’t add columns to those source tables/ranges or otherwise manipulate them post-landing. To manipulate the data further, I create another worksheet (following your advice that “worksheets are cheap”), then use dynamic arrays to build “tables” that select columns, filter rows, add calculated columns, etc. I almost always do that work in LET formulas, which I love b/c I document that “code” to help me troubleshoot or enhance in the future. That approach has worked well for me. Note: I don’t have ‘big data’: record counts are mostly < 1,000 rows; sometimes more, and almost never > 100,000. So, my approach may not work for other use cases. OTOH, at least 50% of my ETLM (M for Manipulate after Load) workbooks have multiple data sources that I link together either via PQ during T or via DA formula during M. My approach handles that complexity well, except that some workbooks take a while to refresh, prob b/c DAs are dynamic. I downloaded your PDF of tips to analyze/mitigate slow workbooks (thank you!) & am using it as I come across workbooks that are particularly irksome.

[@MyOnlineTrainingHub]

Cheers, Jim! I agree, most column adding should be done with tools like Power Query where possible. Much more efficient.

[@cede30]

I'll stick with copy-paste.

[@MyOnlineTrainingHub]

What! 😁 Seriously, if that works best for you. TBH, if you never have to copy the formula again, then that's fine, but if you're adding data and need to keep updating the formula range, then it's worth doing it this way.

[@johnmccormick2883]

I agree … my eyes glossed-over in the first example… looks like a good way to make sure nobody can maintain the spreadsheet but yourself… job security lol

[@johncowie3092]

Agreed how is this simpler a double click on the bottom right of the top cell copies down the range

[@RichardJones73]

It's all about being dynamic. Simple spreadsheet like you probably do won't need to be dynamic, but more complicated evolving ones beg for this kind of thing

[@goldenghostinc]

Also I'm assuming that this will have a impact on the size of the file? I guess that with this methode the file size will be much smaller kwhen working with big data sets) as it's not an "individual" formula per row. Will definately try this out.

[@robertgraham7245]

Like how you used this technique to show the formulas in C15, C16, C17. Practicing what you preach!

[@MyOnlineTrainingHub]

Glad you noticed 😁

[@kd6lor]

The are incredibly cool and powerful functions but I would hate to come back to a simple spreadsheet months later and try to edit or reuse the data. The level of complexity under the hood of this makes this very difficult unless you have a super high level of mastery of Excel. The opposite of the KISS principle. Excellent tutorial as always. Love to watch and learn.

[@MyOnlineTrainingHub]

Thanks for watching and taking the time to share your take on these functions. 😊

[@glwit]

Keep It Short & Simple ❤

[@alexanderbaranov5418]

Thank you for sharing brand-new possibilities with the crowd!

[@MyOnlineTrainingHub]

My pleasure, Alexander 👍😊

[@fabricioantonello8023]

Great video Mynda, thanks for opening my eyes for those functions. I was looking at MAP function as I have a situation I need to pass 1 array or rows and 1 array of columns and do a calculation for each intersection.... but that doesn't seem to work although at face value it creates an array with the correct number of columns and rows. Would you have any super wisdom on an advanced use of MAP function? Maybe an idea for a new video? I can send you an example if the explanation was not great - which I am sure it was not.

[@MyOnlineTrainingHub]

Thank you! I haven't used MAP much yet either, so don't have anything I can point you to. sorry.

[@jayo3074]

I tend to disagree with everyone in the comment section. This way is much more professional, reduces errors, easier to audit, and follows good modelling practice. Advanced users who build models for clients will love this.

[@MyOnlineTrainingHub]

Cheers, Jay! The comments reflect a person's willingness to learn and grow. It can make interesting reading 😉

[@sc100200090]

No doubt another door opened to, and widening the scope of, the Excellent World of Excel!

[@MyOnlineTrainingHub]

Hope you can make use of these new functions.

[@dariolucaspitale6670]

Dear Mynda, thank you so much for this new video, It's great, like all the other videos. I have a question, I don't know if I can ask you a question, if not, please excuse me. Are these new solutions with spill just more convenient or are they also more efficient in terms of calculation and speed? For example, If I have a column of 150,000 rows, is it faster with SPILL formula or is it better to have 150,000 formulas? Thank you so much. Have a good day. Dario

[@MyOnlineTrainingHub]

Glad you enjoyed my video, Dario. Great question about speed. I haven’t speed tested them, so can’t say, sorry.

[@dariolucaspitale6670]

Thank you very much for your answer.@@MyOnlineTrainingHub

[@bccabernet]

absolutely brilliant video, Mynda! I will be sharing this and off to watch the Lambda video.

[@MyOnlineTrainingHub]

Awesome! Thank you!

[@TGithinji]

As informative as ever. Why does my Excel become slow when I use the Spilled array formulas?

[@Seftehandle]

Ok, this is a good reason for me to get into lambda and the newest formulas added by excel. I didnt get how lambda recognized which col /row in the formula, so i guess i will need a little learning to do. Great job at keeping us learning

[@MyOnlineTrainingHub]

Awesome to hear you're keen to try them out. BYCOL/BYROW pass each column/row to LAMBDA one at a time for LAMBDA to apply the formula, so it's not which col/row, it's all columns/rows, one at a time. Hope that clarifies things.

[@sachin.tandon]

New style of video with the video in video! Great explanations. Hopefully that silver plaque on the shelf will turn gold soon!

[@MyOnlineTrainingHub]

Thanks so much 🙏😁

[@josepepe741]

Thanks a lot for your free classes. I always very pleased with your videos and explanations.

[@MyOnlineTrainingHub]

Glad to hear that 🙏😊

[@alexanderbaranov5418]

Background in soft blue and yellow Ukrainian colors is superb!

[@MyOnlineTrainingHub]

I did that on purpose 😉

[@jimfitch]

Hear hear!

[@chrism9037]

Thanks Mynda! Love these new functions

[@MyOnlineTrainingHub]

Awesome to hear, Chris!

[@ExcelUpNorth]

Dang! That is how you flex your Excel formula skills! Great video 😁 Thinking ahead for future growth in the dataset is so underrated.

[@MyOnlineTrainingHub]

Thanks so much, Darryl! Hope you can make use of them.

[@Ruantjie14]

Wondering how does ByCol interact with other spilled arrays. I'd like to have columns spilled and then for instance if sales are spilled and cost of sales aswell. Wondering if this could assist in spilling the Gross Profit (Sales less Cost of sales) across the same number of columns. 🤔

[@MyOnlineTrainingHub]

Great question! Next week I’ve got a scenario where I use these functions to return an array that spills to rows and columns 😉

[@jackcurl2005]

Interesting, and well explained. My problem is: this is not Highlander-There can more than one. Not sure how to produce a list of the matching scorers.

[@MyOnlineTrainingHub]

Thank you! If you get stuck, post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@tmb8807]

I used this to count rows in a table where the value in the date column fell within certain years. COUNTIFS would spill results for multiple years, but since it doesn’t accept arrays for the range I couldn’t extract the year from the date directly (could use a helper column, or greater-than-less-than dates, but both of those felt untidy somehow). Whereas SUM, with filter-style criteria, gave me the result I wanted, but wouldn’t spill. So the solution was to wrap SUM in BYROW! Call me crazy but I actually prefer writing these kinds of formulas than just using PQ a lot of the time. Just have to keep an eye on performance, although the dynamic array functions seem extremely efficient.

[@MyOnlineTrainingHub]

Awesome to hear! 😊not crazy 😉

[@damorgman]

Definitely will use these! Thanks so much!👏

[@MyOnlineTrainingHub]

Awesome to hear!

[@nairobi203]

Very Nice. I did not know TAKE function, but I also tried function OFFSET. If there are more than one top scoreres, you only get the first one in the list unfotunately... Or is there some magic formula, which creates you a list?

[@hBrynx]

You could combine BYCOL, TEXTJOIN, FILTER and MAX functions into a formula that would do that. Using Mynda's example worksheet: =BYCOL(C5:E14,LAMBDA(colRng, TEXTJOIN("; ", 1, FILTER(B5:B14,colRng=MAX(colRng))))) You could also use the REDUCE function to get the same result or to spill the result across multiple rows.

[@MyOnlineTrainingHub]

@nairobi203 glad you liked it. You could use the RANK. @hBrynx Nice! Thanks for sharing your formula.

[@nairobi203]

@@hBrynx Nice!!! Genius,.... thanks for sharing the code..

[@nairobi203]

@@MyOnlineTrainingHub Nice too. the code would then be (but I had to use RANK.EQ). My data rabge is slightly different than in your video example. =BYCOL(F41:H51;LAMBDA(colRng;TEXTJOIN(" - ";TRUE;FILTER(E41:E51;RANK.EQ(colRng;colRng;0)=1))))

[@Antonio01989]

Great video, thank you! May want to use XMATCH instead of MATCH in future videos as bit better :)

[@MyOnlineTrainingHub]

Thank you! I forget about XMATCH most of the time cause I rarely need to specify the search mode. 😁

[@sigmamale-hp3nu]

simply awesome, incredibly genius))))....thnx dear Linda

[@MyOnlineTrainingHub]

Thanks so much! 😊🙏

[@SriramM289]

Hi.....Write formula once and apply Ctrl+D to copy the same formula across the cells. In what way is ByCol or ByRow is beneficial than 'Ctrl+D' option ?

[@MyOnlineTrainingHub]

Because if you add more columns/rows, you need to go and manually press CTRL+D again. With BYROW/BYCOL, you allow for more rows or columns when you write the formula so you never have to update it. Of course, if you're not adding more rows/columns, then just double click the fill handle to fill down or drag across.

[@francinagoh2541]

Thank you for sharing your knowledge with us. It is incredible cool way of doing.

[@MyOnlineTrainingHub]

Glad you enjoyed it! 😊🙏

[@mogarrett3045]

excellent thank you

[@MyOnlineTrainingHub]

Glad you liked it!

[@DannyHusky33]

This sounds like something new that you've just taught us. However, what if I'm trying to do a computation in a column that's not right next to the data? Would the "BYROW(range,LAMBDA,(nameofrange,calculation(nameofrange)))" example formula work for that?

[@MyOnlineTrainingHub]

Yes. The formula doesn't have to be adjacent to the table. 👍

[@DannyHusky33]

@@MyOnlineTrainingHub Ok, that's great to know, thanks so much!

[@tonysicily2687]

An incredibly powerful function/formula. But to be honest it is prob quicker for most people to simply copy and paste. Love your channel, still building my own dashboard, after way watching your tutorials. The best on KZbin, not just the tech, but the styling are awesome

[@MyOnlineTrainingHub]

Thanks so much! Yes, if it's a one and done formula that you never need to adjust, then just double click the fill handle, but for tables that are growing, then these formulas are quicker. Keep going with your dashboard, I'm sure it'll be worth the effort.

[@nadermounir8228]

Nice and insightful Video 📹 👌

[@MyOnlineTrainingHub]

Glad you think so! 🙏

[@barttitulaerexcelbart9400]

Hi Mynda: excellent explanation of the LAMBDA function and TAKE etc. But I do not see the advantage of this more complex formula relating to the simple table functionality. But even if you can not make a table, you still have to anticipate on the future range. You could also do this with an IF....

[@MyOnlineTrainingHub]

Thanks for watching, Bart! I agree, Tables are awesome, but sometimes tables cannot be used, for example, any formula that spills cannot be placed in a table. You could use IF, is suppose, but you're still copying it down, plus it only takes on of those IF formulas to be edited so it's different from the rest and the integrity of your model is in question. When you write formulas that have one point of entry for a whole column/row/range, you build more robust models. That's not to say you should never use the other formulas, it's simply a case of horses for courses.

[@barttitulaerexcelbart9400]

Thank you Mynda, I will give it a try. It was just that your example(the input table) was easy to transform in a table, But I understand that this is different for array formulas that can not be stored in tables. One other remark: I saw on Linkedin your visual (one pager) of the comparison between Power BI and Excel. This is so good! maybe update it every half year and post it also on KZbin? @@MyOnlineTrainingHub

[@txreal2]

Maybe I could create shortcuts in AutoCorrect as alias for these complex formulas? And just change the colRng?

[@MyOnlineTrainingHub]

Or like Peter suggested, define names for the common LAMBDAs you want to use, e.g. SUM could be called SumLambda, and then it's simply =BYROW(cell range, SumLambda)

[@tihomirhristov77]

Really cool 👌🏻

[@MyOnlineTrainingHub]

Glad you like it 🙏

[@chakralamurali]

Excellent madam

[@MyOnlineTrainingHub]

Thanks so much 🙏😊

[@luigibru8677]

Well explained!

[@MyOnlineTrainingHub]

Cheers! Thanks for watching.

[@ramzamthel8014]

thanks

[@MyOnlineTrainingHub]

Welcome 😊

[@karanpathak7436]

Can we just make a table of it and name them and use them in formula that will keep adding for next row

[@MyOnlineTrainingHub]

Functions that spill can't be used in Tables, if that's what you're referring to by 'making a table of it'. However, you could define a name for the cells you want to reference and use that in BYROW. Alternatively, if you are happy to use Tables, as I showed in the very first example, then you don't need to use BYROW because the table will automatically fill down the formula, as I mentioned at the very beginning. Hope I've understood your question.

[@Kokoro-Gamer]

I experience Excel slowness when pasting new data to formatted table, the table has a lot of formula. Is there any solution?

[@MyOnlineTrainingHub]

Not off the top of my head. I'm happy to take a look at your file to see if there's an obvious reason. You're welcome to post your question and anonymised Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@s1ngularityxd64]

that's so cool 😲

[@MyOnlineTrainingHub]

Thank you!

[@balbonie4788]

Hi Mynda, may I know what tool do you use to make the array formula show value when you hover your cursor to the formula bar just like at 3:20? Thanks!

[@MyOnlineTrainingHub]

It's a new feature in Microsoft 365. If you don't have it, you can use the F9 key to evaluate parts of the formula.

[@balbonie4788]

Thanks Mynda! More power to you!

[@iankr]

Wonderful!

[@MyOnlineTrainingHub]

Many thanks, Ian!

[@odettebellanero206]

Does it work using table range?

[@MyOnlineTrainingHub]

You can't enter dynamic array formulas inside a table, but you can reference table ranges with them.

[@IamTheReaper911]

Just when I think there's nothing else you can teach me..... You go and totally redeem yourself 😉 Along those lines 😉😉 💯😎

[@MyOnlineTrainingHub]

🤣🤣🤣🙏😍

[@billcolton6373]

Unfortunately, I'm not able to have the LAMDA function.

[@MyOnlineTrainingHub]

These functions are currently only available in Microsoft 365.

[@billcolton6373]

@@MyOnlineTrainingHub I have that, but not the business one. My bad, I do have it, it's the automate is not available for me.

[@7absinth]

Only one question. Why? What's for? What changes? Furthermore it is not universal for all Excel versions.

[@MyOnlineTrainingHub]

These formulas spill the results, so you can write the formula in one cell and it will automatically copy down/across and grow as your data grows. New features are never available for old versions of Excel.

[@7absinth]

​@@MyOnlineTrainingHubI see. But what's for? What changes? It does the same math using the classic formulas that are available in Excel. I have never thought that my Excel which is 2021 is old :)

[@Seftehandle]

Hmm, I am rethinking using index match again as most new formulas are connected to arrays

[@MyOnlineTrainingHub]

Yeah, if you don't have 365, then this is a non-starter. That said, if you do have 365 and you're wanting to use INDEX to avoid arrays, then it's good to know that INDEX can also spill results, return arrays in 365. i.e. old functions can now spill if the formula results in an array.

[@apollosht]

This looks like a solution in search of a problem. Cool function but I fail to see how adding this complexity betters my life

[@MyOnlineTrainingHub]

If you spend time updating formulas to accommodate new rows or columns added to a table, then these functions will save you time. If you write a formula once and never touch it again, then there's not a lot of benefit. But these functions can do a lot more than automatically update, as you'll see in my video next week.

[@chineando8078]

Ufff. I feel sometimes excel is this complicated so when you finally crack the code, you feel like the smartest person. This particular function, in my opinion, not worth it. Thanks for the guide though, most of the times I save your videos in Excels favorite to have a library of useful functions

[@MyOnlineTrainingHub]

Awesome to hear you find my videos helpful 🙏😊

[@ageloskolaitis7821]

I expected a much easier way to do this

[@MyOnlineTrainingHub]

I know it seems a bit convoluted, but once you get the hang of it, it's easy 😉

[@ageloskolaitis7821]

@@MyOnlineTrainingHub thank s for the reply. Keep up the good work!

[@brightdaniel1830]

First to comment

[@MyOnlineTrainingHub]

Thanks for watching!

[@cgi2173]

Yes, but if it is relevant to what you already use, what is wrong with doing it?

[@cgi2173]

Then, if I continue to watch your video and read the comments where I get the gist....and then I understand what you mean. 🙃🙄🤣

[@MyOnlineTrainingHub]

Thanks for watching and questioning. Glad it’s clear now 😊

[@ricksmithy6990]

She looks prettier than the morning dew gleam off the meadow valley sunrise in that outfit.

[@MyOnlineTrainingHub]

Aw, you're too kind 🙏

[@steven.h0629]

👍😎✊

[@MyOnlineTrainingHub]

🙏😊

[@arindambhattacharya7848]

Sorry..it is more complex. Traditional copy paste is much better

[@MyOnlineTrainingHub]

For one time copy and paste, maybe, but if you’re adding data to a table and need to drag the formula down, then this is more efficient.

[@Alex-bf9ro]

I think powerquery can already replace all these useless formulas

[@MyOnlineTrainingHub]

Sounds like I didn't do a very good job of explaining why you might want to use these formulas, Alex. Yes, you can do what BYROW does (to a degree) in Power Query, you can also do it with VBA and Office Scripts. You can't do the row totals in Power Query. Plus, Power Query requires a refresh to update the calculations, whereas formulas are instant. Don't get me wrong, I love Power Query, but I also love these new functions and both have their benefits, but which is better depends on the scenario.

[@Alex-bf9ro]

@@MyOnlineTrainingHub powerquery is limitless, you can append multiple rows in just a single wokrksheet by using folder as a source, the refresh could be done by setting refresh when open the worksheet. in my opinion working with formulas is outdated , m language is just more powerful

[@johncowie3092]

NOT SIMPLER!!!!!!!!!!!

[@TGithinji]

Learn about lambda functions and it will become easier to comprehend.

[@MyOnlineTrainingHub]

I agree, for one and done formulas, just copy them down, but if you've ever had to update formulas every week/month for reports then this is a huge time saver and results in a more robust report, less prone to error.

[@johncowie3092]

Pedagogical absolute nonsense

[@huseyinburaktasci4668]

Thank you for sharing! They are literally great formulas. However, I am curious about how the selected range can be extended automatically by formulas as well? :)

[@MyOnlineTrainingHub]

Pleasure! At 2:30 I start to explain how you enable the formula to automatically extend as you add more data. i.e. using the TAKE function.