5 Excel INTERVIEW Questions You NEED to Get RIGHT
ฝัง
- เผยแพร่เมื่อ 7 ก.ค. 2024
- Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
Excel is indispensable in today's professional world. Whether you're preparing for an interview or aiming for a promotion, Excel skills are crucial. This video covers the top five Excel interview questions you must know.
Want to ROCK your Next Interview? Check out the complete Excel Essentials course 👉 www.xelplus.com/course/learn-...
Top Excel Interview Questions:
▪️ VLOOKUP: Understand its syntax, limitations, and advanced alternatives.
▪️ Absolute vs. Relative Cell Reference: Learn the differences and applications.
▪️ Pivot Tables: Discover their significance in data analysis.
▪️ Conditional Formatting: Explore how to highlight critical data dynamically.
▪️ IF and Nested IF Functions: Grasp these functions for logical data operations.
Picture of Satya Nadella by By Brian Smale and Microsoft - Satya Nadella Microsoft leadership profile page, CC BY-SA 4.0, commons.wikimedia.org/w/index...
⯆ Blog post link: www.xelplus.com/top-5-excel-i...
LINK to XLOOKUP video: • Discover What XLOOKUP ...
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#excel
Be prepared for your next interview. Sign up to our bestselling Excel Essentials course here 👉 www.xelplus.com/course/learn-excel-essentials/
Hi Leila.. thanks for the 5 EXCEL interview questions. Even when you think you know the answer.. it is good to test and challenge yourself so you don't freeze in the headlights when someone asks you point blank. Your courses, videos, web site and blog posts are full of so much value. Anyone wanting to up their skills in EXCEL should have you at the top of their list. Thanks for all the knowledge, tips, techniques and inspiration that you share with the community. We are all better at what we do because of what you do. Thumbs up!
That's so nice Wayne, thank you. I really appreciate the kind words. I'm always happy to see your comments.
Awesome 100% TRUE about the questions during an interview!!!
Thanks teammate!
OMG!!! The first three questions is exactly what I was asked at Amazon interview (in the same order). I wish I watched this video prior to the interview, not after. Thanks a lot!
I have conducted quite a number of interviews for data managers. I dont usually test them on memorizing excel formulae and the nitty details because that stuff can easily be looked up. You need to be working with these functions everyday to have them imbedded in your memory. What i test them is 1.Tables (and related operations like filtering) , 2.Pivot tables and 2.charts. 4. [Optional].I then ask them if they have ever used Excel power tools (Power query, Power Pivot) to analyze big volumes of data. I usually provide a relative big dataset for them to demonstrate their skills levels of 1,2,3. If they can use their initiative and try out the optional 4, they they get hired. In addition to the above i also test them on basics of databases (Relational database design , SQL)
Having been interviewing the past week, I have found exactly this to be true. I am asked more regarding HOW I am using Excel in the real world. For example- "How do you use Pivot tables in your current position?" "Give an example of a way you have used Excel power tools." "Give an example in detail of how you analyzed data using Excel and what was your outcome?" I've only been asked one time what functions I use, and I froze up and flat out said a few and then stopped and said, "good grief I can't remember." (I got an offer lol).
@@jessicasteele7829 Good points. My brain is meant to solve their business problems, not fill it with flashcard trivia of keyboard shortcuts and precise formulas for things.
Interviewer: What shortcut do you use for N?
Me: Give me a keyboard and I'll press it, but no idea which button it is... actually I think I edited that feature because I liked the old macro shortcut I used before they added feature N. No idea what I'm doing here, can I just load the data into a RStudio instead?
You're indeed amazing. Keep up the good work Leila
Thank You Leila, I have tried to learn Excel from other courses.With not very much good luck. But the way you explain everything is perfect. Thank You Again.
Awesome! Exactly what I've been waiting for. Definitely going to check out this course!
Index-Match can be used to pivot too. For instance, when your primary key in the reference table has multiple rows but you need to pivot from long format to wide in your main table. Index-Match in combination with AND and IF can confirm that the next row down matches the same PK as the previous row.
I just had an interview two days ago, and vlookups and pivot tables did indeed come up.
Hope the interview went well :)
Leila you are great!!you have helped me so much I love index and match,,but now I want to learn Xlookup:)
This is amazing, Leila! Thank you.
Thanks your classes on Udemy and videos on TH-cam, I can now answer these questions easily. Thanks your for effort and passion in teaching.
Very useful advice. Thanks, Leila!
Superlative job as always!
Great video once more.
Now can you think of a few questions they SHOULD be asking but aren't?
Basic user...
Q1. Where do you look to see what is actually in a cell?
Q2. What is the correct way to enter a date in Excel?
Q3. What is the kbd shortcut to close a file, but keep Excel open?
Q4. What comprises a 'Good' data set.
Q5. How do you turn a Data Range into an Excel Table?
More adv...
Q6. How do you get to Power Query?
Q7. What is the difference between a Pivot Table and Power Pivot?
Suggested answers:
A1. The formula bar will show this. What you see in a cell is either the answer to a formula, a referenced cell, or 'Formatted' data in the cell. (One of Mike's Golden Rules - formatting is a facade)
A2. The same as your PCs regional settings. i.e. the same as you see in the lower right of your screen. If I use the German format in UK it is NOT recognised as a date by Excel but simply as text, denying me the ability to use any of the date features and functions inc. Timeline slicers. (This can be got around using 'Locale' data types when doing Power Query.)
A3. Ctrl + W. This means you keep Excel open and only have to use your machine's resources to open each file, and not to re-open Excel each time. Sure it only saves a few seconds each time but 10 secs x 100 files in a day = more than quarter of an hour wasted each day. The important thing is that if an applicant knows this they will also know many more time-saving and productivity tips.
A4. Nothing touching the data range that is not part of the data, Headers at the top and in bold, dates in correct format, totals not touching (they are a calculation done on the data - not a part of the data). Additionally to make lookups easier - Unique IDs in the left column if 'Lookup Data' and should contain a timestamp if 'Fact Data'.
A5. Click anywhere inside and Ctrl+T. The next thing you do is immediately give it a sensible name.
A6. DATA Ribbon - Get & Transform Data Group.
A7. Pivot Tables summarise a single data set, Power Pivot does the same for multiple data sets using related fields in the various data sets.
What do other people think SHOULD be asked?
Well, I would hire you right away :) Great list. Thanks for your input!
very sweet and simple. Thanks Leila!
I've just enrolled and purchased this course! Excited! Thank you as always, Leila!
Great! Thank you for your support. Looking forward to getting your feedback.
Thank you for this, will come in handy when I go to interviews. Love your channel! :)
My pleasure Ryan. Glad you like it.
Really valuable information. Thank You.
1) VLOOKUP
2) F4 (Lock Feature)
3) PIVOT Table
4) Condition Formatting
5) IF Function
Thanks a lot for your guidance!!
Thank you so much my great Teacher
Have you recently attended an Interview where Excel questions were asked? What were they? Please share below.
Not as a test, but because I had Excel and basic VBA in my CV, I was asked what I used that skill for. I explained that I used small, almost embarrassing simple, macros and such to speed up and reduce error in monotone data entering and presentation. At least I got a few lifted eyebrows and smiles.. lets hope it was enough to tip the scale 🙃
It's a good idea to show the problem and see the different approaches. Thanks for sharing @Up4Excel
One of the one question frequently ask in interview regarding excel that is how you handle data almost 5 million rows in excel? Can you please share your experience about this question
I had an Excel practical test where I was asked (a) perform linear interpolation and (b) build a product code text from components (i.e. concatenation).
@@adeelqureshi9339 Power query + power pivot
this was amazing thank you
Amazing video! Thanks!
Totally agree Leila, every company i have worked for in the last 20 years has been ruled by the VLOOKUP command and Pivot tables.
Glad you can confirm it Steve.
Thanks for shairing , excel lady.
That's really helpful.
My pleasure. Glad to hear it's useful.
You are the best Excel Teacher on the Planet. Thank You so Much..
Thank you very much 😘
Congrats on your new course. Of course I am signing up. Your videos have helped me so much.
That's great Natasha. Looking forward to getting your feedback!
There is one more limitation in vlookup is that it cannot look up a value more than 255 character in length
Gr8
Thnx
I usually watch ur Vdos from a different perspective.
I recommended my HR team and staff to join the course.
Simplicity is the Beauty of ur Vdo.
That's great. Many thanks for your support!
excellent tips ,thanks
Thanks for sharing!
Great video as usual ! I now realize that I had to learn Excel in English
after u showed the answers i was like o yeah i know that its just i leaned excel in Dutch
I use index , match, match usually as an alternative to xlookup, but do mix it up sometimes ... anyway really helpful your videos Leila.. keep it up.. the dynamic date filters video was one Highlight that particularly helped me at work
I'm glad the video was helpful at work.
Very helpful! Lovelyyyyyy
Great video Leila. Thanks for sharing.
My pleasure.
That was cool!!
You're the best!
Employer: Asks the common Excel questions for job interviews slightly similar to those in this video.
Applicant: Answers the questions exactly how Leila explained them.
Employer: Wait, are you subscribed to *Leila Gharani's TH-cam Channel?*
Applicant: Yes, Sir. I am... With the notification bell icon to get notified of all new and upcoming videos.
Employer: (Instantly stops the interview. Crumples and throws the Applicant's CV/resume in the trash bin... With an intimidating look on his face.)
Applicant: (Nervous and shaking. Telling to self: "OMG, I didn't make it.".)
*Employer: CONGRATULATIONS! YOU'RE HIRED. And let me tell you something, kid. This is top corporate secret. Highly classified. **_I'm also subscribed to Leila and watch all her videos._** Don't tell anyone.*
#excel #msexcel #microsoftexcel
Mark, thanks for the laugh to get my day started right 😂
@@LeilaGharani It's great to know that, Leila. My fingers just started typing (tapping) those. LOL! Seriously, we owe you a lot with what we learn from your Channel. Please keep the great content coming. Thank you so much.
This comment is very cringeworthy
Wow! Thank you, lady!! 👍
You are so welcome!
This Is amazing thanks
Thanks, Leila.
Thanks for sharing mam was very helpful.
Great tips! Excel is such a useful tool
It sure is Doug!
Thankyou so much man for your great job
My pleasure Prakash.
Very Useful stuff from Leila..
Thank you for making video. But still i just to clarify 1 things if you will not used to any formaul on daily basic routine activity.after few days you will forget which formula will be work on which case.
Thanks again👍
Great video. Would you call these questions good for advanced or intermediate Excel questions. If it is intermediate, what are the typical advanced level Excel interview questions?
Vielen Dank Liebe Leila ,so practical and useful.
Viele liebe Grüsse 🤗
Danke liebe Katerina 😘
Excellent
Excel Boss Leilaaaa!
thanks alot!
The Queen of Excel is always imaginative and Creative in your style
I'm trying my best :)
@@LeilaGharani real queen
i’ve just signed up for the course 😀
Thank you for your support. I hope you will like it.
Thanks a lot Leila 😃
You're very welcome :)
Leila, this video is very useful, short in time, rich in mind.
I'm glad to hear that :)
@@LeilaGharani My pleasure : )
Thanks Leila
If this is advanced I’m a f’ing excel wizard
No not advanced - still even today if you look at most job adverts they mention Pivot Tables and V Look ups so this video is still relevant in 2024
جزاك الله خيرا
Nice video mam, really helpful for interview, love you.
I'm glad to hear that :)
You are an amazing instructor! I have learned a lot of things from your videos.
Thanks
thank you
Thanks mam . Considering my request
TY
I wish we learned this stuff in school
well done
I was asked in mis executive interview, pivot table and chart, index and match, pivot slicer, short cut keys of ctrl and shift etc...I cleared interview but i Not joined coz of salary issue. Now i am in account department in e-commerce company
thanks...
Awesome
Wonderful and very useful video Leila; I had asked a candidate a question; "I am using TRANSPOSE function and after using I'm getting an VALUE error, so what is the possible mistake I'm making ?", he could not answer.
I can imagine. You're a tough interviewer Sachin :)
Hey i like your videos and style of your videos content. Best of luck and keep the good work mam.👌👌
I'm glad to hear that :)
Finally some advanced level questions not the "how to make text bold" like in other videos lol
Definitely one of the best! Thanks Leila for sharing
Glad you like it Romeo.
Great Liela
It all depends what level of expertise you are looking for. If I get someone who says they are expert as Excel, I tend to ask questions about VBA, DAX measures and m formulae. That tends to sort out real experts from those who are merely proficient. If I was looking for a very good model builder I'd probably throw in a question about customising the ribbon using html or something like that. If I'm looking for someone who is only proficient at the basics however then the sort of questions posed in the video are about right.
Good points Malcolm. Thanks for the feedback!
Love you maam
Have you got a video/lesson how to use a excell data filter in a html page / webpage to use as a Dropdown-Menü for my webpages?
During an interview, I was given a test. I was given a model to clean up without using a mouse. I got through it, but must not have completed the task fast enough. Not a problem, I am know working for a much larger company.
Strange test :) Glad it worked out for you.
I get the concept, but that's still kind of dumb. I really hate hare-brained schemes recruiters and interviewers cook up because they think Edward Nigma is some kind of role model.
Well, good that you got to work in a better place. That's kinda hilarious test. I remember first time I got to work with Excel, I didn't know much and I was learning on my own based on the job requirements. You come across stuff you haven't done before and automatically you gonna do your research and learn it on spot - my problem was that I had the logic but didn't know how to apply it, which i think it is the case for many people.
Recruiters tend to forget that you do learn a lot by doing it, especially if you're new in the field. In 2 months I knew more than the others that were there way before me... it just matters how knowledge hungry you are. They should focus on the basics and things that are required for the job, not the damn "clean up stuff without mouse".
@@jxggxr_dxvhi! Just going for an in-take clerk position and they said they'll test my basuc excel skills. Which questions do you think I should prepare on besides the ones in this video?
Always learnt a lot from your videos. I have a query. I am using combobox ActiveX control. I have selected a range but the data in it are not hard-coded but derived by formula. Hence at times blank space are appearing at the end of the drop-down list.
Is there a way to make the appearing list dynamic without changing the formulated range selection
Hi, mam
You have a good knowledge
I'm glad to hear that :)
Hi Leila can you please advise what excel formulas i need to learn to work for retail merchandising , do you have any clips useful so i can watch it
You are amazing!! You have changed my worls in excel. After that, I'm looking to excel not just calculator. It contains planning, managing, charting whatever you want I think it reflects a real World!
Thanks for everything
I'm very happy to hear that Emre. I hope you enjoy the course.
I'm starting a new job tomorrow where I mentioned xlookup at the interview.
Congratulations Michael! I wish you a good start.
What was their response? "X-what now?"
@@drillcream yes but in a more accounting like manner. I think they said oh I'll have to google that.
Thank you, Leila, for the new course on Udemy! Can we expect the next course to be on Power BI, please??
No but currently Power Query is planned.
Leila Gharani Awesome! Will be waiting for the course to launch! 🤞🤞
I knew all of these 🙂
Very well presented. :)
One point needs to be caution though... XLOOKUP is super cool, no doublt. However if we are answering that in an interview, we have to be extremely careful before the interviewer may not have the knowledge of XLOOKUP. Before we show off our Excel skill, I'd suggest we ask the interviewer what Excel version the company is currently using, and response accordingly. 😉Make sense?
Absolutely, good point!
Thank you so much Maam. It helped me ease my tension a little bit as I have an interview tomorrow. By the way, I belong to the city of Satya Nadella :}
Best of luck!
Hello Madam Good morning, i know which Excle was batter Office 365, 2016 or 2019
i sow and learn in your video every Thursday i see video and i try to know more and learn
I am on the interview panel next week ... I do hope that they have seen this video
Let's hope they do :)
@@LeilaGharani Oh no they didnt :-(
I only heard about XLOOK up amongst a few others, not first hand experiences, because I'm not using the 365 version. I use the prompt for each argument of many functions over the years, so I have not spent a lot of energy memorising the syntax . So it's advisable to remember the syntax by heart?
There is another vlookup/hlookup limitation. In vlookup et al both the lookup value and column/row lookup need to be in the lookup array. This forces the lookup value to be on the edge of the array and the result to only be found in one direction. Lookup, Index/match, sumproduct separate the two to allow more flexibility.
Very true Alex. Thanks for your feedback.
Hi Madame, thank you very much for your videos are very useful, I just have an issue when I am choosing " sign and space after , excel is transforming into $ sign !!! ??? This issue is unwanted and annoying although I ve tried to eliminate it I couldn`t !!!
If you can help me it you will save my life 😁, cheers from Manchester !!
amazing topic . thank you love from paksitan
Hey Leila, may I know if this "excel essentials for the real world" course is updated with the latest new functions/features released recently in 2023?
Hi Ms Leila, this is non-related question but ur response would very helpful. Can u recommend the best screen video recorder and editor for video tutorials like urs. Thanks so much.
I use Camtasia. You can check out my gear here: www.xelplus.com/resources/#gear
I have used Xlookup but I prefer Index-Match instead.
Really? Why do you prefer it?
The column issue with Vlookup can be overcome by "columns" function - VLOOKUP(ref,table_array,COLUMNS(A:D),0) ?
This is a great video to test my basic understanding of Excel but sadly, never had chance to answer Excel questions even though I very much wanted to.
So does that return the first instance in one cell or multiple cells to one or across multiple cells? That looks like I could REALLY use it for something if I understood that better.
@@Juxtavarious No, it simply counts the number of columns in your dataset up until the item you want to return. For example, if your lookup value is in A1 and table is in B2:G100 and you want to return the value in column F (the fifth column) then you write:
=VLOOKUP(A1,B2:G100,COLUMNS(B:F),FALSE)
COLUMNS(B:F) in this instance returns the number 5 because it is just a count of the columns. Crucially, when you insert a column in your table - for example, between columns C and D - then, because you have used column references, the formula will automatically update to COLUMNS(B:G) and so will now return the 6th column as there are 6 columns between B and G (inclusive).
Yes Rico is right! Thanks for the good example. To understand better, need to try it out, insert more columns after that to visualize how it works.