Q&A Forum

Excel Skills Test With Excel Interview Questions Needed

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

Editor's Discussion Summary:

Get clear on what the successful applicant needs to know about Excel

Pose questions that are targeted to an understanding of the Excel skills involved

Be wary of VBA enthusiasts

Don't confuse Excel skills with Big Data skills

Don't look to your IT department for Excel insight

Ask the candidate to bring work examples to discuss

There is wide disagreement as to what the real Excel Best Practices are

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 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 tests -engage an Excel guru to help frame some 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.

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

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

Anonymous

(Agent, JKS Solutions, Inc.)
| Oct 14, 2012

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.

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?

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.

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.

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.

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.

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:

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.

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.

1) What is Microsoft Excel? ...
2) What is ribbon? ...
3) Explain Spreadsheet and its Basics. ...
4) How many data formats are available in Excel? ...
5) Specify the order of operations used for evaluating formulas in Excel. ...
6) How can you wrap the text within a cell? ...
7) Explain Macro in MS-Excel.

Business Exchange

Contribute to Community

If you’re interested in learning more about contributing to your Proformative community, we have many ways for you to get involved. Please email content@proformative.com to learn more about becoming a speaker or contributing to the blogs/Q&A Forum.