REAL Excel Interview Tests for Business & Finance Roles

[@KenjiExplains]

📈 My Excel for Business & Finance Course: www.careerprinciples.com/courses/excel-for-business-finance

[@joegrafton5836]

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 😀

[@DatatechAnalytics]

@@corybabcock9969 You are very right. Sumproduct will work better instead of index match. Especially if we don't have unique date for States.

[@lorraineodigwe5687]

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.

[@yves1027]

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!!

[@KenjiExplains]

Lets go! Hope it went well for you :)

[@andresvera323]

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!

[@Alkheezy]

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..

[@KenjiExplains]

Thanks for the awesome comment ! I'm glad it was helpful!

[@joelquinn5347]

Yeah no formular for that...

[@entestgamer3425]

kenji want to say ur the most down to earth helpful finance channel on utube, vids like this are so important,

[@KenjiExplains]

thank you! Really appreciate your comment :)

[@sjdiggs7641]

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.

[@Tepalus]

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.

[@mmacg6440]

@@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

[@Tepalus]

@@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. ^^

[@MetalBreakdown]

I got my job as a financial analyst. This helped so much, thank you from the bottom of my heart.

[@NaturalDota2]

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.

[@Asian_Import]

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!

[@Jalalmaher1983]

One of the most amazing Excel videos I have seen, thank you!

[@mikeattia8750]

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?

[@justbecause6472]

Thank you

[@KenjiExplains]

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.

[@taviousimperial8289]

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!

[@bartoszbereszko]

Thank you!!!

[@HudIi]

You need this for big 4 recruiting excel test

[@daisymiller2179]

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.

[@KenjiExplains]

Awesome to hear!

[@andresvera323]

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.

[@davilawendy]

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 :)

[@noorullahsalarzai6603]

The sample which you worked in video is contradicting with the excel file given in description. Thank you !

[@nickr.4681]

I have no need for this knowledge but this was actually really informative.

[@ercik4725]

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

[@paulocarvalho9406]

Those shortcuts you use are impressive! I am just happy there is a mouse to avoid all of that memorization of keys! 😆 Great video.

[@gllincoln]

Nice video but one minor issue -- US phone number pattern is 3 AC 3 Exchange 4 local. (10 digits) In other words +1(###) ###-####

[@emmanueladeola5122]

You're great! Although I had to slow down the playback speed. So, maybe you wanna slow down abit. But yeah, great work there!

[@jeffsmith1905]

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.

[@Ainigma]

8:59 On german Excel version the "copy values (without format)" is [Alt] -> [R] -> [V] -> [W]

[@michaelquach7046]

Another great video - this kind of exercise is also perfect for students looking to sharpen up before starting an internship!

[@KenjiExplains]

Very true!

[@AlfredPeverelli]

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.

[@harveyts3]

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

[@p_anjali_codes]

This was very helpful, Kindly upload more such business case study questions :)

[@lrok4163]

your classes are super helpful!! thank you!!!

[@lucson7672]

Very good. Thanks for the tutorial. Your explainations are pretty clear.

[@LEQN]

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.

[@joelwillis2043]

yes, looking up and adding numbers doesn't require years of statistics

[@solomonbhandari-young4154]

best teacher on youtube

[@KenjiExplains]

Haha cheers Solomon!

[@emadr5780]

Thank you very much. I would really appreciate it if you could make more of these REAL Excel Interview Tests videos.

[@KenjiExplains]

Noted your suggestion, thanks for watching!

[@muhammadyasir2144]

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??

[@sammy88l99]

Thank you so much Kenji, you are the best of bests. Thank you for making education free 😊

[@KenjiExplains]

My pleasure! thanks for watching :)

[@camlex6310]

Super helpful for my interview prep!! Thanks Kenji

[@KenjiExplains]

Great to hear thank you!

[@SurmaSampo]

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.

[@363slide]

@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!

[@SurmaSampo]

@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.

[@markom001]

Kenji, these are sososo helpful, thank you for all excel videos you already made - a BIG HELP! You explain everything so easily! thank you!

[@KenjiExplains]

Thanks for the awesome comment Marko!

[@nikhilpachauri5761]

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.

[@363slide]

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.

[@peterkoellner7818]

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.

[@emmanuelmonnier4153]

His works only because the first value for each month is NY, doesn't it?

[@simondidier5116]

Excellent sum up before interviews 👍

[@mdsaifullahruman8115]

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❤️❤️❤️

[@aquafortis2579]

Dude u are absoluetly amazing, thank you.

[@jingxuanyap8939]

Thanks Kenji, really can't thank you enough for the valuable content that you have been sharing!!!

[@KenjiExplains]

That's awesome to hear Jing. Thanks a ton for the nice comment!!

[@Farmerjo007]

Love the excel videos ! Please continue making more.

[@KenjiExplains]

Thank you! Yes more coming soon

[@abdimalikbarkadle7559]

Thanks Kenji 🙏🙏🙏🔥. This is the best thing I've ever needed. Keep them coming

[@KenjiExplains]

Great to hear Abdimalik, more on the way!

[@cskong598]

Kenji, It's a great video. Thank you for sharing. I hope there are more similar videos in future.

[@KenjiExplains]

Awesome to hear! Yes, more to come this Sunday :)

[@goldendarkness92]

straight to the point, Love it!!! thanks a lot Kenji.

[@KenjiExplains]

Thank you for watching!!

[@gregdragon369]

Thank you so much for sharing your knowledge

[@algeria_online_fair]

You are a true master !! Thank you for all the techniques you're teaching us

[@danielmwabila8064]

Please keep these up!!! Appreciate you! 😁💪🏾

[@KenjiExplains]

Daniel, thanks for tuning in as always bro! Hope everything is good :)

[@ayoomariana6406]

nice tutorial i have learnt alot today

[@s3icc0]

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

[@ahoodllsai]

Is there a training course to learn excel?

[@ignaciopazgarcia5370]

Kenji, in the future you could be a teacher in HBS or Wharton. You are really good at explaining things you know

[@KenjiExplains]

Hi Ignacio, thank you for the nice message man! Who knows what the future holds!

[@hunterlake2943]

Thanks Kenji!

[@evandieterich7592]

I can do all of this already, can I get a decent paying job with these skills?

[@Wissro]

Love these excel videos, thanks for providing us with such valuable content Kenji!

[@pranitmathur2507]

Make one for investment banking interview questions with some questions shared over pdf or something

[@tingkagol]

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.

[@Boombloom_]

Fantastic explanations, simple but detailed

[@KenjiExplains]

Glad it was helpful!

[@M_Kashif_math-pro]

3:05 ❤

[@Ole.B.S]

Great video Kenji!😁

[@KenjiExplains]

Glad you enjoyed it!

[@sophieswain6369]

Hi quick question. Why when I do convert text to columns does the operations not convert into the names. Thank you!

[@aliejalloh8719]

Hi Kenji, how do I download the file you working on?

[@scottrels8251]

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

[@AAM29290]

I never learned this in college 😩 We used SPSS.

[@sureshbabugannamani7985]

very well explained

[@ebenasare8661]

Ill rather use Power Query for the section 2 question to clean up the data quickly and move on

[@chetanmoheeput6254]

Valuable content which has been explained perfectly! Thanks for sharing.

[@KenjiExplains]

Glad you enjoyed it!

[@nolsn19]

Thank you!!

[@GraciaEdhyta]

thank you so much, educating and interesting😄

[@elxenomorph534]

Thank you sir,, that helped me a lot!!

[@KenjiExplains]

Thanks for watching!

[@kingclueless]

"That candidate really knows his stuff eh?" "Yeah he really does. Better not give him the job, he'll show us up!" 😁

[@omerabdalmajed452]

many thanks Kenji it's usefuly

[@Tepalus]

Wait... That's an actual test in serious interviews? I'm exceling just for fun, and knew everything you told us. :D

[@counterleo]

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?

[@oscarlee1693]

If my excell skills are this strong already. What kind of jobs can I look for? I’m in an unrelated field.

[@abunaa]

Very helpful, thanks!

[@mohammadalhendi2719]

hello Kenji, what if I don't have office 365, what to use instead of Xlookup?

[@yadude101]

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.

[@TheThomasmbajjwe]

Kenji you're the best!

[@KenjiExplains]

thanks for watching!

[@magdasava6807]

Amazing job

[@shabchique7149]

Great video Kenji. Wish me luck, I have to take an analyst test this week. This video will surely help. Thank you!

[@artzuh]

Happy to see that I could pass with ease 😆

[@KenjiExplains]

Lets go!

[@MohamedGamal-pj6wd]

Hi I neeed to ask what is the difference between xlookup and index &match

[@thesenate8268]

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.

[@leon.se3367]

Appreciate the video!

[@jacktuchner8151]

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"

[@KenjiExplains]

Thanks! You need to turn on autocomplete on the excel preferences I think that's what you're missing

[@myY398]

Thank you very much Bro...😁

[@dre5229]

For the last part why didn’t you use format as table instead?

[@kristinedyanpicazo8621]

Thank you☺️

[@MohitKumar-iw5io]

apply vlookup to get region based on State please help me

[@parksplayground2546]

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!

[@KenjiExplains]

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))

[@questionresearch8721]

Thanks again.

[@ahsantahir1289]

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

[@indira6699]

Thank you

[@yadude101]

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...

[@flourishwithMJ]

Thank you❤

[@ThuyNguyen-hs6ec]

Index match doesn't work correctly if you change the State.

[@lantran1617]

Thanks a lot

[@alexalvarezfernandez1463]

Useful. Thank you

[@KenjiExplains]

Glad it was helpful!

[@TechnoHacker1000]

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!

[@KenjiExplains]

Happy to hear that!