Hi Kenji, on the excle sheet when I was practicing there was an error and I don't know where it originated from. My calculation for time of arrival was showing show negative all the way down. e.g -225 What could be the cause of this? Thank you again for your cooporation
WOW KENJI! I'm improving a lot my EXCEL skills thanks to your courses. My workmates are hallucinating how fast I apply formulas and how quickly I finish my assignments! CHEERS!
Great video, thank you! I’m 57 and I used Lotus 1-2-3 on DOS where you had to tell it how many rows and columns you wanted. I’ve been using Microsoft Office since 1998 and the formulas and features that Excel has is always growing, but it continues to be glitchy. I have to use the Trim(Substitute formula to convert cells to where I can get rid of leading spaces and format them as currency or whatever I want. Your channel has great content but I’m pretty sure you’d get extra subs and a lot of views if you did a video on the 10 best Excel workarounds and how to auto save and find xlsb files that aren’t in Recover files. Those are the things that save time and frustration!
Thanks for the video. A few comments: you can use CTRL + ~ to toggle show formulas on and off if you prefer keyboard vs the mouse click option. I would say that there is a level 6 or higher lookup formula that was not mentioned. Its a combination of indirect + index match. This lets you dynamically select the array in question as opposed to having to choose the array manually. The formula would look like index(indirect(concatenate("C", match(),":","C", match()), False),match(),match()). In this example, you can specify criteria by dynamically creating an array address and then looking up a value within that address based on separate criteria. It's definitely a complex scenario but I've come across this before and needed to break free from hard coding the initial array of the index.
Thank you so much for sharing this, it is about 25 years since I did my Excel training, so this has been a brilliant refresher; as well as learning so much more. Many blessings.
Excellent video. As someone who wrote their own accounts programme in Excel and VBA, I knew all the formulas but didn’t know the level one stuff. Sequence, CTRL, shift down arrow and using the F4 key to lock in the $ sign. Well done!
@KenjiExplains The content you produce is very practical and useful for working with finance. Thank you very much for sharing. I have a question, I am interested in the LBO valuation model, can you create a video on this topic? Thank you in advance.
Always learning new skills and tricks in Excel, since I started with spreadsheets back with Lotus 123 rel 3.01 a long time ago, and back when you were limited to a lot less columns and rows. I used to use the string functions to extract data from a single column that I stored information in.
Hey a few things to try. Xlookup replaces ALL functions of Vlookup and IndexMatch and Hlookup, so if you are using any of these functions in any way Xlookup can do it. If you want to look up 2 values you need to use &. Example =XLOOKUP(lookup_value & lookup_value, lookup_array & lookup_array, return_array,””,0,1) this would be used for a vertical data set like in your example. You can also have 2 lookups used in a table format to return the cell in the table where the two cells meet at. =XLOOKUP(lookup_value1,AH3:AH13,XLOOKUP(lookup_value2,AI2:AM2,AI3:AM13,"",0,1),"",0,1) I left the cell references in, but you can get the picture. Also don’t forget about returning non adjacent rows =XLOOKUP(lookup_value2, lookup_array,INDEX(return_array,SEQUENCE(ROWS(return_array)),{1,3,4}),"",0,1) These are just a few examples of what you can do. Great video, but I have to admit that I was more then a little upset with the title of the video being “Make You a PRO User” and then not to learn anything new. I am an average excel user so ….😅
Brother this will be my start from today and upto to the newest video i will develop myself to the professional expertise level so i can add this to my resume , im just college student but looking forward to develop further than i was than never ever i have been
I watch all the advertisements completely that runs at beginning, middle, at the end of video , to make sure the sponsors contribute to your efforts. Best regards from New Delhi India🇮🇳 🇮🇳
Great video!!! The tips will make my life so much easier. 2 questions: 1) in the Revenue by State and Year I formatted the state and year as data validation lists so I could easily change either of the them (or both) and it seemed to still give me the correct Revenue amount. Is there a reason this would not be good practice? 2) When we used the Xlookup for the Product Arrival Date we returned one product (Dasani Water) for 5/16/22 but there were in fact 3 products with that same arrival date. How can you be sure you are returning ALL products arriving on that date? Thank you so much, I will continue to watch ever video you make.
Hi, I didn't know the bonus tips. Really useful. I knew a few of the formulas. I knew of them all but hadn't had the need to use them. Now I can see that they would be useful in my work: especially "Index - Match". Thanks
I was kind of expecting the usage of spilling later on in the video. Also regarding the question how many I already knew… I already knew them all, but I still liked the video as I think it does have valuable information for people whom aren’t aware of these formulas
Thanks for your video. I don’t use index match but the others are frequent flyers. Xlookup is a great replacement. Prior we had to do helper columns if our data was was in left to right. So many new features to include power Query which is a godsend. Nice job on your channel content.
an alternative to the index match is SUMIFS. it can take different conditions from different columns, and since conditions are generally unique, it will sum only 1 value i.e, it will just return that value. if multiple conditions are to be selected from the same column, one can do a SUMPRODUCT(SUMIFS
On your first example of IF function where you compare two values... you can just also use the "CELL=CELL" ex: =C2=C19 then it will put a result indicating TRUE if the values are the same or FALSE if it is NOT... Then on the finding the link of the FORMULA created.. you can also doble-click on cell with formula in it and it will highlight all the included cell in that FORMULA.
Guess I'm a level 4.5 I've never used xlookup nor sumifs just sumif, but the rest I've used . I've needed xlookup I'll try it out going forward. Thanks 😊
Great refresher Kenji! Bravo! Just a question regarding Xlookup; i.e., it can be the case that multiple products arrive at the same day. How do you capture multiple products that arrived on the same day?
This is so awesome. I knew a lot of the formulas but not all. I will definitely watch this and make notes. @kenji you should have a PDF summary for all formulas covered.
@@KenjiExplains Sell the summary for 2-3 dollars. You have many viewers who benefit from your videos. You should benefit as well. If you manage 200,000 sales by the end of the year at 2 dollars each…quick 400? Food for thought. I would gladly pay two dollars for this video’s summary.
Hi Kenji, I am learning a lot from your video tutorial. I appreciate your content or subject matter. Another thing is the courses you introduced where we can enroll to. These are great concerns to increasing knowledge.
How do you decide on a career and job profile to target in college like my batchmates are aiming for IB and stuff but I dont exactly know how to go about it? I am really confused there are so many different things .... I started learning financial modelling and excel not from you though 😅
I want to clean data based on calculated values and highlight them. For instance when a cell value is devided by 3 it should return a number without a coma. E. G 15/3 =5 "don't highlight. 14/3= 4.6 '' highlight"
Thank you for this video. It is a great video for "refreshing" Excel knowledge! Looks like others have made recommendations for high levels. I look forward to it!
I actually tried that and yes it does work for the first year 2018. However, it doesn't seem to for the rest of the years. Let me know how you did it, thanks.
Despite the clickbaity title and that most fas not useful funktion in my uses, this was a good quality video. And the IFERROR did finally fix one annoying issue I had. Although purely aesthetic.
Amazing video. I had an issue with the Vlookup. Although I had the correct items in the list it still showed N/A when I typed. When I copied the cell and pasted it showed the correct amount 🤔
This video was outstanding! We have been using some new programs that are adding more complex material, and this was so helpful in ways to assimilate the data. This is useful for beginners or intermediates who need some refresher training. Thank you!
Get 25% OFF Financial Edge using code KENJI25: bit.ly/3xNV4VW
Hi Kenji, does this course cover PowerPoint for investment presentations too?
Hi Kenji, on the excle sheet when I was practicing there was an error and I don't know where it originated from. My calculation for time of arrival was showing show negative all the way down. e.g -225 What could be the cause of this? Thank you again for your cooporation
I can't thank you enough. You break each point down and put it in context.
Although it's not rocket science, some tutorials make it very much so!
This video is so underrated, it should've reached 100000 views by now
Thank you!! Hopefully does haha 😆
@@KenjiExplains good luck and keep em coming 👍👍👍👍👍👍
× 5
So true
@@KenjiExplains Thank you so much, Kenji 😊😊👍👍👍 You're our HERO 🙇🙇🙇🙇♀️🙇♀️🙇♂️🙇♂️🙇♀️🙇♀️🙇♀️
You by far have the best tutorial videos. Thank you for providing so many helpful videos.
WOW KENJI! I'm improving a lot my EXCEL skills thanks to your courses. My workmates are hallucinating how fast I apply formulas and how quickly I finish my assignments! CHEERS!
They don’t k know Kenji
Careful! Management will often get rid of them, give you double-work and pay you the same! That's when you need to become a consultant! :-)
Great video, thank you! I’m 57 and I used Lotus 1-2-3 on DOS where you had to tell it how many rows and columns you wanted. I’ve been using Microsoft Office since 1998 and the formulas and features that Excel has is always growing, but it continues to be glitchy. I have to use the Trim(Substitute formula to convert cells to where I can get rid of leading spaces and format them as currency or whatever I want. Your channel has great content but I’m pretty sure you’d get extra subs and a lot of views if you did a video on the 10 best Excel workarounds and how to auto save and find xlsb files that aren’t in Recover files. Those are the things that save time and frustration!
Thanks for the video. A few comments: you can use CTRL + ~ to toggle show formulas on and off if you prefer keyboard vs the mouse click option. I would say that there is a level 6 or higher lookup formula that was not mentioned. Its a combination of indirect + index match. This lets you dynamically select the array in question as opposed to having to choose the array manually. The formula would look like index(indirect(concatenate("C", match(),":","C", match()), False),match(),match()). In this example, you can specify criteria by dynamically creating an array address and then looking up a value within that address based on separate criteria. It's definitely a complex scenario but I've come across this before and needed to break free from hard coding the initial array of the index.
I think the formula you have written is something I'll have to work on to understand!
You say match() in your formula, why isn’t there anything filled in?
@@joukenienhuis6888 it's for your data
Thank you so much for sharing this, it is about 25 years since I did my Excel training, so this has been a brilliant refresher; as well as learning so much more. Many blessings.
I am 46 and still learning excel, you have been of immense help. I knew some of these formulas but not all. Thank you kenji
Awesome to hear Antony :)
Thanks for the bonus tip. I am beginner it helps a lot for me 😄
Excellent video. As someone who wrote their own accounts programme in Excel and VBA, I knew all the formulas but didn’t know the level one stuff. Sequence, CTRL, shift down arrow and using the F4 key to lock in the $ sign. Well done!
thank you!
Kenji, You are a very good teacher /lecturer and a slick marketer. Wishing you all the best
@KenjiExplains The content you produce is very practical and useful for working with finance. Thank you very much for sharing.
I have a question, I am interested in the LBO valuation model, can you create a video on this topic? Thank you in advance.
Always learning new skills and tricks in Excel, since I started with spreadsheets back with Lotus 123 rel 3.01 a long time ago, and back when you were limited to a lot less columns and rows. I used to use the string functions to extract data from a single column that I stored information in.
I love your videos! Learning excel for free at home and I feel so confident!
Wow! Bonus tips are life savers when working with complex project files. Thanks!
So going by this video I'm an excel pro. Great. Please Kenji do a video explaing the Offset function that's one formular I'm yet to understand
Hi Kenji, would love to see a video following up your one on PE. Could touch on carry, co-invest,waterfalls, etc.
Hey a few things to try. Xlookup replaces ALL functions of Vlookup and IndexMatch and Hlookup, so if you are using any of these functions in any way Xlookup can do it. If you want to look up 2 values you need to use &. Example =XLOOKUP(lookup_value & lookup_value, lookup_array & lookup_array, return_array,””,0,1) this would be used for a vertical data set like in your example.
You can also have 2 lookups used in a table format to return the cell in the table where the two cells meet at. =XLOOKUP(lookup_value1,AH3:AH13,XLOOKUP(lookup_value2,AI2:AM2,AI3:AM13,"",0,1),"",0,1) I left the cell references in, but you can get the picture.
Also don’t forget about returning non adjacent rows =XLOOKUP(lookup_value2, lookup_array,INDEX(return_array,SEQUENCE(ROWS(return_array)),{1,3,4}),"",0,1)
These are just a few examples of what you can do. Great video, but I have to admit that I was more then a little upset with the title of the video being “Make You a PRO User” and then not to learn anything new. I am an average excel user so ….😅
Thank you for showing the index match. That is. Exactly what I need.
Great to hear!
Thank you, i was trying very hard to understand this formula, your videos help me a lot. Thank you
best thing of your videos is, you keep it simple and short. thanks for sharing knowledge.
Brother this will be my start from today and upto to the newest video i will develop myself to the professional expertise level
so i can add this to my resume , im just college student but looking forward to develop further than i was than never ever i have been
I watch all the advertisements completely that runs at beginning, middle, at the end of video , to make sure the sponsors contribute to your efforts.
Best regards from New Delhi India🇮🇳 🇮🇳
I knew all of them and use them constantly. Your videos are well done.
Excellent work, one of the most helpful excel tutorials i've ever seen.
Thanks sir for the lecture and for indirectly reminding me of what should be done to iron things in the future in particular.
When Kenji posts, you watch. Simple. Best Xcel Channel ❤
thanks! I didnt know the xlookup and was always confuse on how to use index match. it was very clear
Great video!!! The tips will make my life so much easier. 2 questions: 1) in the Revenue by State and Year I formatted the state and year as data validation lists so I could easily change either of the them (or both) and it seemed to still give me the correct Revenue amount. Is there a reason this would not be good practice? 2) When we used the Xlookup for the Product Arrival Date we returned one product (Dasani Water) for 5/16/22 but there were in fact 3 products with that same arrival date. How can you be sure you are returning ALL products arriving on that date? Thank you so much, I will continue to watch ever video you make.
Hi kenji
I know all, except for the sequence and the strings
Learnt something.
Thank you very much.
Hi Kenji,
I don't know how to thank you for explaining such complicated formulas in a very simple way. Can I get classes from you please?
Thank you soo much..I thought I knew excel but I was humbled quick..This video is so amazing.Learned a lot.Gonna watch the others too ❤🤩
the best excel teacher in youtube, good job.
I really need a 1 hr video of you explaining these excel analysis. great job bro and much love from kenya
Thank you, its the first time ive understood some of the more complex formula's
I've never used the indexmatch, well I learned a new one so thx for that...
Very useful video thank you! I do use all these functions for my accounting job
Thanks for the course. Didn’t know the xlookup. You are a clear and straightforward instructor.
Hi, I didn't know the bonus tips. Really useful. I knew a few of the formulas. I knew of them all but hadn't had the need to use them. Now I can see that they would be useful in my work: especially "Index - Match". Thanks
Nice video. Up to the point. I use all of aforementioned formulas and I found it helpful for a quick reference/reminder
I love how you explain everything. Easy to understand. Thank you,
you are the first who attach the file with which I can practice! thanks! you deserve my subscribe.
I proly use like 2-3 of these on a regular basis ; def not the first couple though. nice vid
A big thumbs up for this video, those indexmatch functions save my day, thank you!
I was kind of expecting the usage of spilling later on in the video.
Also regarding the question how many I already knew… I already knew them all, but I still liked the video as I think it does have valuable information for people whom aren’t aware of these formulas
Hello Kenji ,you are a real tutor.love your lectures
Thanks for your video. I don’t use index match but the others are frequent flyers. Xlookup is a great replacement. Prior we had to do helper columns if our data was was in left to right. So many new features to include power Query which is a godsend. Nice job on your channel content.
I recently discovered power query too, it is indeed a time saver too
I was taking the excel course for the exam and this definitely helps reviews the formulas again so thanks for the video!!
an alternative to the index match is SUMIFS. it can take different conditions from different columns, and since conditions are generally unique, it will sum only 1 value i.e, it will just return that value.
if multiple conditions are to be selected from the same column, one can do a SUMPRODUCT(SUMIFS
On your first example of IF function where you compare two values... you can just also use the "CELL=CELL" ex: =C2=C19 then it will put a result indicating TRUE if the values are the same or FALSE if it is NOT... Then on the finding the link of the FORMULA created.. you can also doble-click on cell with formula in it and it will highlight all the included cell in that FORMULA.
Guess I'm a level 4.5 I've never used xlookup nor sumifs just sumif, but the rest I've used . I've needed xlookup I'll try it out going forward. Thanks 😊
Wow! Great video. I knew some of the 1 to 3 stars and vlookup formula. Very useful video as well as bonus tips. Thanks for sharing!
Great refresher Kenji! Bravo!
Just a question regarding Xlookup; i.e., it can be the case that multiple products arrive at the same day. How do you capture multiple products that arrived on the same day?
You'd have to change how the data is displayed perhaps by creating a pivot table?
Hi Basit, you can use the filter function to return multiple results.
Knew them all but I enjoyed the video.
I didn't know where those buttons were for showing formula and formula dependants.
Great vid as always.
Awesome to hear, glad you enjoyed it!
This is so awesome. I knew a lot of the formulas but not all. I will definitely watch this and make notes. @kenji you should have a PDF summary for all formulas covered.
Thank you Brian, noted the suggestion :)
@@KenjiExplains Sell the summary for 2-3 dollars. You have many viewers who benefit from your videos. You should benefit as well. If you manage 200,000 sales by the end of the year at 2 dollars each…quick 400? Food for thought. I would gladly pay two dollars for this video’s summary.
Hi Kenji, I am learning a lot from your video tutorial.
I appreciate your content or subject matter.
Another thing is the courses you introduced where we can enroll to. These are great concerns to increasing knowledge.
Hey Kenji amazing content as usual. If you could make a video on how do we take assumptions in financial models , it would be helpful?
Noted thank you!
Excellent video, so useful, and sure will be handy on a day to day use. Thank you for sharing, I am so grateful.
Thank you, Kenji! Insightful, clear and concise.
Thanks Kenji! Really did not expect Index -- Match could be used so efficiently. Learned another tip for except from you today!
It's nothing as satisfying as knowing your framework.
Thank you make these formulas so easy to understand.
Thanks Kenji, great video. You make complex formulae easy to follow and understand.
1:03
2:11
4:39
8:14 Ag 11:27
How do you decide on a career and job profile to target in college like my batchmates are aiming for IB and stuff but I dont exactly know how to go about it? I am really confused there are so many different things .... I started learning financial modelling and excel not from you though 😅
Outstanding! Dear Kenji: best regards from Limón, Costa Rica
Fantastic video! Will binge all your vids now , so helpful
These formulas are quite helpful. Thank you!
A question -
Is there a way to copy just the value from a cell with a formula in it?
After you cooy that cell, you select your paste options and 'past values'
I want to clean data based on calculated values and highlight them. For instance when a cell value is devided by 3 it should return a number without a coma. E. G 15/3 =5 "don't highlight. 14/3= 4.6 '' highlight"
today by chance saw your tutorial. thank you for great video.
Acabo de encontrar tu canal, es lo máximo. ¡Muchas gracias!
This is awesome. Thank you very much. The presentation is top notch.
The best excel tutorial that I have come across
Thank you!
Thank you for this video. It is a great video for "refreshing" Excel knowledge! Looks like others have made recommendations for high levels. I look forward to it!
Great job with this collection of formulas/functions!
Great video! Just curious as to why you gave the IfError formula four stars for difficulty?
You know you're an excel pro when all your formulas end with )))))),"").
I knew O out of 10 formulas. THANK YOU!
great tips as alwas keep going kenji i've learned a lot from you thank you
Thank you so much for this, it was incredibly useful and well made. You're a great teacher!
Thank you!
Great video. Please note that for the levels 5 example where you use the Index and Match, a simple VLOOKUP would have indeed worked!
I actually tried that and yes it does work for the first year 2018. However, it doesn't seem to for the rest of the years. Let me know how you did it, thanks.
this video was eye-opening for me, learnt some new formulas here. cheeers
Despite the clickbaity title and that most fas not useful funktion in my uses, this was a good quality video.
And the IFERROR did finally fix one annoying issue I had. Although purely aesthetic.
Thank you for improving my knowledge. :)
Index match match is what I didn’t use much and really needed it
Sir , I have learn and seen so many things from experts in excel but Well experienced and expert than you till yet 😉
Amazing video.
I had an issue with the Vlookup. Although I had the correct items in the list it still showed N/A when I typed. When I copied the cell and pasted it showed the correct amount 🤔
This was awesome thank you! I love these short vids.
Thanks Kenji! Very useful content!
your videos keep getting better keep it up!
Thank you man
I use all of these quite often with Baseball Pool Spreadsheets, Budgets/Tracking, GTSport stuff....
thank you very much Kenji. This is very useful, just thanks a lot.
Great explanation. thank you for your videos.
Thank you again Kenji, appreciated!
This video was outstanding! We have been using some new programs that are adding more complex material, and this was so helpful in ways to assimilate the data. This is useful for beginners or intermediates who need some refresher training. Thank you!
Thank you very much. The content was useful for me.
Very well explained.. thanks alot
Thank you for watching as always 🙌
You sir! Hats off for crisp n precise tutorial! 😊👍
You absolute legend. Thank you.