2 NEW Excel Functions ELIMINATE Copying Formulas 

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

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

Awesome to hear, Chris!

Glad to hear that 🙏😊

Yes, great points. Thanks for sharing 🙏😊

Thanks so much! 😊🙏

Awesome to hear!

My pleasure, Alexander 👍😊

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.

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

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

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

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.

Awesome! Thank you!

Glad you enjoyed it! 😊🙏

Glad you liked it!

Cheers! Thanks for watching.

Glad you think so! 🙏

Thanks so much 🙏😁

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.

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

Glad you like it 🙏

Many thanks, Ian!

Glad you noticed 😁

Hope you can make use of these new functions.

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

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

Great tip!

Indeed a great tip

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.

Welcome 😊

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.

Thanks so much 🙏😊

Thank you!

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

I did that on purpose 😉

Hear hear!

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

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

Thank you very much for your answer.@@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 😉

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

Keep It Short & Simple ❤

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.

Thanks Mynda! More power to you!

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

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

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

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.

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

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.

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

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

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

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.

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 RU-vid? @@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)

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.

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.

Awesome to hear! 😊not crazy 😉

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

🤣🤣🤣🙏😍

These functions are currently only available in Microsoft 365.

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

Thanks for watching!

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.

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.

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.

​@@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 :)

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

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

Awesome to hear you find my videos helpful 🙏😊

🙏😊

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

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

Aw, you're too kind 🙏

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.

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.

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

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

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.

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.