Hi Kenji, great job with your videos. However, i don't really appreciate the way your lessons dwell so much on using shortcuts rather than teaching people how to use navigations. There are dozens of shortcuts. its not realistic to expect people to memorize all of them. Teach people how to navigate then you can alternatively show them shortcuts. i want to purchase your courses but i am skeptical because i don't want lectures that are full of just shortcuts.
I have to say that I just did an excel test for IB in M&A yesterday and it was almost the exact same. Reorganize a P&L statement, use sumifs for the revenue, and the drop down list … I would have been so lost without this video!!
Wow.. The best teachers can make things sound simple and look simple very few can simplify the entire process like this. If I could hit the "Like" button 5 more times I would. Thank you for sharing this with us..
I took a financial modeling class in college and it was the hardest class I ever took out of my finance courses but well worth. I found this test to be silly its so easy but I cant imagine being new to excel and trying to figure that out. I must say this video was superb.
So on a scale of 1 to 9, how hard was this class? And how hard would you clasify this test in the vid (for context of your knowledge level). I found this here to be super easy, and I'm generally interested in finance amd data science, and want to switch from architecture to that.
@@Tepalus Well I Majored in Finance with honors, and also took an upper-division honors level financial modeling course,...and I can say without a doubt it was the most brutal class I have ever taken, classes like calculus, honors-python, and advanced statistics didn't even come close in difficulty. As for the test in the vid, its stupid easy xD
@@mmacg6440 Well, thanks for the answer then. Helped alot. I thought this test couldn't be close to reality. Still, I want to switch as I really don't see the point in architecture. ^^
Great video! Two things. 1. I don't think IndexMatch is a good solution to the first problem. Consider the formula you wrote at 3:17: INDEX($G$13:$G$27,MATCH(C7,$D$13:$D$27,0),MATCH($B$9,$C$13:$C$27,0)) The second nested formula MATCH($B$9,$C$13:$C$27,0) that gives you the column number returns 1, but that's only because NY happens to be the first item in the range $C$13:$C$27. If the problem had asked you to look up the Net Income for CT instead (i.e. if you changed the value of B9 to CT), this formula would have returned 2, which would have caused an error, because the array in your Index Match formula ($G13:$G$27) only has 1 column. The same error would have resulted had the Income table been organized differently - for example, if NY had been the second or third item on the list instead of first, you would have gotten an error. A better solution would have been to use FILTER: FILTER($G$13:$G$27,($D$13:$D$27=C7)*($C$13:$C$27=$B$9)) This works regardless of which state we're looking up (you could change the value of B9 to CT or FL) or how the Income table is sorted. 2. As someone with a fair amount of Excel skill who is actively searching for a new job, what are some industries / job titles that value this skill highly? What kind of positions would even require a test like this?
Hey there, I totally agree. In hindsight it's not the best formula to put there (got carried away on camera lol). Will add a comment on the video with your inputs as a correction! As for what roles value excel, most analyst roles (FP&A, private equity, investment banking, etc.). Also some business-oriented big tech roles like account management, strategy, or operations. Generally though, they won't just test your excel skills, but also your general knowledge of the field you're applying to. For example in FP&A you might have an excel test, and also need to create a 3 statement model on excel.
I am actively looking for jobs and I have been asked to take excel tests for accounting roles to gauge how well I know how to use excel. So I would say accounting positions at least there may be more though!
As a applied finance student, I really love to spend my time on the Kenji Explains and I plan to do the complete finance and valuation course and excel for business and finance course.
In the last exercise you could write this two formulas =TEXTSPLIT(PROPER(TRIM(LEFT(B6,LEN(B6)-2)))," ") and =UPPER(RIGHT(TRIM(B6),2)) This is another approach to solve that problem!
I learned all this through trial and error at my new job. I got hired for a computer science position and thought I would be exclusively developing web-based solutions. Turns out they also use A LOT of Excel ODBC reports. Now making formulas like these are second-nature!
Hi Kenji, Thanks for your videos! I found one mistake in your second formula in Question 1. If you change the state b9 cell for example CT, it would result in an error or #REF. It wouldn't be possible to use that formula. What you could do is create a concat unique code column between state and period and create a vlookup or xlookup.
Hi Kenji, thank you for taking the time and explain some of the shortcuts, could you please let me know if you have any for google sheets too? I have an interview next week! wish me luck I'll comment how it went :)
Hey Kenji, awesome video. Great shortcuts and great process. Just a heads up, those were not phone numbers, they were zip codes. #####-####. Phone numbers would have had 10 digits and a zip codes Is little more likely with addresses.
Another easy way to do it is to use sumproduct. When you asked to use 2 different formulas my first 2 ideas were sumifs and sumproduct and i didn't even consider index+match. About the second task. 1st task is about writing formulas so you dont have to do it manually and in cleaning youre doing most of the work by hand. Using Text to Columns is perfectly fine if you don't plan on repeating this task in the future. But what happens if your boss gives you 10 more positions to clean every single day? It's much cleaner to use power querry for cleaning data (except for the Trim function that requires some extra steps). Overall exercises are decent but in reality you would put everything from the 1st exercise in pivot table and filter it without even bothering to write formulas and the second one is just for one time data cleaning
Thanks Kenji, Another way to summarize per state plus month is to use XLOOKUP and use the ampersand to combine the results in D35, i.e =XLOOKUP(B9&D33,C13:C27&D13:D27,G13:G27) where D33 holds the months using data validation.
Hey everyone just wanted to point out the index match formula I use in Q1 works, but it isn't the best option. Probably a better option would have been to use the filter function as Mike Attia from the comments has pointed out!
It doesn't actually work unless NY is the first state listed in the data. the array that you are indexing just has 1 column, so it only "works" because the "vertical" match function is returning 1. If NY came second, the function would error. The way to index match this question is to create a unique identifier for each row, so match the concat(state,month) to concat(state_range,month,range). That's how you index match when all of the your categories are in the rows, not columns.
The FILTER function is a (relatively) new feature so not always available on older installs. And there are always plenty of legacy Office installs, even though it's a security risk. So knowing how to use INDEX/MATCH properly is invaluable. Named ranges and tables can helps a lot with readability too. Dynamics ranges are important when the data size can change. Finally, if you are using the bleeding edge Office techniques, LET and LAMBDA are really powerful and basically transform Excel's formulas into a functional programming language. The issue with this is they're not supported by most organisations yet so you can't rely on their availability. Of course, if you're trying to actually get stuff done and not just pass job interviews, you should be using the Excel Data Model and Power Pivot to prepare and present your data. You won't always be given everything in nice fixed-sized Excel tables. Usually you'll need to pull from some other report, tool, log or website to get your data. You should avoid manually or VBAutomagically pulling into data into countless spreadsheets, the Data Model can do the job faster and cleaner, it's basically SQL for excel.
I would have used SUMIFS and SUMPRODUCT in problem 1. Pretty much the same solution with two different classic functions. Doing a index/match makes assumptions on the dataset (uniqueness of state/yearmonth) that are not given in the problem. Using the spill functions is even better as it showcases knowledge of brand new functionality in Excel. Going into spill functions, then the data-cleansing job should have been solved with the new TEXTSPLIT that is in beta release now :) Another beta function, CHOOSECOLS would have allowed you to cleanse, split and format the data in a single formula. You have to show of your skills if you want the job :)
Thanks a lot bro for giving us this kind of knowledgeable videos ... Your Videos are really good for learning and also so easy to understand. plz upload this kind of videos a lot in youtube. I like your videos and also try to watch every single videos❤️❤️❤️
I find it interesting that people interview for Excel. Although I will tell you that if someone tells me they are an expert I ask what CTRL+SHIFT+ENTER does. If they can tell me that's good enough. lol
The speed with which you use short cut keys is really brilliant. I ve been working on excel sheets since last 8 years but still haven't got much hold of the usage of excel short cut keys.
It really makes you realize how simplified for use Excel is. You really don't need years of experience in statistics or epidemiology to be able to use it somewhat efficiently, all you need to know is the existence of the functions and understand their components.
Index Match can be used well here for both question 1 and 2. Needs to be an array though with multiple criteria and it will allow you to change the state at will for the net value. Question 1: =INDEX($G$13:$G$27,MATCH(1,(C7 = $D$13:$D$27)*($B$9=$C$13:$C$27),0)) , then hit CTRL SHIFT ENTER to enter this as an array and drag across. Question 2: =INDEX(G13:G27,MATCH(1,((D33 = D13:D27) * ("NY" = C13:C27)),0)) , again do the same above. Edit: unless using excel 365, then doesn't need to be an array.
It seems the answer to question 1 is incorrect .The second method only works for the state NY and not any other. Index function takes three arguments, lookup array, row and column. For state NY, the second match results 1 so that the first column is selected. For any other state (e.g. CT, FL), the second match returns 2 or higher and will result in a #REF! error.
Well ... not bad, most of my collegues will not know how to solve some of the problems ... there is my ideas to solve: Q1 obviously SUMIFS and FILTER; Q2 dynamic dropdown created as array (# to reference) + FILTER Q3 PROPER + TRIM + TEXTSPLIT .... all in a single step ... (for huge dataset I will go with PowerQuery) total time to solve - 2-3 minutes
Hi Kenji, quick question on the index match here. I may be wrong, but I think your solution is incorrect. The Index array you put is just a one column array, so what's the point of doing a match for the column_num? It seems to only work because NY is the first state for each period. For example, if you flip the order of the states, and have CT as the first state instead of NY, it wouldn't change the result. Please let me know. Thank you!
Hey there, you're absolutely right. In hindsight I didn't use the index match well there (even though it worked). A better solution would have been to use FILTER formula: FILTER($G$13:$G$27,($D$13:$D$27=C7)*($C$13:$C$27=$B$9))
not knocking you giving knowledge away, just think the index - match is a tad misleading as it would only work on a list like this, if you had 2 NY for any month it would only return the first value. Better to go with aggregate(9,6,array) or my preferred formula sumproduct, powerful and underused
Just to clarify for some people that may be interested. Excel skills are pretty much irrelevant for business strategy roles. Strategy sit far above the layer of business where these excel skills are generally needed. Yes I am a Director of a company.
@Padraig Joseph yeah but if you're going to be lazy, then at least be properly lazy and leave out the second match and replace it with a hard coded 1. Or better, just type in the correct numbers in each cell!
@Padraig Joseph Independant directors and directors that have been otherwise sourced from outside of the organisation. If you think the BOD is just a bunch of company execs then you don't understand the modern composition or role of boards. The job of a board is to make strategic decisions, hold liability for specific regulatory and feduciary responsibilities, set straegic objectives and give direction to the CEO and their executive management team. None of this needs excel. Also a large proportion of of BOD members are lawyers and they are not exactly experts in excel. In fact if your communicate information to a BOD via spreadsheets ecpect them to tell you to go away and come back with useful information rather than just large and time consuming arrays of data.
Great video. just a quick question: when you enter in the formulas, how do you get excel to give you the auto suggestions, ie "SUMIFS, "adds the cells specified by a given set of conditions or criteria"
at 8:37, alt+H+O+I doesn't work for Mac's-what's the Mac shortcut for this , cause this doesn't work for me-thnx - this tutorial is quite good, i wish u did tons more Excel review test questions, but some of these shortcuts don't work for Mac But from another tutorial I learned if you double click bottom lower right of column where letters are at the top, it does the same thing.
I love your videos, but unfortunately i own apple products to which the shortcuts in your videos do not always apply. So i was wondering, if you have ever used excel on an Apple product (MacBook, IMac etc.).If so it would be awesome, if you could make a video about apple related shortcuts for MS Office products, but especially excel?
Hey yeah unfortunately I'm using a windows so the keyboard shortcuts are different. Overall, as the large majority of corporate roles use windows, so that's why I prefer to teach in windows.
Not much difference. Although xlookup can be more resource heavy. So if you are dealing with lots of data, it's better to use index&match. It will load way faster.
at 4:16 , i don't understand your match for the column , as your map is going down a list - wouldn't that just result in another row #? Seems like your getting two row #'s with both match functions...
I don't get the Index&Match solution in Q1, it talks about a row MATCH and a column MATCH, but both period and state are expressed as row in their own column?
📈 My Excel for Business & Finance Course: www.careerprinciples.com/courses/excel-for-business-finance
Your videos are sooooooooooo good 👏 I have a new job to go to in 3 weeks so just fleshing up on excel and you have been most helpful 😀
@@corybabcock9969 You are very right. Sumproduct will work better instead of index match. Especially if we don't have unique date for States.
Hi Kenji, great job with your videos. However, i don't really appreciate the way your lessons dwell so much on using shortcuts rather than teaching people how to use navigations. There are dozens of shortcuts. its not realistic to expect people to memorize all of them. Teach people how to navigate then you can alternatively show them shortcuts. i want to purchase your courses but i am skeptical because i don't want lectures that are full of just shortcuts.
I have to say that I just did an excel test for IB in M&A yesterday and it was almost the exact same. Reorganize a P&L statement, use sumifs for the revenue, and the drop down list … I would have been so lost without this video!!
Lets go! Hope it went well for you :)
Wow.. The best teachers can make things sound simple and look simple very few can simplify the entire process like this. If I could hit the "Like" button 5 more times I would. Thank you for sharing this with us..
Thanks for the awesome comment ! I'm glad it was helpful!
Yeah no formular for that...
I took a financial modeling class in college and it was the hardest class I ever took out of my finance courses but well worth. I found this test to be silly its so easy but I cant imagine being new to excel and trying to figure that out. I must say this video was superb.
So on a scale of 1 to 9, how hard was this class? And how hard would you clasify this test in the vid (for context of your knowledge level).
I found this here to be super easy, and I'm generally interested in finance amd data science, and want to switch from architecture to that.
@@Tepalus Well I Majored in Finance with honors, and also took an upper-division honors level financial modeling course,...and I can say without a doubt it was the most brutal class I have ever taken, classes like calculus, honors-python, and advanced statistics didn't even come close in difficulty.
As for the test in the vid, its stupid easy xD
@@mmacg6440 Well, thanks for the answer then. Helped alot.
I thought this test couldn't be close to reality.
Still, I want to switch as I really don't see the point in architecture. ^^
Great video! Two things.
1. I don't think IndexMatch is a good solution to the first problem. Consider the formula you wrote at 3:17:
INDEX($G$13:$G$27,MATCH(C7,$D$13:$D$27,0),MATCH($B$9,$C$13:$C$27,0))
The second nested formula MATCH($B$9,$C$13:$C$27,0) that gives you the column number returns 1, but that's only because NY happens to be the first item in the range $C$13:$C$27. If the problem had asked you to look up the Net Income for CT instead (i.e. if you changed the value of B9 to CT), this formula would have returned 2, which would have caused an error, because the array in your Index Match formula ($G13:$G$27) only has 1 column. The same error would have resulted had the Income table been organized differently - for example, if NY had been the second or third item on the list instead of first, you would have gotten an error.
A better solution would have been to use FILTER:
FILTER($G$13:$G$27,($D$13:$D$27=C7)*($C$13:$C$27=$B$9))
This works regardless of which state we're looking up (you could change the value of B9 to CT or FL) or how the Income table is sorted.
2. As someone with a fair amount of Excel skill who is actively searching for a new job, what are some industries / job titles that value this skill highly? What kind of positions would even require a test like this?
Thank you
Hey there, I totally agree. In hindsight it's not the best formula to put there (got carried away on camera lol). Will add a comment on the video with your inputs as a correction!
As for what roles value excel, most analyst roles (FP&A, private equity, investment banking, etc.). Also some business-oriented big tech roles like account management, strategy, or operations. Generally though, they won't just test your excel skills, but also your general knowledge of the field you're applying to. For example in FP&A you might have an excel test, and also need to create a 3 statement model on excel.
I am actively looking for jobs and I have been asked to take excel tests for accounting roles to gauge how well I know how to use excel. So I would say accounting positions at least there may be more though!
Thank you!!!
You need this for big 4 recruiting excel test
kenji want to say ur the most down to earth helpful finance channel on utube, vids like this are so important,
thank you! Really appreciate your comment :)
I got my job as a financial analyst. This helped so much, thank you from the bottom of my heart.
One of the most amazing Excel videos I have seen, thank you!
As a applied finance student, I really love to spend my time on the Kenji Explains and I plan to do the complete finance and valuation course and excel for business and finance course.
In the last exercise you could write this two formulas =TEXTSPLIT(PROPER(TRIM(LEFT(B6,LEN(B6)-2)))," ") and =UPPER(RIGHT(TRIM(B6),2)) This is another approach to solve that problem!
I learned all this through trial and error at my new job. I got hired for a computer science position and thought I would be exclusively developing web-based solutions. Turns out they also use A LOT of Excel ODBC reports. Now making formulas like these are second-nature!
Hi Kenji, Thanks for your videos! I found one mistake in your second formula in Question 1. If you change the state b9 cell for example CT, it would result in an error or #REF. It wouldn't be possible to use that formula. What you could do is create a concat unique code column between state and period and create a vlookup or xlookup.
Hi Kenji, thank you for taking the time and explain some of the shortcuts, could you please let me know if you have any for google sheets too?
I have an interview next week! wish me luck I'll comment how it went :)
I think what the teacher said is very reasonable. It made me understand and taught me a lot. If I can, I will always like it.
Awesome to hear!
Another great video - this kind of exercise is also perfect for students looking to sharpen up before starting an internship!
Very true!
Hey Kenji, awesome video. Great shortcuts and great process. Just a heads up, those were not phone numbers, they were zip codes. #####-####. Phone numbers would have had 10 digits and a zip codes
Is little more likely with addresses.
This was very helpful, Kindly upload more such business case study questions :)
Another easy way to do it is to use sumproduct. When you asked to use 2 different formulas my first 2 ideas were sumifs and sumproduct and i didn't even consider index+match. About the second task. 1st task is about writing formulas so you dont have to do it manually and in cleaning youre doing most of the work by hand. Using Text to Columns is perfectly fine if you don't plan on repeating this task in the future. But what happens if your boss gives you 10 more positions to clean every single day? It's much cleaner to use power querry for cleaning data (except for the Trim function that requires some extra steps). Overall exercises are decent but in reality you would put everything from the 1st exercise in pivot table and filter it without even bothering to write formulas and the second one is just for one time data cleaning
Those shortcuts you use are impressive! I am just happy there is a mouse to avoid all of that memorization of keys! 😆 Great video.
Hi Kenji.
Loved your awesome videos.
Kindly tell us which excel version are you using since I have 2016 and XLookup function is not there??
The sample which you worked in video is contradicting with the excel file given in description.
Thank you !
your classes are super helpful!! thank you!!!
You're great! Although I had to slow down the playback speed. So, maybe you wanna slow down abit.
But yeah, great work there!
I have no need for this knowledge but this was actually really informative.
Very good. Thanks for the tutorial. Your explainations are pretty clear.
Thanks Kenji, Another way to summarize per state plus month is to use XLOOKUP and use the ampersand to combine the results in D35, i.e =XLOOKUP(B9&D33,C13:C27&D13:D27,G13:G27) where D33 holds the months using data validation.
Thank you so much Kenji, you are the best of bests. Thank you for making education free 😊
My pleasure! thanks for watching :)
Kenji, these are sososo helpful, thank you for all excel videos you already made - a BIG HELP!
You explain everything so easily!
thank you!
Thanks for the awesome comment Marko!
Hey everyone just wanted to point out the index match formula I use in Q1 works, but it isn't the best option. Probably a better option would have been to use the filter function as Mike Attia from the comments has pointed out!
It doesn't actually work unless NY is the first state listed in the data. the array that you are indexing just has 1 column, so it only "works" because the "vertical" match function is returning 1. If NY came second, the function would error. The way to index match this question is to create a unique identifier for each row, so match the concat(state,month) to concat(state_range,month,range). That's how you index match when all of the your categories are in the rows, not columns.
The FILTER function is a (relatively) new feature so not always available on older installs. And there are always plenty of legacy Office installs, even though it's a security risk.
So knowing how to use INDEX/MATCH properly is invaluable. Named ranges and tables can helps a lot with readability too. Dynamics ranges are important when the data size can change.
Finally, if you are using the bleeding edge Office techniques, LET and LAMBDA are really powerful and basically transform Excel's formulas into a functional programming language. The issue with this is they're not supported by most organisations yet so you can't rely on their availability.
Of course, if you're trying to actually get stuff done and not just pass job interviews, you should be using the Excel Data Model and Power Pivot to prepare and present your data. You won't always be given everything in nice fixed-sized Excel tables. Usually you'll need to pull from some other report, tool, log or website to get your data. You should avoid manually or VBAutomagically pulling into data into countless spreadsheets, the Data Model can do the job faster and cleaner, it's basically SQL for excel.
I would have used SUMIFS and SUMPRODUCT in problem 1. Pretty much the same solution with two different classic functions. Doing a index/match makes assumptions on the dataset (uniqueness of state/yearmonth) that are not given in the problem. Using the spill functions is even better as it showcases knowledge of brand new functionality in Excel.
Going into spill functions, then the data-cleansing job should have been solved with the new TEXTSPLIT that is in beta release now :) Another beta function, CHOOSECOLS would have allowed you to cleanse, split and format the data in a single formula. You have to show of your skills if you want the job :)
8:59
On german Excel version the "copy values (without format)" is [Alt] -> [R] -> [V] -> [W]
Nice video but one minor issue -- US phone number pattern is 3 AC 3 Exchange 4 local. (10 digits) In other words +1(###) ###-####
Thanks a lot bro for giving us this kind of knowledgeable videos ... Your Videos are really good for learning and also so easy to understand. plz upload this kind of videos a lot in youtube. I like your videos and also try to watch every single videos❤️❤️❤️
Thanks Kenji 🙏🙏🙏🔥. This is the best thing I've ever needed. Keep them coming
Great to hear Abdimalik, more on the way!
Kenji, It's a great video. Thank you for sharing. I hope there are more similar videos in future.
Awesome to hear! Yes, more to come this Sunday :)
Thank you very much. I would really appreciate it if you could make more of these REAL Excel Interview Tests videos.
Noted your suggestion, thanks for watching!
Super helpful for my interview prep!! Thanks Kenji
Great to hear thank you!
Dude u are absoluetly amazing, thank you.
Thanks Kenji, really can't thank you enough for the valuable content that you have been sharing!!!
That's awesome to hear Jing. Thanks a ton for the nice comment!!
Love these excel videos, thanks for providing us with such valuable content Kenji!
Love the excel videos ! Please continue making more.
Thank you! Yes more coming soon
I find it interesting that people interview for Excel. Although I will tell you that if someone tells me they are an expert I ask what CTRL+SHIFT+ENTER does. If they can tell me that's good enough. lol
straight to the point, Love it!!!
thanks a lot Kenji.
Thank you for watching!!
best teacher on youtube
Haha cheers Solomon!
Thank you so much for sharing your knowledge
The speed with which you use short cut keys is really brilliant. I ve been working on excel sheets since last 8 years but still haven't got much hold of the usage of excel short cut keys.
Excellent sum up before interviews 👍
nice tutorial i have learnt alot today
Please keep these up!!! Appreciate you! 😁💪🏾
Daniel, thanks for tuning in as always bro! Hope everything is good :)
thank you so much, educating and interesting😄
"That candidate really knows his stuff eh?"
"Yeah he really does. Better not give him the job, he'll show us up!"
😁
It really makes you realize how simplified for use Excel is. You really don't need years of experience in statistics or epidemiology to be able to use it somewhat efficiently, all you need to know is the existence of the functions and understand their components.
yes, looking up and adding numbers doesn't require years of statistics
Index Match can be used well here for both question 1 and 2. Needs to be an array though with multiple criteria and it will allow you to change the state at will for the net value.
Question 1:
=INDEX($G$13:$G$27,MATCH(1,(C7 = $D$13:$D$27)*($B$9=$C$13:$C$27),0)) , then hit CTRL SHIFT ENTER to enter this as an array and drag across.
Question 2:
=INDEX(G13:G27,MATCH(1,((D33 = D13:D27) * ("NY" = C13:C27)),0)) , again do the same above.
Edit: unless using excel 365, then doesn't need to be an array.
His works only because the first value for each month is NY, doesn't it?
Kenji, in the future you could be a teacher in HBS or Wharton. You are really good at explaining things you know
very well explained
Valuable content which has been explained perfectly! Thanks for sharing.
Glad you enjoyed it!
many thanks Kenji
it's usefuly
Thanks Kenji!
Very helpful, thanks!
It seems the answer to question 1 is incorrect .The second method only works for the state NY and not any other.
Index function takes three arguments, lookup array, row and column. For state NY, the second match results 1 so that the first column is selected. For any other state (e.g. CT, FL), the second match returns 2 or higher and will result in a #REF! error.
Wait... That's an actual test in serious interviews? I'm exceling just for fun, and knew everything you told us. :D
Thank you very much Bro...😁
Is there a training course to learn excel?
Great video Kenji!😁
Glad you enjoyed it!
Thank you sir,, that helped me a lot!!
Thanks for watching!
Thank you
Well ... not bad, most of my collegues will not know how to solve some of the problems ... there is my ideas to solve:
Q1 obviously SUMIFS and FILTER;
Q2 dynamic dropdown created as array (# to reference) + FILTER
Q3 PROPER + TRIM + TEXTSPLIT .... all in a single step ... (for huge dataset I will go with PowerQuery)
total time to solve - 2-3 minutes
Amazing job
Fantastic explanations, simple but detailed
Glad it was helpful!
3:05 ❤
Hi Kenji, how do I download the file you working on?
Thank you☺️
Kenji you're the best!
thanks for watching!
Hi Kenji, quick question on the index match here. I may be wrong, but I think your solution is incorrect. The Index array you put is just a one column array, so what's the point of doing a match for the column_num? It seems to only work because NY is the first state for each period. For example, if you flip the order of the states, and have CT as the first state instead of NY, it wouldn't change the result. Please let me know. Thank you!
Hey there, you're absolutely right. In hindsight I didn't use the index match well there (even though it worked). A better solution would have been to use FILTER formula:
FILTER($G$13:$G$27,($D$13:$D$27=C7)*($C$13:$C$27=$B$9))
not knocking you giving knowledge away, just think the index - match is a tad misleading as it would only work on a list like this, if you had 2 NY for any month it would only return the first value. Better to go with aggregate(9,6,array) or my preferred formula sumproduct, powerful and underused
You are a true master !! Thank you for all the techniques you're teaching us
Appreciate the video!
Just to clarify for some people that may be interested. Excel skills are pretty much irrelevant for business strategy roles. Strategy sit far above the layer of business where these excel skills are generally needed. Yes I am a Director of a company.
@Padraig Joseph yeah but if you're going to be lazy, then at least be properly lazy and leave out the second match and replace it with a hard coded 1. Or better, just type in the correct numbers in each cell!
@Padraig Joseph Independant directors and directors that have been otherwise sourced from outside of the organisation. If you think the BOD is just a bunch of company execs then you don't understand the modern composition or role of boards.
The job of a board is to make strategic decisions, hold liability for specific regulatory and feduciary responsibilities, set straegic objectives and give direction to the CEO and their executive management team. None of this needs excel.
Also a large proportion of of BOD members are lawyers and they are not exactly experts in excel.
In fact if your communicate information to a BOD via spreadsheets ecpect them to tell you to go away and come back with useful information rather than just large and time consuming arrays of data.
Thanks again.
Great video. just a quick question: when you enter in the formulas, how do you get excel to give you the auto suggestions, ie "SUMIFS, "adds the cells specified by a given set of conditions or criteria"
Thanks! You need to turn on autocomplete on the excel preferences I think that's what you're missing
Hi quick question. Why when I do convert text to columns does the operations not convert into the names. Thank you!
Thank you❤
Thanks a lot
Great video Kenji. Wish me luck, I have to take an analyst test this week. This video will surely help. Thank you!
Useful. Thank you
Glad it was helpful!
Make one for investment banking interview questions with some questions shared over pdf or something
hello Kenji, what if I don't have office 365, what to use instead of Xlookup?
I can do all of this already, can I get a decent paying job with these skills?
Fabulous video.. v well explained
Thank you!
Un crack en Excel!
Happy to see that I could pass with ease 😆
Lets go!
Ill rather use Power Query for the section 2 question to clean up the data quickly and move on
I never learned this in college 😩
We used SPSS.
Impressive.
I think using index match in the first example is a bad idea because if there are multiple NY data for January it won't sum it up like sumifs does.
at 8:37, alt+H+O+I doesn't work for Mac's-what's the Mac shortcut for this , cause this doesn't work for me-thnx - this tutorial is quite good, i wish u did tons more Excel review test questions, but some of these shortcuts don't work for Mac
But from another tutorial I learned if you double click bottom lower right of column where letters are at the top, it does the same thing.
I love your videos, but unfortunately i own apple products to which the shortcuts in your videos do not always apply. So i was wondering, if you have ever used excel on an Apple product (MacBook, IMac etc.).If so it would be awesome, if you could make a video about apple related shortcuts for MS Office products, but especially excel?
Hey yeah unfortunately I'm using a windows so the keyboard shortcuts are different. Overall, as the large majority of corporate roles use windows, so that's why I prefer to teach in windows.
If my excell skills are this strong already. What kind of jobs can I look for? I’m in an unrelated field.
Hi I neeed to ask what is the difference between xlookup and index &match
Not much difference. Although xlookup can be more resource heavy. So if you are dealing with lots of data, it's better to use index&match. It will load way faster.
How long would they give you for a test like this?
The video says around 30 minutes
Around 30 is reasonable. Keep in mind you might have to explain things live so the interviewer will be watching throughout
at 4:16 , i don't understand your match for the column , as your map is going down a list - wouldn't that just result in another row #? Seems like your getting two row #'s with both match functions...
I don't get the Index&Match solution in Q1, it talks about a row MATCH and a column MATCH, but both period and state are expressed as row in their own column?