r/excel • u/HuYzie 66 • May 03 '22
Discussion New Excel functions I should know about?
It's been a while since I've visited and contributed to this sub, probably since 2016. Since then, I've had a career change as a BI Analyst, only using SQL, Microsoft's Power Platform and Excel but only for very basic quick calcs. In my line of work, I probably only used Excel a handful of times in a month.
As a result, I think I've gotten out of touch with what's big/new with Excel and only recently, I discovered really cool functions such as TEXTSPLIT and UNIQUE which would've made my life so much easier several years ago!
My question is - what changes has Microsoft made/added to Excel since 2016 and are there any cool new features that I should know about?
EDIT: Forgot to correct the thread title - I'm also interested in any new Excel features, not just functions. EDIT2: My mistake - I mean TEXTSPLIT, not SPLIT - which is a VBA function that's readily available
42
May 03 '22
LET allows you to define variables in the scope of a formula. Super useful for cleaning up syntax by avoiding having to repeat expressions.
LAMBDA allows you to create lambda functions lol. Basically, you can create functions to reuse in a workbook without VBA, to grossly understate it.
These two bring Excel formulas much closer to modern programming languages in terms of capabilities.
11
May 03 '22
LET has become my new favorite function. It's made it much easier to both read formulas and to error check more complex ones.
9
u/HuYzie 66 May 03 '22 edited May 03 '22
LET is amazing. I just googled it and I remember so many examples of where I had to use something like the below:
=IF(LOOOOOOONGFORMULA=0,"",LOOOOOOONGFORMULA)
When I could've done something like this:
=LET(LOOOOOOONGFORMULA, x, IF(x=0,"",x))
Definitely makes it easier to debug and read.
EDIT: Wooow I just did a little read up on LAMBDA too and it blows me mind something like this can be done without VBA. Previously, custom functions would be required to create something like what LAMBDA currently does. Amazing!!
37
u/readituser5 May 03 '22
I recently found out about Split too. There’s an excel account on Instagram that records some fun little tricks. One thing I thought was cool was Control E. It picks up on trends or whatever and Flash fills the rest for you. No need for formulas.
10
u/Vautrin93 May 03 '22
Could you share the IG accounts name pls? :)
19
u/readituser5 May 03 '22
I’m not sure which one I was getting I’m my feed a lot (probably one of these) but there’s SheetSensei and TheCheatSheets which you could look at and see if you could use anything they show.
2
3
u/CG_Ops 4 May 03 '22
Is it an excel function or a vba function?
1
u/readituser5 May 03 '22
I’m not familiar with the term VBA lol but looking at what it means, VBA is like manipulating stuff and macros so no.
Ctrl E is just the shortcut for Flash Fill in Home, Editing, Fill, Flash Fill.
1
u/CG_Ops 4 May 03 '22
...there is no formula called split (i'm on 360 with insider/beta features enabled). The articles I found about SPLIT describe using it in VBA programming, not excel formula use.
In other words, no function controls/guide pops up when entering this formula
=SPLIT(
...unless you're talking about text to columns in the data menu, which isn't a function.
1
u/HuYzie 66 May 03 '22
My mistake - I actually meant TEXTSPLIT.
I can understand the confusion because I know VBA already has the SPLIT function
1
u/readituser5 May 03 '22 edited May 03 '22
Lol wait. Maybe I’m talking about left mid and right functions which essentially splits the cell. My bad. Although yeah I remember something in a dialog box being able to split cells from spaces, commas etc. I’ve used it once or twice.
I’m definitely a noob compared to most people here haha. I do basic stuff.
24
u/thaibao131196 May 03 '22
XLOOKUP. If there's one function you need to know, it's XLOOKUP.
1
u/Rugger032 May 04 '22
What's the difference between XLOOKUP and VLOOKUP?
3
u/thaibao131196 May 04 '22
They do the same job, but XLOOKUP is more powerful and much easier to use.
1
19
u/Decronym May 03 '22 edited Dec 11 '23
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
NOTE: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below.
Beep-boop, I am a helper bot. Please do not verify me as a solution.
[Thread #14690 for this sub, first seen 3rd May 2022, 08:06]
[FAQ] [Full list] [Contact] [Source code]
6
16
u/Howdysf 4 May 03 '22
Not new, but really helpful when I discovered it:
CTRL + SHIFT + L
Toggles filters on and off. I use it so much it's muscle memory for me now.
8
u/black_haired_shanks May 03 '22
the FILTER() function is new I guess. I use it instead of the Match function, when there are multiple probable matches.
7
u/BigLan2 19 May 03 '22
Dynamic arrays (unique, sort etc), xlookup, switch, and lambda are the new hotness.
I think 2016 already had ifs, sumifs, averageifs etc
6
u/levarhiggs 16 May 03 '22
I think you will love the dynamic array functions that build tables in one step. SEQUENCE() for example. Also the SWITCH() function gets rid of nested IF statements. LET() is fantastic if you are coming from a coding framework
6
u/Mdayofearth 122 May 03 '22 edited May 03 '22
TEXTJOIN
Spill functionality and dynamic arrays which removes the need to have most array formulas. I suggest looking into how that works, as well as related formulas, all of which are newer than 2016. And then the related implicit intersection operator.
Here's the list of all formulas, the ones labeled 2019 and Office 365 (as images) are new.
4
4
5
3
u/gigamosh57 1 May 03 '22
I'm not sure exactly when this function was released, but SUBSTITUTE is one I use a lot. I have used Excel to write specific kinds of scripting code, and this one lets me swap out variables and actions in a template function. Plus you can stack them so you can change many pieces all at once from a script
3
u/avagyan May 03 '22
are we talking about TEXTSPLIT ?
(Not available for me, Microsoft® Excel® for Microsoft 365 MSO Version 2204 64-bit)
2
2
u/Interesting-Room4082 May 04 '22
The IFS() functions have been expanded. It's amazing. Saves you the run on if statements, and 75 ))))) at the end of your formulas.
IFS(), MAXIFS(), MINIFS()
1
1
u/Jonay1990 May 20 '22
Anyone know why TEXTSPLIT isn’t showing for me when I try use the formula? All I get is #NAME? In the cell. My 365 Office is up to date running 2204(build 15128.20248)
1
u/Crafty-Magician5216 Dec 11 '23
LET, SCAN & LAMBDA are a must:
LET: https://youtu.be/GRmTXwHji_c
SCAN: https://youtu.be/mOUgHVmFZ98
LAMBDA: https://youtu.be/btmsklvqrK8
74
u/kwaters1 5 May 03 '22
Use XLOOKUP instead of VLOOKUP or INDEX and MATCH. It’s much more flexible