Last week I was in a cafe and saw 2 professional-looking middle-aged gentlemen at a table.
One guy told the other how he’d advised someone not to take a computerized Excel exam as part of an interview. His personal experience was that those exams are just frustration.
“You get in there and find out they’ve disabled keyboard shortcuts, but if that’s how you work, then what are they really testing?”
I politely interrupted and loudly reinforced the decision to skip that test. “You’re right! Don’t take those things!”
INTERVIEWING FOR EXCEL SKILL
This question about interviewing for Excel skill has come up again; this time on a lively LinkedIn thread.
- What questions do you ask of someone who claims to be an Excel expert?
- Do Pivot Tables and VLOOKUP separate the pros from the hacks?
- Should VBA be required of someone purporting to be an Excel expert?
The originator of the LinkedIn thread explained the disappointment of a continuous stream of self-described Excel pros coming in to interview, taking the Excel exam, failing, and leaving humiliated. I found the article unfortunate and was reminded of my original blogpost on this topic:
In Interviewing, Excel Skills & Data Management Mentality the overall point was my mantra: Excel is only a tool. Use of the tool can be taught to the person who has some grasp of data stewardship.
This time around, I’ll go deeper into the problem of programmed Excel testing, and offer alternatives.
2 PROBLEMS WITH EXCEL TESTING
1. Excel is too doggone big
Excel users develop skill in areas where they use Excel on a regular basis. I think it’s unfair to invite someone in and embarrass them by testing them in areas that are not likely to be where they know they shine–and especially when the computerized test disables certain features.
From the employer’s perspective, maybe they don’t need what the candidate is skilled at. But with this canned testing, the employer doesn’t get to see the size of the gap between the candidate’s skill and the employer’s need.
2. Contexts are too diverse and nuanced
Each business has their own data management needs; they have their own processes and business rules.
Let’s say we have 2 analysts, Al and Marcey and they work at 2 different companies. Both are skilled at building Excel dashboards and are good at what they do. In their previous jobs:
- Al had to clean his own data before building dashboards, and incorporated a little VBA.
- Marcey didn’t have to clean her own data and doesn’t know VBA, but she constantly had to build different kinds of dashboards.
Who’s the expert?
Who’s more valuable?
What should Al and Marcey be asked during an interview?
Answer: IT DEPENDS! What does the employer need?
THE REAL PROBLEM WITH COMPUTERIZED EXCEL TESTING
If we rephrase Bruce Lee’s wisdom to fit this blogpost, we’d say:
“It’s like using Excel to process payroll.
If you focus too much on Excel,
you’re gonna eff up people’s money.”
People, Processes and Tools are the components necessary for heavenly glory. The right person manages the processes as well as the tools.
ALTERNATIVES TO EXCEL TESTING
Ask Candidates To Present An Excel Portfolio Of Past Work
- Does their work look thorough?
- Can they explain what they did or do you detect mindless paint-by -numbers construction?
- Can you see where the candidate’s strengths and weaknesses are?
- Is there evidence of focusing on a pimped-out spreadsheet vs. getting a job done?
- Are they going to accept criticism of their 3-D 20-slice pie chart or are they going to defend it?
- If someone had to troubleshoot their work, how difficult would it be to follow the thought process and organization?
- How long is the bridge between the portfolio and the employer’s needs?
Notice that I didn’t say anything about VLOOKUP or nested functions. Right now, let’s focus on the heavenly glory and the moon, not the finger.
Have Candidates Look At Your Real Data And Real Needs
Sometimes the person doing the interviewing doesn’t know enough about Excel assess a purported solution. Therefore, what such a person should listen for is how the candidate asks questions.
While looking at your Excel workbooks
- Is the candidate asking about the processes around the workbooks?
- Are questions and suggestions being presented with the business objectives ever-present?
- Are they understanding enough of the formulas such that functions they’ve never seen before aren’t completely alien?
Look For Passion
As Rick Grantham and I agree on ExcelTV: real analysts light up when they talk about data! Do your candidates light up?
A passionate analyst will ensure that formulas accurately reflect business rules, and test for unusual scenarios. A passionate analyst will accept criticism of their garish 3-D 20-slice pie chart.
CONCLUSION
People, Processes, Tools and Passion. Those are the main ingredients in a responsible analyst role. Those will get you to the heavenly glory of turning data into intelligence. The right person who has passion and thinks about processes can be taught to use the tools.
Love it, Oz. Brilliant redux. At the back of my CV, I have four full-page screenshots of different dashboards/models/applications I’ve built over the years, as well as a paragraph or two explaining each of them. Those screenshots put me head and shoulders above other candidates. Show, don’t tell. Plus I often show up to interviews with my laptop, in case they want to see any of them in action. And often I email through documentation for one of the projects, so they can see that I can not only build complicated things, but explain them to less sophisticated Excel users.
And I too have left an excel test demoralized, because it was not clear to me what the actual questions were asking.
Also, I believe that while a CV is the place to document your skills,
a cover letter is the place to document your passion. Here’s an excerpt
from mine:
<Blockquote>
Probably the two things than run deepest
through my CV are commercial analysis and Excel/VBA Development, and I’m
particularly engaged when I can combine aspects of the two.
In
particular I enjoy developing tools that primarily use Excel as a front-end for
graphical data presentation/dashboards, business intelligence, analysis, and
modelling…although I often bolt it to a back-end database with some SQL. I also do quite a bit of query building in SQL
– particulary around modelling of energy tariffs and lines charges for large
corporate electricity users. (Basically I build quite complex queries that take
a site’s half hourly usage data and reconstructs both their lines charges and
their energy charges, so that I can compare the cost to other line
charge/energy pricing configurations). But while I play around with SQL quite a bit,
I’m no DBA.
To give you a good idea of the kind of work I’ve
been doing recently, I attach a couple of recent outputs that should give you a
good idea of the quality and scope of my recent work,
and also help you work out whether I’d be a good resource given your specific
needs.
The first is a PowerPoint-based user-guide I put
together in support of a project costing model I just constructed for IRD’s
Busine
And the second is a PowerPoint-based training
resource based around increasing organisational
awareness of efficiency opportunities when it comes to use of Excel and
automation of Excel-based business processes. On the training front, outside of work hours I
also regularly contribute technical and training articles on excel sites
that get some pretty respectable traffic. For instance, http://dailydoseofexcel.com/archives/2013/11/14/filtering-pivots-based-on-external-ranges/
(a website focused at fairly advanced developers) and http://chandoo.org/wp/2013/11/18/using-personal-macro-workbook/
(a website focused at intermediate business users looking for efficiency tips). </blockquote>
You hit the nail on the head. This is a subject that has been difficult for me to articulate. Thank you for bringing clarity to the point that value is best placed on an analysts understanding of analitical principles. Not just how well she utilizes a particular application.
This has been debated for a very, very long time (as with most things Excel related), and honestly I don’t profess to follow LI discussions very much (don’t much like the medium). As someone who has been on both ends of this particular spectrum, I agree standardized [Excel] testing won’t give you the entire picture, and unto itself can ultimately be misleading. That being said there is definitely a place for it. My philosophy is everything in moderation. I think a test should be part standardized, part practical, part oral, and part examination (e.g. straight-up Q&A).
Do I think everyone needs to know VBA? The only viable answer is: it depends. Depends what the job is, what is required, best practices/skills to be employed, etc. If it’s called for, then yes, otherwise not necessarily. I’ve seen brilliant consultants/developers create some amazing solutions without a single stitch of VBA. I’ve seen some people have some fairly ridiculous VBA-based interview questions as well.
While the alternatives you mention are really good ideas, they’re indeed just a different kind of testing, more of a practical exercise, but one that will give you insight on a person. The tests I *really* hate are the MOS certification tests, which basically test whether you know the one way to find a resource through the UI, which is basically ridiculous and boils down to rote memorization, which I’m confident I could teach a monkey to do (pretty sure I have).
Most employers, when they need someone with “Excel skills”, it’s most likely they have no idea what they need, but know what they want. It’s probably made it’s way onto the job application because they use Excel heavily, or have had some people in the past who are moderate to very good in Excel. This doesn’t always translate to needing someone who is good in Excel. For example, if I’m a financial modeler, and I’m applying for a job to build templates, since I can do all of my work extremely fast in Excel, I’ll probably rate myself an 8 or 9 out of 10 in Excel and say, “yeah, I’m an Excel expert.” However, if I get the job and I’m building calendar templates all day, my skills haven’t really gone to use, and now I’m a 2/10 in this arena. Point being it’s hard to find a really well-rounded and true Excel expert.
When someone purports themselves as an Excel expert my red flags go up. I consider myself a realist, my wife would consider me a pessimist, but I believe we don’t know as much as we think we do. Meaning, yeah, I know a lot about Excel, but there’s so much I *don’t* know about it. I’ve done amazing things, but others will keep doing amazing things and make my jaw drop well beyond when I stop using the application. We learn something new every day, and everyone is teachable (who is willing). Knowing what someone can do currently in the application isn’t nearly as important as what you can help/guide them do.
In the end I don’t think there’s one “perfect” way of doing things [testing], but I can safely say that a well-rounded mixture of methods will always give you the best idea of what your candidates are capable of.
Jeff Weir that’s a smart solution. I’d never thought to just go ahead and include work samples. Very smart. It’s a proactive approach that can cut through to whatever needs to be done. Show, don’t tell, and don’t take those darned automated exams. LOL!
I took one of those tests and felt both embarrassed and enraged. As you describe, sometimes the actual question wasn’t clear. Also, the test seemed to be rooted in a particular work style. The experience was more like a dirty prank than a forthright skills assessment.
Your response to the situation is really good.
Zack Barresse Thanks for the reply and I appreciate the perspective from not only the employer side but also deeper insight into the reality that a lot of companies aren’t clear on what they need.
Sure, I did only suggest another form of testing. Realizing that some kind of testing needs to happen, I figured a more pragmatic approach.
And this whole expert-thing and guru-thing … LOL! That kind of talk needs to go away.
In a situation where everyone should have learned the same things; e.g., a college class, then, a one-size-fits-all test makes sense. But with a professional skill, I don’t see how a standardized test can help. At least, in my experience, when it’s programmed to nudge a person in certain directions and take specific features away, those exams are like the uncle who comes over and invites the kids to pull his finger.
How have the standardized tests been helpful in your experience?
OzData Certainly not any of the Certiport tests! I hate those, and I’m sure anyone else who has taken one of them can attest, they are severely limited. I’ve written many tests (mostly in the fire service I’ll admit), and I know it’s very difficult to write *good* test questions as well as answers. IMO they’re limited by their very nature, and that’s how they should be used – in a limited fashion. I think we can all agree any results are certainly non all-inclusive, but should be for general knowledge topics. Advanced topics could be problematic, although are doable.
For example:
Which one of the following will make the row of cell A1 absolute: a) $A1, b) $A$1, c) A1, d) A$2
I think we can reasonably deduce the number of people get this right are broken into two major categories: those who got it right, and those who guessed it right (I mean, c’mon, it’s a 25% chance, 33% if you’re even reading the entire question and answers). While those who got it wrong most likely do not understand absolute versus relative referencing. This is not only measurable but informative, and that’s the point of a test question. It’s not an open-ended question, which makes it suitable for a standardized test.
An example of a question which wouldn’t be so good on a test is: Which is faster, VLOOKUP or INDEX/MATCH? 😉
Glad you discussed the excel tests. I had debated takng one for years. My impression from study guides is that they focus on cosmetics rather than structure & math.
I’d be interested in seeing an article on creating an excel portfolio. Particularly, presenting my work without sharing confidential information.
ernie johnson thanks for the comments. Excel testing is a peculiar thing.
One way of sharing your skill without sharing sensitive info is to use dummy data. I’ve done a couple of blogposts on that and they include video tutorials.
https://datascopic.net/generate-data/
https://datascopic.net/data-generator-2/
I also maintain a spreadsheet that has thousands of random names, fake city names, etc.