❓What’s the trickiest Excel question you’ve faced in an interview? - Download the practice file here and follow along: bit.ly/exceltest24file - Learn more about the Advanced Excel Formulas course: bit.ly/exceltest24course - Download the Custom Number Formats cheatsheet: bit.ly/exceltest24cheatsheet - Master Excel with my courses: bit.ly/exceltest24courses
Fantastic video summarizing top tips & tricks in Excel! I'm always fascinated to find new things I wasn't aware of! Love the "filter by color" option! Thank you for sharing these great videos!!!
I actually aced it. On the last question I had opted to create a "Key" column where I used TEXTJOIN to concatenate the columns together, but I had the feeling you may have had a more elegant way of doing it, I was quite surprised when I noticed our method matched lol. I guess that is the tried and true method after all. One thing I'd like to mention though as it pertains to your "Concatenated Data Alt." column. While it gets the job done, in this scenario, I highly recommend always adding a delimiter when making such "keys". I recall working on a project awhile back where I was I had created a key column to facilitate look-ups, but the data in both columns was somewhat similar. So for example, in row 2 I had values R12 and 11, however in row 8 I had values R121 and 1. The lookup continuous returned the value on row 2, instead of the value on row 8. A bit of an edge case, but it definitely changed how I did things. Great video, great exercise, thanks for all that you do!
Thanks for the interesting video. I found the number formatting and the last tip on highlighting duplicates very helpful. I'm also loving the new hairstyle, by the way. Happy holidays🎉 and thanks once more for making Excel Fun!
Tabulator is a natural column separator in Excel. So if we copy the text into Notepad (Notepad++, PSPad, etc.), then we just replace the comma and space with a tab and copy the data back through the clipboard.
Hi Mynda Absolutely a fantastic review of important formulas. Even though most of them might be familiar, I don't necessarily use them in my daily work. So, it was a great review and refresher. Thanks again.
I always learn something new with your videos ❤❤. P. D. I am using your videos as way to replace my addiction to tiktok. And it is working 😊 Thank you!
I scored 4.5 out of 8, instead of v lookup and Index Match,I used x lookup,but overall it was a great video to test our excel skill, please Mam continue this once in a month. Thank you very much Mam
This is great, always something new to learn. Although I fear all the knowledge we build up becomes worthless as people can just use AI to find solutions.
AI - yes and no. AI is similar to a search engine, but it's still quicker to do the work if you know how, than write the prompt for AI and to and fro with it to get answers.
Great for practical use. In my opinion on Q6 you should reference the pivot table not to 'Q6'!$B$8:$E$83, but to 'Table4', allowing new rows and new data. Extra bonus points for me?? ;) Thanks for sharing another great video.
I did reference table 4 in the video. But maybe you just downloaded the file and didn't watch the video, because on investigating why you made this point, I see the file has a reference to cells instead of the table.
Can you please do a video on slow changing dimension tables? The one I found generates a line for each possible date for each product, which is fine, but I have calculated that within a year of having 1000 products, this list will already grow to more than 300 000 rows? This is using Power Query. My other question is how do you do this within an Excel file not using Power Query (just a vlookuptable that changes over timer).
Dimension tables should contain unique values, so you should never have a date dimension table with 300000 rows. Maybe I misunderstood. You can generate a date/calendar table in Power Pivot itself via the Design tab > Date Table > New.
The Business Support Administrator jobs may have tests this hard. With Customer Service jobs they are not that bothered because ithey are mainly just using a CRM and abit of Excel for allocation and to check you have completed training. - very simple usage. I did come across a gov maths test which would have been handy to use a spreadheet because it asked questions on tables which took forever using a calculator - hint sometimes better to have a second laptop (shhh). Good to brush up on your percentages, algebra, and ratios before any tests. With no spreadsheet or calculator the easiest way is to break hard maths into easy maths calcs and put it together at the end so you can do it in your head eg 700/8 = 350/4 = 175/2 = (100/2 + 75/2) = 87.5
I learned the order with a little phrase. Please explain to my dear Aunt Sally. (PEMDAS) Math has order. We even had an art contest to draw Aunt Sally. 😀 I remember it to this day and that was 45 years ago.
Thanks for watching 🙏 I agree, Q1 is technically calculating markup %. As I said to someone others who also commented, I just tried to distill it into Layman's terms to simplify it for non-accountants, but it looks like that just added a different layer of confusion 🤭
This was fun. But re PEMDAS, you should remove "and" after "Division" otherwise it makes it look like M, D, A and S are at the same level of operation hierarchy.
There's a little mistake in Q1 since gross margin (or profit % as stated) is: (selling price-cost price)/selling price... other than that, thank you for the video and the download file! 😉
Thanks for watching 🙏 I agree, Q1 is technically calculating markup %. As I said to someone else who commented, I just tried to distill it into Layman's terms to simplify it for non-accountants, but it looks like that just added a different layer of confusion 🤭
Hi Wayne, I agree, it is technically markup %. I just tried to distill it into Layman's terms to simplify it for non-accountants, but it looks like that just added a different layer of confusion 🤭
You're not alone. Everyone struggles with index match initially. My advice would be to just write index match formulas 10-20 times to encode it to memory (so that it becomes easy). The mistakes you make the first couple of attempts will also help you spot mistakes faster the next couple of attempts and at the same time reinforce your understanding of the different components of the formula and what goes where.
@rafiullah-zz1lf great to hear you passed! Check out my INDEX & MATCH in 4 minutes video and download the practice file to master it: ruclips.net/video/-4yCXpv-drg/видео.html Like @boozedduck said, practice is the key.
Would this be a real test of Excel skills for a job interview? It was too easy (thanks to MOTH 😊). I need to brush up on my custom number formats though. They come in handy, but I always have to dig to get it just right. 😉
Thanks for watching. Yes, this would be a test for an early entry job. It progressively gets more challenging, so it enables the recruiter to establish where your skill level is at.
@@MyOnlineTrainingHubThanks for your kind response. I admire your commitment . You reply to every query on comment session despite 656k+ subscribers. Could you please tell how to connect with you I'm In Accounting and Finance field need ur valuable guidance
Are these questions common or are the easier than usual? I've been working as a data analyst for a few years and during the interview they just asked me if I knew vlookup. I replied that I knew hlookup, xlookup, Python and R as well, and it was enough to land the job
It really depends on the job you're going for. This is a broad range of skills, but certainly not advanced. Sounds like you will blow their socks off in your new job! Congratulations.
I received feedback today that I failed an interview I attended last week because I scored 0 on the excel assessment but scored past the pass mark in the rest of the interview. 🤣😂 How embarrassing!
Considering how most companies dislike independent thinking, I probably would've failed right at the first question because my answer is =D7/C7-1 😂 Otherwise I would've gotten the job. Btw, the @ is missing in sheet A7.
Hi Mynda, Very good examples and I kindly want you to publish like this or a bit hard excel puzzels for us in order to examine our knowledge about excel 🙏 And, I solved this challenge with different techniques additional with yours. Q2.1 - How many employees do we have age data for? A2.1- *=COUNTIF(C5:C14;""&"") or SUMPRODUCT(--(C5:C140)*IFTEXT(B5:B14))* Q2.3 - How many email addresses are missing? A2.3- *=SUM(--NOT(D5:D14""))* Q3.2 Bonus *=(E12:E21>H9)*(F12:F21>=H10)*(E12:E21)*5% [Array product]* or *=MAP(E12:E21,F12:F21,LAMBDA(r,c,IF(AND(r>8,000,c>=4),r*5%,r*0)))* *[Dynamic array formula MAP]* or *=IF((E12:E21>8,000)*(F12:F21>=4),E12:E21*5%,E12:E21*0)** [instead of using AND formula because it doesn't allow it to spill]* Q8.1 - Identify the duplicate rows in the dataset below. I used a Helper column except for your solution like below: *=COUNTIF(B7:E26,"="&B7:E26)-1* ---->*[Dynamic array, spilled range as shown below]* It returns *{1,1,1,2 1,1,1,4 0,0,0,2}* ----> so it means that 1 and bigger 1 values return TRUE, 0 values returns FALSE values. As a result, I used this dynamic array list so as to use condutional formating *(just TRUE values)* and indicate dublicate rows.
Thanks for sharing the alternate approaches. The fact that there are so many ways to achieve the same thing is one of the great things about Excel, but it also has downsides because some are more efficient than others. I like your alternate solution for 8.1 and the spillable formulas for 3.2 which allow for growth, although this could also be achieved with the data in a Table.
![Blox Fruits Dragon Rework Update [Full Stream]](http://i.ytimg.com/vi/EqDAp8udhm0/mqdefault.jpg)
❓What’s the trickiest Excel question you’ve faced in an interview?
- Download the practice file here and follow along: bit.ly/exceltest24file
- Learn more about the Advanced Excel Formulas course: bit.ly/exceltest24course
- Download the Custom Number Formats cheatsheet: bit.ly/exceltest24cheatsheet
- Master Excel with my courses: bit.ly/exceltest24courses
It's good to know our Excel level. We tend to believe that we know more than we really do.
We sure do!
Fantastic video summarizing top tips & tricks in Excel! I'm always fascinated to find new things I wasn't aware of! Love the "filter by color" option! Thank you for sharing these great videos!!!
Awesome, it's great to hear you're learning something new!
A great illustration , thanks a lot Mynda (greetings from Mexico)
Gracias! 😊
Excelent information! What a privilege. Thanks a lot.
Glad you found it helpful!
Review of important formulas, note 10! Thank you Mynda.
Thanks for watching, Luciano!
I thought I understood all these! I didn't! Thanks for a great video refresher Mynda.
Glad it was helpful!
Great video Mynda!
Cheers, Chris 🙏
I actually aced it. On the last question I had opted to create a "Key" column where I used TEXTJOIN to concatenate the columns together, but I had the feeling you may have had a more elegant way of doing it, I was quite surprised when I noticed our method matched lol. I guess that is the tried and true method after all.
One thing I'd like to mention though as it pertains to your "Concatenated Data Alt." column. While it gets the job done, in this scenario, I highly recommend always adding a delimiter when making such "keys". I recall working on a project awhile back where I was I had created a key column to facilitate look-ups, but the data in both columns was somewhat similar. So for example, in row 2 I had values R12 and 11, however in row 8 I had values R121 and 1. The lookup continuous returned the value on row 2, instead of the value on row 8. A bit of an edge case, but it definitely changed how I did things.
Great video, great exercise, thanks for all that you do!
Yay! You get the job 😅 You're absolutely right about using a delimiter if you have similar data you're trying to decipher!
Thank You!
Best Wishes!
My pleasure 😊
Thanks for the interesting video. I found the number formatting and the last tip on highlighting duplicates very helpful.
I'm also loving the new hairstyle, by the way. Happy holidays🎉 and thanks once more for making Excel Fun!
Thank you x 3 😁
Tabulator is a natural column separator in Excel. So if we copy the text into Notepad (Notepad++, PSPad, etc.), then we just replace the comma and space with a tab and copy the data back through the clipboard.
Nice alternative. Thanks for sharing.
no words can express my gratitude to you and this amazing video.🌹🌹🌹🌹🌹🌹🌹
Thank you so much 😀
Hi Mynda
Absolutely a fantastic review of important formulas. Even though most of them might be familiar, I don't necessarily use them in my daily work. So, it was a great review and refresher. Thanks again.
Thanks for watching, Ivan!
thanks a lot (greetings from Baghdad)
You're welcome! Thanks for watching!
I always learn something new with your videos ❤❤. P. D. I am using your videos as way to replace my addiction to tiktok. And it is working 😊 Thank you!
That's awesome to hear! Keep it up!
some interesting reminders, never noticed destination on text to column👍
Glad I could share something new 😊
Same here :)
Very nice video!
Glad you liked it!
9:33 And yet another way of splitting the text would be to use Power Query! 😀
Yes, absolutely. Power Query test is for another day 😁
I scored 4.5 out of 8, instead of v lookup and Index Match,I used x lookup,but overall it was a great video to test our excel skill, please Mam continue this once in a month.
Thank you very much Mam
Thanks! Glad you enjoyed it. VLOOKUP or XLOOKUP will get you the points 👍
This is great, always something new to learn. Although I fear all the knowledge we build up becomes worthless as people can just use AI to find solutions.
AI - yes and no. AI is similar to a search engine, but it's still quicker to do the work if you know how, than write the prompt for AI and to and fro with it to get answers.
@@MyOnlineTrainingHub AI is a great tool no doubt, but we are the ones giving sense and double checking it that info. We almost supervise the AI 😅
I am not looking for a job but this was fun as a trivia 🙂 Thanks.
Glad you enjoyed it!
Got the job!!!!
Congratulations, Chris! 😁
Great for practical use.
In my opinion on Q6 you should reference the pivot table not to 'Q6'!$B$8:$E$83, but to 'Table4', allowing new rows and new data. Extra bonus points for me?? ;)
Thanks for sharing another great video.
I did reference table 4 in the video. But maybe you just downloaded the file and didn't watch the video, because on investigating why you made this point, I see the file has a reference to cells instead of the table.
@@MyOnlineTrainingHub Sorry, indeed I just downloaded the file, did the excersice and didn't watch the video.
Great video, as always. I didn't get 5.1 and 8.1 right, and the bonus for Q7.
Thank you! Glad you learned some new tips.
Very Good work. May the Lord Bless you!
Thank you very much!
Can you please do a video on slow changing dimension tables? The one I found generates a line for each possible date for each product, which is fine, but I have calculated that within a year of having 1000 products, this list will already grow to more than 300 000 rows? This is using Power Query. My other question is how do you do this within an Excel file not using
Power Query (just a vlookuptable that changes over timer).
Dimension tables should contain unique values, so you should never have a date dimension table with 300000 rows. Maybe I misunderstood. You can generate a date/calendar table in Power Pivot itself via the Design tab > Date Table > New.
So even I have no experience as data entry, I can put my excel skills on resume?
The Business Support Administrator jobs may have tests this hard. With Customer Service jobs they are not that bothered because ithey are mainly just using a CRM and abit of Excel for allocation and to check you have completed training. - very simple usage. I did come across a gov maths test which would have been handy to use a spreadheet because it asked questions on tables which took forever using a calculator - hint sometimes better to have a second laptop (shhh). Good to brush up on your percentages, algebra, and ratios before any tests. With no spreadsheet or calculator the easiest way is to break hard maths into easy maths calcs and put it together at the end so you can do it in your head eg 700/8 = 350/4 = 175/2 = (100/2 + 75/2) = 87.5
Thanks for watching! Good tip on brushing up on maths.
@@MyOnlineTrainingHub Your vid was Great. Many thanks. You and Leila are my faves.
I learned the order with a little phrase. Please explain to my dear Aunt Sally. (PEMDAS) Math has order. We even had an art contest to draw Aunt Sally. 😀 I remember it to this day and that was 45 years ago.
Thanks for sharing 😁
Profit % is calculated of the selling price and not of the 'cost price'.
Thanks for watching 🙏 I agree, Q1 is technically calculating markup %. As I said to someone others who also commented, I just tried to distill it into Layman's terms to simplify it for non-accountants, but it looks like that just added a different layer of confusion 🤭
This was fun. But re PEMDAS, you should remove "and" after "Division" otherwise it makes it look like M, D, A and S are at the same level of operation hierarchy.
Thanks for watching and the feedback.
There's a little mistake in Q1 since gross margin (or profit % as stated) is: (selling price-cost price)/selling price... other than that, thank you for the video and the download file! 😉
Thanks for watching 🙏 I agree, Q1 is technically calculating markup %. As I said to someone else who commented, I just tried to distill it into Layman's terms to simplify it for non-accountants, but it looks like that just added a different layer of confusion 🤭
In your first question you are calculating the Markup% not the Profit% (or GP%).
The Profit%(GP%) has sales as denominator, not cost as denominator.
Hi Wayne, I agree, it is technically markup %. I just tried to distill it into Layman's terms to simplify it for non-accountants, but it looks like that just added a different layer of confusion 🤭
Thanks to your earlier videos I know all of them. But struggling with index match😂
You're not alone. Everyone struggles with index match initially. My advice would be to just write index match formulas 10-20 times to encode it to memory (so that it becomes easy). The mistakes you make the first couple of attempts will also help you spot mistakes faster the next couple of attempts and at the same time reinforce your understanding of the different components of the formula and what goes where.
@rafiullah-zz1lf great to hear you passed! Check out my INDEX & MATCH in 4 minutes video and download the practice file to master it: ruclips.net/video/-4yCXpv-drg/видео.html Like @boozedduck said, practice is the key.
Would this be a real test of Excel skills for a job interview? It was too easy (thanks to MOTH 😊). I need to brush up on my custom number formats though. They come in handy, but I always have to dig to get it just right. 😉
Thanks for watching. Yes, this would be a test for an early entry job. It progressively gets more challenging, so it enables the recruiter to establish where your skill level is at.
Is it true? Accounting is high paying job? Please clear my doubt
Yes, accountants earn good money.
@@MyOnlineTrainingHubThanks for your kind response. I admire your commitment . You reply to every query on comment session despite 656k+ subscribers. Could you please tell how to connect with you I'm In Accounting and Finance field need ur valuable guidance
I haven't practiced accounting for over 20 years, so I'm not the best person to guide you, sorry.
Are these questions common or are the easier than usual? I've been working as a data analyst for a few years and during the interview they just asked me if I knew vlookup. I replied that I knew hlookup, xlookup, Python and R as well, and it was enough to land the job
It really depends on the job you're going for. This is a broad range of skills, but certainly not advanced. Sounds like you will blow their socks off in your new job! Congratulations.
This is incredibly easy. I thought maybe you were going to get into macros, vba, and python. Lol
They would be very specific skills. This is more of a way to establish your level.
For Q5.1 DOLLAR Function does the job
Yes, it does! 😊 Nice tip. Thanks for sharing.
I received feedback today that I failed an interview I attended last week because I scored 0 on the excel assessment but scored past the pass mark in the rest of the interview. 🤣😂
How embarrassing!
At least you got some constructive feedback and now you can work to remedy that. Hope you find the practice file useful.
Full marks - this is Excel 101.
So, EZ-PZ mellon squeezy. ;-)
…what do mean “lemons”?… LOL! :-)
👏congrats, Geert! Thanks for watching.
Considering how most companies dislike independent thinking, I probably would've failed right at the first question because my answer is =D7/C7-1 😂 Otherwise I would've gotten the job. Btw, the @ is missing in sheet A7.
Thanks for watching and sharing the alternate formula... and the typo in my file! Fixed now 😅
Xcel and clones are indispensable these days.
I refuse to play the game requiring testing prior to interviewing
I refuse to hire you.
@@drsteele4749 Fine. I wouldn't work for you.
Huh, nice to know could do a majority of these. What i couldn't i now know (aside from that array stuff). I consider myself an average user 😅
Glad you found it helpful! 😄
Hi Mynda,
Very good examples and I kindly want you to publish like this or a bit hard excel puzzels for us in order to examine our knowledge about excel 🙏
And,
I solved this challenge with different techniques additional with yours.
Q2.1 - How many employees do we have age data for?
A2.1- *=COUNTIF(C5:C14;""&"") or SUMPRODUCT(--(C5:C140)*IFTEXT(B5:B14))*
Q2.3 - How many email addresses are missing?
A2.3- *=SUM(--NOT(D5:D14""))*
Q3.2 Bonus
*=(E12:E21>H9)*(F12:F21>=H10)*(E12:E21)*5% [Array product]* or
*=MAP(E12:E21,F12:F21,LAMBDA(r,c,IF(AND(r>8,000,c>=4),r*5%,r*0)))* *[Dynamic array formula MAP]* or
*=IF((E12:E21>8,000)*(F12:F21>=4),E12:E21*5%,E12:E21*0)** [instead of using AND formula because it doesn't allow it to spill]*
Q8.1 - Identify the duplicate rows in the dataset below.
I used a Helper column except for your solution like below:
*=COUNTIF(B7:E26,"="&B7:E26)-1* ---->*[Dynamic array, spilled range as shown below]*
It returns *{1,1,1,2
1,1,1,4
0,0,0,2}* ---->
so it means that 1 and bigger 1 values return TRUE, 0 values returns FALSE values. As a result, I used this dynamic array list so as to use condutional formating *(just TRUE values)* and indicate dublicate rows.
Thanks for sharing the alternate approaches. The fact that there are so many ways to achieve the same thing is one of the great things about Excel, but it also has downsides because some are more efficient than others. I like your alternate solution for 8.1 and the spillable formulas for 3.2 which allow for growth, although this could also be achieved with the data in a Table.