REAL Excel Interview Tests for Business & Finance Roles

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.

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

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

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

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

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

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

[@michaelquach7046]

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

[@KenjiExplains]

Very true!

[@sammy88l99]

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

[@KenjiExplains]

My pleasure! thanks for watching :)

[@lrok4163]

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

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

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

[@abdimalikbarkadle7559]

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

[@KenjiExplains]

Great to hear Abdimalik, more on the way!

[@goldendarkness92]

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

[@KenjiExplains]

Thank you for watching!!

[@Wissro]

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

[@user-jd8mw9sw8h]

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.

[@Farmerjo007]

Love the excel videos ! Please continue making more.

[@KenjiExplains]

Thank you! Yes more coming soon

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

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

[@chetanmoheeput6254]

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

[@KenjiExplains]

Glad you enjoyed it!

[@lucson7672]

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

[@anjalipandey3730]

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

[@danielmwabila8064]

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

[@KenjiExplains]

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

[@camlex6310]

Super helpful for my interview prep!! Thanks Kenji

[@KenjiExplains]

Great to hear thank you!

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

[@nickr.4681]

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

[@gregdragon369]

Thank you so much for sharing your knowledge

[@mrCetus]

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

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

[@Ainigma]

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

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

[@abunaa]

Very helpful, thanks!

[@simondidier5116]

Excellent sum up before interviews 👍

[@aquafortis2579]

Dude u are absoluetly amazing, thank you.

[@leon.se3367]

Appreciate the video!

[@hunterlake2943]

Thanks Kenji!

[@diwakarmishra2241]

Thank you

[@ayoomariana6406]

nice tutorial i have learnt alot today

[@GraciaEdhyta]

thank you so much, educating and interesting😄

[@lantran1617]

Thanks a lot

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

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

[@noorullahsalarzai6603]

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

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

[@kristinedyanpicazo8621]

Thank you☺️

[@omerabdalmajed452]

many thanks Kenji it's usefuly

[@solomonbhandari-young4154]

best teacher on youtube

[@KenjiExplains]

Haha cheers Solomon!

[@questionresearch8721]

Thanks again.

[@sureshbabugannamani7985]

very well explained

[@algeria_online_fair]

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

[@Tepalus]

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

[@flourishwithMJ]

Thank you❤

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

[@thehi101000]

Great video Kenji!😁

[@KenjiExplains]

Glad you enjoyed it!

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

[@Boombloom_]

Fantastic explanations, simple but detailed

[@KenjiExplains]

Glad it was helpful!

[@M_Kashif_math-pro]

3:05 ❤

[@shabchique7149]

Great video Kenji. Wish me luck, I have to take an analyst test this week. This video will surely help. 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

[@myY398]

Thank you very much Bro...😁

[@fivepotfury1132]

Un crack en Excel!

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

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

[@TheThomasmbajjwe]

Kenji you're the best!

[@KenjiExplains]

thanks for watching!

[@MetalBreakdown]

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

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

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

[@kingsleyuju1867]

Impressive.

[@gurharkrishanbureau187]

Fabulous video.. v well explained

[@KenjiExplains]

Thank you!

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

[@alexalvarezfernandez1463]

Useful. Thank you

[@KenjiExplains]

Glad it was helpful!

[@artzuh]

Happy to see that I could pass with ease 😆

[@KenjiExplains]

Lets go!

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

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

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

[@mohammadalhendi2719]

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

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

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

[@aliejalloh8719]

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

[@sophieswain6369]

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

[@AAM29290]

I never learned this in college 😩 We used SPSS.

[@pranitmathur2507]

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

[@Ja.floppa]

Cheers lad

[@KenjiExplains]

Cheers!

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

[@beckysu9728]

cool

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

[@ahoodllsai]

Is there a training course to learn excel?

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

[@baotrannguyen7872]

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

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

[@gllincoln]

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

[@ebenasare8661]

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

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

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

[@yasirbinmusleh4931]

IS IT THE SAME IF I WANT TO SUMIFS THE REVENUE AND EXPENSES BECAUSE IT DIDN'T WORK.

[@oscarlee1693]

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

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