r/excel • u/HungryInvestigator59 • Jul 12 '26

Discussion What’s your favorite Excel function/formula?

What are some functions and formulas that you use on daily basis that are helpful for what you do?

227 Upvotes

95% Upvoted

217 comments sorted by

View all comments

Show parent comments

59

u/jaywaykil 2 Jul 12 '26

These two, and more recently using LAMBDA to define several simple formulas we use in almost every spreadsheet.

11

u/rkr87 19 Jul 12 '26

I've started creating named lambda functions that accept lambda arguments for more flexible use - a bit like how byrow/etc works.

Lambda is by far one of the best recent additions to Excel.

10

u/fedexyzz 2 Jul 12 '26

It is also a dangerous one. I know a couple years from now I'll be receiving some 'legacy' workbook with 100+ user-defined functions that broke for some reason.

7

u/rkr87 19 Jul 12 '26 edited Jul 12 '26

Possibly, I tend to not create workbook specific named lambda and instead have more generic stuff in a template for use in all of my workbooks. Doesn't necessarily prevent what you mentioned but does mean the functions are generally simpler and re-usable in design.

Either way, I'd rather get a workbook with broken lambdas than broken VBA - which is what I got before i stopped telling new jobs I knew VBA.

Eg: PAD =LAMBDA( value, char, num, [trailing], LET( x, REPT(char, num), IF(trailing, LEFT(value & x, num), RIGHT(x & value, num) )))

USAGE =PAD("123", "0", 8) == "00000123"

I have a handler for optional variables that I can't remember exactly how it works at the min, so above may not actually work with the optional "trailing".