Excel questions to ask in an interview

Hi CB,

I'm a VBA Guru and I can't answer any of your questions

Just some observations:

1.) What xladept said.....except I am not a Guru.

2.) Are your interview questions assessing Excel knowledge / skills or are they testing someone's ability to articulate? To do something is one thing.....to "explain" is another. Question 6 is a good one to point to. That very question has shown up on the Forum. In one particular case two of us made the attempt.....I was one of them. It was a case of "I can see it, but how can I say it?"

3.) Perhaps in addition to the questionnaire supply a collection of typical challenges you would want the candidate to demonstrate their skills on.....much like the way people pose problems, upload sample workbooks on the Forum and then give assignments. This might be at least if not more revealing of Excel knowledge / skills.

You've drawn a tough assignment. I wish you the best.....been there....done that one.LOL!

I would be interested to see your answers to those questions?

Seems you are looking for way above basic/novice level, so perhaps bring in questions regarding SUMPRODUCT(), ARRAY's, INDIRECT(), INDEX/MATCH, as well as basic data input sheets and summary/output sheets?

Originally Posted by FlameRetired

3.) Perhaps in addition to the questionnaire supply a collection of typical challenges you would want the candidate to demonstrate their skills on.....much like the way people pose problems, upload sample workbooks on the Forum and then give assignments. This might be at least if not more revealing of Excel knowledge / skills.

You've drawn a tough assignment. I wish you the best.....been there....done that one.LOL!

Thanks for the input - I suspect they are slowly transitioning me from worker bee to supervisor bee with how my role has been changing in recent months...

To your point I did note that I am attaching Excel files to the email I send to candidates:

Originally Posted by CBJason

I do have a couple questions that reference some files showing they know how to actually create a table array, and run a VLOOKUP against it.

Here's a direct copy/paste of my questions as I sent them to the candidates:

We’ve assembled a short list of Excel-related questions as a follow-up from our discussion yesterday. Please take some time and complete these questions to the best of your ability. We’d like it back by 5pm today.

1. In your own words, please explain the difference between a table array and a VLOOKUP?
2. What is the formula to combine two columns of data into one in Excel 2013?
3. In your own words, please explain what it means when you see “#NA” in an Excel field?
4. A customer has a report that they get from a Pivot Table that you have created and data gets refreshed on the Pivot Table weekly.
a. What’s the minimum number of steps (include all mouse clicks – either right or left as a step in the process) needed to refresh the Pivot Table with a new week’s worth of data?
b. What are those steps?
5. What is your favorite color?
6. What is the command to remove leading and/or trailing white spaces from a column of data?
7. How can you quickly get the average, sum, and count of a range of data without running any formulas or calculations in Excel?
8. Using whatever Excel functions, tools, and commands you like, please edit the Comcast.xlsx file here URL REDACTED)to do the following:

a. Remove all the empty lines of blank space in the rows and columns

Re-format so all cells have black text and no background fill.

Add the driver name in front of the vehicle number. (Hint: You’ll need to use both the RFID Technicians file and the Comcast file!) When you are finished, save it as a new file with your name “JohnDoe.xlsx” and send back via email.

9. Often customers will reach out to us to get metrics to demonstrate that to their senior management that they are getting value from our product. To prove this out, they ask us to get numbers for things like idle, mileage, fuel consumption, etc. Yet often when crunching the numbers, we will see an initial decrease across the board, but then over time, the trend reverts to up-ticking again rather than decreasing. This is a huge challenge to overcome. Additionally, customers will often want to see either totals and/or averages per vehicle or by region.

In considering this expectation, explain why numbers and metrics will not show reductions as expected.

In calculating averages, I do the following:

Calculate the average per region for the quarter

Region 1 has 100 Vehicles and their average idle is 32 minutes
Region 2 has 700 vehicles and their average idle is 45 minutes
Region 3 has 500 vehicles and their average idle is 40 minutes

I then calculate the fleet average from the above to be 39 minutes. Is this a valid average? Why or why not? If this is not a valid average calculation, what would the correct one be and how do you calculate it?

So, my line of thinking is that I am not only getting Excel questions answered, but also an indicator of how they think, their logic, and ability to reason through problematic scenarios...

@ post #6

So, my line of thinking is that I am not only getting Excel questions answered, but also an indicator of how they think, their logic, and ability to reason through problematic scenarios...

Cool....sure glad I'm not interviewing for this one. LOL

I guess the first question that springs to mind is, do you know the answer to those questions? What answer are you expecting? And what if they give a different answer or use different terminology? I'd probably want a little more clarity around some of the questions. For example, "2. What is the formula to combine two columns of data into one in Excel 2013?" Do you mean into a third column (as a helper column)? Why in Excel 2013? Is there a specific way you would do it in Excel 2013 that you couldn't use in earlier versions? Are you expecting the answer to be CONCATENATE(...)? I'd probably fail that as I NEVER use CONCATENATE ... unless I'm working on a Client spreadsheet and that's what they've used. I use ampersand (&) to concatenate values together.

So, what do you want these candidates to do? Something that you can already do? Or are you expecting them to do things that you can't do? And how do you ask someone to answer questions and describe approaches that you haven't mastered?

Sorry ... just playing devil's advocate. It's not a simple question and, consequently, there's not a simple answer.

I recently answered this thread: Requirements List For MS Excel Users (All Versions)

The first link in my response is: http://www.financial-edu.com/excel-l...3-advanced.php

That describes a book which looks at what the author believes are topics to be covered at different levels of experience (another debate altogether).

So, maybe you need to look at that for some ideas, decide what you're looking for in a candidate and pick questions that cover the relevant topics.

And, for example, don't ask pointed questions like, "tell me all you know about Pivot Tables". Ask them, "if they were given a table of data in Excel, what tool, tools or functions might they use to analyse the data". And see if they talk about Structured Tables, filtering, sorting, Pivot Tables, Charts, and maybe Dashboards. Then you can delve behind the scenes and ask them to expand on one or more of their approaches. Historically, I probably would have used an Advanced Filter to get a list of unique items on a separate worksheet, and then use SUMPRODUCT or later SUMIFS to get the totals.

Anyway, just some thoughts.

Regards, TMS

Last job I was interviewed for which required extensive use of Excel, I didn't get. Too clever for my own good. It was a practical exam on a computer using Excel. Fair enough. I answered all the straightforward questions but I wanted to give a comprehensive answer to one specific question. If I recall, they had data in a workbook and they had each month's data in a separate sheet. I had a really neat solution in mind but, in the time allowed, I didn't get it to work. Seems the solution they were looking for was three separate VLOOKUPs added together. I could have knocked that out in seconds and had 10 minutes to spare on the test.

If it will help, I will provide my answers to those 6 questions (as someone who would like to believe I am at least a 7 out 10 skill wise).

1) I think I would get this one, though it would leave me scratching my head. A lot would depend on whether or not I would recognize that you are using "table array" to refer to the 2nd argument of the VLOOKUP() function. Assuming that, in the heat of the moment, I make that connection, my response would probably be a discussion of the difference between a "function" and an "argument" in that function. I think that would have been essentially the same answer as the one you gave. The reason this would leave me scratching my head is that it seemed "awkward" to compare a function and an argument, because they seems such different concepts. I might ask this question more as "describe the 4 arguments of the VLOOKUP() function".

2) I think I would have failed completely at this one. Mostly because, as an engineer, I almost never have cause to combine or manipulate text strings, my thought process would not have even taken such a generic question and went down the text string road. I probably would have come up with something like "it depends on what you mean by 'combine'. For example, if you want to sum two values, you would use the + operator or the sum function." Basically, my response would be "define the mathematical relation you want to use to combine the two columns and I will come up with a combination of formulas/functions that will perform that operation."

3) I would probably get this one, though I would wonder how thorough of an answer. Yes, N/A is the error given by lookup functions when lookup_value is not found. I'm sure it can have other meanings in the context of other functions.

4) I would probably get this one: TRIM() or CLEAN().

5) This I may get, though I may also note that I never use those fields in the status bar so I tend to "uncheck" those options so they aren't usually available to me. I prefer to put those functions in the spreadsheet so I can use those values in other calculations.

6) This one feels more like a statistics question than an Excel question. Not knowing how statistically rigorous you would expect a candidate's response to be, I would probably attempt to explain how averages are calculated, how one might use an average of an average, and why, as your answer points out, that is sometimes not appropriate. The hard part of this answer is that the answer really depends on the statistical meaning of the analysis you are performing.

In many ways, I think #6 plays into your statement that "programming is different from math". Yes they are conceptually different, but I find them very interrelated. I find that I cannot program a spreadsheet to perform an analysis if I do not understand the math behind the analysis. It seems like this question is intended to see if the person understands the kind of math you need them to understand. I might suggest that this question be more specific rather than so generic. I would even be tempted to make these kind of questions into dreaded "story problems" (Here's some data, how would you compute the overall student body average?).

Well OK - Here's how I would have answered:

In your own words, please explain the difference between a table array and a VLOOKUP
A VLOOKUP is a Worksheet Function while a Table Array is a table wherein you could perform the Lookup.
What is the formula to combine two columns of data into one in Excel 2013?
For r=2 To LastRow:Cells(r,c+2)=Join(Array(Cells(r,c),Cells(r,c+1)),” “):Next r
In your own words, please explain what it means when you see "#NA" in an Excel field
It means that there has been an error and to detect it you test by: If IsError
What is the command to remove leading and/or trailing white spaces from a column of data?
For r=2 To LastRow:Cells(r,c)=Trim(Cells(r,c))
How can you quickly get the average, sum and count of a range of data without running any formulas or calculations in Excel?
Dim R As Range,WF As Object, A As Single, S As Single, C As Long
Set R=Range(“A2:Z23”): Set WF=WorksheetFunction
A=WF.Average(R):S=WF.Sum(R):C=WF.Count(R)

Or you could just select the range and check the status bar(afterthought)
Explain the danger in calculating an average of an average
You must be sure to weight the averages