this is wild. i'm glad i found this because i'm gearing up to do a written test and an excel exercise in a few days. thank you so much for the work you put into the video and the tips!!
I was playing along and did a couple of them slightly differently. Q1 was such a great question, so simple but really had to think about it. I used CTRL + G to go to A100000, then CTRL+SHIFT+UP to fill the range back to A3. Didn't know you could select a range the way you did, that will prove very useful!. Q3 I used SUMIFS(range,range,">"&LARGE(range,11)). In power query I would have removed the blanks by filtering a column and would never have considered your method but it's a good tip to learn! Great video!
I love it that you actually took the test Calum. And it just goes to show, there are always multiple ways to solve something in Excel. But it's great to know more than one as their usefulness varies from case to case.
Thanks blink2008. I find the depth does not play a role if you can explain things clearly. If you can make someone understand, you can then take them as deep as you'd like :)
This is truly excellent teaching and SO helpful for the work I do every day in excel. Could you make more of these kind of videos - the examples are fantastic learning tools as they are immediately applicable! Many thanks to you👍
Thank you Irene. Means a lot! Was thinking about a more diversified series differentiated by the position one applies for. Could be coming in January 😀
Loved it! Thanks. Regards from South Africa. (learned something new - the index to get top empty rows and use that value in the next step to remove top rows in sample file).
Thank you so much for this amazing tutorial. I learned so much from it it's remarkable! In 40min i upped my data skills 10 notches. It helped consolidate what i knew of arrays into an applicable real world scenario and the fact that you contrasted and compared the more "traditional" or antiquated ways to solve those problems in Excel with the latest and hippest was really useful. My only regret is that you didn't include a link to a test question file and more crucially to the 4 text files that were transformed in power query. I literally just discovered Power query and those text files would've been a great tool for practice. Anyway, fantastic job and i am now
Hey Random, first of all, thank you for your kind words! And sure you can get the files. Here is the link to the files: excelolympics.com/wp-content/uploads/2021/05/Question-4-Files.zip
I just ran into your channel a couple of days ago and I’m in awe for how easy it seems, and not just for one way to do it, but multiple ways. Definitely mind blowing.
There are always multiple ways to do things in Excel, and sometimes which is best could be determined by the data you are using, so it's always great to know how to do something multiple ways. Thanks you for all the kind words Nestor. It's comments like yours that give creators like me a lot of "energy" to create so thank you again!
@@ExcelOlympics completely agree, regarding the best way is according the data you’re working for. I’m still a rookie when it comes to Excel but I hope with more practice I would be mastering, and of course, with videos like yours would help my journey. Thanks! Anytime, Gašper! I’m eager to watch more videos of you.
Brilliant you just solved so many of the things I’ve struggledd to solve. Thank you once again. If you still have the files I would love to follow your tutorial hands on. Tracy
I have to say this is hands down the best Excel video I have watched so far. If there ware more people teaching this level of skill I can't imagine how faster and better the BA world would be. You have my gratitude subscription and like on this. Keep up the great work!
Great job. I continue to watch videos from several Excel MVP’s and am always amazed with the new features and techniques you come up with. One of my challenges is to have new applications to use these great features (i.e. Power Query, Unique, etc); After using a few of them, work becomes repetitive and it’s just updating the data with new data so I find it challenging to develop a need to use theses new great features unless I have a new assignment requiring me to think differently. Thanks for all your great research and easy to follow and use examples. Tom
very informative video sir. that would help me a lot in working with excel..... good thing you're not the CEO of the company that i'm aiming for, or there's no way that i'll ever get hired😁
I am completely blown away. From start to finish I couldn't take my eyes off the screen! you are really an MVP ! could you please share the files with me. It will really help me practice and master these teachings. Thanks in advance!
Thanks for those kind words Temilola. Means a lot! You can find the main Excel file here: excelolympics.com/wp-content/uploads/2021/07/Job-Interview.xlsx and the files you need forTask number 4 can be downloaded from here: excelolympics.com/wp-content/uploads/2021/05/Question-4-Files.zip May they serve you well!
OK, I expected to learn exactly nothing from this video, and then the very first trick: BAM! I mean: selecting a range using the Name Box: that was a new one for me. Thanks! :-) The rest I knew (even though I would do the PQ part differently, not needing an index column and not needing all those AUX-queries). Good stuff! PS: I learn from ExcelisFun, My Online Training Hub, Leila Gharani, Mr.Excel, and others. PS2: Dynamic Arrays are my favourite part of Excel (vectorisation* is King!), although I’m very fond of PQ-M (as I call it) (and DAX when I need it) and LET() and LAMBDA, too. As Excel goes these are happy times! :-) *: who in his right mind decided to call it “lifting” and “broadcasting”? What were they thinking? What? Is MS in television, now? Like what? MSNBC or something? Oh, wait… ;-)
Hi Geert. A day when you learn something new in Excel is a great day in my book. And as you said. There comes a point where it becomes harder to find new Excel knowledge and judging by all you wrote you've reached that point some while ago so it's a small honor to serve you up with a new trick. Glad to do so!
@@ExcelOlympics oh no, I have not plateau-d by any means, I learn new stuff all the time - great or small. And in terms of PQ or PP I’m just an intermediate user. In terms of Excel “Classic” maybe a little better. I like to grow my knowledge by unifying Excel Tables, PQ-M and the Datamodel-DAX (there’s a lot of similarities, and many of the new concepts of -say- DAX (e.g. evaluation contexts) can be ported to the other environments). The focus shifts to creating simulation models of ever increasing complexity that solve real-world challenges in real time that the end user can utilise (fairly) easily. For the uninitiated things like that may look like magic. And for those who are willing to learn: a lot of sparkles of insight occur (I like to explain things at work, too).
In Question 5, what is the last symbol you added to the formula for the source in the Data Validation dialogue box? I couldn't get the screen to be clear enough to see how you did that to keep it dynamic. Thanks soooo much for all of this content! I'm learning lots, including directions for continuing to keep learning.
Thanks for the kind words Courtney. The symbol is a #. In the "dynamic arrays" world that implies that you are referencing not only the cell but also the entire range that the formula in the cell defines.
These are amazing. I have an interview tomorrow that requires me to take an Excel test. Edit- I failed the test lmaooo. I was preparing for hard stuff and they got me with the basics
Thank you Gasper. Great video. All Tasks are good, but Q4 & Q5 are awesome. I would appreciate if you would send me the files you used. Also, please keep up with the EXCELLENT work. I've looked at a few other "Interview Exam" videos. But none like yours!
So, you will need 2010 or later version of Excel... In 2010 and 2013 you need to download it separately and in 2016 and all that followed it was built in.
Hey Gašper Kamenšek, first of all, thank you for sharing your knowledge with us. I just have one question regarding question 3. Why does the three different solutions you provided gives different sum values??
Hi Miguel. You are welcome and I hope you already found innovative ways to use the new-found knowledge. In regards to your question. First of all, you have a keen eye for spotting that. The reason is that a whole range of values that I am summing up is created with the RANDBETWEEN function and gets recalculated every time I change anything on the sheet. So between the time it takes me to recalculate it another way, it's a totally different set of values that I am summing up.
@@ExcelOlympics Thank you very much for the prompt answer. I found this video super enriching!I will follow your videos from now on as it is truly an excel olympics. *^^* Keep it up :)
This is great information but one problem is that I wanted to be able to download the spreadsheet so that I could work in the errors myself. I realize the first question is blank but the others are not.
Sure thing Vincent. You can find the main Excel file here: excelolympics.com/wp-content/uploads/2021/07/Job-Interview.xlsx and the files you need forTask number 4 can be downloaded from here: excelolympics.com/wp-content/uploads/2021/05/Question-4-Files.zip May they serve you well!
Hello, is that ok if you could make available the big file with the txt files and the transformed data you di so that I can practice the importing on my end and comparing with yours to see if I am getting right. So I need data to play with and follow with your exercises. Thank you again for your generosity.
You can find the main Excel file here: excelolympics.com/wp-content/uploads/2021/07/Job-Interview.xlsx and the files you need forTask number 4 can be downloaded from here: excelolympics.com/wp-content/uploads/2021/05/Question-4-Files.zip May they serve you well!
Hi John, glad to hear you liked the video. You can find the main Excel file here: excelolympics.com/wp-content/uploads/2021/07/Job-Interview.xlsx and the files you need forTask number 4 can be downloaded from here: excelolympics.com/wp-content/uploads/2021/05/Question-4-Files.zip Enjoy
This is pretty cool. I have done VBscripting of editing excel files but still am not aware of some of these basic functions. Normally I don't have to worry about it, but apparently for a test next week I do. Also, my very first question is: What is faster? Doing it manually or spending 20-30 minutes trying to figure out the elegant solution and is this a one time thing or will I have to perform this calculation multiple times?
I guess the question is if you will have to do it again. If it's a one off, you can practice manual cleanup to stay sharp in Excel. But if it's an ongoing thing, then there is no question Power Query is the best way to get it done.
You can find the main Excel file here: excelolympics.com/wp-content/uploads/2021/07/Job-Interview.xlsx and the files you need forTask number 4 can be downloaded from here: excelolympics.com/wp-content/uploads/2021/05/Question-4-Files.zip May they serve you well!
I can never get half of the things on video instructions to do the same thing. I put an apostrophe in front of and number and tried to find it with goto special but it couldn't find it. I typed it in the exact same way but it can't find it. I must have something different in my excel setup I guess.
Hi TheHaratashi. Some functions I was using to show one of the possible solutions are only available in Office 365. So it is possible that other Excel versions were not able to find them.
Great with what you showed us - but just give us the quickest way to get answers - me personally do not care about all the ways to get an answer - just give me the fastest and simplest way - too many steps and possibilities given - how did you get "go-to" did you right-click????
Hi. I totally get what you are saying. You see the issue is. All Excels do not have the same commands and whereas there might be multiple solutions, some may not be available for many people. This is particularly important when it comes to Office 365 and newest arrivals like Dynamic arrays and the New Data Types.
Sure thing. You can find the main Excel file here: excelolympics.com/wp-content/uploads/2021/07/Job-Interview.xlsx and the files you need forTask number 4 can be downloaded from here: excelolympics.com/wp-content/uploads/2021/05/Question-4-Files.zip May they serve you well
Hi Gasper. Awesome video!. Nice to measure my current skills against your tests. I'd love to get the files, especially the .txt files for the PQ task. I'm building a store of PQ procedures to practice and memorize. So, having the data files gives me a way to solve and save the solution and then go back and practice it until I've got it in the bank. I've always deleted variable top rows by replacing blanks with null values and then filtering out the nulls, but it can be unreliable if other data gets in the way. Your add an index column and then filtering to the start of the table with the column name to access the row number is great. Glad to have that new trick in the bag. I'm on your mailing list, so if you can send me the files or a link, would be great. Thanks for all the great videos and lessons. Learning lots of new things from you every time :)) Thumbs up!!
Thanks for the kind words Wayne and isn't it a great day when you learn something new about Excel? I love those days. And I also love the fact that those days come 365 times in a year (on a non-leap year) 😀
Task2, you have that dropdownlist of the working functions when you select Average,Sum and so on, how do you do that ..? i have been looking for something like this
Hello Sir, One quick tip from my side: when you write a formula using just one function (any function), there is no need to write in the end the closing parenthesis, just press Enter, and Excel is there for you and add that closing parenthesis. This would save you one second, per formula. Were you aware of this trick ? By the way, excellent 5 interview questions, I would love an one-to-one discussion with you, about other 5 interview questions, of your choice, just to test my 20+ years of Excel knowledge.
Hey Romulus. I'll admit. When it comes to pressing Enter and expecting Excel to "autocomplete" my parenthesis... I will probably never get used to this. But it goes beyond just finding it difficult to change one's habits. Writing formulas in so many different places (Excel, Power Pivot, Power BI, Power Query...). The Autocomplete doesn't apply to everything and therefore gets no repetitive use... As far as the one-to-one is concerned. I am thinking of recording part two (just because the first video was so well received by the viewers, and a lot of requests for additional questions have been posted).
It is simply that symbol inserted through cell formatting. It's a trick you use to make Data Validation Drop Down Lists "visible" as one can only guess otherwise. If you wand I can send you a cell format to use.
@@ExcelOlympics Hi , I just discovered this excellent video. Could you also send me the cell format for the Data Validation display trick? This is fantastic. Thank you.
Sure thing Carl. You can find the main Excel file here: excelolympics.com/wp-content/uploads/2021/07/Job-Interview.xlsx and the files you need forTask number 4 can be downloaded from here: excelolympics.com/wp-content/uploads/2021/05/Question-4-Files.zip May they serve you well!
Sure thing. You can find the main Excel file here: excelolympics.com/wp-content/uploads/2021/07/Job-Interview.xlsx and the files you need forTask number 4 can be downloaded from here: excelolympics.com/wp-content/uploads/2021/05/Question-4-Files.zip May they serve you well!
You are totally correct and that is due to the fact that the numbers were made using the RANDBETWEEN function in Excel and if you look closely you will see they change with every press of the Enter key. Sharp Eyes 😀.
Here you go Tut. You can find the main Excel file here: excelolympics.com/wp-content/uploads/2021/07/Job-Interview.xlsx and the files you need forTask number 4 can be downloaded from here: excelolympics.com/wp-content/uploads/2021/05/Question-4-Files.zip May they serve you well :)
You can find the main Excel file here: excelolympics.com/wp-content/uploads/2021/07/Job-Interview.xlsx and the files you need forTask number 4 can be downloaded from here: excelolympics.com/wp-content/uploads/2021/05/Question-4-Files.zip May they serve you well!
@@ExcelOlympics Tanks for answering . Your videos are so useful. i am using office 365. Language is not important. I am using this technique frequently now. I subscribe your channel. Have a nice day.
So, unemployed person without money, stuck with Excel 2013(PQ,PBI), should not get the job because of lack of UNIQUE/FILTER knowledge? These two funtions are like: freeware for masses and paid version for pro. Or like: it wasn't Excel all these years till now!
My massage definitely wouldn't go towards someone's version of Excel but rather towards how up to date are they with Excel news. Totally agree that it is much harder to do if you don't have the latest version but nevertheless requires only the inquisitive mind. And it's important to note all 5 tasks are solvable in many different ways and none of them is a deal breaker by itself. It all comes down to the person sitting opposite to you.
As a beginner with Excel, will conditional formatting work if two or more numbers are the same? th-cam.com/video/0ZryTtce1sE/w-d-xo.html Will that method only work if all of the numbers are different?
Hi Mark. It will "work" but it can give unexpected results. So numbers 10, 11, 11, 11, 12, 13,14,15,16,16,16 will get ranked as 1,2,2,2,5,6,7,8,9,9,9 and as you can see there are more than 10 numbers in the top 10 😀.
@@ExcelOlympics Depending on the context, I think it would be proper to include "duplicate" values in a top ten list. For example, test scores. (More than one person could get the same test score.) PS. I also learned the keyboard shortcuts: Alt + = and Ctrl + G from watching the first part of this video. Thank you!
I Totally agree @Mark and it does function in that way. You could also use the rank function which has multiple variants and you can simply use the one that best fits your scenario. Learning shortcuts is never a bad thing 😀.
@@ExcelOlympics It is actually just not there. I have an online version of Excel that came on my new computer. However, I have a subscription and I guess I need to download Office Suite. Just thought it interesting the thing I needed to follow your exercises is just not there.
Look, its 25 questions on the SHL Robert Half test and you have less than 25 minutes to complete. Issues are too many options to solve and u fail. It’s bullshit! I used excel when it was shit!
You can find the main Excel file here: excelolympics.com/wp-content/uploads/2021/07/Job-Interview.xlsx and the files you need forTask number 4 can be downloaded from here: excelolympics.com/wp-content/uploads/2021/05/Question-4-Files.zip May they serve you well!
this is wild. i'm glad i found this because i'm gearing up to do a written test and an excel exercise in a few days. thank you so much for the work you put into the video and the tips!!
Nice to hear you found it useful and good luck on your test.
Excellent job, sir. I've never seen a video that put the knowledge process through such easy-to-follow steps.
Thank you for your kind words Steve. I will attribute this to 18+ years of running Excel courses 😀.
"If you drag down to the row 10000 , the day is over , the test is over" ...hahaha ...That is what i did before I see this video...Thank you.
You are welcome Angel. Imagine how much time you will save from now on 😀.
Same, I actually struggle with it before getting to the correct method. Very helpful
🤣🤣🤣
I was playing along and did a couple of them slightly differently. Q1 was such a great question, so simple but really had to think about it. I used CTRL + G to go to A100000, then CTRL+SHIFT+UP to fill the range back to A3. Didn't know you could select a range the way you did, that will prove very useful!. Q3 I used SUMIFS(range,range,">"&LARGE(range,11)). In power query I would have removed the blanks by filtering a column and would never have considered your method but it's a good tip to learn! Great video!
I love it that you actually took the test Calum. And it just goes to show, there are always multiple ways to solve something in Excel. But it's great to know more than one as their usefulness varies from case to case.
You are an excellent instructor; however, I am a word person and not so much a numbers person. I will watch this video again. Thank you.
i just love how this guy talks and delivers his content. very useful content also a lot more in depth and complicated than other content creators.
Thanks blink2008. I find the depth does not play a role if you can explain things clearly. If you can make someone understand, you can then take them as deep as you'd like :)
@@ExcelOlympics going to tatto that on my chest haha. please make more practical & complex (content) excel/power query videos
mind blowing, great educational video and i loved it
You are amazing!! I've never seen these skills from any other excel teaching sites! I am glad I came across your teaching! Thanks
Thanks a lot!
This is truly excellent teaching and SO helpful for the work I do every day in excel. Could you make more of these kind of videos - the examples are fantastic learning tools as they are immediately applicable! Many thanks to you👍
Thank you Irene. Means a lot! Was thinking about a more diversified series differentiated by the position one applies for. Could be coming in January 😀
@@ExcelOlympics That’s great news, can’t wait! (I love the question and answer format.)
This is excellent. I am preparing for a job interview myself and need to dust-off that long unused knowledge! Fingers crossed :)
Thanks Hanka and it goes without saying, all the best at the interview.
Gasper, you are such an excellent Excel teacher. Thank you for sharing👍🌹
Thank you for those very kind words Kebin!
Now This Is How You Share And Disseminate Information. A Big Thumbs Up To You Sir And Many Many Thanks. You Now Have One More Sub.
Thank you for those kind words Joseph.
I love your delightful videos. Thank you again.
Loved it! Thanks. Regards from South Africa. (learned something new - the index to get top empty rows and use that value in the next step to remove top rows in sample file).
Thank you so much for this amazing tutorial. I learned so much from it it's remarkable! In 40min i upped my data skills 10 notches. It helped consolidate what i knew of arrays into an applicable real world scenario and the fact that you contrasted and compared the more "traditional" or antiquated ways to solve those problems in Excel with the latest and hippest was really useful. My only regret is that you didn't include a link to a test question file and more crucially to the 4 text files that were transformed in power query. I literally just discovered Power query and those text files would've been a great tool for practice. Anyway, fantastic job and i am now
Your videos are amazing :)
Such a nice thing to hear Radosvet Josifov. Thanks for your kind words. Any Excel topics you would want to see covered on the channel?
Very informative and yet easy to follow! Kudos!
Thank you for the kind words Joshua.
So helpful to have this guide for the process to use when interviewing candidates - thanks
Great to use where it fits. This is a test for advanced Excel users. May it serve you well Grainne.
Excellent video, Thankyou so much!
Liked and Subscribed. I will check out your other material soon.
How can I get the txt files from task 4?
Hey Random,
first of all, thank you for your kind words! And sure you can get the files. Here is the link to the files: excelolympics.com/wp-content/uploads/2021/05/Question-4-Files.zip
that's a good way in excle to recalculate . thanks, sir.
It's my pleasure Johirul. Glad to hear that you enjoyed the video.
Hey Gašper, big fan of yours. Keep it going. I'm glad to found this channel.
Great to hear that Nestor. I'll see you on the next one 😀
I just ran into your channel a couple of days ago and I’m in awe for how easy it seems, and not just for one way to do it, but multiple ways. Definitely mind blowing.
There are always multiple ways to do things in Excel, and sometimes which is best could be determined by the data you are using, so it's always great to know how to do something multiple ways.
Thanks you for all the kind words Nestor. It's comments like yours that give creators like me a lot of "energy" to create so thank you again!
@@ExcelOlympics completely agree, regarding the best way is according the data you’re working for. I’m still a rookie when it comes to Excel but I hope with more practice I would be mastering, and of course, with videos like yours would help my journey. Thanks!
Anytime, Gašper! I’m eager to watch more videos of you.
Thanks for the training. Please I need the data for practise. Thanks
Brilliant you just solved so many of the things I’ve struggledd to solve. Thank you once again. If you still have the files I would love to follow your tutorial hands on. Tracy
I have to say this is hands down the best Excel video I have watched so far. If there ware more people teaching this level of skill I can't imagine how faster and better the BA world would be. You have my gratitude subscription and like on this. Keep up the great work!
Thanks for your kind words Georgi. This means a lot and also gives inspiration for future videos.
Thank you very much Sir very helpful
Thank you for those kind words Ankit!
Welcome sir and really helpful tips
Glad to hear you're finding the videos enlightening.
Great job. I continue to watch videos from several Excel MVP’s and am always amazed with the new features and techniques you come up with. One of my challenges is to have new applications to use these great features (i.e. Power Query, Unique, etc); After using a few of them, work becomes repetitive and it’s just updating the data with new data so I find it challenging to develop a need to use theses new great features unless I have a new assignment requiring me to think differently. Thanks for all your great research and easy to follow and use examples. Tom
Thanks for those kind words Tom. And I love how you keep challenging yourself. Keep rocking!
very informative video sir. that would help me a lot in working with excel.....
good thing you're not the CEO of the company that i'm aiming for, or there's no way that i'll ever get hired😁
Thank you!!! Subscribed!
You are welcome. And thank you for the subscription.
Superb content, loved it.
Thank you for the kind words Sanoj.
Thank you so much for this! This is mind-blowing!
Thanks Shruthi. That means a lot!
Great tips.
Thanks John.
Absolutely enjoyed your work! Super!
Thank you for those kind words Andrea. Glad to hear you enjoyed the video. I hope you picked up a few tricks along the way.
I am completely blown away. From start to finish I couldn't take my eyes off the screen! you are really an MVP ! could you please share the files with me. It will really help me practice and master these teachings. Thanks in advance!
Thanks for those kind words Temilola. Means a lot!
You can find the main Excel file here:
excelolympics.com/wp-content/uploads/2021/07/Job-Interview.xlsx
and the files you need forTask number 4 can be downloaded from here:
excelolympics.com/wp-content/uploads/2021/05/Question-4-Files.zip
May they serve you well!
I have to look you up on Udemy. I want to be in your Classroom. I am 10x better with my excel thinking :). Thank you.
I definitely can't apply to you. I hope I have easier tasks to solve tomorrow 😁 But thanks, I learned a lot.
That is great to hear Ulrike. Good luck tomorrow!
A little birdie told me you got the job. Congrats!
99,998 :-) 6:40
True Tom, true. Keen eye 😀
Great video. I want to work as data analyst and those video is excellent for improving Excel skills.
Thanks Giorgi. Glad you found the content educational.
OK, I expected to learn exactly nothing from this video, and then the very first trick: BAM!
I mean: selecting a range using the Name Box: that was a new one for me. Thanks! :-)
The rest I knew (even though I would do the PQ part differently, not needing an index column and not needing all those AUX-queries).
Good stuff!
PS: I learn from ExcelisFun, My Online Training Hub, Leila Gharani, Mr.Excel, and others.
PS2: Dynamic Arrays are my favourite part of Excel (vectorisation* is King!), although I’m very fond of PQ-M (as I call it) (and DAX when I need it) and LET() and LAMBDA, too. As Excel goes these are happy times! :-)
*: who in his right mind decided to call it “lifting” and “broadcasting”? What were they thinking? What? Is MS in television, now? Like what? MSNBC or something? Oh, wait… ;-)
Hi Geert. A day when you learn something new in Excel is a great day in my book. And as you said. There comes a point where it becomes harder to find new Excel knowledge and judging by all you wrote you've reached that point some while ago so it's a small honor to serve you up with a new trick. Glad to do so!
@@ExcelOlympics oh no, I have not plateau-d by any means, I learn new stuff all the time - great or small. And in terms of PQ or PP I’m just an intermediate user. In terms of Excel “Classic” maybe a little better. I like to grow my knowledge by unifying Excel Tables, PQ-M and the Datamodel-DAX (there’s a lot of similarities, and many of the new concepts of -say- DAX (e.g. evaluation contexts) can be ported to the other environments).
The focus shifts to creating simulation models of ever increasing complexity that solve real-world challenges in real time that the end user can utilise (fairly) easily. For the uninitiated things like that may look like magic. And for those who are willing to learn: a lot of sparkles of insight occur (I like to explain things at work, too).
Love it Geert. Keep on rocking!
Thanks! You are on target!
In Question 5, what is the last symbol you added to the formula for the source in the Data Validation dialogue box? I couldn't get the screen to be clear enough to see how you did that to keep it dynamic. Thanks soooo much for all of this content! I'm learning lots, including directions for continuing to keep learning.
Thanks for the kind words Courtney. The symbol is a #. In the "dynamic arrays" world that implies that you are referencing not only the cell but also the entire range that the formula in the cell defines.
These are amazing. I have an interview tomorrow that requires me to take an Excel test.
Edit- I failed the test lmaooo. I was preparing for hard stuff and they got me with the basics
Best wishes Mirela. You got this!
Can you tell us what were the questions and on what they tested? Pivot tables or other tests?
Thank you Gasper. Great video. All Tasks are good, but Q4 & Q5 are awesome. I would appreciate if you would send me the files you used. Also, please keep up with the EXCELLENT work. I've looked at a few other "Interview Exam" videos. But none like yours!
Thank you for those kind words. Could you just ping me on LinkedIn with your email and I'll see if I can find the file 😀
So the best question is to ask several ways to a certain outcome
Brilliant tutorial - I just need to update my excel to the current version with Power Query
So, you will need 2010 or later version of Excel... In 2010 and 2013 you need to download it separately and in 2016 and all that followed it was built in.
Hey Gašper Kamenšek, first of all, thank you for sharing your knowledge with us. I just have one question regarding question 3. Why does the three different solutions you provided gives different sum values??
Hi Miguel. You are welcome and I hope you already found innovative ways to use the new-found knowledge.
In regards to your question. First of all, you have a keen eye for spotting that. The reason is that a whole range of values that I am summing up is created with the RANDBETWEEN function and gets recalculated every time I change anything on the sheet. So between the time it takes me to recalculate it another way, it's a totally different set of values that I am summing up.
@@ExcelOlympics Thank you very much for the prompt answer. I found this video super enriching!I will follow your videos from now on as it is truly an excel olympics. *^^* Keep it up :)
This is great information but one problem is that I wanted to be able to download the spreadsheet so that I could work in the errors myself. I realize the first question is blank but the others are not.
Thank you for that info Lex. I'll check and see what went wrong.
Please I need to try my hands on this, please share the file.
Thank you
great video
Thanks Nic.
can you do a full power query tutorial if possible my bro?
That is on the ToDo list. Not sure when it's going to happen though but it will.
May I request a copy of the file used in these examples/presentation. Thanks.
Sure thing Vincent.
You can find the main Excel file here:
excelolympics.com/wp-content/uploads/2021/07/Job-Interview.xlsx
and the files you need forTask number 4 can be downloaded from here:
excelolympics.com/wp-content/uploads/2021/05/Question-4-Files.zip
May they serve you well!
Hello, is that ok if you could make available the big file with the txt files and the transformed data you di so that I can practice the importing on my end and comparing with yours to see if I am getting right. So I need data to play with and follow with your exercises. Thank you again for your generosity.
You can find the main Excel file here:
excelolympics.com/wp-content/uploads/2021/07/Job-Interview.xlsx
and the files you need forTask number 4 can be downloaded from here:
excelolympics.com/wp-content/uploads/2021/05/Question-4-Files.zip
May they serve you well!
@@ExcelOlympics Thank you.
Hi, please can you send me the files for this super exercise.Thanks
Evening what are the most Formulars or purchases
It's very helpful, thankyou
Thank you for those kind words Balakrishna J.
You just added to my knowledge base, thanks for your help. Please send me the file for practice.
Glad to hear that Lola.
Hi Kasper. Really enjoyed your video. If possible can you please send me the 4 files you used in question 4. Cheers and thanks.
Hi John,
glad to hear you liked the video.
You can find the main Excel file here:
excelolympics.com/wp-content/uploads/2021/07/Job-Interview.xlsx
and the files you need forTask number 4 can be downloaded from here:
excelolympics.com/wp-content/uploads/2021/05/Question-4-Files.zip
Enjoy
This is pretty cool. I have done VBscripting of editing excel files but still am not aware of some of these basic functions. Normally I don't have to worry about it, but apparently for a test next week I do. Also, my very first question is: What is faster? Doing it manually or spending 20-30 minutes trying to figure out the elegant solution and is this a one time thing or will I have to perform this calculation multiple times?
I guess the question is if you will have to do it again. If it's a one off, you can practice manual cleanup to stay sharp in Excel. But if it's an ongoing thing, then there is no question Power Query is the best way to get it done.
Excellent
Thanks for this helpful video! My 'Series' option seeems to be greyed out so I can't click it. Do you know how to rectify this please?
You probably have an active cell. Press Esc and see if that helps.
@@ExcelOlympics Thank you!!!
Thanks for the video, you're a boss!!
Thanks Math. Glad you enjoyed the video.
@@ExcelOlympics I hope to be a boss like you one day 🙏
Thank you for those kind words Math.
Gasper, one word Excellent, where Can i find the txt files ?, From France i watch all your vidéos congrats
You can find the main Excel file here:
excelolympics.com/wp-content/uploads/2021/07/Job-Interview.xlsx
and the files you need forTask number 4 can be downloaded from here:
excelolympics.com/wp-content/uploads/2021/05/Question-4-Files.zip
May they serve you well!
I cannot use unique fonction because i have excel 2016, i Can only use the power query solution or pivot table
You are right Pierre, there is no UNIQUE in Excel 2016.
I can never get half of the things on video instructions to do the same thing. I put an apostrophe in front of and number and tried to find it with goto special but it couldn't find it. I typed it in the exact same way but it can't find it. I must have something different in my excel setup I guess.
Hi TheHaratashi. Some functions I was using to show one of the possible solutions are only available in Office 365. So it is possible that other Excel versions were not able to find them.
Great with what you showed us - but just give us the quickest way to get answers - me personally do not care about all the ways to get an answer - just give me the fastest and simplest way - too many steps and possibilities given - how did you get "go-to" did you right-click????
Hi. I totally get what you are saying. You see the issue is. All Excels do not have the same commands and whereas there might be multiple solutions, some may not be available for many people. This is particularly important when it comes to Office 365 and newest arrivals like Dynamic arrays and the New Data Types.
This is great! Could I please get the files used sent to my email. Thank you so much. Great job
Thanks ,I would like to have the files please,thank
Sure thing.
You can find the main Excel file here:
excelolympics.com/wp-content/uploads/2021/07/Job-Interview.xlsx
and the files you need forTask number 4 can be downloaded from here:
excelolympics.com/wp-content/uploads/2021/05/Question-4-Files.zip
May they serve you well
Hi Gasper. Awesome video!. Nice to measure my current skills against your tests. I'd love to get the files, especially the .txt files for the PQ task. I'm building a store of PQ procedures to practice and memorize. So, having the data files gives me a way to solve and save the solution and then go back and practice it until I've got it in the bank. I've always deleted variable top rows by replacing blanks with null values and then filtering out the nulls, but it can be unreliable if other data gets in the way. Your add an index column and then filtering to the start of the table with the column name to access the row number is great. Glad to have that new trick in the bag. I'm on your mailing list, so if you can send me the files or a link, would be great. Thanks for all the great videos and lessons. Learning lots of new things from you every time :)) Thumbs up!!
Thanks for the kind words Wayne and isn't it a great day when you learn something new about Excel? I love those days. And I also love the fact that those days come 365 times in a year (on a non-leap year) 😀
Task2, you have that dropdownlist of the working functions when you select Average,Sum and so on, how do you do that ..? i have been looking for something like this
It's the Total Row of a Table. You must use the Format As Table command and then simply turn on the Total Row (Ctrl+Shift+T).
@@ExcelOlympics Thank you!
Hello Sir,
One quick tip from my side: when you write a formula using just one function (any function), there is no need to write in the end the closing parenthesis, just press Enter, and Excel is there for you and add that closing parenthesis. This would save you one second, per formula. Were you aware of this trick ? By the way, excellent 5 interview questions, I would love an one-to-one discussion with you, about other 5 interview questions, of your choice, just to test my 20+ years of Excel knowledge.
Hey Romulus. I'll admit. When it comes to pressing Enter and expecting Excel to "autocomplete" my parenthesis... I will probably never get used to this. But it goes beyond just finding it difficult to change one's habits. Writing formulas in so many different places (Excel, Power Pivot, Power BI, Power Query...). The Autocomplete doesn't apply to everything and therefore gets no repetitive use...
As far as the one-to-one is concerned. I am thinking of recording part two (just because the first video was so well received by the viewers, and a lot of requests for additional questions have been posted).
On question #2...there's an down arrow next to january.. what is that and how do you formulate the cell to do that? Thanks!
It is simply that symbol inserted through cell formatting. It's a trick you use to make Data Validation Drop Down Lists "visible" as one can only guess otherwise. If you wand I can send you a cell format to use.
@@ExcelOlympics Hi , I just discovered this excellent video. Could you also send me the cell format for the Data Validation display trick? This is fantastic. Thank you.
My mind is completely blown. Is there anyway you can send me the files so I can practice with you please?
Sure thing Carl.
You can find the main Excel file here:
excelolympics.com/wp-content/uploads/2021/07/Job-Interview.xlsx
and the files you need forTask number 4 can be downloaded from here:
excelolympics.com/wp-content/uploads/2021/05/Question-4-Files.zip
May they serve you well!
@@ExcelOlympics Thank you
Hello, great video !! Would I be able to get the txt files to practise please !
Sure thing.
You can find the main Excel file here:
excelolympics.com/wp-content/uploads/2021/07/Job-Interview.xlsx
and the files you need forTask number 4 can be downloaded from here:
excelolympics.com/wp-content/uploads/2021/05/Question-4-Files.zip
May they serve you well!
@@ExcelOlympics Thank you !! will for sure get the job now 😁😁
I have no doubt 😀. Good luck.
Was it me or did task 3 examples give 3 different totals?
You are totally correct and that is due to the fact that the numbers were made using the RANDBETWEEN function in Excel and if you look closely you will see they change with every press of the Enter key. Sharp Eyes 😀.
Is that ok if you can provide the files for us to practice. Thank you.
Here you go Tut.
You can find the main Excel file here:
excelolympics.com/wp-content/uploads/2021/07/Job-Interview.xlsx
and the files you need forTask number 4 can be downloaded from here:
excelolympics.com/wp-content/uploads/2021/05/Question-4-Files.zip
May they serve you well :)
@@ExcelOlympics Thank you.
Please send me the files you used in your video, thank you.
You can find the main Excel file here:
excelolympics.com/wp-content/uploads/2021/07/Job-Interview.xlsx
and the files you need forTask number 4 can be downloaded from here:
excelolympics.com/wp-content/uploads/2021/05/Question-4-Files.zip
May they serve you well!
20:55 I'am not use english language. i write # caracter but i take error. you are write H5# and it is work. but i can't.
i'm find a video about this. i understand my fault. th-cam.com/video/0S5lPoXHX_c/w-d-xo.html
I believe that syntax will only work in Excel as part of Office 365. So if you have any other version of Excel it will not work...
@@ExcelOlympics Tanks for answering . Your videos are so useful.
i am using office 365. Language is not important. I am using this technique frequently now. I subscribe your channel. Have a nice day.
So, unemployed person without money, stuck with Excel 2013(PQ,PBI), should not get the job because of lack of UNIQUE/FILTER knowledge? These two funtions are like: freeware for masses and paid version for pro. Or like: it wasn't Excel all these years till now!
My massage definitely wouldn't go towards someone's version of Excel but rather towards how up to date are they with Excel news. Totally agree that it is much harder to do if you don't have the latest version but nevertheless requires only the inquisitive mind.
And it's important to note all 5 tasks are solvable in many different ways and none of them is a deal breaker by itself. It all comes down to the person sitting opposite to you.
Oh God, I have already failed. LOL. I am so rusty. Don't use it, lose it.
As a beginner with Excel, will conditional formatting work if two or more numbers are the same?
th-cam.com/video/0ZryTtce1sE/w-d-xo.html Will that method only work if all of the numbers are different?
Hi Mark. It will "work" but it can give unexpected results. So numbers 10, 11, 11, 11, 12, 13,14,15,16,16,16 will get ranked as 1,2,2,2,5,6,7,8,9,9,9 and as you can see there are more than 10 numbers in the top 10 😀.
@@ExcelOlympics Depending on the context, I think it would be proper to include "duplicate" values in a top ten list. For example, test scores. (More than one person could get the same test score.)
PS. I also learned the keyboard shortcuts: Alt + = and Ctrl + G from watching the first part of this video.
Thank you!
I Totally agree @Mark and it does function in that way. You could also use the rank function which has multiple variants and you can simply use the one that best fits your scenario.
Learning shortcuts is never a bad thing 😀.
No fill button :(
Sorry to hear that Sheri. Fill button grayed out or not present at all? Did you go for the mouse shortcut or Home/Fill/Series?
@@ExcelOlympics It is actually just not there. I have an online version of Excel that came on my new computer. However, I have a subscription and I guess I need to download Office Suite. Just thought it interesting the thing I needed to follow your exercises is just not there.
Oh, yes. You will need a desktop version of Excel to use some of the features I demonstrated in this video.
Look, its 25 questions on the SHL Robert Half test and you have less than 25 minutes to complete. Issues are too many options to solve and u fail. It’s bullshit! I used excel when it was shit!
Where can I find these files please to practice with? Thanks
You can find the main Excel file here:
excelolympics.com/wp-content/uploads/2021/07/Job-Interview.xlsx
and the files you need forTask number 4 can be downloaded from here:
excelolympics.com/wp-content/uploads/2021/05/Question-4-Files.zip
May they serve you well!