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

Why don't you create a test for them to perform? Develop 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.

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.

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

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....

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.

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.

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.

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.

Ask the candidate how they reconcile an accounting sub-ledger (AP, AR, FA, INV, Projects) to the General Ledger. They will have to understand the inner workings of the accounting module to tie it to the GL. The only way to get the data out is a download to Excel. The format described will tell you if they understand the guts of reconciling and the data manipulation concepts required.

I look for an encapsulated and consumable response of about 3-5 minutes that gives me a visual idea of the work they know how to do, I don't look for all the technical response details that Charlie mentioned above because if I can't determine that the candidate functions at a level of overall understanding of the process I don't care what excel functions and tools they know how to use.

Ask them how they build a system query. This will tell you a lot, and keep them talking and allow you to probe deeper. Or they will not know.

Members in this forum are assumed here for the learning opportunities. I'm disappointed to find most Finance and Accounting organizations outside of public accounting do not include portals for assessments and certifications to keep staff skills up-to-date.

Verbal assessments for Excel & Finance/BI systems seem limited compared to a actual scored assessment, paper-based or computer application (If you can't afford online app, used screenshots and formulate questions that require selection of correct sequence or missing/next step info. Heavy keyboard users might need alternate format, and will perform differently with drop menus.)

For DB assessment, walking through a scenario, screenshots, etc., are some ways to do a conversational assessment. Asking for a sample and having candidate describe their process, techniques could be informative.

An assessment/certification tool that also provides existing staff continuous improvement is a great investment that will provide enhancement/value to overall department/organization performance.

I'm an experienced Excel developer and VBA programmer. I've worked in Big 4 public accounting as an auditor and I've been building sophisticated custom Excel/Access applications for management reporting / FP&A departments for 15 years using Excel, Access, VBA, Sql, that work with enterprise systems such as PeopleSoft or Sybase.

I'm happily surprised to see that Charley Kyd and I read the same newsletters!. Charlie is a well respected pioneer of Excel Dashboards whose ideas have been adopted by Microsoft in the last few releases of Excel. I was creating sparklines in 2003 based on Charlie's techniques...Excel now has incorporated Sparklines into the application itself. So yes, what Charlie said above, totally agree. If you hear Dynamic Range Name or Reverse Pivot Table Method...that's all I'd need to hear.

But that said, the title of this thread caught my attention because I've long said the same thing...most managers can't properly assess a candidate's technical skills in an interview.

In my experience, world class organizations don't test candidates with a spreadsheet or even a written exam...because the better way is to simply have them interview with someone who does have a high level of skill...either a seasoned analyst or even inviting someone from IT to join the interview.

The testing approach is wrong-headed because the test itself doesn't tell you all that much. "It takes one to know one" applies here. Without a license or certification to go by, only someone who has all or some of the skills your looking for is going to recognize it in a candidate.

I once had two managers at a top-tier investment bank give me a sheet of paper with a short problem on it and they asked me to write a basic Sql statement to solve the problem, which was fine, but it was during and part of the interview, not a test to be handed in.

There has to be a meeting of the minds if your want to properly assess a candidate's skill. With more technical candidates it gets easier because they'll establish right on their resume their experience and abilities.

One difficulty is that some people who go beyond the basics spend some time in what I'll call an intermediate level of skill where they learn very complex things by reading books on Excel, and they'll impress a manager, but people with this level of experience often haven't learned to simplify, and their work tends to be more complicated than it needs to be and no one else understands it.

You don't really learn how to simplify studying Excel, but you do learn this studying programming languages and databases, so if your candidate has a reasonable background in these areas, then you've probably found someone with an accomplished skill set