Static to dynamic: Excel formulas for reducing manual work

★ Want to automate Excel? Check out our training academy ★

★ Get the example file ★
Sign up for our free Insiders program and get the example file:

★ About this video ★
Most Excel users update formula-based reports with manual actions (inserting rows, copying and pasting data, copying down formulas, etc.).

But if we use dynamic array formulas in the right way, we can avoid all of those actions and the reports can update themselves.

INCLUDES:
0:00 Introduction
0:14 Example file
0:59 Formulas
6:29 Totals
8:00 Formatting
9:26 Testing
10:04 Wrap-up

★ Download 30 most useful Excel VBA Macros ebook for FREE ★

★ Where to find Excel Off The Grid ★

#MsExcel

[ExcelOffTheGrid]

Sign up for our free Insiders program and get the example file:
File Reference for this video: 0226 Static to Dynamic

[Excelambda]

Great video !! ✌
Alternative for "Days in period" (BYROW):
=MAP(B7#, G7#, LAMBDA(x, y, MAX(MIN(y, EOMONTH(C3, 0))-MAX(x, EOMONTH(C3, -1)), 0)))
"Monthly Cost" :
=LET(v, F7#/H7#*I7#*(D7#="live"), VSTACK(v, SUM(v)))

In general, whenever a vector, vert or horiz, I do not use BYROW or BYCOL, always MAP.
There is an intrinsic functionality difference btw MAP and BYROW when vectors (1D arrays) (in formulas, with references they act the same)
The simplest exercise to visualize and understand the difference
=BYROW(SEQUENCE(4), LAMBDA(x, CONCAT(SEQUENCE(, x))))
delivers:
1
1
1
1
instead of the correct result of MAP
=MAP(SEQUENCE(4), LAMBDA(x, CONCAT(SEQUENCE(, x))))
1
12
123
1234
✌😉

[houstonsam]

Fantastic presentation.

Your recent work explaining application of lambdas, helpers, and general spilled arrays has given me understanding that I put to work immediately in my job and that in turn is helping us ask and answer questions we've not been able to analyze before. I bet you didn't know you were helping a specialty chemical R&D function improve the operational management of its field technical support work.

[huseyinburaktasci]

Fantastic! It is a very advanced example. Thank you Mark!

[ExcelOnTheRoad]

Very nice approach with the VSTACK and the Total!

[ismaelkourouma]

I have been looking the solution of Total line 2 years ago without success.
I am really excited to seen this now. I always think how to automate the recurrent and boring works so this comes at the right time. Now just need to take time and learn from it. Thanks Mark

[patrickschardt]

Wonderful lesson. Showing why things are done a certain way, revisiting logic, and more

For the formatting for the main data, that could also semi-dynamic. Just check if a there’s a value in the one of the columns for each row… then apply the needed formatting for each column

[RonDavidowicz]

Another great video showing practical uses for some advanced functions

…..or you could just add “regular” formulas to your data table and a totals row.😊

[Swizzletwice]

Excellent video. I love how well he explains functions (why and why not) and wrapping formulas.

[excelrobot]

Love it, great example for demonstrating the process of converting something that already existed. The Excel team really needs to make conditional formatting support dynamic Applies To ranges so the formatting can expand with the arrays.

[XAbuBadrX]

Really nice, , the techniques i learned from this Video is so much
Really aprecaite your videos
Thank you

[mohammedelsakally]

Thank you Mark for your always valuable tricks and content that you are providing to us all time ... much appreciated 🙏 ...

[Hello-bnyc]

fantastic. Thank you very much. Great content as always

[KimzCraftz]

Great video with some topics I can apply immediately into my work. Thank you.

[JoseAntonioMorato]

Dear Mark,
Since it's to automate the calculations, I also automated the "End Date" column of the "Date" table:

[alessandragallaogatarossi]

Sensacional!!! Parabéns Mark!!! Adoro seu canal e seu conteúdo! Pena que a cotação reais X dólar não ajuda comprar o seu curso...

[kebincui]

Very creative, excellent as always. Thanks Mark ❤👍

[adhamm]

You are brilliant!! Thanks brilliant your dynamic reports and power query skills are another level and am learning a lot. Can you please tell how to use lambda& byrow with filter function (more than one criteria).

[serdip]

Thanks for another very interesting presentation demonstrating advanced Excel features.

I was thinking that all of the calculations could be done within the Power Query editor, thus eliminating all formulas and conditional formatting. If that's the case, then there would be no need for a separate report tab. Can you advise when it would not be possible to use Power Query alone to get everything done? Thank you kindly.

[BenoitCorbeil-wh]

Just add your total before the titlle, more easy and when to much lines you don't have to go to the end of the line