Excel Skills Test With Excel Interview Questions Needed

I have a problem that you may be able to help. I want to do this, and how would you do it?

Listen carefully and if you can have him or her do it on the computer. You can also let the candidate draw on paper or use the white board.

Bob,

As an Excel MVP, I agree with the basic direction that Len suggests. Here are some thoughts about the points he made...

1. The IT department seldom knows much about Excel. As an institution, they typically dislike it. I would rely on them more for their opinions about a candidate's knowledge about your data management issues, and how Excel interfaces with the data. But I would rely on your own staff to evaluate Excel skills that involve using the data when it's actually in Excel.

2. A guru could suggest a use case that has nothing to do with your company's needs. Instead, I would try to get an internal guru to ask Excel questions that directly address your company's needs and technology.

3. Virtually every example of a candidate's work will represent a report or analysis that would need to be updated periodically. Really useful Excel people will be able to explain how their Excel examples were updated quickly and easily when needed.

(Warning: Most Excel users probably won't have a good solution for the update problem. And many who do will be able to express it only in terms of 3rd-party software that your company doesn't own. But if she starts to ask your guru questions in search of a potential solution, then you've got a candidate with the right attitude.)

4. There is wide disagreement about what best practice means in the Excel arena. I'd be more interested in finding a person who could discuss the issues intelligently than in finding someone with a rigid methodology that the IT department agrees with. In fact, using IT standards for Excel work can impose unacceptable delays in getting real work done.

Also...

...I would be very careful of people who are VBA enthusiasts. It's true that one can create a lot of great solutions with VBA. But I've seen many problems where an employee created poorly documented VBA systems, and then left the company. Then the company had to hire me or someone like me to make sense of it all.

If they want to show you something they did with VBA, ask them to show you their code. Look for comments in the code. (Comments typically have a green font.) If they don't write understandable comments their code, you don't want them to write VBA for your company.

(Many people say they don't comment code they write for themselves. But within a few months after a programmer writes code, he will have forgotten what he did and why. So comments ALWAYS are important.)

...Finally, here are three questions that could help you to find someone who knows how to work with data in Excel:

a) What are the relative merits of using VLOOKUP vs INDEX-MATCH? When would you use one technique rather than the other? (This is sort of a trick question. If the candidate knows about INDEX-MATCH, she'll have little good to say about VLOOKUP.)

b) What are the relative merits of using SUMIF vs array formulas vs SUMPRODUCT? When would you use one technique rather than the other? (If your candidate doesn't also mention the SUMIFS function, he's not expert in working with data in Excel 2007 or 2010. If he can discuss array formulas, he's at least an intermediate user. If he can discuss SUMPRODUCT with confidence, he knows quite a lot about working with lists of data in Excel.)

c) What spreadsheet functions would you use to pull data from a PivotTable? (Primarily, you're looking for her to mention the GETPIVOTDATA function. But it wouldn't hurt if she also mentions INDEX-MATCH.)

While Charley's questions were extremely good (I learned something), what are you hiring the person to do?

Is this person's sole duty to create masterful Excel spreadsheets or are the spreadsheets adjunctive to their job.

That would determine the depth of query.

Thanks all for the very helpful commentary!

I look for someone to mention checksum totals so they can quickly identify errors.

In addition to the great suggestions above, you always have the option of open ended questions if real examples are not available. Example:

Give me an example of when you created a report from scratch for a [sales/operation/finance] executive. What was the problem to solve and what techniques did you use to prepare the report?

You can follow up with "How was the report received?" and "If you had it to do over again, what would you have done differently?"

Great suggestions above....however, you need to determine the depth of knowledge that is relevant to the job. Excel is not rocket science - it can be learnt. The key is to determine the adaptability of the candidate.

If I were interviewing, I will create some spreadsheets with raw data that the candidate will see in his "new" job and ask questions as to how the candidate will extract a report from the data in a particular prescribed format. Maybe creating some graphs - if the job requires it, etc.

Just by observing the candidate, you can see how much of excel he knows (i.e. does the person use the key board short-cuts OR goes through the menu all the time, etc.)

Great discussion!
The tools (we are discussing excel here) are means for achieving a goal. I so often see excel using big data (I'd say exceeding 10MB, some may say 50MB - external data connections) or complexities where it is not appropriate, given that a data warehouse would handle even 10TB. I do not want my audience using the tools inappropriately.

I'd like a good candidate to suggest alternatives, access or SQL at times, if it is appropriate (shared and cross-referred documents). If the supervisors / hiring managers do not know enough to test excel skills, they need to attend a few training themselves. Just the data functions in excel 2010 would amaze any statistician, there are more than you would imagine, be it tax, business, maths...

For specific questions, internet provides them, but you need context of your specific situation, third party tools can do wonders too, do you need someone proficient on a particular tool. The advantages and disadvantages of Vlookup is one of my favourite questions, although I no longer use the function itself too much. Protection options, working with tables, document sharing, multi-user access, templates, page-setup's, macro's, VBA. The questions should follow where the candidate's strength is and not make them uncomfortable where they obviously do not have deep knowledge. I do not need to test a candidate further if they accept they have not worked much with charts. In today's world, soft skills of teamwork, attitude could matter more than even the actual proficiency in the tool.

Awesome thread and neat that it continues to get replies with the initial question being posted some time back. Thanks all...I plan to share some of this content with my team.

I would suggest asking the candidate bring some examples of his/her work in Excel and have them walk you through some of the details and have him/her make some revisions you suggest. Personally, I have used Excel for many years almost my entire work day. I am not sure how well I could demonstrate my skills during an interview addressing a problem with data that I have not seen before and on hardware I don't normally use. Just the difference between a full keyboard and an ultrabook takes some adjustment with a lot of advanced Excel techniques.

"There is wide disagreement about what best practice means in the Excel arena. I'd be more interested in finding a person who could discuss the issues intelligently than in finding someone with a rigid methodology that the IT department agrees with. In fact, using IT standards for Excel work can impose unacceptable delays in getting real work done." I agree with this and many other statements in this thread. I work at a University in budget & finance. We have many student assistants working for us that blow most of us out of the water when it comes to excel skills, power users we call them. However, many lack the creative problem solving skills, proving technical know how is not enough.
I think having someone in the room that is a power user from the department you are hiring for would be more useful than almost any other resource. For example, how I use tools in excel at the division level of the University is a lot different than those who work in Data Warehousing dept., Budget Office, or Admissions Statistics. That being said, we each could easily adapt, learn and create functions to do those jobs. I love excel and when I get into a room with others like me and we start talking about how we use are favorite tools or created an unusual formula in excel I know what level of knowledge the other user has almost immediately. What a fun conversation this is!

I'm not an Excel expert. However, I'm writing to call attention to the wreckage that I have seen in dozens of middle-market private companies (MMCs) by well-intended professionals using Excel in parallel with an accounting system. It's a widespread malady that I call 'Excel-itis,' and it's life-threatening. . .for the company.

In my experience advising MMCs over the last two decades--and I'm not talking about situations involving either Big Data or multiple and incompatible systems here, but just one accounting system--when Excel-itis afflicts a company, at least one of three situations is present:

(1) Those afflicted with Excel-itis don't know how to use the existing accounting system;
(2) The accounting system is the wrong one; and/or
(3) The accounting system comprises bubble-gum, bailing-wire. . .and prayer.

The result is chaos because those carrying the Excel-itis bug (a) don't document the Excel files (they don't know how to), (b) don't know how to keep them updated, and (c) don't have the organizational skills to spot duplications and needed linkages among different files. The chaos often results from the following:

(1) Companies have undergone rapid growth and outgrown the existing system;
(2) Companies are trying to "save money" (good luck with that);
(3) Owners of such companies don't understand their own accounting needs; and/or
(4) The outside CPA firm has, in effect, "retired" on the client's payroll.

As a CPA myself, I'm especially outraged when I see (4) above. But, in my experience teaching CPAs in 33 states, most smaller CPA firms are owned by go-along/get-along partners whose primary interest is maintaining the annuity flow of fee revenue that comprises the individual partner's 'book of business'. That book is the primary determinant in their compensation. If the book is threatened, the typical small-firm partner will move heaven and earth to protect it, and, either through ignorance or self-interest, will put the firm's interest ahead of the client's. I've seen this more times than I can shake a stick at. I'm fond of saying that if CPAs had been running the United States in 1776, we'd still be working for King George. Go-along, get-along.

The inescapable fact is that growing companies outgrow the skills and experience of their professional advisers, esp. the CPA firm and the law firm. They must move up the economic food chain of such advisers, or the bad advice they're getting can, eventually, take the company under, and it will never know why. Besides commendable loyalty to what is, in a figurative sense, 'friends and family' in the existing advisers, the much-higher hourly rates that come with moving up can stun owners used to a much lower price point.

They often say, "We can't afford that." That is because, in areas where their knowledge is not deep (e.g., accounting, lawyering, IT, banking, management consultants [I am one]), they live in a 'cost world,' not a 'value world.' High-priced advisers should--SHOULD--be able to make or save a client company more than it charges. They do so through spotting potential problems before they occur, by introducing their clients to larger and more-profitable potential customers, and by avoiding the sky-high opportunity costs that lousy accounting, lousy lawyering, lousy IT work, et al., impose. "Saving money" by continuing to pay advisers who are in over their heads becomes hideously expensive. . .and most owners never understand either why the expense was so high or that it was easily avoidable. Growing companies can end up with the economic equivalent of a 20-story building resting on a two-story foundation. It will fall down.

The best way for an MMC to improve its odds of not getting caught in this tar-baby embrace lies in forming a Council of Advisers. This is a Board-equivalent, but without the D&O expense. Some years ago, I wrote a series of four articles on how to do this for an AICPA publication. Anyone who would like PDFs of these articles is welcome to e-mail me: wmiller (at) beckmill (dot) com.

I hasten to add that the typical MMC should "save money" (again!) by trying to form such a Council without working with someone who's done it before. It's not nearly as easy as my articles make it sound. In our work providing that service, we also construct a financial composite against which an MMC can benchmark its past and future performance. That helps them avoid the typical problem that most MMCs have: they assess this year's performance by comparing it with last year's.

Here are a few more thoughts...

1. If you insist on MCAS certification, as Valerie suggested, you could miss out on some outstanding candidates. To illustrate, I'm an Excel MVP, one of about 120 in the world, but I don't know of any Excel MVP who claims to have an MCAS certification.

2. Years ago, when I worked at Hewlett-Packard, pre-screened candidates for professional positions spent time with five or six members of the staff. Each member was specially chosen to learn the candidate's competence in a specific area. My area, for example, was managerial finance.

As I remember, I'd ask about time-value-of-money issues, breakeven analysis, the DuPont Formula and Sustainable Growth Rate concepts, how to minimize errors in financial reports, the relevance of the Standard Error of the Estimate to management reporting, and so on. I'd also ask questions to which I didn't know the answer, hoping to learn something from the candidates. Similarly, today, a strong Excel user on your staff could talk shop with a candidate about spreadsheets for an hour and learn how skilled she is with Excel.

One way to assess the results of that conversation would be to ask your Excel expert something like, "Does she know only about what Jimmy does? Or is she up there at Jane's level?"

3. I used to ask a question like, "We have this problem. How would you solve it?" If you do the same, your candidates might give you some useful ideas occasionally. But more importantly, you'll learn a lot about their knowledge, intelligence, and intellectual curiosity from the questions they ask about the problem you pose.

With regard to spreadsheets, you could briefly explain areas of Spreadsheet Hell that you're struggling with, or complaints from readers about your Excel reports, or problems with errors in your reports, or types of analyses that you haven't figured out how to create yet, or problems with slow calculation times, and so on. And then ask the candidate what ideas they have for solving these problems.

By asking about practical issues like this, you're shifting the conversation from what they know to what they could *accomplish* with what they know.

4. If there's any way to ask the candidate to bring samples of his best Excel work, that could tell you a lot. And it could generate many questions that could help you assess his level of skill. Also, of course, you should ask enough questions to assure yourself that the candidate actually created the sample, and didn't just update a workbook created by someone else.

Charley Kyd
ExcelUser.com

SAAS solutions are available for Accounts receivable management, can any of peers help by sharing some solution of accounts receivable management in excel.

@Balbir Singh --> don't do it, use the/any SAAS solution, Excel is not an AR management tool (even QuickBooks is superior)

Agree totally.

I agree with the most of the suggestions above. It seems to me that the best way to test Excel skills of potential employee is to ask for MOS Excel Certificate (formerly MCAS certificate).

I remember when on the job interview they wanted to see if I have any basic information about Excel so they have asked me about doing multiplication table and I failed..