REAL Excel Interview Tests for Business & Finance Roles
Вставка
- Опубліковано 27 чер 2024
- Learn excel interview test questions for business & finance professionals.
Among the most common excel test involve SumIFs, Index Match, Vlookups and data cleaning exercises.
🆓 Download the Excel Interview Test here: view.flodesk.com/pages/621861...
📈 The Complete Finance & Valuation Course: www.careerprinciples.com/cour...
👉 Excel for Business & Finance Course: www.careerprinciples.com/cour...
Whether you're recruiting for finance roles (asset management, investment banking, financial analysis) or business roles (account management, business development, operations) it's likely you'll encounter an excel test. In this video I provide a REAL excel test interview where I walk you through a sample answer. You can also download the file and try it for yourself. Best of luck recruiting!
LEARN:
📈 The Complete Finance & Valuation Course: www.careerprinciples.com/cour...
👉 Excel for Business & Finance Course: www.careerprinciples.com/cour...
📊 Get 25% OFF Financial Edge Using Code KENJI25: bit.ly/3Ds47vS
📹 My FREE videos with MoneyUnder30:
- Index Funds Explained: bit.ly/IndexFundsMU30
- How to Invest: bit.ly/HowToInvestMU30
- Stock Market Investing: bit.ly/MU30Stockmarketinvesting
- How Zero Commission Brokers make money: bit.ly/CommissionFreeTradeMU30
- 50/30/20 Budgetting Rule: bit.ly/503020Method
SOCIALS:
📸 Instagram - / kenji_explains
🤳 TikTok - www.tiktok.com/@kenjiexplains...
GEAR:
📹 My Favorite Books & Gear: kit.co/kenjiexplains
▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
Chapters:
0:00 - Excel Test Guidelines
0:58 - Q1 Formulas (sumifs & indexmatch)
4:38 - Q2 Formulas (data validation & lookup)
6:45 - Q3 Data Cleaning
Disclaimer: I may receive a small commission on some of the links provided at no extra cost to you.
📈 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.
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...
One of the most amazing Excel videos I have seen, thank you!
Thank you so much Kenji, you are the best of bests. Thank you for making education free 😊
My pleasure! thanks for watching :)
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 :)
your classes are super helpful!! thank you!!!
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!
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!!
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!
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.
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. ^^
Another great video - this kind of exercise is also perfect for students looking to sharpen up before starting an internship!
Very true!
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!
straight to the point, Love it!!!
thanks a lot Kenji.
Thank you for watching!!
Thanks Kenji 🙏🙏🙏🔥. This is the best thing I've ever needed. Keep them coming
Great to hear Abdimalik, more on the way!
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
Super helpful for my interview prep!! Thanks Kenji
Great to hear thank you!
Valuable content which has been explained perfectly! Thanks for sharing.
Glad you enjoyed it!
Very good. Thanks for the tutorial. Your explainations are pretty clear.
Thank you so much for sharing your knowledge
Please keep these up!!! Appreciate you! 😁💪🏾
Daniel, thanks for tuning in as always bro! Hope everything is good :)
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.
Dude u are absoluetly amazing, thank you.
Excellent sum up before interviews 👍
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!
Very helpful, thanks!
This was very helpful, Kindly upload more such business case study questions :)
Those shortcuts you use are impressive! I am just happy there is a mouse to avoid all of that memorization of keys! 😆 Great video.
I have no need for this knowledge but this was actually really informative.
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 :)
You are a true master !! Thank you for all the techniques you're teaching us
thank you so much, educating and interesting😄
Thanks Kenji!
Appreciate the video!
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❤️❤️❤️
Kenji, in the future you could be a teacher in HBS or Wharton. You are really good at explaining things you know
8:59
On german Excel version the "copy values (without format)" is [Alt] -> [R] -> [V] -> [W]
Great video Kenji!😁
Glad you enjoyed it!
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.
Thanks again.
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!
many thanks Kenji
it's usefuly
Thank you❤
Thank you
Thanks a lot
nice tutorial i have learnt alot today
Amazing job
Thank you☺️
Fantastic explanations, simple but detailed
Glad it was helpful!
3:05 ❤
I got my job as a financial analyst. This helped so much, thank you from the bottom of my heart.
very well explained
Great video Kenji. Wish me luck, I have to take an analyst test this week. This video will surely help. Thank you!
Thank you sir,, that helped me a lot!!
Thanks for watching!
Kenji you're the best!
thanks for watching!
The sample which you worked in video is contradicting with the excel file given in description.
Thank you !
Thank you very much Bro...😁
Useful. Thank you
Glad it was helpful!
best teacher on youtube
Haha cheers Solomon!
You're great! Although I had to slow down the playback speed. So, maybe you wanna slow down abit.
But yeah, great work there!
Fabulous video.. v well explained
Thank you!
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
Un crack en Excel!
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??
Impressive.
Wait... That's an actual test in serious interviews? I'm exceling just for fun, and knew everything you told us. :D
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
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.
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.
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 :)
Nice video but one minor issue -- US phone number pattern is 3 AC 3 Exchange 4 local. (10 digits) In other words +1(###) ###-####
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.
Wow this channel is amazing. Kenji i thank you for your videos you will be a huge help for the next few years for me!
Happy to hear that!
Happy to see that I could pass with ease 😆
Lets go!
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?
"That candidate really knows his stuff eh?"
"Yeah he really does. Better not give him the job, he'll show us up!"
😁
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
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
I never learned this in college 😩
We used SPSS.
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?
Hi Kenji, how do I download the file you working on?
Cheers lad
Cheers!
cool
Is there a training course to learn excel?
Ill rather use Power Query for the section 2 question to clean up the data quickly and move on
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?
Hi quick question. Why when I do convert text to columns does the operations not convert into the names. 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
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...
Why does the IndexMatch not work if we change the state to CT or FL? It only works for NY.
At the same cell you have NY. If we change it to CT or FL, it gives back an error
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))
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.
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.
If my excell skills are this strong already. What kind of jobs can I look for? I’m in an unrelated field.
I can do all of this already, can I get a decent paying job with these skills?