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

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

[@zainabkiani4008]

I have an interview tomorrow and I am practicing with your videos . Great work . Thank you

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

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

[@Jalalmaher1983]

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

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

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

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

[@michaelquach7046]

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

[@KenjiExplains]

Very true!

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

[@lrok4163]

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

[@p_anjali_codes]

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

[@sammy88l99]

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

[@KenjiExplains]

My pleasure! thanks for watching :)

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

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

[@lucson7672]

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

[@Ainigma]

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

[@DrillYorkBeat]

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

[@KenjiExplains]

Great to hear Abdimalik, more on the way!

[@nickr.4681]

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

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

[@solomonbhandari-young4154]

best teacher on youtube

[@KenjiExplains]

Haha cheers Solomon!

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

[@Farmerjo007]

Love the excel videos ! Please continue making more.

[@KenjiExplains]

Thank you! Yes more coming soon

[@danielmwabila8064]

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

[@KenjiExplains]

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

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

[@noorullahsalarzai6603]

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

[@goldendarkness92]

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

[@KenjiExplains]

Thank you for watching!!

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

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

[@aquafortis2579]

Dude u are absoluetly amazing, thank you.

[@camlex6310]

Super helpful for my interview prep!! Thanks Kenji

[@KenjiExplains]

Great to hear thank you!

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

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

[@Wissro]

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

[@kingclueless]

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

[@TheThomasmbajjwe]

Kenji you're the best!

[@KenjiExplains]

thanks for watching!

[@simondidier5116]

Excellent sum up before interviews 👍

[@ayoomariana6406]

nice tutorial i have learnt alot today

[@GraciaEdhyta]

thank you so much, educating and interesting😄

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

[@Ole.B.S]

Great video Kenji!😁

[@KenjiExplains]

Glad you enjoyed it!

[@gregdragon369]

Thank you so much for sharing your knowledge

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

[@chetanmoheeput6254]

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

[@KenjiExplains]

Glad you enjoyed it!

[@abunaa]

Very helpful, thanks!

[@elxenomorph534]

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

[@KenjiExplains]

Thanks for watching!

[@Tepalus]

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

[@omerabdalmajed452]

many thanks Kenji it's usefuly

[@Boombloom_]

Fantastic explanations, simple but detailed

[@KenjiExplains]

Glad it was helpful!

[@M_Kashif_math-pro]

3:05 ❤

[@algeria_online_fair]

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

[@KenjiExplains]

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!

[@cgfetner]

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.

[@daniel.watching]

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.

[@Maelstrom000]

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

[@sureshbabugannamani7985]

very well explained

[@myY398]

Thank you very much Bro...😁

[@hunterlake2943]

Thanks Kenji!

[@nolsn19]

Thank you!!

[@leon.se3367]

Appreciate the video!

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

[@magdasava6807]

Amazing job

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

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

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

[@kristinedyanpicazo8621]

Thank you☺️

[@artzuh]

Happy to see that I could pass with ease 😆

[@KenjiExplains]

Lets go!

[@aliejalloh8719]

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

[@shabchique7149]

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

[@alexalvarezfernandez1463]

Useful. Thank you

[@KenjiExplains]

Glad it was helpful!

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

[@camsport2258]

=SUMPRODUCT($G$13:$G$27*($D$13:$D$27=C7)*($C$13:$C$27=$B$9)) maybe it is to late but that may help someone else :)

[@flourishwithMJ]

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

[@gurharkrishanbureau187]

Fabulous video.. v well explained

[@KenjiExplains]

Thank you!

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

[@ahoodllsai]

Is there a training course to learn excel?

[@questionresearch8721]

Thanks again.

[@indira6699]

Thank you

[@lantran1617]

Thanks a lot

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

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

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

[@gllincoln]

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

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

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

[@sophieswain6369]

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

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

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

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

[@AAM29290]

I never learned this in college 😩 We used SPSS.

[@willzinner8813]

How long would they give you for a test like this?

[@tecnifibre88]

The video says around 30 minutes

[@KenjiExplains]

Around 30 is reasonable. Keep in mind you might have to explain things live so the interviewer will be watching throughout

[@mohammadalhendi2719]

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

[@fivepotfury1132]

Un crack en Excel!

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

[@kingsleyuju1867]

Impressive.

[@Nick-vt4pu]

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?

[@KenjiExplains]

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.

[@evandieterich7592]

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

[@ebenasare8661]

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