Excel Developer Interview Questions

We are looking for some Excel/VBA devs. They need to be good, or better.

I’m not a big fan of silly questions that have no bearing in the real world, but equally, I need in 20 minutes or less, to get a good sense of whether the candidate is going to cope in the harsh reality of financial services spreadsheeting. (And over the phone).

Here is what I have come up with so far:

Whats the keyboard short cut to open the VBAIDE?.
Name 3 new functions in Excel 2007
Explain scope
How would you translate one set of product codes into another (assume 1 to 1 matching)
What is the SQL to return a list of products where the maturity date is after June 2010.
In excel what is a volatile function? Name 2?
In VBA what does option explicit do for you, and how do you set it.
What size of spreadsheets have you worked with?
What is the main body interested in spreadsheet quality?
Name a couple of good on-line resources for Excel VBA
Describe decent code
Talk about combining Excel and C#
Describe the best and worst spreadsheets you have worked with

Have you got any favourite questions?

I’m not thinking of ‘pass or fail’ type questions, more ones that will allow us to get a good sense of people strengths and weaknesses. Just technical, someone else will be covering the other stuff.

cheers

simon

Advertisements

55 Responses to “Excel Developer Interview Questions”

  1. martin rushton Says:

    “What do you think of merged cells?”

  2. Mel Glass Says:

    Hi Simon – I want to talk to you….can you email me on mel.glass@easasoftware.com

  3. Simon Says:

    nice one Martin, that’s a straight pass or fail right there.
    Mel I will drop you a line, have you got some good but secret interview questions?

  4. mikewoodhouse Says:

    Option Explicit is clearly necessary and a wrong answer’s a show-stopper. Assuming they’re still around, an understanding of Option Private Module is also good to have.

    I like to see that they understand that “named ranges” are more than that – for example, can they describe a way to update charts automatically when, say, a time series is extended.

    Do they know any of the following names: Walkenbach, Peltier, Chandoo, Wallentin, Kusleika, etc? (or in other words, are they active, interested and engaged with the wider community?)

    How about the three types of error-trapping and what the differences are?

    Personally I’d also like to see evidence that they understand and use the basics of OO as far as they apply within VB, but that may be just me.

  5. JP Says:

    “What is the main body interested in spreadsheet quality?”

    “Talk about combining Excel and C#”

    What do these questions have to do with Excel/VBA?

    • Syswizard Says:

      Only in Visual Studio 2010 and Dotnet 4.0 has C# been enhanced to support NAMED PARAMETERS. Until then, writing Excel Add-ins in C# has been very difficult…no intellisense was available.

  6. dougaj4 Says:

    Best serious question:

    Describe how you would transfer a large array of numbers (say 500 x 500 cells) to VBA, do some processing on the numbers, and write the results back to the spreadsheet.

    Best “tricky” question:

    In XL2007, how do you copy a selected range to the clipboard as an image that will display “as printed”?

    Mike – you forgot that Murphy fella.

  7. A Dashing Blade Says:

    1) What’s the difference between using Workbook_Open and Auto_Open to kick stuff off?
    2) What can cause a #NAME! error in a worksheet cell.
    3) Arrays, collections and dictionarys as data structures. Compare and contrast
    4) Not a trick question! What is displayed in the message box ie what is the value of intCounter after it has left the For . . . Next loop?

    Sub PrintInAMessageBox()

    Dim intCounter as Integer

    For intCounter = 1 to 10

    ‘do something

    Next

    Msgbox(intCounter)

    End Sub

    Still gobsmacked by how many people get the last one wrong!

    One absolute no no on a cv imo is the line “I am regarded as the excel expert in my current role”. This ensures cv goes in thre bin.

  8. A Dashing Blade Says:

    Oh yeah, and if they can’t answer this one without having to pause for thought then put the phone down . . .

    5) What is the syntax of a VLOOKUP() function?

  9. sam Says:

    “Name 3 new functions in Excel 2007”

    Thats the tragedy….there are just about 3 …..SUMIFS/COUNTIFS/IFERROR

    • Adam Vero Says:

      @sam: AVERAGEIF (and …IFS) too! ()

      What *by law* must the text of your first MSGBOX function be?
      What *by law* must the icon on your first macro button be?

      (according to Smurfy’s Laws, of course ;-) )

      Possible Q: How do you force a re-evaluation of the calculation dependency tree (and a re-calc)?

    • teyly Says:

      Hmm, count again. … and what about IFERROR()?

      That should actually be a question: In XL 2007, how can you avoid IF(ISNA(Vlookup(something)),””,Vlookup(something))?
      Some may come up with IF(ISNA(MATCH(something)),””,Vlookup(something)), which is nice, but the correct answer should be IFERROR(Vlookup(something),””)

      • teyly Says:

        Ughh, Simon, can you turn off the “justified” formatting? I feel stretched.

      • Adam Vero Says:

        Sam mentioned IFERROR.

        Even better question:
        when / why should you NOT use IFERROR as a replacement for an ugly IF(ISNA(… ?

        (answers relating to models with possible errors in the source data of the VLOOKUP as distinct from lookup values not being found, and whether or not you want to trap them differently.
        Obviously if you don’t care what the error is, or guarantee 100% the source has no errors itself because it is only flat data, not resulting from formulas, then IFERROR is very useful. We really need IFNA(x,y) as a condensed function too, IMHO)

    • Bob Phillips Says:

      “Name 3 new functions in Excel 2007″

      … and then tell us why you shouldn’t use them!

  10. Mike Staunton Says:

    I fear you might have snookered yourself – the first question should ask if they read Smurf; if no, hang up; if yes, you’ll have to prepare a new set of questions different from those suggested already here

  11. A Dashing Blade Says:

    Surprised no-one’s mentioned anything about getting real-time data into a spreaddy (Reuters/Bloomberg). Have they programmed with either of these API’s, eg difference between asynchronous and synchronous BBG calls.

  12. Ian S Says:

    Hmm. There are some very articulate questions accumulating here. But, dare I whisper it, most technically oriented people aren’t all that articulate — except when they’re in front of their favourite workbench.

    So I’d suggest asking your questions in front of a spreadsheet, and letting the candidate’s hands operate the keyboard while they *show* you their answers.

    After all, isn’t that what you’ll be expecting them to do if you hire them?

    And the way they navigate around the screen may tell you as much as, perhaps more than, the way they navigate natural language in answering you.

  13. Rob Says:

    In the past, I’ve asked candidates what technical questions they would ask if they were interviewing. Gives you quite an insight. Candidates with a rather over-inflated sense of their own abilities and knowledge will usually come up with a convoluted ‘trick’ question, while the most able candidates are often interested in asking very specific questions about aspects of the ‘basics’ – a bit like the commentors here, really!

  14. Simon Says:

    Thanks, all good
    mike, yes, OO would be good, hence the C#, knowing how crap VBA classes are would be a good start.

    JP, what has spreadsheet quality got to do with Excel development?? (you are right – nothing, but it should!)
    I would expect Excel devs at the level we are looking would be comfortable with other languages apart from VBA.

    Dashing – all good, market data especially, and we have some UDF fun here at the mo (I am converting them slowly to xlls). ‘Excel expert’ too true!

    Sam and co, yeah asking for 5 new generally useful functions would be too cruel. (CUBE is a little wasted as we aren’t on AS)

    mike S – yes will watch out for 100% correct!

    Ian, I totally agree but its not that easy on a phone interview. I might take a lapper to the face to face ones.

    Rob, I think thats an excellent idea, and I like your interpretation.

  15. Syswizard Says:

    Just review their code.
    1) If they aren’t naming their variables properly and consistently,
    they’re no good.
    2) if they employe redundant code instead of functions or procs….
    they’re no good.
    3) if their GUI forms are not clean and functional and intuitive…
    they’re no good.
    Forget all of the rest.
    3 new functions in Excel 2007…..that’s joke, right ?

  16. Steve Says:

    Passing by ref and by val
    Whats really going on here?

    p.s. no point in quoting something you read (probably more than 100 times in 20 different books, none of them explain it properly)

    After 10 years of VBA and 3 years of C# I know – and I won’t be telling any of you guys, why should I.

    99% of guys who write code just don’t know

    The answer is so simple really, but I won’t say cause everyone will just say oh I knew that cause I’m so smart (not)

    this applies to C#, VBA, whatever

    The only question I ever need to ask and the only question I never help anyone with (you guys don’t really need my help anyway – cause your all so clever, right?)

    • Freddy Says:

      Some sort of mucky attempt to replicate putting variables or pointers to variables on the stack ? (Though presumably they’re all just handles to some sort of object table somewhere.)

      “you guys don’t really need my help anyway – cause your all so clever, right?”

      Well, I’m clever enough to know how to spell “you’re” …

  17. Steve Says:

    p.s. a tip, start by thinking at the electronic level, and then work your way up from there.

    lol

  18. sam Says:

    My fav question is to check a persons “Database IQ”

    Q : Lets assume you have data (sales/manhours…anything) of 4 people working in 4 regions. How would you store it in Excel

    A : 95% of the people do the following
    N S E W
    a 10 20 30 ..
    b ………….
    c …………..
    d ………….

    I then tell them that the right way is to store it in a Database structure…

    Name Region Amt(metric)
    a N 10
    a S 20

    • Adam Vero Says:

      I like this, you are absolutely right it gives a flavour of how they approach real-world data with foresight to what they might want to do to it (filter, sort, pivot etc).
      Follow up ask them to take whatever method they have used and describe three ways to produce a sum of sales for a and c in regions N and E combined.
      These are probably better kept for the second round face to face stuff, though, so you can watch how they do it.

  19. Simon Says:

    SUMIFS has revolutionised my life

  20. Simon Says:

    “What do you think of shared workbooks?”

  21. Charles Williams Says:

    What are the advantages and disadvantages of Linked workbooks?

    Followed by:
    And what methods would you use to get rid of external Links?

  22. Patrick O'Beirne Says:

    What is an alternative formula to VLOOKUP

    How do you create a dynamic range name

    Show them some formulas drawn from the kind of spreadsheets you already have in your organisation and ask what they do.

    Describe two kinds of controls on spreadsheet use

    Describe how to implement input control and data integrity checking inside a spreadsheet ; how can they be defeated; and how would you find out if they had been overridden?

  23. Simon Says:

    “Show them some formulas drawn from the kind of spreadsheets you already have in your organisation and ask what they do.”

    *Bloody ‘ell!!* – keep it real – no one know what most of that crap does!!
    :-)

    I like the idea of a bit more focus on control and data integrity, thanks Patrick.
    Links – Good idea Charles ta

  24. Biggus Dickus Says:

    This is a BIG topic to say the least.

    These are all good questions but I think ithe idea of sitting the person down in front of Excel and seeing how they analyze a big, complex model is the biggest “tell”. Where do they go in the file? What do they look at? Do they dig into every corner of the Workbook inckuding Macros.

    Once done you then get them t explain what’s going on in the file, how it operates and maybe even things they would do differently (although that may be tough if they’re trying to kiss your a** for a job :-)).

    The fact is that in the end once you hire this person you have to either merge your styles together or train them on the way YOU do things. There are many ways to do spreadsheets (which is one of its strengths AND weaknesses) and if someone works for you or works with you on a project for a client you’d better design you spreadsheets the same way. Have a set of “Best Practices”.

    It’s not a simply asking them questions I’m afraid.

    Dick
    p.s. Does this mean you’re looking for an assistant in Geneve? Make me an offer I can’t refuse and I’ll be there – volcano permitting of course :-)

    • Syswizard Says:

      re : “There are many ways to do spreadsheets (which is one of its strengths AND weaknesses”
      Of course…out of 5 ways, 3 are terrible, 1 is OK, and 1 is best.
      Someone without the experience is likely to make a bad choice.

  25. Gordon Says:

    Good questions, but if you can provide a sample file that has some data and a brief description of the desired end state in advance of the interview, then 5-10 minutes reviewing the candidate’s method and code with them would be a very good insight into both their technical merit, and their ability to communicate and work as part of a team (important and often overlooked).

    The task doesn’t have to be that difficult, just something that has a wide variety of ways in which it could be accomplished, and let’s face it that means almost anything in Excel.

  26. Simon Says:

    This MacBook is great for typing on btw.
    The Swiss keyboard (kezboard!) takes a bit of getting used to, but its easy to write Genève.
    I’d say coding C++ will be hard as there is no sign of curly brackets. (don’t they use them in objective C?)

  27. Simon Says:

    {} alt 8&9 not so bad

  28. John Drummond Says:

    25 years experience in financial spreadsheets and I get half marks.

    Hmmmm…

    Anyone who knew how to combine C# and Excel is just too frightening in my book!

    Can’t see anything financial in those questions, doesn’t that matter?

    All the best Simon

    • Simon Says:

      Sorry John your post got spam binned,
      I just downloaded bf the other day to add to my list of things I want to review.

      I’m just doing the tech review, someone else is dealing with the business side of things.

      The only thing more frightening than knowing how to combine C# and Excel, is those that think they do, but don’t!

  29. Doug Glancy Says:

    Array formulas and/or How to use SumProduct to count combinations of items in a list. For addin coding, I think application-level events are important to understand.

  30. Adam Vero Says:

    I like all the input going into this thread (and the cousins of it appearing in other places).

    I have used similar techniques when trying to find techies / sysadmin types. The idea of a bunch of ‘triage’ questions which gives you a sense of someone’s overall level of experience is useful, then you keep the really specific stuff for if they make it the next stage (which might be the first face to face or a second round with different people). Writing code (or command line stuff for sysadmins) on a whiteboard without the benefit of auto-complete, tooltips and other IDE “value-add” separates the men from the boys.

    Questions about quality, about new features in 2007, about C# are great to give you that flavour without necessarily being a pass/fail. If someone gets loads of stuff right but doesn’t know a SPRIG from a twig from a branch of code, it’s not a showstopper if they hit lots of other marks. I think the only absolute pass/fail question on Simon’s original list is the first one.

    Equivalent sysadmin question to the one about quality might be “what is an OEM licence and what might the implications be when you swap or retire machines?”. Maybe they don’t know, or don’t care. The guy that can tell me why it matters shows he cares about legal compliance and topics broader than purely technical. It’s not a pass fail, but I like to know if someone is thinking about the broader picture.

    How would you set up the share and NTFS permissions for a folder for the HR department of three people? Like many spreadsheet models, this has several reasonable answers, one or two “best practice” ones. Follow up at next stage interview might be to get them to actually do this while you watch, then throw a spanner in the works because one of the accounts team needs access to a subfolder but nothing else. See how they deal with this.

  31. Harlan Grove Says:

    Late to the party:

    When are Change or Calculate event handlers useful, when not? What should they do when used?

    What can and can’t be done with validation rules? What’s the biggest hole in validation?

    In which situations would table-driven programming be useful in spreadsheet development? Meaning used directly by macros, not by cell formulas.

    And my pet peeve: how many volatile function calls are acceptable? When does it make more sense to use [V|H]LOOKUP, INDEX, OFFSET, INDIRECT?

    —-

    With respect to a point Adam Vero made in a reply nested so far in I couldn’t reply directly to it, it ain’t just IFERROR that traps too much. ISNA is more useful than ISERR or ISERROR, but it’s not an option for trapping result errors from FIND and SEARCH, which return #VALUE! rather than #N/A when they don’t find substrings. What’s needed is the ability to trap specific errors while allowing all others to propagate. Other example: trapping only #DIV/0! returned by AVERAGE when its arguments could refer to empty ranges. What’s needed is a way of BOTH trapping only the #DIV/0! error value AND checking whether any of AVERAGE’s arguments contain #DIV/0! I have my own methods for doing this, but it’d be nice if it were easier/less cumbersome.

  32. ross Says:

    I dont wont to work for you,

    you ask too many questions!

  33. Simon Says:

    Ross
    I was just discussing a role with an agency – they reckon the client has 3 (THREE!!) rounds of interviews/tech evals!

    So far I have only needed 2 or 3 questions from the list to gauge candidates.

  34. Syswizard Says:

    re: “I was just discussing a role with an agency – they reckon the client has 3 (THREE!!) rounds of interviews/tech evals!”

    Forget agencies…they’re worthless. They make the money and you do the work.
    They have no other purpose other than extracting money for the least effort.

  35. interview questions Says:

    Hello friends,see the sample interview questions for all type of jobs here in below link:
    interview questions

  36. Al Gill Says:

    Darn – very late to this party but did anybody think to ask: “Do you have about your person a USB stick of useful XL stuff, code/tatt that you’ve accumulated, random utils, cunning charting stuff from that guy Peltier, spreadsheets from projects you’ve previously worked on etc.?” Any thoughts on this as a question? It only lets you down with the XL equivalent of script kiddies but they should be obvious from one other question (eg option explicit) right?

    As to how you find and remove links, my answer would be “go and read the ****** instructions for the links util. I wrote for you last time I was here” – not sure how that would score though.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: