Hello folks…
Time for a fun & useful survey. This time lets talk about Excel Interview Questions.
Many of you are silently becoming awesome in Excel, data analysis, charting, dashboard reporting, VBA, Power Pivot and business skills, thanks to all the time you spend on Chandoo.org. I am sure there will be a day in near future, when you have to face another interview and be selected for a challenging, fun & high paying role.
Likewise, there is also a significant portion of you who are too good in your job that you will become a senior manager, VP or CXO, or better still start your own business. When the tables have turned, you will be the one looking for smart, dedicated, talented and fun individuals to join your team and make you look even more awesome.
So my question for both prospective interviewees and wannabe Excel pros,
According to you, what are the best Excel interview questions?
I will go first.
My top 5 Excel interview questions
Assuming I am looking for an analyst who can take any data dumped at her and turn it in to insights, actionable statements or management summaries, I will ask her below questions for sure.
- How do you lookup particular items from a large data set? Discuss various approaches, why they work, where they fail using this example data set.
- Can you make this ugly, confusing model in to usable, simple & elegant one?
- Assuming you have sales data of various products in several regions over the last 36 months, what kind of charts you prepare to help us understand what is going on and where we should focus?
- Can you analyze the same sales data using pivot tables. Can you compare pivot analysis with formula approach and comment?
- How well do you know about our business? Can you create a high level model of how we make money in Excel? After I explain how we make money in words of course.
- Bonus question: Which resources you use to keep yourself ahead of others in this position. (websites, books, training programs etc.)
What about you?
Go ahead and share your best interview questions. Use your experience as an interviewer or interviewee. Click below link.
Take up Excel interview questions survey.
Have an interview coming up soon?
Then brush up your skills. Start with these pages and let your curiosity go wild.
56 Responses to “What are best Excel interview questions? [survey]”
1. Assuming you have good amount of data/information how would you analyze it/process that you would follow.
*What ever process you may follow, it good to starts with verifying the accuracy of data (stress testing) to be used, followed by storing*
2. What are dashboard and why do organisations prepare/use it. Why spend on it when there are no direct monetary gain to it?? (The second statement depends)
*word TREND was what my boss was expecting*
3. Here is a piece of paper, jot down three areas where you were able to analyse data and suggest improvements from it.
*One of what I showed was Pareto*
4. What other software do you know which can be used for reporting/dashboard purpose.
*SAP Dashboard - used, SPSS - less knowledge, SAS, R - heard*
5. How would you understand the requirement of management for a particular dashboard/report. (We dont want what you prepare instead we want you to work on what we require)
*Will note down the requirements and make possible sketches and share with the dignitaries then work on the whole concept*
1. Can you provide a stand-alone work sample demonstrating your dashboarding/coding/numbercrunching/commercial analysis prowess. [Could be a spreadsheet, or code to automate something, or a report etc]
2. What resources do you routinely use? e.g. books, blogs, forums.
3. Can you provide a stand-alone work sample demonstrating your dashboarding/coding/numbercrunching. (Yes, I know I already said that)
4. Can you tell me about some of the newer enhancements to Excel recently, and what difference they make. (Answer: PowerPivot, IFERROR, Tables, Slicers, Sparklines etc)
5. Can you show me on this computer how you would research a method to solve this question: [Some hard question re Excel e.g. "Synchronize Multiple Pivot Tables"
Jeff, I really like your list because it focuses on assessing what the candidate and and can't do. It doesn't try to capture a chimera of absolute expertise.
» Do you use VBA to automate your work in Excel ?
» If yes, how often do you use it?
» What basic or standard process / steps do you follow while doing so ?
» What are the basics of setting up a list / database in Excel ?
» Do you use the Advanced Filter feature ?
» If yes, show a sample of a complex criteria applied in any project you have worked on.
» What is the one major advantage of Advanced Filter over Auto Filter ?
» How would you go about auditing a table containing columns of different data types (text, numbers, dates, formulas, etc.)
» On a scale of 1 to 10 (10 being highest), how many / how often do you use keyboard shortcuts while working in Excel ?
Here are a few more Chandoo.
Your post has got me thinking a lot about this topic. 🙂
» Do you have a Personal Macro Workbook setup ?
» If yes, what are the most common macros you use in your daily work ?
» Have you customised the QAT in your Excel setup ?
» If yes, what are the most common commands you use from the QAT in your daily work ?
» Do you use Range Names in Excel ?
» If yes, list a few ways in which they help in your work.
» Describe one new thing (maybe a feature, process, shortcut, etc.) that you have learnt and started using in Excel during the last 3 months ?
» List a few ways in which you would reduce file-size of a LARGE workbook (say, 50MB to 100MB), containing lots of data, formulas, pivot tables, etc.
» If you were to suggest ONE new or different feature you would like to see in Excel, what would it be ?
( ME: Lookup values to the left or above or downward. Yes, I know INDEX + MATCH does the job, but a dedicated function for this would be nice 🙂 )
I might post a few more later 😉
Hi KV
I would be interested in your answer to this question? As it's an area I need to improve on...
» List a few ways in which you would reduce file-size of a LARGE workbook (say, 50MB to 100MB), containing lots of data, formulas, pivot tables, etc.
Hi Kevin, here are a few I can list from my experience.
I'm sure there will be many more ideas from others on this forum, and I'd love to hear about them 🙂
- If you have Excel 2007 or above, start saving large workbooks in the Excel Binary Workbook (xlsb) format.
It reduces file-size significantly, *even* if you're already saving it in xlsx or xlsm formats (which are ZIP files at their core).
As for XLS files, I have seen 75mb files being reduced to around 15mb when saved in xlsb format.
Of course, like all good things it has some downsides.
You can read more about this here:
http://social.msdn.microsoft.com/Forums/en-US/0993e144-2e32-4915-be17-ff076902f983/excel-binary-workbook-xlsb
- If you have lots of pivot tables in the workbook, remove the option "Save data with table layout" in all of them.
This helps to reduce file size considerably.
I have found that the trade-off betwen file size reduction and pivot table performance is worth it.
- If you have a database with lots of formulas / array formulas, see if you can reduce the number of columns with formulas.
E.g. if there is a one-time calculation in a column, which doesn't change often after it is calculated, then you can consider keeping the formula only in the first record (for reference), and convert the rest of the records in that column to values.
In such cases a lot of care should be taken to ensure that the formula cells are indeed not required to be recalculated after the initial calculation is completed.
- If you have LOTS of cells linked to external workbooks, consider reducing the number of workbooks with active links in the current workbook.
Yes, this may require you to re-think your workbook structure and logic -- which may not be such a bad thing, if it's a workbook that has grown in complexity over a long time 😉
Ha. Snap, KV. http://chandoo.org/wp/2013/09/29/i-said-your-spreadsheet-is-really-fat-not-real-phat
Ha ha ha... Jeff 😀
I'm no longer a secret agent 😉
(I simply used my browser's auto-store setting for the name and email ID earlier)
And thanks for the loooong list of tips for reducing file-size !
Will definitely go thru it in more detail.
It appears everyone else is hiring for a more technical position...
I would look to find out four (Excel related) things from a potential candidate:
1) Describe an example of how the candidate used Excel to answer a question or present information, and what the outcome was.
2) Have the candidate tell me what the most complex/sophisticated formulas/operations they have used are and to explain why those formulas/operations were necessary.
3) Describe a time when the candidate was asked to do something he/she did not know how to do, and how the candidate handled the request.
4) What is the next thing in Excel the candidate wants to learn or improve at?
What are the most common Excel formulas that you use daily? Can you explain the syntax for all of those?
Excel Shortcuts: What percentage of Excel Keyboard shortcuts you use in a day-to-day excel usage?
What do you do if you get stuck with a complex Excel Formula? (Search blogs, request on a Forum etc.,)
I'll try to remember few more 🙂
KV, that's an excellent list. I particulary like these ones:
» List a few ways in which you would reduce file-size of a LARGE workbook (say, 50MB to 100MB), containing lots of data, formulas, pivot tables, etc.
» Describe one new thing (maybe a feature, process, shortcut, etc.) that you have learnt and started using in Excel during the last 3 months ?
» Do you have a Personal Macro Workbook setup ? If yes, what are the most common macros you use in your daily work ?
» Do you use VBA to automate your work in Excel ? If yes, how often do you use it? What basic or standard process / steps do you follow while doing so ?
Thanks for your appreciative words, Jeff 🙂
[…] Chandoo posted an interesting blogpost on “The Best Excel Interview Questions.” This is interesting for many reasons. For one, in Excel forums this question comes up a lot […]
For my current position as a marketing analyst, I was simply give a workbooks with a great deal of leads and sales data, goals and previous forecasts. I was then instructed to "analyze this data and tell us what you see. You have 3 days, and can then present your findings."
I worked like a dog on that project, and should note that I got the job. Even prepared a 30 slide powerpoint deck to present to them.
As for what I would ask someone: :"Tell me about the most complex thing you've ever done in excel?"
How do you do a lookup to the left?
What is your process for creating a dashboard?
Do you use the sumproduct function? Tell me what you do with it.
How do you use range names?
You want to grab some data from a range, and be able to dynamically select certain chunks of it, based on a drop down list, how would you do that?
One problem with any of these questions, though, is that excel is not an entirely linear learning process.
The guy that I inherited my job from was a demon when it came to creating very complex, linked workspaces. But had never heard of solver.
As for VBA, it can be overrated. I always try to design my stuff so that it isn't needed that much, with the exception of perhaps reformatting things. If your workbooks and sheets are intelligently designed, you wont need too much of it.
@AKS, I disagree with your statement:
"As for VBA, it can be overrated. I always try to design my stuff so that it isn’t needed that much, with the exception of perhaps reformatting things."
It certainly has far more potential uses than just reformatting things 🙂
Perhaps you haven't really needed to explore VBA in your work with Excel.
But used the right way, it can be one of the most useful things you could learn in Excel (or Office).
I firmly believe that learning VBA has the potential for improving your productivity in Excel geometrically.
Having said that, I will definitely agree that thinking about and organising your work on paper, before jumping into Excel cannot be emphasised enough.
What I'm trying to say is, intelligent design is not an alternative to using VBA.
Or vice-versa !
As for VBA, it can be overrated. I always try to design my stuff so that it isn’t needed that much
Yes, you don't want to overuse VBA. But the same could be said of formulas. I showed someone how SUMIFS worked in answer to a specific problem involving one fairly small range of cells. Next thing they put something like 10,000 of them in their workbook and came back to me because it took a couple of minutes to do anything. So I used some VBA to execute some SQL against the workbook. Much faster.
If your workbooks and sheets are intelligently designed, you wont need too much [VBA]
Yep. Intelligent design. As Dick says over at http://dailydoseofexcel.com/archives/2013/06/25/how-to-be-great-at-excel "Many things in Excel go from impossible to simple by changing how the data is organized."
But VBA makes a great lever to an intelligent designer, if they have the requisite skills to do it right. My personal macro workbook saves me countless copying, pasting, and clicking every day.
Let us pray:
Lord grant me the VBA skills to automate the things I cannot easily change; the knowledge to leverage fully off the inbuilt features that I can; and wisdom to know the difference.
Amen.
..."Let us pray:
Lord grant me the VBA skills to automate the things I cannot easily change; the knowledge to leverage fully off the inbuilt features that I can; and wisdom to know the difference.
Amen."...
Simple. Elegant. Complete.
Just like a good spreadsheet.
You Rock!!
AMEN!
» How much time do you spend weekly at chandoo.org? 🙂
BTW, Fantastic list, KV!
That's a good point Antonio 🙂
And thanks. . . 😉
Another thing I'd ask is "Do you use SQL or DAX? Can you show me some examples of your code in these languages." If they don't, then I'd check how good their VBA was, and draw some conclusions as to whether they'd be able to pick up SQL or DAX.
The reason is data sources are getting bigger - much bigger than the Excel grid will or should handle - so we want to make sure that people are skilled at getting data in as near a final form as possible from a relational database or similar into Excel. Because if they can't, then all the Excel skills in the world won't help much.
Hi ,
My take is that no amount of questioning can really evaluate a candidate , since what we are looking for is proficiency ; you don't evaluate a machinist by asking them ; you tell them to machine a piece , and then see how well they have done the job , and in how much time.
Before you evaluate a candidate , you should know what you expect from them ; are you going to recruit them solely for their proficiency in Excel , or will Excel proficiency be a component in a wide range of competencies.
The Excel proficiency that is required in Actuarial science will be different from the Excel proficiency required in technical design.
That said , there are some basic techniques such formula based sorting , searching ( VLOOKUP , INDEX/MATCH , FIND , SEARCH ) , cell formats ( both normal and conditional ) , data validation ,...
The easiest way is to give them 6 of the formulae that have been thrown up in the Challenges section of this forum , and ask them to explain how the formulae work !
Heck, even I'd have trouble explaining how some of those beasts work, Narayan!
Another take on this conversation is that there's a serious information asymmetry problem here:
* Excel proficient people (like those commenting above) know exactly why they are valuable and how someone could best gauge their skills.
* the people hiring Excel/data analysis/dashboarding skills know practically nothing about the subject and/or the skillset required to execute it efficiently. All they know is that the JD calls for "intermediate to advanced Excel skills". But they don't know what that actually means. I do...I'm intermediate, and Dick from Daily Dose of Excel is advanced, because his code has more Class (Modules) than mine. 🙂
I've been amazed at some interviews I've gone to for pretty technical roles, where not one technical question was asked of me. Instead, they've just asked questions to uncover soft-skills e.g. "Can you tell us about a time when you had to work with someone difficult. What did you do?" et cetera. [Is it bad to answer back "Why? Are you difficult to work for?"
A lot of those jobs were for Government positions, where the guidelines they have to follow are so tight that they probably can't deviate from the same script they serve up to everyone without opening themselves to some kind of risk that they'll be accused of not having a level playing field for candidates.
Consequently sometimes when I've heard back that I didn't have the particular skillset the position called for, I've thought "That's fine, your choice...but how the heck could you possibly know anything about my skillset given the questions you asked".
My preference before I waste possibly an hour of my time and three hours of the collective interview panels' time is to have a coffee with the hirer, walk them through my CV, let them know what kind of stuff I'm really good at, and hear from them whether they think that's a good match for what they actually want. Because if I'm not a good match, I don't want the job.
But again, all too often bureaucracy gets in the way...instead of this being seen as initiative on my part, they're too worried about it being seen as favoritism on their part.
One of the most left field questions I was ever asked was "How many boats do you think were sold in New Zealand last year? Just give me an estimate, and let me know your reasoning"
Now THAT is a great question.
@Narayan, you have a valid point about being unable to evaluate a candidate for their Excel skills thru questioning alone.
But then that would be true for any other job interview too.
The point of asking these questions is to understand whether the interviewee is aware of these features and aspects of Excel, and if so, does he have the knowledge and experience to put them to practical use in the job.
As for Excel skills required in different fields -- I suppose the smart interviewer will ask the interviewee domain-specific questions in Excel.
And I would also assume that when Excel-related questions are asked, the person would either have a laptop to showcase his work during the interview.
Or, if specific questions are being asked by the interviewer, they would have a computer on which the person would be asked to demonstrate his / her ability.
It goes without saying that the person's non-Excel background, education, experience, etc. would count equally (if not more than) their Excel skills.
And it's not going to be an either-or choice.
Sensible and intelligent interviewers would certainly consider BOTH these aspects before making a hiring decision.
Hi KV ,
Not necessarily ; when you are interviewing candidates for a Sales position , your best way to evaluate them is to get them to talk , about themselves , what they like , what they hate , anything ; the more they talk , the more they reveal themselves.
This is not true of positions where technical skills are involved ; and I think Excel skills are highly technical ; you cannot write a great Excel formula unless you have the background ; yes , even with the best background , you need creativity , but at least for routine day to day work , what is needed is knowledge and the experience of having used that knowledge in a variety of situations ; this need not be obvious in talk.
Narayan
Another thing...all to often, about the only time a workplace will look at how good you are at skill acquisition is at the interview.
Most workplaces would do far better if they concentrated on post-interview skills acquisition. But people development - especially where Excel is concerned - is often woefully inadequate.
I would like to hire good people that I know are going to get technically better. I would like to write some annual targets into their performance objectives that let them prove they are getting better. I would want to objectively measure their skills base each year, so that I could gauge whether they did in fact get better.
Hi Jeff:
It could not have been said any better.
I have observed a few 'technically superb Excellites' who, on the job, produced just enough to keep their jobs; whereas some others with minimum knowledge but with a reasonable analytical mind came up with the best ideas/results and improved their Excel skills to achieve it by taking courses in Excel and/or getting help.
If I may say so, it is analytical-cum-positive mind first and the skillset is next.
Kia Ora,
Compared to the Chandoos of this world, I would only consider my Excel skills Intermediate, the scariest part of that is that for most of my career, I have been the "Excel Guru" at various companies (incl a Fortune 500 subsidiary).
Most interviews for Analyst roles that I've been to have been similar to what Jeff Weir describes.
Although some agencies 'test' your excel skills, they do it with outdated excel versions, and where shortcuts (like right-click) aren't usable.
Interviewers want to know your level of excel skills, your ability with various ERP (accounting) programmes and they accept your self-evaluation.
Until I signed up for Chandoo, I used to tell people I was advanced and when I got the job and they saw what I could do, they always seemed amazed at what excel could achieve compared with what they were previously doing.
How humbling it was to find the Chandoo site and discover how much more there is to know.
It's like saying that, there can't be a God because within what I know, He doesn't exist, and then having someone tell you something outside your sphere of knowledge which proves to be true, and making you realise you don't know everything after all.
Thank-you Chandoo, you challenge me to better myself, although I still have yet to get my head around sumproduct, let-alone index-match - and as for vba . . .
Kia Ora back at you, Kryonic70x7. What part of New Zealand are you in? I'm in Wellington, and am always happy to meet up with fellow Excellers. Will flick you an email soon.
I was surprised that the agency skill testing had me at Advanced, and like Kryonic70x7 I'm considered to be the onsite guru.
I'm forever learning new things on here and am nowhere near advanced.
However, to keep my fellow colleagues boosted, I mentally add several extra levels above advanced, like Expert, Guru and Ninja.
[…] Chandoo’s excellent post What are best Excel interview questions? you’ll find some great comments to help you land that next job, in the rare case that the […]
How much do you think you are passionate about Excel? (or show me your passion for Excel)
Tell me about 3 things that Excel should include and how they will work.
Do a SWOT Analysis/TOWS Matrix of Microsoft Excel (If Chandoo is looking for to hire a stratgegic planner for his web business 😀 )
I am finding it surprisingly difficult even to begin to answer this question.
As a finance manager, while I always want to hire Excel jockeys, I don't go into the trenches like some of these interviewers do (VBA, pivot merging, etc.). I simply ask one question:
"When you watch others do things in Excel, are you impressed, indifferent, or perturbed? Explain your answer."
If they say "indifferent," then I presume they don't really take Excel seriously. If they say "impressed" and express a degree of desire in learning new things, I might ask them to elaborate. If by "impressed" it means they are ALWAYS learning new things, I take that to mean that the person's Excel experience is minimal. However, if they say "perturbed," I ask why. If they say something like "because most people don't use shortcut keys," or "don't use macros" or "write clunky formulas" etc. etc., then I'm confident they are ready to hit the ground running (with Excel stuff).
This interview question is also kind of tricky, because most interviewees do not want to admit that they are annoyed about anything. As an interviewer, I'm just looking for someone who is honest about their limitations, is eager to learn, but also has something to teach me and the others on my team.
I'm glad "perturbed" was the "correct" answer, because this is definitely what my gut response is, but as you mention, it feels like something you shouldn't admit in an interview. I would probably say I'm perturbed by their inefficiencies, but I love teaching others new tricks.
I Agree with Brian. I love to help others in excel, but i'm very impatient watching other fumble through excel. I have to hold myself back from offering to "drive".
Hi Dave, that is such a wonderful question because it is forward-thinking in how they approach Excel.
I agree that going through the laundry list of formulas and features isn't the best. Maybe they just haven't seen the need for some of them. Maybe they've learned it, but as we all know -- use it or lose it.
This question is really helpful because it covers what is *actually* going on around them in the office, and their reaction to it. I would put myself in the category of people who are the expert at the office but an active learner on the web.
So it is "perturbing" to see the average office worker use Excel, but highly educational to see a real veteran do it. This question definitely identifies the person who may not know everything about Excel yet, but has the desire to learn much more.
Actually, I see I wrote this nearly three years ago, and George Mount's post below pinged my inbox and brought me back here to view the topic again. A lot has happened in three years, one is extremely relevant to this issue.
I'm a finance executive now, and about a year after my response above, I was interviewed where I currently work, and one of the people on the interview committee asked me this:
"How do you set up a spreadsheet?"
Honestly, that was his question. Here's the rest of the dialogue as best as I can recall:
"What do you mean?"
"How do you set up a spreadsheet to do your job?"
"Well, it depends on the job. Are you trying to assess my skill level in Excel?"
"Yes."
"Well, the more mature I become in my career, the less impressed I become with fancy formulas and complicated worksheets, and the more impressed I become with those who use Excel as a tool to get to the right answer, even if it isn't how I would do it. Was there a problem with Excel skills in the Finance department previously?"
"Well, no."
"Okay, well, I used to teach Excel at a Fortune 500 company, I've seen people do things well, and not so well, but again, I've learned to look past that and appreciate speed and accuracy over method. In a word, I don't care HOW my team gets the right answers, I just want them to get the right answers. Does that help?"
"Yes it does."
I know for many of the readers here, that sounds snobby and perhaps condescending, but for upper level management, I can't spend the time to be concerned with HOW my team does things, so long as they deliver a high level of execution. Furthermore, Finance and Accounting is NOT terribly difficult in terms of its mathematical demands (the deepest I get is simple linear regression), and most of what Excel can do for my discipline it could handle quite well back when I first started using it in 1996. I think the interviewer was just impressed with people who are good at Excel, and used it as a token of general intellectual ability, which is something I actually disagree with vehemently.
And yes, I got the job.
I dig it! That is such a good point about the manager caring THAT it's done (quickly and accurately) over HOW it's done.
I recently posted a Udemy course about a guide to Excel interviews -- would love to have you take a look at it: https://www.udemy.com/excelinterviews/
I would really appreciate the feedback from an experienced finance manager who "gets it." If you wouldn't mind messaging me at george@georgejmount.com -- I can get you a coupon to check out the course free and I would love to maybe even do a discussion with you to feature on the blog.
Thanks so much, Dave.
Best,
George
georgejmount.com
Looks like an interesting course, George. Ultimately though, what I said above about Excel in an interview situation is just my own management style. Some managers in finance and accounting, again, think that Excel skill is an adequate proxy for general intelligence, and I do NOT agree with that whatsoever. Case in point: my accounting manager has 40 years experience, her Excel skills are 'basic' (at best), but she's the best accountant I've ever known in 25 years of experience at five different companies - most of them Fortune 500s or monster international companies with anywhere from 30 to 200 full-time finance and accounting professionals. She asks me frequently to help her make better Excel formulas in her work, and I always respond "how much time will that save you?" and she nearly always says "not much." So in my mind, if she continues to execute doing things the way she already knows, my assistance in helping her tighten up some of her formulas doesn't yield benefits for anyone. That's just me sticking to A) allowing her the freedom to do her work as she sees best, B) not being a crutch to her continued development and skillset, and C) if her rate of execution doesn't benefit with "higher formulas" than the ones she currently uses, then I don't really care.
Excel is neat - its impact on the world cannot be understated - but I think a lot of people in my field (again, Finance) put too much weight on it. For other purposes, like IT, applied math, physics, statistics, etc. I'm sure a lot of the questions people posed here are perfectly relevant, but for my field, as long as a person can use it to meet deadlines, then I'm satisfied.
Please forgive me. I’m from IT. We look at things a little differently it seems. Most of these responses focus on what I’d call “language syntax” and “platform tools” questions. They basically answer, “Does the candidate know how to use the tool?” Before I expand beyond that, let me throw in one more question along those lines:
“When would you use a Class module?”
Okay, now let’s see if the candidate can work efficiently.
“What is in your library of formulas and routines and where are they stored?”
Let’s determine if the candidate understands the nature of XL.
“When should we use XL and when should we use a database?”
Lastly, let’s see if the candidate can go beyond crafting clever workbooks and eventually graduate to Team Lead, Business Analyst, Manager or even Director.
Scenario: A department head just called for our help. She setup a meeting with some of her staff to explain the project. She says it’s big but she’s sure XL will work. I’ll be out of the office. I’m sending you to represent us. When I get back I’ll need your report.
Question: What will your report to me include?
And as a follow on I’d ask:
“You’ve come to the conclusion this shouldn’t be done at all. How would you present this finding?”
Higher positions require people who instinctively ask a simple question before solving any problem, “Is this the right thing to do?” This isn’t a bad question to ask ourselves at any level – including the lowly XL tech who might consider: Is this the right formula? Is VBA appropriate? Should this be done in XL? Should it be done at all? Staff who ask such questions and can come to appropriate conclusions quickly are far more valuable to me than those focusing purely on finishing their assignment. And should staffers determine their project shouldn’t be done at all, those who can present such findings in a way that BUILDS relationships instead of creating confrontation are even more valuable and likely to climb the corporate ladder.
Possible responses to the first question can include things like:
• Who is impacted can be critically important. The report should include names of participants and impacted parties and how this impacts each.
• Is XL the proper tool for this? Does this duplicate other systems? Does this bypass existing systems?
• Will this save time? Whose time? Enough to cut overtime? Enough to cut staff?
• Will it save money? Hard cash, future investments, potential expenditures?
• Will it reduce errors? Do those errors cost us? How?
About how much will the project cost? How many development hours, testing hours, training hours? What will be the ongoing maintenance costs? Any new PC’s required? Any new staff? Any network infrastructure?
A possible response to the follow on question might include:
“I would present the project’s return on investment and then ask permission to proceed.”
Executives understand business and politics. The business end is usually straight forward. If their project costs too much and the politics don’t support it, the executive will kill the project upon seeing no ROI. By asking permission to proceed the staff member demonstrates trust in the executive and willingness to support the executive no matter what they decide.
Hiya Craig. Very nice response, as always. When would you use a Class module? When I know that there's always going to be someone else in the organization that has taken the leap to understanding and using class modules. (Or that you know how to find an external person at that level, and will hire them rather than throw your under-skilled internal staff at at.) Till then, we'll keep it in a form that the people following me are likely to be able to troubleshoot.
What is in your library of formulas and routines and where are they stored? Heaps of stuff. Some I even wrote myself. Most I pinched from Craig Hatmaker's amazing Beyond Excel blog!
When should we use XL and when should we use a database? We'll use a database when IT let us use a database, and start giving us some actual support, and when I know that there's someone else in the unit that has taken the leap to understanding and using databases. Till then, we'll keep it in a form that the people following me are likely to be able to troubleshoot.
Is this the right thing to do?. That's a brilliant question. Reminds me of a task I was assigned to model something that couldn't be modeled with any degree of certainty, statistical or otherwise. Their thoughts: anything is better than nothing My response: this will be as good as nothing. It will be just as likely to steer us into making a wrong decisions as a right one. Rather than me taking 4 months to build this, why don't we just use =RANDBETWEEN(0,100)
I seem to spend a lot of time arguing why my skills shouldn't be used, or building things in Excel that could be built better in something else if that were an option, based on the 'as good as nothing' and the 'who will maintain this' arguments above.
Is XL the proper tool for this? Does this duplicate other systems? Excel is the only tool provided to business that provides any kind of automation capability (Harlen Grove). Anytime we ask IT for a quote to automate or build something, they come back with really large quotes. So we keep using Excel because we've already paid for it, and we've already paid for the staff member that programs it (me).
You have a gift. You can "rake me over the coals" and still make me laugh. Hopefully my IT brethren will shed bigotry, see the light, and embrace XL and end user computing as we have.
I had as a head hunter one very simple Excel-question covering all areas: "What was the most complex tool / spreadsheet you have ever developed in Excel?"
I got very interesting answers.. 😉
List of Advanced project done on excel other then normal reporting.
I've developed a 1hr 3 scenarios test.
It's near impossible to complete all three with a perfect score within the one hour. This allows us to see what their prioritisation and time management skills are like....what scenario did they choose to do first and how they work under pressure etc.
We usually email the candidates the test at a prearranged time (assuming that they have passed the first interview)
The test looks at:
-data analytics
-graphing abilities
-opportunity to use pivots
-job knowledge specific to the role
-presentation (did the candidate try to present the data to the audience or just try to crunch the numbers)
-formula knowledge
-bonus points for spending 30secs on google images to grab the company logo and add it to the summary page etc
I then use a scoring guide to ensure consistency across the candidates.
It's scary how many could talk with some knowledge in the interview process but score low on the testing. Knowledge is no good if you can's apply it.
I have learned even from reading some of your tips from above discussion. I am constantly learning new things from this website and other. Lots of great tricks out there. I love all of the responses. Thanks.
I am a consultant and develop reports for clients and did the same when I worked inside a company. So I am interviewing on the other side.
The main question is: How do you feel about pivot tables? If I get a shudder and blank stare back, I know that I need to generate reports that are uber basic for the client to update and not to incorporate pivots. Make formulas a bit more understandable.
If the client embraces pivots, then I can use pivots in my solutions, but again the goal is to keep it simple to update and maintain.
Most of the time, clients just assume you know what you are doing and don't ask a huge amount of technical expertise questions.
As to the questions posed in original blog:
1. When looking at a large list of data, will use filters to review, will sometimes generate a quick pivot to get a feel for the data (I love pivots) and will look for reconciling points to source documents. Then look at how to generate a story from that data.
2. Of course can make simple and elegant! What points are relevant to you? what aren't you getting out of this model.
3. - 5.. Look for ways to make gathering data more efficent (and ultimately the report). sometimes its just a small tweak.
What story are looking to tell with this data.. What is important to you? Do you have a span of colors you like (believe it not this is important)/or style preference? How do you want to update chart or report by dates, customer or ?
Fun ideas and thoughts... Fran
Great post ! At least now I have general ideas what will be asked by interviewer .. haha.
Firstly,
these are some great examples of questions that I would include in my interviews going forward,
However, since discovering chandoo's blog (and all the others out there) I think there are so few good users of Excel. I earnt 2 promotions and a new role because of everything I learnt here.
my question will be:
have you heard of Chandoo.org? if Yes then go straight to Chance, if you pass Go collect £200 on the way
Though I was never interviewed in Excel nor I will be interviewing anyone soon, I have the following questions in mind albeit for different roles -
1>What is Array formula and when you will use it.
2>What is 3D formula. Write the macro for sumif3d (god level vba)
3>Is there any formula to get all sheet names in workbook
4>Write some complex logic in conditional formatting
5>Get the day in letters from date ( if some one does by formatting, then he is good to get the job 😉 )
6>Query the data from web (query the html tables, how to solve div tag issues?)
7>Ask innocently, how does Table function used 😉
8>Give some operations management problems to use solver
9>Where to use waterfall / pie of pie charts? scaling issues..
[…] The other day, undercover Excel secret agent KV came up a great list of interview questions in response to Chandoo’s great article What are best Excel interview questions? […]
Well, this is a topic near and dear to me as I have recently released a course on "getting hired with Excel:" https://georgejmount.thinkific.com/courses/hiredwithexcel
I had almost no experience with Excel when looking for my first job, and I know that's what sunk me at the job interview. So this course is meant to be what I wish I'd had as my own interview preparation.
Like with any selling, it is important to identify and solve your customer's "pain point." For people looking for a data analyst, the "pain point" for me as a hiring manager is messy data. So my "seller," the interviewee, should show me that he can solve this for me.
So an interviewee/prospective analyst needs to show he understands the importance of data cleansing. I like to ask if he's heard of "data wrangling" or if he can estimate how much time I spend preparing vs analyzing data.
I'm less keen on asking specific Excel features. I'd rather a candidate know a couple really well and be versatile in applying them than a candidate know everything under the sun and have few use cases.
In my course, I cover just VLOOKUP and PivotTables, but in extreme detail, with the goal of "how does this help me deal with messy data?" in mind.
This is an excellent thread and it has given me a lot to think about for my course and beyond. Thanks to everyone for participating and to Chandoo for a great post as usual!
Wow everyone, this is such an informative and important post. It's one thing to know the bells and whistles in Excel -- another to use Excel as a means to solving real-life needs at the workplace.
I've developed a Udemy course called "Hired with Excel: The Essential Guide to Interviews" that I would love you all to check out. It's at https://www.udemy.com/excelinterviews/
Use Chandoo50 for 50% for you all -- since I've learned so much from you and would love to get your thoughts on the course.
Best,
George
georgejmount.com
[…] At Chandoo.org, “What are the Best Excel Interview Questions?” […]
everyone, this is such an informative and important pos nice