What a great tutorial and thanks for the accompanying file and references to additional tutorials. You are doing a great job. Thank you for all the help you are providing. Super!!
You are the star ⭐ .. Love you❤. For me your videos are very relevant. I learned so many excel formulas and many more other things from you. Super useful knowledge you are sharing with all of us. Thank you again.
Great summary! Even when you know these functions, there is always something new to learn and new ways to use them. Thanks for demonstrating. Thumbs up!!
I've always found it much easier teaching people things like IF statements etc. using the Function Argument window as (at least in my experience) it's laid out in a way that's easier to understand. I remember when I was starting out on my Excel journey, seeing people write arguments in a single line always confused me but as soon as I was introduced to the function argument window it was like a lightbulb was switched on.
Thanks for the tips ! Subtotal exclude hidden rows from Autofilter and Advanced filter (even if the values aren't in a table, with both 9 and 109 argument) adding 100 to the operator argument only applies to manually hidden rows (or hidden by an outline)
Thank you Mynda for this super useful video! I have been using most of them regularly, but found a lot of value addition and ideas through your examples! Keep up the great work! 😊👍
I find writing pseudo code beforehand helps structure the formula and makes it easier to write. Really good tutorial on how to use these functions. Thanks!
Good video, if you just can be a bit more articulate about how you get tha some important data first and after how the formula functions are applying !
Thanks for your feedback. Although I'm not sure what you mean. Each function has a more in depth video you can watch for a deeper dive. See this post with those videos: www.myonlinetraininghub.com/top-10-intermediate-excel-functions Hope that helps. If you have any questions, please reach out via our Excel Support forum: www.myonlinetraininghub.com/excel-forum
Bwaha I completely forgot about Ben 10. He used to be my sister’s cartoon crush. Great tutorial, currently on maternity leave so it’s nice to recap all the popular functions from time to time. Xx
Hi thanks for the super helpful video. I have one question: why do you use "fill without formatting" instead of "double click" to drag the formula down? thanks.
Because I have formatting in the cells and if I just double click to fill down the first cell's format will be applied to every row and I didn't want that.
Great tutorial, Mynda! Thanks. I regularly use most of these. Exceptions are AGGREGATE (which I basically ignored until now) & GETPIVOTDATA (which I’m using increasingly since you released that tutorial).
5:24 For logical AND() and OR() prefer the mathematical notation, that's easier for huge number of conditions: see examples below: AND() => =IF(([@Popular]="Yes")*([@[Salary $k]] =IF(([@Popular]="Yes")+([@[Salary $k]]
Thank you Mynda for this handy tutorial. Although XLook up is running exactly to find values from the range, it is not spilled for dublicate values in a range like; {“apple”, “orrange”, “banana”, “apple”} {10, 5, 20, 15} - - it returns just 10 not 15. I think it must be improved to be returned results regarding dublicate values in same range. And I would like to learn what the differences between Getpivotdata and Cube formulas are? Thank you
My pleasure, Emre! If you want to spill multiple matches then you can use the FILTER function: ua-cam.com/video/ZCQAweoAdOw/v-deo.html GETPIVOTDATA requires a PivotTable to be in the workbook to reference. You can use GETPIVOTDATA with both regular and Power Pivot PivotTables aka Data Model. CUBE functions only work with OLAP cubes e.g. the Data Model/Power Pivot and they reference the data directly from the data model, i.e. you don't need to build a separate PivotTable.
@@MyOnlineTrainingHub I need to learn about Cube functions because nobody knows its properties and it has magic properties in order to prepare dashboards easily. Your tutorials and lectures are so impressive and I admire your topics and clear expressions. I will keep progressing your tutorials. Thanks a lot again, Mynda
Hi Mynda! Thanks for this! And as usual, there was an easter egg in there... or part of one... that had nothing to do with the actual theme! You were building a formula and referencing a cell that was buried under the formula building dialog and you clicked a cell in the desired column a row or two below and then somehow moved it up to the correct row that was under the dialog... how did you get it to move?
I discovered that the VLOOKUP between 2 workbooks is fine if both files on locally stored. However, when both workbooks are in the OneDrive folder the VLOOKUP does not appear to work in the same manner. I'm not sure if you are able to recreate this and do a video on how users are able to correct the problem.
Hi John, in order to reference external files in Excel Online you must use the URL reference, rather than the folder path reference you might be used to when working in the Excel desktop app. You can see an example of the URL reference by copying a cell from one file and pasting it as a link in another file while working in Excel Online.
@@MyOnlineTrainingHub Perhaps I didn't clarify the problem. I am using Excel 365 on my computer (not online). When I have 2 workbooks in a local directory the VLOOKUP works between the workbooks. However, when I move these workbooks to my local OneDrive folder the VLOOKUP does not work. I will endeavour to replicate the problem so I can tell you the error I am getting.
You can override the table structured references manually by typing the cell references in, but there's no way to turn them off or convert the formula to A1C1 style automatically. IMO structured references are way better because they're more intuitive and make the formula easier to read and write.
Trivial, but XLOOKUP looks to me like an improved version of LOOKUP (in the same way XMATCH is an updated MATCH) - the syntax of the first three arguments is exactly the same as LOOKUP. VLOOKUP syntax is quite different (and loathsome IMO!). Funny thing is I have seen people using IFERROR with XLOOKUP a fair bit.
Yes, XLOOKUP is an improvement on all lookup functions 😊 Good point about XLOOKUP not needing IFERROR/IFNA, I should have mentioned that for those who don't realise it's built in.
OR means that you are looking for something that fulfils one or more of your criteria. AND means you are looking for something that fulfils _all_ of them.
Hi Mynda. 'Top 10 Most Important Intermediate Excel Functions?' That is brave of you! I suspect it depends upon the areas in which one works, personal programming style and, most importantly, the version of Excel one is targeting. Like one of your earlier respondents, VLOOKUP has always been on my 'avoid' list; it has its place, just not on my worksheet. I always found 'in-place' filtering to be a cheap and nasty technique (I do not wish to indulge in baby games of peek-a-boo with my data) so, for me, the SUBTOTAL tricks are of little relevance. Nowadays, I always use the FILTER function (that you did identify) to ensure the workbook shows what I intend and does not depend on the state of filters. Back in the day, this involved Advanced Filter with Extract, which was a bit of a mission. Now, for me, it is the Lambda that is overwhelmingly the most important function. It comes at a price, I have had to consign all legacy versions of Excel to the trash can along with the steam-driven abacus! Recently, I had the rewarding experience of taking a previously written Lambda function and copying it to another workbook and solving a challenging problem in one step. chandoo.org/forum/threads/calculating-a-moving-weighted-average-during-resource-scheduling.38231/page-3 Still, your title stated 'intermediate' and perhaps that cuts out LAMBDA, its little brother LET and the support crew SCAN, REDUCE, MAP and MAKEARRAY.
😁 Brave, maybe. I was pitching the intermediate level at those people who think they have advanced Excel skills when they actually are closer to beginner level, so LET and LAMBDA are definitely advanced. IMO VLOOKUP is an important stepping stone function for those learning Excel to discover some of its power, but of course XLOOKUP or INDEX & MATCH are better.
Mynda, you are the star of UA-cam ~~ I'm serious. Your tutorials are fact filled and so well presented. Thank you for all of your hard work.
Aw, thanks so much, Al 😊
What a great tutorial and thanks for the accompanying file and references to additional tutorials. You are doing a great job. Thank you for all the help you are providing. Super!!
You're very welcome, Dale!
You are the star ⭐ .. Love you❤. For me your videos are very relevant. I learned so many excel formulas and many more other things from you.
Super useful knowledge you are sharing with all of us. Thank you again.
Thanks for your kind words 😊
Great summary! Even when you know these functions, there is always something new to learn and new ways to use them. Thanks for demonstrating. Thumbs up!!
Thanks for your support, Wayne!
Your videos got me promoted in my firm this year so thank you!!!!
Wow! Congratulations, Mingddo 🙌 so pleased I could help.
I've always found it much easier teaching people things like IF statements etc. using the Function Argument window as (at least in my experience) it's laid out in a way that's easier to understand. I remember when I was starting out on my Excel journey, seeing people write arguments in a single line always confused me but as soon as I was introduced to the function argument window it was like a lightbulb was switched on.
Good to know, Andrew. Thanks for sharing.
am addicted now ;) Great simplified explanations that stick. thank you so much! great presentations
Thanks for your kind words 🙏
Thanks for the tips !
Subtotal exclude hidden rows from Autofilter and Advanced filter (even if the values aren't in a table, with both 9 and 109 argument)
adding 100 to the operator argument only applies to manually hidden rows (or hidden by an outline)
Glad you liked them 😊
Never learned so much in 24 minutes before- Thanks Mynda
Awesome to hear, Philip!
Thank you Mynda for this super useful video! I have been using most of them regularly, but found a lot of value addition and ideas through your examples! Keep up the great work! 😊👍
Thanks for your support, Vijay!
Excellent tutorial and the use of the index and match and also the subtotal - 10/10
Glad you liked it! 🙏
I find writing pseudo code beforehand helps structure the formula and makes it easier to write. Really good tutorial on how to use these functions. Thanks!
Great idea 👍
Thanks a lot Mynda, for such a useful and nicely demonstrated excel tutorial video.
Glad it was helpful, Shiffa!
Good video, if you just can be a bit more articulate about how you get tha some important data first and after how the formula functions are applying !
Thanks for your feedback. Although I'm not sure what you mean. Each function has a more in depth video you can watch for a deeper dive. See this post with those videos: www.myonlinetraininghub.com/top-10-intermediate-excel-functions Hope that helps. If you have any questions, please reach out via our Excel Support forum: www.myonlinetraininghub.com/excel-forum
These are very useful functions. Great. Thank you Mynda!
Glad you like them, Luciano!
Bwaha I completely forgot about Ben 10. He used to be my sister’s cartoon crush. Great tutorial, currently on maternity leave so it’s nice to recap all the popular functions from time to time. Xx
😁I found this dataset in an old file I created when my kids were small!
Hi thanks for the super helpful video. I have one question: why do you use "fill without formatting" instead of "double click" to drag the formula down? thanks.
Because I have formatting in the cells and if I just double click to fill down the first cell's format will be applied to every row and I didn't want that.
Many thanks for this video, very clear, I wish you to be close to me all my work day long :) you make my work life easier thanks from France
You are most welcome, Cath!
Excellent Mynda. I rarely use AGGREGATE but I can definitely see uses for this. Thanks!
Thanks, Chris! That's great to hear.
Mynda, thank you so much for your videos!
You are most welcome!
Very useful tutorial 👍 thank You
Great to hear!
Great tutorial, Mynda! Thanks. I regularly use most of these. Exceptions are AGGREGATE (which I basically ignored until now) & GETPIVOTDATA (which I’m using increasingly since you released that tutorial).
Great to hear, Jim! Have fun with AGGREGATE 😉
Thank you Mynda! You are gorgeous as always!
Glad it was helpful 😊
Hi Mynda!Great Tutorial On These Really Important Functions...Thank You :)
Thanks so much, Darryl!
As usual, a great presentation. thank you
Thanks so much, Thomas!
5:24 For logical AND() and OR() prefer the mathematical notation, that's easier for huge number of conditions: see examples below:
AND() => =IF(([@Popular]="Yes")*([@[Salary $k]] =IF(([@Popular]="Yes")+([@[Salary $k]]
You must be old school, Roger 😊 by all means use whatever is easiest for you as there's no efficiency gain either way AFAIK.
Thank you Mynda for this handy tutorial.
Although XLook up is running exactly to find values from the range, it is not spilled for dublicate values in a range like;
{“apple”, “orrange”, “banana”, “apple”} {10, 5, 20, 15} - - it returns just 10 not 15. I think it must be improved to be returned results regarding dublicate values in same range.
And
I would like to learn what the differences between Getpivotdata and Cube formulas are?
Thank you
My pleasure, Emre! If you want to spill multiple matches then you can use the FILTER function: ua-cam.com/video/ZCQAweoAdOw/v-deo.html
GETPIVOTDATA requires a PivotTable to be in the workbook to reference. You can use GETPIVOTDATA with both regular and Power Pivot PivotTables aka Data Model. CUBE functions only work with OLAP cubes e.g. the Data Model/Power Pivot and they reference the data directly from the data model, i.e. you don't need to build a separate PivotTable.
@@MyOnlineTrainingHub I need to learn about Cube functions because nobody knows its properties and it has magic properties in order to prepare dashboards easily.
Your tutorials and lectures are so impressive and I admire your topics and clear expressions.
I will keep progressing your tutorials.
Thanks a lot again, Mynda
Thanks for your kind words and support, Emre 😊
Really valuable content. Thank you.
Glad you think so!
Very useful as usual
Great to hear, Alexander!
Nice and easy, thanks for sharing!
Thanks for watching, Karina!
This is great.I love this tutorial.😄
Thank you! Cheers!
Excellent dear, excellent!!!
Thank you very much!
Hi Mynda! Thanks for this! And as usual, there was an easter egg in there... or part of one... that had nothing to do with the actual theme! You were building a formula and referencing a cell that was buried under the formula building dialog and you clicked a cell in the desired column a row or two below and then somehow moved it up to the correct row that was under the dialog... how did you get it to move?
I used the arrow keys to move to the correct cell 😉 Glad you found that useful.
@@MyOnlineTrainingHub Arrow keys… of course! don’t I feel silly now! Thanks Mynda!
thanks for the wonderful content
hey Dear ! Check my channel as well related to MS Excel
Glad you enjoy it!
Thanks very useful. So much to learn, so little time :-)
Chip away John. You'll get there 😊
how do you get the formuals to autofill down the column ? is that a key combination?
Double click the bottom right of the cell containing the formula you want to copy down.
I discovered that the VLOOKUP between 2 workbooks is fine if both files on locally stored. However, when both workbooks are in the OneDrive folder the VLOOKUP does not appear to work in the same manner. I'm not sure if you are able to recreate this and do a video on how users are able to correct the problem.
Hi John, in order to reference external files in Excel Online you must use the URL reference, rather than the folder path reference you might be used to when working in the Excel desktop app. You can see an example of the URL reference by copying a cell from one file and pasting it as a link in another file while working in Excel Online.
@@MyOnlineTrainingHub Perhaps I didn't clarify the problem. I am using Excel 365 on my computer (not online). When I have 2 workbooks in a local directory the VLOOKUP works between the workbooks.
However, when I move these workbooks to my local OneDrive folder the VLOOKUP does not work.
I will endeavour to replicate the problem so I can tell you the error I am getting.
Good explain
Glad you liked it 😊
Love the tshirt!
Thank you 😊
Is it possible to view the cell references instead of the table column names, e.g., =IF(AND(B6="Yes",C6
You can override the table structured references manually by typing the cell references in, but there's no way to turn them off or convert the formula to A1C1 style automatically. IMO structured references are way better because they're more intuitive and make the formula easier to read and write.
Trivial, but XLOOKUP looks to me like an improved version of LOOKUP (in the same way XMATCH is an updated MATCH) - the syntax of the first three arguments is exactly the same as LOOKUP. VLOOKUP syntax is quite different (and loathsome IMO!).
Funny thing is I have seen people using IFERROR with XLOOKUP a fair bit.
Yes, XLOOKUP is an improvement on all lookup functions 😊 Good point about XLOOKUP not needing IFERROR/IFNA, I should have mentioned that for those who don't realise it's built in.
Damn... I saved it to watch later ...why didn't i watch it ealrier. Sorry Mynda. Great video...
😁 Glad you liked it!
Thank you or sharing!
My pleasure!
Can anyone help me the download the excel file? When clicked the link it took me a website but there is no excel file there.
The link is under the video on that page. You'll see a heading 'Download Workbook' and instructions on how to download.
@@MyOnlineTrainingHub 🙏 Thanks
Is there any way to add xlookup in office 2016
No, sorry.
Thanks
My pleasure 😊
hey Dear ! Check my channel as well related to MS Excel
lieber Lehrer, in plain English, what's the exact difference between AND & OR? Logically seem quite sim.
OR means that you are looking for something that fulfils one or more of your criteria. AND means you are looking for something that fulfils _all_ of them.
@@tmb8807 awesome 🙏 ! My mother language is German that’s why I was confused
download links are not in the description.
Oops, it's there now and here to save you looking: www.myonlinetraininghub.com/top-10-intermediate-excel-functions
Great
Thanks, Naveen!
Maybe this will help me get a legal job before 30
Hope it helps! All the best with your search.
Hi Mynda. 'Top 10 Most Important Intermediate Excel Functions?' That is brave of you!
I suspect it depends upon the areas in which one works, personal programming style and, most importantly, the version of Excel one is targeting. Like one of your earlier respondents, VLOOKUP has always been on my 'avoid' list; it has its place, just not on my worksheet. I always found 'in-place' filtering to be a cheap and nasty technique (I do not wish to indulge in baby games of peek-a-boo with my data) so, for me, the SUBTOTAL tricks are of little relevance. Nowadays, I always use the FILTER function (that you did identify) to ensure the workbook shows what I intend and does not depend on the state of filters. Back in the day, this involved Advanced Filter with Extract, which was a bit of a mission.
Now, for me, it is the Lambda that is overwhelmingly the most important function. It comes at a price, I have had to consign all legacy versions of Excel to the trash can along with the steam-driven abacus! Recently, I had the rewarding experience of taking a previously written Lambda function and copying it to another workbook and solving a challenging problem in one step.
chandoo.org/forum/threads/calculating-a-moving-weighted-average-during-resource-scheduling.38231/page-3
Still, your title stated 'intermediate' and perhaps that cuts out LAMBDA, its little brother LET and the support crew SCAN, REDUCE, MAP and MAKEARRAY.
😁 Brave, maybe. I was pitching the intermediate level at those people who think they have advanced Excel skills when they actually are closer to beginner level, so LET and LAMBDA are definitely advanced. IMO VLOOKUP is an important stepping stone function for those learning Excel to discover some of its power, but of course XLOOKUP or INDEX & MATCH are better.
Thanks
My pleasure, Ali 😊