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?
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!
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?
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!
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 RU-vid 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
@@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.
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
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.
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.
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.
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.
Hi Leila, I have been trying to learn Excel formula and become advance, but i am confused from which formula should I start the basic and jump intermediate and advance, I would appreciate if you advise.
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
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.
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?
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?
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.
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👍
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 🙃
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).
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).
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?
Good Suggestions. But I figured with Excel's Power Tools notably Power Query and Power Pivot, pretty much all if these can be accomplished with few clicks of mouse. And during the interview or test if the company say you can't use mouse, I will just say to them, hire somebody else then, I got bigger fish to fry.....
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.
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?
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
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 :}
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
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 !!
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.
Had a nightmare the day before. I was invited to an interview where i'd take an excel test. I was taken to a room and told to wait a couple of minutes for the interviewer to come. The door opens and in comes Leila. Almost had a heart attack!
I train Excel, and in my view the range_lookup argument for VLOOKUP is not very well explained by the Insert Function or Intellisense features i.e. the term "approximate match" is misleading. I prefer to explain it as "Do you want to implement a range look-up method? Yes or no?" I then like to say WHY you'd use a range lookup i.e. if the value you are looking up is effectively continuous, such as an annual income for tax rates, where the income is broken up into bands or ranges.
