How to assess Excel and data management skills in a job interview

Bob Stenz's Profile

Excel skills assessment questions for job interviewsExcel and data management (including working with large volumes of data and databases) skills are often assumed upon finance and accounting professionals. However, some positions are better served by those who are exceptionally strong in this area. How does a hiring manager assess (or maybe offer an Excel skills test) Excel and data management skills in an interview?

Answers

Topic Expert
Member's Profile

Database skills needed are so specific to the database in question so I'm not sure how address that portion of your question. However I will address my opinion to the Excel side to your question.

I consider myself an above average user of Excel, and I too have found this to be a hard skill to assess in interviews. I have had quite a few people tell me that they use Excel often and I then upon hire I find they can barely to more than do simple formatting and a sum function.

I've started to ask what types of "functions" they have used in their previous experiences. Again basic users don't often list more than "sum", however advanced users will start saying "sumif", "vlookups", and "if".

I've also started asking what is their favorite/most useful advanced function. Very few say "pivot tables" or "macros" but I snatch those people up. However if they can't think of any at all that means their excel skills are probably not up to filters etc (which is a must for my department).

And finally I've asked if they've taken any classes and what tips/tricks they learned. Usually this helps me determine if they hated the class (thus will be resistant to learning how to manage some of my advanced worksheets) or if they enjoyed learning new ways of working in the program.

I do think that the best would probably be to give a test of some kind, and even if they don't know how if they can follow the direction to get the desired results then they would work. However we haven't started doing that (yet).

To comment, and for full access, login or register
Member's Profile

Here's a treasure trove of free excel spreadsheets, free accounting spreadsheets, plus all kinds of other excel resources, such as tests, tutorials and more:

http://www.proformative.com/resources/free-accounting-spreadsheet

Plus, this free "Excel Shortcuts Cheat Sheet"http://www.proformative.com/whitepapers/excel-shortcuts-cheat-sheet

Enjoy!

Best... Sarah

To comment, and for full access, login or register
Proformative Advisor
Member's Profile

DB/SQL is typically a logic issue, so having sample logic questions (a la google) seems to be reasonable.

For excel, I've asked for (and been asked for) samples...I'm not sure that's the most effective way, and is a time sink. An approach I prefer is to simply present an excel file prior to the interview and ask them to do something with it (that could be solved easily with a {sumif} as above)...and see what they actually do.

An alternative approach is to show a solution to a problem, and ask how they would have solved it...that reveals both their skills and their creativity. Caveat: I've erred on the side of presenting solutions that are well out of the league of my applicants...so have gotten "wow" as a response instead of an actual answer.

To comment, and for full access, login or register
Member's Profile

Why don't you create a test for them to perform? Devleop a database and ask them to create a pivot table to extract out certain data. Or have them create a VLookup formula with the same database of information. Someone doesn't have to understand what the data means to create these formulas and such, they have have to know how the data is formatted and they should be able to create the forumla/pivot table to accomplish the task.

To comment, and for full access, login or register
Topic Expert
Member's Profile
Tier One Services, LLC

My services have been engaged as an outside consultant to perform these assessments, and this approach has been very successful for my clients. The advantages of a live assessment rather than an exam include seeing underlying successful or unsuccessful habits as well as assessing the candidate's ability to pick up new skills under pressure.

Keep one thing in mind: When the candidate has the right attitudes and habits, new or expanded skills are easily learned. So whatever assessment technique you do use, be sure you retain the ability to illuminate those attitudes and habits.

To comment, and for full access, login or register
Proformative Advisor
Member's Profile

Bob,
My favorite interview prompt on this topic - Tell me about an Excel tool that you built from scratch that you are most proud of.

My experience is that responses yield two pieces of information. 1. A listing of advanced features the candidate has used in practice. 2. A clue of how the candidate approaches problem solving with Excel.

Good luck with hiring.

Henry

To comment, and for full access, login or register
Member's Profile

I agree with Scott and Jaime. Ultimately, you want smart employees with a lot of initiative to learn. And frequently we find ourselves in companies and departments that use Excel in specific ways with formatted spreadsheets that have been in place forever. Work environments can get stagnant as far as reaching out for better ways to engineer the wheel in the face of every day production and deadlines. I think a Excel test would be a great idea. Since you can never know everything about Excel (it's capabilities seem to be endless) make the test encompass all the major formulas and functions in which your accounting department depends heavily. I have taken several classes for Excel, but invariably I get back to work, get back in my normal groove and only use 2 or 3 of the nifty features I've learned. Additionally, Henry's suggestion is a great one. Have the prospective employee either describe a SS he or she has built from scratch, or better yet, have them bring it with them to the interview and let them show you how it works and let them lead you through it.... with only sample data of course....

To comment, and for full access, login or register
Member's Profile

Personally, I find it easy to see if someone has the basic skills I need them to have just by watching them work. I say you put them in front of a computer and a dummy database and ask them to perform the tasks that you need performed. The candidate should be able to figure out what functions they need to use.

If they can do it, they might have a couple of intelligent questions about your objective and maybe about the data itself. Then the candidate should be able to get right to work. Even if they don't get the right results from the direct question, (especially in the short time available in an interview), if you are watching them work, AND IF you know know the task at hand yourself, you will see how well they are navigating the spreadsheet. This basic navigation and initial set up of formulas and functions should provide you with some insight into their skill set.

To comment, and for full access, login or register
Member's Profile

Agreed Daniel. Creating this kind scenario would pull back the curtain and let the person show what he or she knows. The real division of skill level comes not just in knowing different tools in the tool kit, but being able to design spreadsheets that are truly efficient, accurate, and NOT FRAGILE in their construction - and that is a sign of a true pro. One more thing. I have repeatedly seen companies lean on Excel way too much to patch the holes in their data management. Their data becomes fragmented and hard to integrate when they don't look up and see that their ERP or compilation of different software packages are not serving them well any longer.

To comment, and for full access, login or register
Member's Profile

I often pose the following type of excel skills assessment questions for job interviews: Tell me some things you can do in Excel that you believe the average user would not be fully aware. This often leads to more probing questions and then to the point of: Show me.

To comment, and for full access, login or register
Topic Expert
Member's Profile

Specifically addressing your question about databases...

First, your company probably has software that exports data to Excel. Or your Excel users can import CSV files. In either case, Excel users wind up with worksheets that contain data in many rows and relatively few columns.

You could ask candidates how they would go about turning that massive amount of data into useful information. Ask what Excel features and worksheet functions they might use.

(You're looking for terms like: Excel Tables, SUMIFS, SUMPRODUCT, array formulas, INDEX-MATCH, charts, range names, conditional formatting, and Power Pivot. You're also looking for "reconciliation" and "error-finding" and "alerts.")

And ask what they could do to speed up this process every period. If their answer includes "write a macro" I would challenge that opinion forcefully. Or I would say, "I REALLY don't like that idea. Can you tell me why that might be?"

(In answer to this question, you're looking for concepts like: documentation, training, management oversight, coding errors, orphan code, and pass-down problems.)

Second, your company might be using PivotTables to connect Excel to databases. If so, you could tell the candidate that, and ask specifically how she would turn PivotTable data into management insight.

At the minimum, you're looking for GETPIVOTDATA. But you really have a strong Excel person if you hear terms like dynamic range name, SUMPRODUCT, AGGREGATE, charts, Tabular PivotTables, and Power Pivot.

You might also ask how he would turn PivotTable data from several sources into one management report. Again, you're looking for a mention of the same data-returning methods.

As you pose these questions, also pay attention to what they ask you so as to clarify your questions. Do they ask about a one-time vs a periodic report? Do they ask whether dates are involved and what format the dates have when they arrive in Excel? Do they ask how clean your source data is? Do they ask about seasonality? Do they ask how your Excel reports are distributed? Do they ask if management wants interactive solutions or static reports?

That's off the top of my head. At the minimum, it should give you a pretty good idea about whether you have an above-normal Excel user on your hands.

To comment, and for full access, login or register
Topics: