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.
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!
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.
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.
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.
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.
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 💖💖💖
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.
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.
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.
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?
@@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.
@@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.
@@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).
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?
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.
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.
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
i have a problem in seperating the data with column. the numbers with (.5) is changing to a custom format, which is [13/01/1900 20.05.00] meanwhile it should be 332.5. it makes the data chaos and the format less than is messed up. How do I fix it?
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.
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.
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 ?
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.
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!
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.
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.
@@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.
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')
@@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.
@@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
👉 Take our Excel Course! www.careerprinciples.com/courses/excel-for-business-finance
What's the cost, and duration?
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.
I particularly like the videos are focused on the tutorials and in under 12 minutes I've brushed up excel skills.
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!
Thanks for the awesome comment! Made my day :)
totally agree with this 👍
how petty, how frivolous
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.
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.
My new favorite page on UA-cam. Been learning quite a lot. Thank you!
Great to hear! Thanks for watching :)
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.
Your explanations are simple and fluid. Hats off and thank you! 🙏🙏🙏
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.
i ever see such type of amazing classes .thank you so much and happy learning
Thank you Kenji for all the effort you put in to make these videos.
Thank you for watching them 🚀
Great tutorial! Thanks, Kenji!
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.
Hey thank you for the video suggestions! I'll keep them noted :)
Your videos are so easy to follow. Thank you !
Hi Kenji Such a beautiful and helpful one. Hoping to see more related videos of excel test preparation. Thanks
Hey check out my newest video for more!
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 💖💖💖
Thank you so much for the support!
@@KenjiExplains get a room
Learned a lot here and I’ve been using Excel for 6 years. Great vid!
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.
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.
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.
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?
@@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.
@@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.
@@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).
Watch this video to pass excel test
ua-cam.com/video/csZIeanIfGI/v-deo.html
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.
Another great video kenji!
wow, once again would like to thank Michael for helping out on my Amazon excel interview case.
Great to hear Tristan!
You are awesome 👍. I enjoy your excel videos and they are very helpful. Keep it up!
Happy to hear that!
1.Seach vlook up & Index Match
2.Goal seek
3.Text to columns
4.Visaulize -Graph Creation
5. Creation of pivot table
Going to be an excel pro soon!! Cheers
Great to hear!
9:47 I didn't know who to cheer for when seeing Roger Nadal play Rafael Federer play against each other.
Hi do you have a coupon code for your business & finance course? Thanks! And your videos are very informative.
Hey, thanks for your interest! Use EMAIL10 for 10% OFF: www.careerprinciples.com/courses/excel-for-business-finance
Kanji never disappoints hats off man
that was super helpful! thanks!
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?
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.
#2 is a trick question! The customer pays the sales tax on top of the sales price, it’s not netted out of revenue.
Really Great job ,Thank you !!1
Thanks so much for this one.You are Aweeesome.Between Missed Dovak Njokovic in the last data set 😂😂😂
Excellent!
Watch this video to pass excel test
ua-cam.com/video/csZIeanIfGI/v-deo.html
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.
If your solutions are needlessly inefficient, they're not solutions, they're problems.
Thank you ❤😊
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
i have a problem in seperating the data with column. the numbers with (.5) is changing to a custom format, which is [13/01/1900 20.05.00] meanwhile it should be 332.5. it makes the data chaos and the format less than is messed up. How do I fix it?
Nice. I didn't know about the what if function. I would have just done a calc of $10000/profit of selling 1 bottle
No
Awesome!
Where can i get excel practice questions?
I don’t want course, just questions
thank you bro
Definetely I will prepare my interviews with those tips!! Thanks Kenji!😁
Watch this video to pass excel test
ua-cam.com/video/csZIeanIfGI/v-deo.html
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.
Haha that’s awesome to hear! Glad you’re finding value in the videos. Best of luck in your career 👍
Watch this video to pass excel test
ua-cam.com/video/csZIeanIfGI/v-deo.html
Very well explained
Glad it was helpful!
Solid prep questions! Keep up the good work 👍
Appreciate it!
How to get by quarter and year in excel?
thanks my dude
Any time!
Nice content, just feel it’s kinda too basic even for an accountant role
Hi there, the download link is broken once you submit your name and email address. can you please fix?
Should be fixed so sorry!
Awesome
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.
For Q3 u cañ use flash fill
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?
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 ?
As an engineer I feel like I would be set (probably over-qualified tbh) if these are the questions asked in an datascience interview
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.
Should be fixed sorry about that!
I do like those videos, but with translated excel, most commands just don't work anymore. They translated them too.
Nice
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!
Look up the OFFSET function. Instead of referring to a single cell in your formula, let Offset pick it from a larger range.
Most common based on what statistics?
For Q3, I'd filter on cell colour so that anything over $300 was not visible
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.
Which question is most common?
The formulas one in Q1 we've seen quite often!
I guess using the Filter feature after text to columns is too easy.
Instruction is identify matching not hide non-matching
all of this is fine, until you move on to PowerBI and DAX formulas. The syntax is similar but different just enough!
Real werld? Where u from?
cant believe im 3 hours late, but anyway im here
Lets go!
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.
How come you tax the sales? Shouldn't you tax the profit? Total tax cost = quantity of bottles * (Sales-Cost) * tax rate
Because I was thinking more sales tax/VAT tax
@@KenjiExplains okay, sure
It's a sales tax, not Corporation Tax
@@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.
Rafael Federer and roger nadal 😂
Too fast for. Me. I always have too watch it. Again
Sorry to hear! Will make them slower in the future
Watch this video to pass excel test
ua-cam.com/video/csZIeanIfGI/v-deo.html
You can always reduce the playback speed. Click the settings button, click the arrow in front of Playback speed and select a slower speed.
Is this a thing? Do people really have to do excel problems for interviews?
yes....we've had excel interviews for Amazon, Tesla etc.
I think those are assessments and they check your skills
I would seriously question why you would abuse excel for such things instead of any of the hundreds of better suited tools?
It could be made better
Sorry to hear that...can you elaborate so I can improve future videos?
Anyone promoting VLOOKUP in training content has no credibility.
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')
Technical interview questions for Excel? Laughable. How much do these people make in their positions. I have to change my career.
please stop teaching vlookup
Because xlookup is a thing now? Or why do you suggest stopping?
@@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.
@@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
Excel for business and finance, how long does this course take, and what's the price?... @kenji
All the information is on the landing page here: www.careerprinciples.com/courses/excel-for-business-finance
XLOOKUP