Excel Skills Test With Excel Interview Questions Needed

Bob Stenz's Profile

Excel Interview Questions

Excel 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 an Excel skills test) Excel and data management skills in an interview? Do you have an Excel skills test and/or Excel interview questions?

Answers

I wish more hiring managers would do this (and also continue to encourage their staff to become more proficiency, including how to use ALL the features in the ERP system). You can address this a few ways:
1. Bring your IT colleague into the interview OR engage an Excel guru;let him/her ask Excel interview questions and provide some feedback to you afterwards
2. Have a PC/laptop with sample data in the interview room and ask the recruit to perform some Excel skills tests -engage an Excel guru to help frame some Excel interview questions and tests.
3. Ahead of the interview, ask them to prepare some examples of their work in this area and bring it along on a flash drive. But, make sure the data is not confidential to their previous employer.
4. Remember to look at both experience and technique-someone may be a great data manipulator, but they have poor discpline/best practices when it comes to being entrusted with raw data and being relied upon to produce accurate information (e.g. version control, risky short cuts, etc).
5. Review your plans with HR/legal for any concerns.

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

Bov, 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
Member's Profile

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.

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

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

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

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.

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

Thanks all for the very helpful commentary!

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

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

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

In addition to the great suggestions above, you always have the option of open ended questions if real examples are not available in an Excel skills test. 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?"

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

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

To comment, and for full access, login or register

This debate has been ongoing since 1992, at least.

1. Accountants coming from the Big 4 do not have the Excel skills necessary to drive process change and improvement in a large accounting department. They like to think they do, but they have never worked with Big Data. Until they do, there is nothing they will add to efficiency.

2. Big Data manipulation is solely about being able to configure a report in the format needed for Excel, a datasheet with contiguous headers. Sorry for a moment I lapsed into tech talk. Most accountants do not know what a datasheet is and they don't know how to stack data to achieve the format needed for pivot tables and other tabular construction.

3. Macros are completely unnecessary, making a focus on VBA skills unnecessary as a basic skill. Why? See item 4.

4. The core misunderstanding about any spreadsheet is that it must be developed into an application in order for it to have value. Untrue. This is a belief held by those who have built a career in IT or as a programmer. There is value in separating the data from the structure, yes, but in Accounting, its more important that the lower level staff be able to manage and maintain the model with an intermediate grasp of the tool. Unless the manager wants to be tied to the reconciliations for the rest of their career.

5. There are some models and concepts that are best handled by the advanced functionality of excel like forecasts, budgets, but data manipulation for the purpose of reconciliation and reporting and analysis in general is not benefited by the more complex functions. Again its about what the staff is able to maintain and manage without letting things get degraded.

6. Where there are large data extractions and massive summaries necessary, the first concern of management should be "Is this really necessary to configure a complex model or can we do this a much simpler way?" By and large, those who prefer total complexity usually want kudos for strutting their stuff rather than getting things done in common sense ways. These people will never prefer a simpler solution, but time and time again I rebuild their methodology by adding in some common sense and it usually helps the accountants manage their process more easily.

7. Key to making excel work for you: Make sure your transaction codes in the systems are applied consistently to the correct types of transactions. What?! Yes this is one one the major issues with database data - garbage in garbage out. If its not working in your system, its not going to be any better when you put it in Excel.

8. Know that you will need to train your newer staff on the types of Excel models you use because no company EVER uses the same set of formulas or data patterns.

9. You can use a test during the interview if you have a specific specific specific skill that the candidate must have. Otherwise don't waste your time, just require an Excel MCAS certification. Note that the MCAS certification does not test the Excel skills that are actually needed by accountants in Big Data environments but it does cover a wide range of newer features.

10. How you use Excel in your accounting department is largely based on how many non-integrated systems you have to reconcile, how many special reports you need to produce that your IT staff does not have the skills to produce using SQL, and how technologically proficient the Accounting Manager or Controller is. Think of it like constructing a building, if the construction supervisor doesn't know what the tools do and doesn't know what tools to use on the job, the workers are not likely to have the tools they need to get the job done. Think about that when the Accounting Manager's job is to train their department. If there is a lot of complaining that the staff just don't have the skills, even though they all have accounting degrees and a work history, the problem may lie with your Supervisory function.

Technology is a set of tools. There is no tool that is beyond the comprehension of a strategist. Excel is no different.

However the leap you will need to make to go from 2 dimensions to 3 dimensions is how fast you learn to ride the One Trick Pony called The 3-Tiered Model.

After 20 years I rarely meet anyone who knows how to properly construct a 3 tiered model in Excel. This is is the problem throughout all of accounting today when it comes to knowing how to use the tool called Excel.

I'm a One Trick Pony and I only do one trick, and time and time again it creates efficiency and removes 100's of hours from accounting departments without collabortive IT resources, which is most of them.

Excel is a workbench, but its rarely thought of that way.

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

Valerie,

When does the Excel Spreadsheet really become the province of the IT Department and more sophisticated database programming skill sets?

In other words, why download (even in a 3 tier system, if we're talking about the same paradigm) and manipulate even mid-size data, when report generators in the base database programs can do a better job?

Wayne

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

I think this is a great question. I remember being in an interview for accounting manager and the interviewer asked me to give her a journal entry for buying a new asset. Seldom do we ever ask basic technical questions. I usually assume technical expertise from the resume and focus on personality fit and behavioral questions in interviews. We have all seen a new hire who was over their head. So in addition to the normal interview, I always follow the example above and ask the interviewee, "What's the JE for purchasing this box of kleenex?" I can definitely see asking some excel-based questions.

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

Usually, data management is more about understanding tables (concept not feature), relationships, and transforming data.

I would suggest you try to have an in depth discussion with them about the last time they worked with a large volume of data.

Do they know the common tables for their ERP source system?
How do they extract the data?
How do they use tools (Access, Excel, or other) to transform the data into useful information?
How do they test their product to know about accuracy and completeness?

Your best candidates will run with this before you get too far and start asking you about your systems, data, tools, and challenges.

Best luck hiring someone great!!

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

There is a specialty in IT called data warehousing. The programming side of this specialty knows how to work with enormous data sets.

The key is a) system analysis (making sure they know what you want), b) testing (which is as much their responsibility as yours - suggestion, given them sample datasets where you know the answers to your questions) and c) data validation/purity, which again is both parties responsibility.

Remember that old adage, Garbage in is Garbage out!

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

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

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

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.

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

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.

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

"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!

To comment, and for full access, login or register