NEW Excel Formulas You Need to Know

5 New Excel Formulas You Should Know

In this video we go over some of the best new excel formulas Microsoft has released. These include the CHOOSECOLS formula which we can use th choose specific columns from a table. The TEXTSPLIT & TEXTJOIN to split and merge columns of text. The REGEX functions (REGEXTEST and REGEXEXTRACT) to extract text from an email and test if a certain condition is true. The GROUPBY function to group values and replace pivot tables, and the AI.FILL and AI.EXTRACT functions using an Excel add in. Finally, we have a bonus formula called DATEDIF to find the difference between 2 dates.

LEARN:

SOCIALS:

▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬

Chapters:
0:00​ -​ CHOOSECOLS
1:18​ - Adding SORT
1:53​ - Adding TAKE
2:20​ - TEXTSPLIT
3:50 - TEXTJOIN
5:55​ - REGEXTEST
6:59​ - REGEXEXTRACT
7:50​ - GROUPBY
8:56​ - PERCENTOF
9:38​ - AI.FILL
10:43​ - AI.EXTRACT
11:20​ - DATEDIF

[arnonomdo]

I still think the function should be called "DATE-DIF, " not "DATED-IF"

[antoniobacic]

Beautiful, thank you Kenji.
The TAKE + SORT + CHOOSECOLS is very helpful.

[xaviergrewal]

Kenji, your videos are brilliant! Crisp and absolutely easy to follow👌🏽🙏🏽

[christianwinter]

Good to know, that the TEXTSPLIT function can take a list of delimiters as an argument and not only one. That was new for me👍

[JHatLpool]

Nice demonstrations of some amazing new features. The capability of Excel is now unbelievable.

[im.nurhadi_]

These combination of Take, Sort and Choosecol can be use as alternative of GSheet Query on Excel,
A real game changer on Excel formulas,
Thanks!

[sallyho]

Thanks. Can you do a video in comparing 1st and 2nd and what is the easiest:best way to extract difference?

[radiali]

Really your taking us to super levels, than and many thanks

[garybaotutorial]

Thank you for this. I am wondering which tools you are using for making this wonderful videos.

[rajsingh]

It seems we are not even using 10% of excel functions. Thanks for the info

[islammohsen]

To extract the Emails, you can use Ai.Extract formula better as you explained it in previous video. I would like to thank you, i learn from you a lot ❤❤

[radiali]

correction : "Really you are taking us to super levels, thanks and many thanks"

[JoseAntonioMorato]

Dear Kenji,
Spilled formula and with a header it is better:
=REDUCE({"First Name", "Last Name", "Age", "Outcome"}, B3:B14, LAMBDA(a, b, VSTACK(a, TEXTSPLIT(b, {" ", ", "}, , 1))))

And TEXTJOIN with the right delimiters is better too:
=TEXTJOIN({" ";", ";", "}, , C3#) 🤗

[sivabvbs]

Is regex under beta version? Coz as an employe with subscription to ms365 I am no getting the =regex while using. Can anyone help how to enable

[wallywechs]

Hello Kenji, please share Excel version you are using with those updated formula.

I need that .exe file to be on same page with you

[calilthomas]

I Still don´t have these functions and my account is an insider one. What can I do to have them? Already updated excel several times.

[willzinner]

do you know if the AI one will become a part of Excel (not an addin) at some point?

[jezzzNL]

will all this work in MS Office Home or only for MS 365?

[Dawoodkhan-ryss]

Sir from where should I download free updated MS Office for long time

[joblesstess]

create a regex to help me identify if there are numbers in a list of