5 MUST-KNOW Excel Interview Questions

Подписаться 522 тыс.

Просмотров 220 тыс.

50% 1

Most common Excel Interview Test Questions you need to know.
👉 Excel for Business & Finance Course: www.careerprinciples.com/cour...
🆓 DOWNLOAD Free Excel file for this video: view.flodesk.com/pages/62ffa6...
In this video we go over 5 of the most common excel interview test questions for business & finance roles such as a business analyst or a financial analyst. The questions are:
Q1: Use two different formulas to look for a value within a table. You can use vlookup, xlookup, or index match for this
Q2: Find the quantity sold to reach 10k in profit. You can use goal seek under what if analysis for this
Q3: Clean this raw set of data. For this you can use the text to columns feature, alongside the conditional formatting to identify the relevant values
Q4: Visualize the data. For this you can use a combo chart with two different axis
Q5: Show by quarter, the quantity sold, the total sales, and the average sale price. This can be found using a pivot table
Note: there is more than one way to do these questions. If you have any other methods, comment them below!
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
SOCIALS:
📸 Instagram - careerprinc...
🤳 TikTok - / career_principles
🧑‍💻 LinkedIn - / careerprinciples
GEAR:
📹 My Favorite Books & Gear: kit.co/kenjiexplains
▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
Chapters:
0:00 - Q1
2:25 - Q2
4:03 - Q3
7:03 - Q4
9:08 - Q5
Disclaimer: I may receive a small commission on some of the links provided at no extra cost to you.

Опубликовано:

14 июн 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 135

@KenjiExplains Год назад

👉 Take our Excel Course! www.careerprinciples.com/courses/excel-for-business-finance

@bennyblanco8973 Год назад

What's the cost, and duration?

@Chrisdb851 10 месяцев назад

I particularly like the videos are focused on the tutorials and in under 12 minutes I've brushed up excel skills.

@Dexcelo Год назад

Your explanations are simple and fluid. Hats off and thank you! 🙏🙏🙏

@mikefochtman7164 Год назад

Nice bit of info. I'd just add that for the last problem, you could format 'total sales' and 'average cost' to some form of currency. That would get rid of those long trailing decimals and put 'sales' figures in a more obvious form.

@valscripted Год назад

I especially like that you have a basic understanding of design and examples. This doesn't sound particularly surprising, but so many courses and tutorials use a dull monochrome, barely formatted table for their examples. Then they use the simplest, most boring names like John Doe and Jane Doe, and maybe one or two more columns before they say that's enough for an example. Here, the tables not only look clear and concise, but you also have a somewhat larger data set on which the formulas can simply be shown even better. A small thing that goes a long way!

@KenjiExplains Год назад

Thanks for the awesome comment! Made my day :)

@sha.aesthetic Год назад

totally agree with this 👍

@mustaphaibrahim7882 Год назад

My new favorite page on RU-vid. Been learning quite a lot. Thank you!

@KenjiExplains Год назад

Great to hear! Thanks for watching :)

@sha.aesthetic Год назад

I just learned the pivot table 10 mins before my interview through this video and didn't even practise my self and performed it in the job assessment for the first time. I did make a slight error but it was majorly fine. Thank you for saving me at the last moment.

@Autonova Год назад

Learned a lot here and I’ve been using Excel for 6 years. Great vid!

@StephenBoothUK Год назад

I’ve been using Excel for data analysis for about 15 years now. I can count the number of times I’ve had to use Index-Match on the fingers of one nostril (although I’ve had to fix other people’s use of them, which have broken, many times; Index-Match is very fragile). These days, PowerQuery and DAX are more useful.

@floraxu8957 10 месяцев назад

Great tutorial! Thanks, Kenji!

@nkanyisomajola705 Год назад

Kenji is half Excel Specialist and half Ctrl + Shift + Down Arrow 😅. I just discovered your channel, I love your content. Learning a lot 🤝🏾 thank you.

@prashantbalodi202 2 месяца назад

Thank you Kenji for all the effort you put in to make these videos.

@KenjiExplains 2 месяца назад

Thank you for watching them 🚀

@vikramadityarathore97 Год назад

Hey Kenji liked the fact you made a video based on interview after quite a while, quite insightful. Could you make video on any finance topic you like next and in the future (not immediately)bring someone from the industry to discuss what skills are required for finance roles like you once brought a financial analyst from Tesla.

@KenjiExplains Год назад

Hey thank you for the video suggestions! I'll keep them noted :)

@chebochitalu6543 Год назад

You are awesome 👍. I enjoy your excel videos and they are very helpful. Keep it up!

@KenjiExplains Год назад

Happy to hear that!

@hisokaamorou4211 Год назад

Another great video kenji!

@TimsWheelbarrowMTG Год назад

I enjoyed this. Given the fiction that this is for an interview setting, it'd be cool to see the good/better/best answers to some of these questions. While it's obviously gilding the lily on Q1 to do named ranges for lookups and potentially ISERROR contingencies for typos in the search field, doing so would make it clearer to an interviewer that you knew your stuff for example.

@tristanontheway Год назад

wow, once again would like to thank Michael for helping out on my Amazon excel interview case.

@KenjiExplains Год назад

Great to hear Tristan!

@CaptainParalytic Год назад

For the Calculation question (2), I'd be tempted to add a Profit Target cell (for example E15) and then have another cell with the formula =CEILING(E15/(C6*(1-C7)-C8)), which would instantly give the number of sales required.

@aysegulkarakas6270 3 месяца назад

that was super helpful! thanks!

@francishubertovasquez2139 Месяц назад

Your computer skills as if it's automatic thats what's in your mind carried out by your quick computer push in its applications, you have a functional sparking neurons in a relaxed mode demeanor, outstanding, how you carry your skills in a space universe environment science thing complex application is something to look forward to, as long as those modern future cities doesn't have bleak toxin poisonous clouds in there and no roving mutational and genetic spliced chaotic monsters, we can appreciate what the sophistication is all about in the near future. Thanks pre.

@user-dm8zs6xq7m 6 месяцев назад

Really Great job ,Thank you !!1

@vladimirz5485 Год назад

Kanji never disappoints hats off man

@Perfectsound88 Год назад

Thanks so much for this one.You are Aweeesome.Between Missed Dovak Njokovic in the last data set 😂😂😂

@flourishwithMJ 11 месяцев назад

Thank you ❤😊

@camlex6310 Год назад

Going to be an excel pro soon!! Cheers

@KenjiExplains Год назад

Great to hear!

@varshasharma4803 Год назад

thank you bro

@gurharkrishanbureau187 Год назад

Very well explained

@KenjiExplains Год назад

Glad it was helpful!

@Scherbyy Год назад

Awesome!

@pavanigoud98 4 месяца назад

Hi Kenji Such a beautiful and helpful one. Hoping to see more related videos of excel test preparation. Thanks

@KenjiExplains 4 месяца назад

Hey check out my newest video for more!

@andreugiro2807 Год назад

Definetely I will prepare my interviews with those tips!! Thanks Kenji!😁

@LWOJ Год назад

Watch this video to pass excel test ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-csZIeanIfGI.html

@solomonbhandari-young4154 Год назад

Excellent!

@LWOJ Год назад

Watch this video to pass excel test ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-csZIeanIfGI.html

@resresres1 Год назад

This only works for basic interview questions. In the real world, the data is never so clean (unless you are just doing basic stuff) and there is too much data to clean up by hand and requires either visual basic coding (built into Excel or using Visual Studio) or some other programming language to clean up the data. In my work, I use Python to read the Excel files, clean up the data or extract the data I want, then output either to a hand built Python GUI or output back to the Excel file or new Excel file.

@calebbrown540 Год назад

2 questions: would you say it is easier to use python to clean data then VBA? Also, I would assume that python has more resources available to learn cleaning scripts then vba, is that a correct assumption?

@annekekramer3835 Год назад

@@calebbrown540 Python is very useful for large amounts of data, or if you get new data every day/week/etc. As an engineer, I don't really need Python, but I can totally imagine that Python is a lifesaver if you work in sales! Just say to your boss it takes a day, run your pre-programmed Python and read a book the rest of the day. Or you could use macros in Excel, but then the data needs to be at least a little bit predictable regarding the format.

@resresres1 Год назад

@@calebbrown540 You are limited with using VBA in Excel, but if you use VB in Visual Studio instead to read the excel files, it can be just as robust as Python when it comes to dealing with Excel files. I have a co-worker who uses VB, while I use Python. From what I've seen Python is faster at handling large datasets and large Excel faster. However, the downside with Python is distribution. If you want others to use your program, then you need to convert to an exe and because Python is an interpreted language it has to package ALL dependant files together, making the exe quite hefty. In comparison, Visual Basics is a compiled language and all of it's dependant files are built into Windows, making the exe quite small and easier to distribute. I get around this by putting my Python exe file onto the server and having co-workers just create a shortcut to it on their computers if they want to use it, instead of downloading it. Python, I think, is easier to code compared to VB though, although some of the VB code I've seen from my coworker didn't seem all that bad.

@resresres1 Год назад

@@annekekramer3835 Python is not just a life saver in sales, but other jobs as well. I'm a quality administrator for a small airplane manufacturer and it has made my job so much easier (and faster) using Python to automate various things. Automating things that people would not have even thought of (not just using it for excel files).

@LWOJ Год назад

Watch this video to pass excel test ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-csZIeanIfGI.html

@michaelquach7046 Год назад

Solid prep questions! Keep up the good work 👍

@KenjiExplains Год назад

Appreciate it!

@JulianFernandez Год назад

thanks my dude

@KenjiExplains Год назад

Any time!

@hunterburns Год назад

Sometimes I wonder how a man like you could be so amazing. Really you are great! Every time I watch each of your videos it makes me feel so good. Always keep up the good work bro. Take love 💖💖💖

@KenjiExplains Год назад

Thank you so much for the support!

@woodbybill1483 Год назад

@@KenjiExplains get a room

@edphillips2998 11 месяцев назад

For the question that required creating a chart, I’d subtract points for not re-setting the base-value of the gross-margin % graph to zero.

@gobindaroy7811 Год назад

Awesome

@daddada2984 Год назад

Nice

@geistzilla Год назад

#2 is a trick question! The customer pays the sales tax on top of the sales price, it’s not netted out of revenue.

@k364k364 Год назад

9:47 I didn't know who to cheer for when seeing Roger Nadal play Rafael Federer play against each other.

@glorynsefik Год назад

please how do we know what value to enter for connor west, l'm a beginner and from the video seems like f6 automatically populated for connor west, the question is how did he get that value? thank you

@deadspeedv Год назад

Nice. I didn't know about the what if function. I would have just done a calc of $10000/profit of selling 1 bottle

@deadspeedv Год назад

@sandrinamichelin9741 Месяц назад

Hi do you have a coupon code for your business & finance course? Thanks! And your videos are very informative.

@KenjiExplains Месяц назад

Hey, thanks for your interest! Use EMAIL10 for 10% OFF: www.careerprinciples.com/courses/excel-for-business-finance

@transformer88 Год назад

Nice content, just feel it’s kinda too basic even for an accountant role

@murewamuresco3468 Год назад

Where can i get excel practice questions? I don’t want course, just questions

@johanlarsson9805 Год назад

Good explanations. However, no PowerQueries to avoid having to redo this every month? No tables, just straigt up data in cells? No structured references to acchive consistency? No multi-category selects for the Index-Match?

@rzlosty Год назад

I think you've misunderstood the purpose of this type of video. It is designed to get someone through a simple Excel competency assessment. Its not supposed to be advanced, or go into things like PowerQuery. I dont think an interviewer for an entry level role (like the ones listed at 0:01) would be looking for PowerQuery knowledge.

@almedinkorda2787 Год назад

Hi Kenji! I like to watch your videos, but still I'm not finding solution of my problem.. How can I calculate data from specific cell in column G for example, but row change itself daily, is there any option to calculate it automatically? Thank you!

@hexahelicen Год назад

Look up the OFFSET function. Instead of referring to a single cell in your formula, let Offset pick it from a larger range.

@ThisisDikky Год назад

Lol... I'm really laughing at myself just seeing Question 2. I had a similar question in a pre-interview Data Analytics Test with Bolt 3 years ago. I wasn't yet advanced in my Excel skills so I didn't know about What-if analysis. I remember it was one of the toughest questions for me as I couldn't even understand perfectly what was required of me. I actually did it manually 😂😂😂, and I guess I passed the test because I had 2 rounds of interviews, though no offer. Thanks Kenji for this little powerful tips. I just subscribe to your channel because I want to learn more about Financial Modelling & Valuation Analysis, even though I have an FMVA certification from CFI.

@KenjiExplains Год назад

Haha that’s awesome to hear! Glad you’re finding value in the videos. Best of luck in your career 👍

@LWOJ Год назад

Watch this video to pass excel test ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-csZIeanIfGI.html

@muqali4203 Год назад

spent the last year digging into excel and visual basic. I havent bothered paying too much attention to efficiency of whatever my solutions come out to be. Seems true to me that if efficiency matters or the data is large enough that pretty much all of excels worksheet features shouldnt be used. Just do everything in visual basic. Maybe if I was working on something with others (where not everyone is a programmer) I would see more value in excels built in worksheet formulas, but for me their only usefulness is that they make it quick to hammer out a new layout for something.

@pa28cfi Год назад

I VBA almost everything within Excel.

@theparabola Год назад

If your solutions are needlessly inefficient, they're not solutions, they're problems.

@Lorentari Год назад

As an engineer I feel like I would be set (probably over-qualified tbh) if these are the questions asked in an datascience interview

@vascorodrigues4567 Год назад

My home PC with Office 365 64bits: *laughs with all the formulas available* UNLIMITED POWER My Company PC with Office 2013 32bits: What's an XLookup?

@highpath4776 Год назад

I think VLookup. HLookup have been around for ages, looks like Xlookup inclusivifies the "exact" qualifier of excel otherwise giving one (an often wrong) near guess ?

@a_country_boy Год назад

How to get by quarter and year in excel?

@i2thadean Год назад

Hi there, the download link is broken once you submit your name and email address. can you please fix?

@KenjiExplains Год назад

Should be fixed so sorry!

@s-nut Год назад

Classic rounding error on Problem Number 2. You cannot sell a portion of a bottle-- 2906 yields only $9998.96 profit, if you allow for the bulk rounding of taxes and allow the original formulas to do their work. Since you are selling by the bottle and pay taxes to the penny, you should fix the tax at $0.56 per bottle, which yields only $9996.64 profit on 2906 bottles. The What-If calculation further hides the rounding error by distributing the error across all the other values (even worse, since it's only a tax rounding problem!). Short answer-- you need to sell 2907 bottles to reach the $10,000 profit mark.

@pa28cfi Год назад

Then you have me, and I start with Alt+F11

@markuseberlein3394 Год назад

I do like those videos, but with translated excel, most commands just don't work anymore. They translated them too.

@Wiki7202 7 месяцев назад

all of this is fine, until you move on to PowerBI and DAX formulas. The syntax is similar but different just enough!

@Zilvaras2 Год назад

Most common based on what statistics?

@MatthewLenton Год назад

For Q3, I'd filter on cell colour so that anything over $300 was not visible

@Hephaestus_God Год назад

It said to identify the cells less than $300 not remove the ones that are above 300 (or filter out the ones below 300). It has a little wording difference.

@AvitalMeira Год назад

can't download the excel file, but seems I subscribed to spam.....google said - The server cannot process the request because it is malformed. It should not be retried. That’s all we know.

@KenjiExplains Год назад

Should be fixed sorry about that!

@101grandengamer7 Год назад

Real werld? Where u from?

@SethBeck Год назад

I guess using the Filter feature after text to columns is too easy.

@counterleo Год назад

Instruction is identify matching not hide non-matching

@willzinner8813 Год назад

Which question is most common?

@KenjiExplains Год назад

The formulas one in Q1 we've seen quite often!

@davidjames1684 Год назад

3:00 - If you already know 1 bottle sold yields $3.44 profit, then why not for the quantity just put =10000 / 3.44 which is about 2,907? Your method shows the interviewer your lack of simple mathematics. 4:26 - I would complain to the interviewer that the rightmost numbers' units are not specified, therefore we cannot filter based on it. Also it states sales less than $300, it doesn't say total sales. What if the 339.5 for example is a $200 sale and a $119.5 sale? Then that whole row should be filtered out.

@tapiwamapfumo6371 Год назад

cant believe im 3 hours late, but anyway im here

@KenjiExplains Год назад

Lets go!

@kenhaley4 Год назад

Good examples, but I wouldn't give you a top score for your answers. You conflated "purchases" with "sales" which are usually totally different transactions in a typical business. Also, you take the time to pretty up the chart colors, but in the last example, you left the Total Sales and Average Sale price unformatted (no commas, decimals or currency symbols). I would also object to an unstated assumption: namely, that each sale in the original question 5 table represents a quantity of 1.

@axeldahlman2405 Год назад

How come you tax the sales? Shouldn't you tax the profit? Total tax cost = quantity of bottles * (Sales-Cost) * tax rate

@KenjiExplains Год назад

Because I was thinking more sales tax/VAT tax

@axeldahlman2405 Год назад

@@KenjiExplains okay, sure

@BigAlCapwn Год назад

It's a sales tax, not Corporation Tax

@highpath4776 Год назад

@@BigAlCapwn Also you pass the raw gross profit to the tax advisors for them to mitigate the tax and bring in the capital allowances and so on and split of overheads.

@Commander_Chief Год назад

Too fast for. Me. I always have too watch it. Again

@KenjiExplains Год назад

Sorry to hear! Will make them slower in the future

@LWOJ Год назад

Watch this video to pass excel test ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-csZIeanIfGI.html

@owales1804 Год назад

You can always reduce the playback speed. Click the settings button, click the arrow in front of Playback speed and select a slower speed.

@gumbilicious1 Год назад

Is this a thing? Do people really have to do excel problems for interviews?

@KenjiExplains Год назад

yes....we've had excel interviews for Amazon, Tesla etc.

@joukenienhuis6888 Год назад

I think those are assessments and they check your skills

@kabirlytics Год назад

It could be made better

@KenjiExplains Год назад

Sorry to hear that...can you elaborate so I can improve future videos?

@ABaumstumpf Год назад

I would seriously question why you would abuse excel for such things instead of any of the hundreds of better suited tools?

@emilwandel Год назад

Technical interview questions for Excel? Laughable. How much do these people make in their positions. I have to change my career.

@theparabola Год назад

Anyone promoting VLOOKUP in training content has no credibility.

@PJMcInerney Месяц назад

not sure if he is 'promoting' - facts are that even as I write this in 2024 so many Companies still make reference to V look up in job adverts ( ok I guess quite of these are agency adverts which can be a bit ' lazy')

@jasonscott6171 Год назад

please stop teaching vlookup

@nickthompson1812 Год назад

Because xlookup is a thing now? Or why do you suggest stopping?

@jasonscott6171 Год назад

@@nickthompson1812 Vlookup references the table you select by column number. Xlookup actually looks up against the column/rows you input similar to index matching. It stops the lookup from break if you start moving columns around etc. Also xlookup does not require the reference cell to be left of the look up. You can do right to left and left to right, up and down by rows also replacing hlookup.

@PJMcInerney Месяц назад

@@jasonscott6171 Look at every single job advert that requires Excel and they still mention V look up - yes even in 2024 - older versions of Excel - pre 2020 will not have X look up

@bennyblanco8973 Год назад

Excel for business and finance, how long does this course take, and what's the price?... @kenji

@KenjiExplains Год назад

All the information is on the landing page here: www.careerprinciples.com/courses/excel-for-business-finance