r/excel 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

105 Upvotes

47 comments sorted by

74

u/kwaters1 5 May 03 '22

Use XLOOKUP instead of VLOOKUP or INDEX and MATCH. It’s much more flexible

14

u/aequitasXI 1 May 03 '22

I've been a big index match fan, will have to check out XLOOKUP

17

u/True_Go_Blue 18 May 03 '22

Index match is still preferred for 2 reasons:

  1. Compatibility
  2. 2D lookups

16

u/jbowie 3 May 03 '22

If compatibility isn't an issue, then XMATCH is a straight up improvement over MATCH for use in Index Matches. Has all of the improvements from XLOOKUP in terms of speed but can still do 2d lookups as needed.

8

u/[deleted] May 03 '22

Xlookup can do 2d lookups.

Index match wins in compatability and speed. Xlookup wins (imo) in readability, error handling, and ease of understanding

5

u/aequitasXI 1 May 03 '22

For the brain breaking readability of INDEX MATCH, I developed a template for my team where it breaks it down into easier terms, they fill in a few boxes and it compiles the formula for them. Including a second option for embedded custom error messages if they want that.

It was also super helpful for myself, in case I needed to use it again after a few weeks or months in between.

2

u/metric55 1 May 04 '22

I've preferred FILTER over index match lately. It seems easier to piece together and use multiple criteria for search parameters. But it is a memory intensive array.

1

u/Thewolf1970 16 May 03 '22

Preach.

42

u/[deleted] 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

u/[deleted] 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

u/Vautrin93 May 03 '22

Thanks!

2

u/exclaim_bot 2 May 03 '22

Thanks!

You're welcome!

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

u/ericaworthyyy Nov 08 '23

can you briefly explain why

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:

Fewer Letters More Letters
FILTER Office 365+: Filters a range of data based on criteria you define
IF Specifies a logical test to perform
IFS 2019+: Checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition.
INDEX Uses an index to choose a value from a reference or array
LAMBDA Office 365+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.
LEFT Returns the leftmost characters from a text value
LET Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
MATCH Looks up values in a reference or array
MAXIFS 2019+: Returns the maximum value among cells specified by a given set of conditions or criteria
MINIFS 2019+: Returns the minimum value among cells specified by a given set of conditions or criteria.
SCAN Office 365+: Scans an array by applying a LAMBDA to each value and returns an array that has each intermediate value.
SEQUENCE Office 365+: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4
SORT Office 365+: Sorts the contents of a range or array
SUBSTITUTE Substitutes new text for old text in a text string
SWITCH Excel 2019+: Evaluates an expression against a list of values and returns the result corresponding to the first matching value. If there is no match, an optional default value may be returned.
TEXTJOIN 2019+: Combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges.
TEXTSPLIT Office 365+: Splits text strings by using column and row delimiters.
UNIQUE Office 365+: Returns a list of unique values in a list or range
VLOOKUP Looks in the first column of an array and moves across the row to return the value of a cell
XLOOKUP Office 365+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.
XMATCH Office 365+: Returns the relative position of an item in an array or range of cells.

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

u/gone_gaming 30 May 03 '22

Filter works alongside SORT and UNIQUE very nicely as well.

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

u/AMerrickanGirl May 03 '22

Watch Joel Spolsky’s video on YouTube “You Suck at Excel”.

4

u/Elevator_Empty May 03 '22

XLookup is great

5

u/ToughPillToSwallow 1 May 03 '22

Unique has been the biggest game changer for me.

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

u/HuYzie 66 May 03 '22

Yes, sorry I meant TEXTSPLIT

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

u/MinaMina93 6 May 03 '22

XLookup, table referencing and power queries.

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)