I find it amazing how time passes and things change. Just watched the video and will assist on numerous chores. I always like to think I am near the top of my tree when it comes to Excel and you catch up on some history and you realise how little you know. Great video and well put together.
Thank you for summarizing these new functions. I recently got a new computer and almost immediately bought MSFT Office 2019 and have been playing with Excel 2019 for a while now. These new functions are very useful and will simplify what we did before in earlier versions of Excel.
How To Excel, I’m not a fan of Microsoft’s subscription software model. I and many companies don’t purchase every new version of Excel, instead we skip the next one and get caught up with the one after that. I have 2019, but the firm I work for has 2016.
Hi John.. thanks for the great summary of the 6 new functions in EXCEL 2019. As always, informative, engaging and on point. Thanks for sharing your knowledge. Thumbs up!!
Are all these functions in excel 365. I particularly like the Ifs. When taking over or auditing another's spreadsheet, nested ifs are a nightmare to unwind/ understand.
Textjoin and IFS seem to be the main useful ones. Anyone could create these functions in vba as well in earlier versions if they wanted that functionality
Buddy, I made 2376 data and each data having hyperlink at relevant column to show link file scanned invoice as pdf or image format But, if some reason drive location renamed or relocate folder (but the content files name remains same), that's why hyperlink on 2376 rows becomes waste. Is any facility to put new hyperlink on each 2376 rows with less efforts?
Kind of. But with IF or IFS function you need to evaluate an expression that evaluates to either true or false. With SWITCH your expression can evaluate to anything.
Thanks for this. Question for the IFS--you didn't put in ranges for the grades because the function evaluates from L to R, and stops when it gets a 'TRUE" responses. Is that correct?
I am a student of BA and want to work in Excel. Is there any future job in Excel? Please sir guide me and give me suggestion. How to improve your skill in Excel to advanced level?
Great Video once again John. Short, Sharp and Precise. Exactly what I need. Do you have any info on when Dynamic Arrays, Xlookup and XMatch will be released? I know that only certain insider subscribers have access for now...
I believe XLOOKUP has rolled out to all insiders as of now and anyone can sign up for insiders. No idea when this will all be released to everyone outside of insiders. Dynamic arrays are already a year old on insiders.
IFS you need to test an expression that evaluates to either true or false (boolean), SWITCH you can test an expression that evaluates to anything (numbers, text, dates or boolean).
In my french version Excel 2007 I have a function named CONCATENER that is included and described (info bubble is there) and supposdly do the same as CONCAT. It never worked. Doesn't mattter much now since I don't need that much as a retiree.
How To Excel .... You could almost as quickly select a range of cells using concatenate albeit you selected them individually. It might be marginally quicker but doesn’t add a lot of useful functionality
I downloaded Office 2019. Which I activated through kms. When I open excel, office 365 is written and when I go to the account and see it, office 2016 is activated there in the product key. While the formulas in it seem to be 2019 ones. After a few days when I opened excel, its display was like office 365. And after opening a few days later, it again became office 2016. And today I uninstalled it and installed Office 2019 back. So I want to know why all this happened. sorry for my bad english
How is concat different from concatenate? Your example produces a piece of overloaded data, which is not good practice. "IFS" looks useful. Nested ifs are a pain.
Good video. But the new functions are pretty useless as one can achieve same things with existing functions. Hey Microsoft - where is this so much awaited XLOOKUP function?
@@HowToExcelBlog Sure it is a little easier. But just compare how often people use multiple IF functions compared to the VLOOKUP function, that still after 25 years, as Will says, can not work on an unsorted range and/or pick up values on left of the range column.
Why didn't you state this in your video instead of saying these are NEW Functions? Make sure you tell the details truth and the background of any function. If it's new it's new if it's old it's old or old but upgraded. Sincerely Excel expert
The more you use any but the most basic cell formula, the worse your worksheets are going to be. Don’t put off learning VBA any longer. You’re digging a hole for yourself.
VBA is a language from 1995 with no real future. There are much better tools available today. Focus on power query, DAX, new office scripts, power automate etc. It's the way of the future.
Yeah well anything except writing unwieldy formulae into cells and then propagating those cell formulae in every direction. I cannot believe how stupid it is to replicate logic all over the place, and even worse, base that logic on rectangular data structures that probably are dodgy in the first place.
Check out my full courses and ebooks here
👉 www.howtoexcel.org/courses/
I find it amazing how time passes and things change. Just watched the video and will assist on numerous chores. I always like to think I am near the top of my tree when it comes to Excel and you catch up on some history and you realise how little you know. Great video and well put together.
Thank you for summarizing these new functions. I recently got a new computer and almost immediately bought MSFT Office 2019 and have been playing with Excel 2019 for a while now. These new functions are very useful and will simplify what we did before in earlier versions of Excel.
Ah, you should have got Office 365 for continued new features.
How To Excel, I’m not a fan of Microsoft’s subscription software model. I and many companies don’t purchase every new version of Excel, instead we skip the next one and get caught up with the one after that. I have 2019, but the firm I work for has 2016.
I am grateful for someone whom I can understand and that is clear. Thank You John
You're welcome Sam!
Hi Can you please confirm all the above functions are available in the office 2019 student version ( single time activation ) ??
Yes, but I'd go for Microsoft 365. It's a much better deal.
How to enable the auto save option as shown in the quick access toolbar..??
Hi John.. thanks for the great summary of the 6 new functions in EXCEL 2019. As always, informative, engaging and on point. Thanks for sharing your knowledge. Thumbs up!!
very clear and concise and great pacing, not too fast or slow. Thank you
Thanks for the positive feedback Brendan.
Are all these functions in excel 365. I particularly like the Ifs. When taking over or auditing another's spreadsheet, nested ifs are a nightmare to unwind/ understand.
Yes, they are in Excel for Office 365 as well as Excel 2019. Office 365 will have all the latest and greatest features.
@@HowToExcelBlog great.... Thanks for the reply. Happy new year !!
Textjoin and IFS seem to be the main useful ones. Anyone could create these functions in vba as well in earlier versions if they wanted that functionality
But sharing VBA user functions is problematic.
@@HowToExcelBlog exactly and tobe honest I hate vba
Buddy, I made 2376 data and each data having hyperlink at relevant column to show link file scanned invoice as pdf or image format
But, if some reason drive location renamed or relocate folder (but the content files name remains same), that's why hyperlink on 2376 rows becomes waste.
Is any facility to put new hyperlink on each 2376 rows with less efforts?
Yes, I think HYPERLINK gets messed up with files in OneDrive. I don't know the solution (if there is one).
Nice, clearly explained and demonstrated. Is SWITCH basically the same as IFS, but with the option to have a default value?
Kind of. But with IF or IFS function you need to evaluate an expression that evaluates to either true or false. With SWITCH your expression can evaluate to anything.
So SWITCH is like a more generalised version of IFS.
Oh, and thanks for the great feedback!
@@HowToExcelBlog Thanks for the extra info and no problem; gotta give credit where credit is due.
Thanks for this. Question for the IFS--you didn't put in ranges for the grades because the function evaluates from L to R, and stops when it gets a 'TRUE" responses. Is that correct?
Yes, exactly. That's also why I am starting with >79 as the first set of conditions.
Is it more efficient calculation wise to use IFS target than nested IF? Thanks
Good question, but I don't know the answer.
The IFS function is a lifesaver, nested IF can be really nasty in some occasions
Yes, I agree more than 3 IF functions and I start to lose track.
I am a student of BA and want to work in Excel. Is there any future job in Excel?
Please sir guide me and give me suggestion. How to improve your skill in Excel to advanced level?
Learn power query, power pivot and DAX in Excel, then learn power BI.
@@HowToExcelBlog Thank you sir
I use VisiCalc. Should I consider upgrading to Excel 2019?
😂
No, VisiCalc is great, stick with it😀
Great Video once again John. Short, Sharp and Precise. Exactly what I need. Do you have any info on when Dynamic Arrays, Xlookup and XMatch will be released? I know that only certain insider subscribers have access for now...
I believe XLOOKUP has rolled out to all insiders as of now and anyone can sign up for insiders.
No idea when this will all be released to everyone outside of insiders. Dynamic arrays are already a year old on insiders.
Thank you dear it very very useful!!!!!
Thanks! Glad you got some use from it 🙂
when do you think the xlook up function will be available to the public?
Sorry, I don't have any idea myself.
But it will only be available in Office 365, not Excel 2019
What's the difference between ifs and switch, beside switch having a default. I haven't played with those functions yet, so thought I would just ask.
IFS you need to test an expression that evaluates to either true or false (boolean), SWITCH you can test an expression that evaluates to anything (numbers, text, dates or boolean).
Great very helpful
Glad it helped you!
*No more nested IFs? Yay!* ♥♥♥
Yes! IFS or SWITCH will do the trick!
Thank you for the informative and to-the-point video :)
No problem Amr!
Will xlookup be available for office 2019 in future...
Nope. Only Office 365.
If they release an Excel 2022 it will likely be in that version.
xlookup is great... worth getting 365 for that and many others
is this function available in excel 365 (16.0.11929.20978)? because it gives me #NAME error for LET
It might still be in insider version only.
techcommunity.microsoft.com/t5/excel-blog/let-names-in-formulas-generally-available/ba-p/1878903
how come my excel 2019 doesnt have insert (picture) feature?
Are these also in Excel 365?
Yes, Office 365 is always the most up-to-date version.
Can you zoom in on the formula, it's too small for mobile phone view
Sorry, the video has already been published.
In my french version Excel 2007 I have a function named CONCATENER that is included and described (info bubble is there) and supposdly do the same as CONCAT. It never worked.
Doesn't mattter much now since I don't need that much as a retiree.
textjoin rulz
@@theRealDavidn Don't have that textjoin function.
how do we do in vertical data
Thanks sir for new update
No problem Subrat!
Thank you so much 👍 🌟 🌟
No problem KD!
How is the "Concat" function different from the "Concatenate" function?
Can reference a range of cells vs only individual cells.
Exactly my thought - the 'TextJoin' and 'Concat' functions add very little to the old 'concatenate' function which has been in Excel for decades
I have to disagree, they add a lot of possibilities you couldn't do with CONCATENATE.
How To Excel .... You could almost as quickly select a range of cells using concatenate albeit you selected them individually. It might be marginally quicker but doesn’t add a lot of useful functionality
Ha, try it with a range of 20 cells and adding a comma to separate the list.
Excel 2019: TEXTJOIN, MAXIFS, MINIFS
Me: Hmm nice
Excel 2019: CONCAT, TEXTJOIN, IFS, SWITCH
Me: FINALLY OH MY GOODNESS WHY DID IT TAKE SO LONG???
Thank you Sir! GREAT Stuff!
No problem. Glad you enjoyed it!
I downloaded Office 2019. Which I activated through kms. When I open excel, office 365 is written and when I go to the account and see it, office 2016 is activated there in the product key. While the formulas in it seem to be 2019 ones. After a few days when I opened excel, its display was like office 365. And after opening a few days later, it again became office 2016. And today I uninstalled it and installed Office 2019 back. So I want to know why all this happened. sorry for my bad english
I'm not sure. Are you signed into multiple accounts like a work and personal?
@@HowToExcelBlog no i didn't sign in in anything
How is concat different from concatenate? Your example produces a piece of overloaded data, which is not good practice. "IFS" looks useful. Nested ifs are a pain.
CONCAT allows you to reference a range whereas CONCATENATE only allows you to reference individual cells.
Examples are just to demo the function 😂
I noticed that all of the 6 functions are present in LibreOffice since at least version 5.2. I wonder who's leading the game...
I'd imagine it comes to Excel first.
great I am waiting for more
Wow, excel has caught up with 1995 Filemaker Pro. Although I do like that "ifs" function.
Filemaker is sheer garbage, Filemaker doesn't understand database theory, Filemaker is like having a cute key to a junk car
@@ach301176 So wrong but that's okay. You don't own it, you don't know it.
Awesome tricks
Thanks Mohan!
Thanks friend
No problem!
1. CONCAT
2. TEXTJOIN
3. MAXIFS
4. MINIFS
5. IFS
6. SWITCH
Yep! 😃
Is there anyone using or have access to the LET Function?
It's new, so only on office insiders. It won't be widely used for a while.
no coverage on xlookup?
XLOOKUP is new in Excel for Office 365 and not available in Excel 2019.
=c3&d3&e3 also works for concat
Yes, but it's a bit harder to enter. Imagine trying to join a range of 20 cells that way.
@@HowToExcelBlog Sure if it's many cells than the advantage of Excel 2019 would be great. Thanks for the video!
Sir, please make a video explaining how to create an excel to tally data export utility on our own. Eagerly awaiting!
Hmm, not sure I know what this means? 🤔
Rather use IF function as opposed to Switch
Xlookup?
Definitely one the learn, but it wasn't introduced in 2019.
OK, I'm sorry but I got stuck where the "deluxe" pizza only has four toppings.
😂
So what's on your deluxe pizza anyway?
So there is no "ELSE" in the "IFS" function? Kind of weird.
Yep, I would agree. But you could use an IF function as your last IFS argument to get an else.
Could also use SWITCH(TRUE,... for something like an IFS with else.
I thought it obvious that the "default" value was the "Else" in the IFS function.
", TRUE, )" placed at the end of an IFS function will behave like an ELSE.
@@gregoryrodgerspowers8755 great tip. Thanks!
IFS - no more checking all of the parenthesis are in the right place... IFS rules! Switch = Case - hoorah!
Certainly easier 😀
why not just Ctrl+E ?
Flash fill will not update like a formula will, so it depends on the situation.
Good video. But the new functions are pretty useless as one can achieve same things with existing functions. Hey Microsoft - where is this so much awaited XLOOKUP function?
You might be able to do the same things, but not as easy.
@@HowToExcelBlog Sure it is a little easier. But just compare how often people use multiple IF functions compared to the VLOOKUP function, that still after 25 years, as Will says, can not work on an unsorted range and/or pick up values on left of the range column.
👍
you spelled tomatoes wrong.. use spellcheck too
🙄
Video does not have proper quality not able to see it clear
Change settings to 1080p
@@HowToExcelBlog thanks
No problem 👍
Great tutorial- but it's 'TOMATOES'!!! :)
🍅?
finally, some python functions built into excel
Which one?
@@HowToExcelBlog concat, join,
i use TEXTJOIN almost daily.
It's definitely a great function.
@@HowToExcelBlog It think it's the most underrated Function in excel right now.
text join
It's a good one
I used the concatenate function in 1990, they just shortened the name.
They added the ability to reference a range. Previous function you had to reference individual cells.
Why didn't you state this in your video instead of saying these are NEW Functions? Make sure you tell the details truth and the background of any function. If it's new it's new if it's old it's old or old but upgraded. Sincerely Excel expert
Different function name + different argument signature + different ability = new function to me 🙂
Sooo
Sooooooooo, did you learn something new?
not impressive at all.....simple functions...
Not everyone is an Excel wizard like yourself.
Wtf ... Can you zoom in please
Wtf... It is zoomed in.
...God, I hate Excel.
Nice video. But you talk too much... It could be a simple 5 minutes video. But you made it up to 10 minutes with too much talking
For 6 functions, that's less than 2 minutes per function 👍
The more you use any but the most basic cell formula, the worse your worksheets are going to be. Don’t put off learning VBA any longer. You’re digging a hole for yourself.
VBA is a language from 1995 with no real future. There are much better tools available today. Focus on power query, DAX, new office scripts, power automate etc. It's the way of the future.
Yeah well anything except writing unwieldy formulae into cells and then propagating those cell formulae in every direction. I cannot believe how stupid it is to replicate logic all over the place, and even worse, base that logic on rectangular data structures that probably are dodgy in the first place.