Test Your Excel, VBA, and SQL Skills

I recently saw a resume with a bunch of VBA and SQL skills on it. I’ve never seen anything like it. I gave up trying to find someone with the right combination of skills a long time ago. Someone to whom I could offload some of my projects. But this had promise.

I couldn’t get a great read on his skills just from the interview, so I, with the help of a really smart coworker, devised a test. We set up a PC, gave him some files, gave him internet access, and watched to see if he could complete the tasks. We’ve never devised a test before, so I had some doubt about whether we did as good a job as we thought. That’s where you come in. If you have Excel, VBA, and SQL skills, I’d like to know how easy or hard you find these questions.

Some of the necessary files are provided below. If you don’t have SQL Server and Adventure works, you can just describe how you would do it using your database of choice. Note that we installed SQL Server Express and Adventureworks on the test PC, so he had all the tools necessary to complete the tasks.

Leave your answers to these questions, your thoughts about these questions, or both in the comments.

  1. Using the Adventureworks2012 database on the local SQL Express, create a report showing Salesperson Name, Total Due, Total Quantity, and the count of unique products for the top five salespeople sorted by Total Due highest to lowest.

    Here’s a sample of the results.

    Salesperson Total Due Total Quantity Unique Products
    David Campbell $44,214,217.28 2,313 126
    Pamela Ansman-Wolfe $46,015,977.48 2,622 128

    Here is the schema needed to get this information

  2. From the LoyTrans.csv file, report the sum of gallons for the TrxDate of April 3, 2017.
  3. In Excel, create an External Data Query to the AdventureWorks data. Bring in the sales order data for the year 2007. Create a pivot table showing the average unit price by product and month.
  4. Open ReadWriteRange.xlsm. Write a VBA procedure to read in all the data in Sheet1, multiply each number by 10, and write the data back to a new worksheet.
  5. Create an Excel workbook where the user chooses a Salesperson. The workbook should display the name, title, phone, address, territory data, and SalesQuota. The user will enter a new SalesQuota number. Your code will update the database with the new SalesQuota number.

I expected this would take 30-60 minutes. So what do you think? Too easy? Too hard? Just right?

It’s a kind of a big file, but…

You can download SkillTestData.zip

29 thoughts on “Test Your Excel, VBA, and SQL Skills

  1. It seems reasonable. I’ve put together a number of excel tests for candidates in the past, and taken a few. My take away from the tests, is that I should be able to see how you work. That will tell me much more than if they get the right answer. For example, if I ask to compare 2 data sets in Excel, and you copy and paste all the information around and don’t use formulas, you’ve told me plenty about how you approach the problem (not well) and how much time you will waste in the future.

    Try to be hollistic about it, and use it as an opportunity to see how they work and think, and how they approach problems.

  2. I agree Jayson. I don’t necessarily care if you got the right answer. I’m more interested in if you know the tools exist, if you understand basic concepts, and how you approach a problem.

  3. Thirty to 60 minutes?! Yikes, that sounds fast to me.

    The first thing I didn’t know was the unique count in #1, but I vaguely remembered, and sure enough, SQL Server has a Distinct argument to the Count aggregate function, so that’s okay. Otherwise I would have gone with a subquery or pivot table connected to a data model.

    In #5, am I understanding that you want the user to change a value in Excel and have it written to the SQL Server database? The only way I’ve done that is with a stored procedure in SQL Server and a VBA routine with AddParameter/CreateParameter functions to avoid SQL injection. That takes time. How would you do it?

    Other than that, I could do everything fairly quickly, but not in an hour.

  4. @Dick, I can do points 2, 3, and 4 the way you are asking for, but it will be easier in Power Query. Does that count?

    @Doug, do you have any post or sample about your solution of #5? That sounds interesting.


  5. Your test is reasonable. Of course it depends on what your clients want. You aren’t asking candidates to demonstrate their graphic/charting capabilities. Do you care about formula knowledge… do they know about INDEX/MATCH? Array formulas?
    Your VBA test seems standalone – that is, you aren’t apparently relying on object library references unless you want them to use one for task #5. Maybe showing they know how to use Scripting, Outlook, Forms, XML? Can they change the color of a chart series using VBA? Send an email and attach the current worksheet?

    For my “interview” most of my time was spent Googling to find the server info so I could connect to the AdventureWorks data since I don’t have SQL Server on my laptop. I eventually went to the M is For Data Monkey location but their vSalesPerson table didn’t have names, only emails?! I used PowerQuery for #1-#3 and got the following base information for #1 just as a quick first cut. I took the default relationships between the tables and I didn’t do any DAX, just the basic column info, since I’m not *really* applying for a job.

    Row Labels Sum of TotalDue Sum of OrderQty Count of ProductID
    (blank) $32,441,339.12 60398 60398
    linda3@adventure-works.com $11,695,019.06 27229 7107
    jillian0@adventure-works.com $11,342,385.90 27051 7825
    michael9@adventure-works.com $10,475,367.08 23058 7069
    jae0@adventure-works.com $9,585,124.95 26231 6738
    tsvi0@adventure-works.com $8,086,073.68 16431 5417
    shu0@adventure-works.com $7,259,567.88 15397 4545
    josé1@adventure-works.com $6,683,536.66 15220 4437
    ranjit0@adventure-works.com $5,087,977.21 14085 3419

    #4 I have some utility macros so my code was quick to adapt but probably ugly as sin. I didn’t touch #5 since I didn’t think I could write to the Data Monkey server as it appears you want to update the server instead of a local file, and my SQL is very rusty though I could probably construct an UPDATE query with all the WHERE clauses if pressed. Personally I’d toss that back to the client’s IT gurus .

    The amount of time it takes a candidate to complete your test may be affected by how you set up the PC. Maybe I’d be smart enough to bring my own personal.xlsb to your interview if I knew what you were planning! Setting up connection strings could chew up a lot of time, but of course you want to see how your candidate decides to approach the problem.

  6. @Doug

    Yes, stored procedure is the most correct answer. The minimum answer is to set a reference to ADO and concatenate a SQL statement together. I can teach best practices, I just don’t want to teach what ADO is.

    I think my time estimates were too aggressive. I need to figure out how to change the questions so that it only takes an hour. Maybe I’ll take the test. When I can complete it in 20 minutes, then it’s probably good for a candidate to take an hour.

  7. @Elias

    I don’t have PQ installed. We’re using Office 2010 and I’m not upgrading until MS fixes their encryption bug or the mainstream support runs out (2022, I think), whichever comes first. Because PQ didn’t get built-in until later, I’ve never really implemented in a production environment.

    For #4, though, I’d like to see some VBA skills. If it’s possible in PQ, maybe I need to change the question to something that’s not.

  8. @GMF

    All of the work is internal – no clients.

    The M is for Data Monkey data must be different. My NULL salesperson was third on the list I think. But that certainly demonstrates an understanding. Your first question would have been “Where’s Power Query?” and you wouldn’t have liked the answer.

    No charting knowledge is necessary. I don’t allow charts that an eight-year-old can’t construct and maintain and I can teach that much.

  9. P.S. Your test is heavily weighted to data analysis. Or perhaps it’s fairer to say your test is weighted toward data extraction… does your work typically involve extracting data for others to analyze, or would analysis be part of your work?

    One of the toughest things for me to deal with when trying to learn DAX is whether the data passes the smell test. It’s easy to write a formula but hard to know if it’s the right formula for complex data. You have to know the data to know if an answer is reasonable. I would think that’s one of the hardest things for you to ensure as a consultant since you’re dealing with unfamiliar data from sales, HR, manufacturing, transport, social, and goodness knows what other sources. How do you know if you’re off by 10% because you didn’t realize the price table’s Effective Date column isn’t used with the expected logic by a client?

    If you agree that is an important skill – to have your work pass the smell test in unfamiliar surroundings – how would you test for that in a candidate? Is there a small dataset (not AdventureWorks) with information from the real world where you could deliberately give them a formula and have them tell you if it’s OK? Financial data from OMB or FRED perhaps on real-world situations like medical spending or consumer price index? Give them two seemingly contradictory views of the same data and have them resolve the issue?

  10. @Dick – “I can teach best practices, I just want to teach what ADO is.” I’m guessing there’s a “don’t” in the second half of that sentence :-)

  11. Three of the four wish-I-could-do-right-now projects on my list require creating a sql database and building a UI to manipulate data (probably in Excel). I’m going to do another post detailing what I was looking for in those questions.

  12. @elias, sorry I don’t have a post to point you to. The best quick example I see is here: http://stackoverflow.com/a/31991476/293078

    However, like many things in our world, the important part is knowing about it. In this case knowing that you can use AddParameter and CreateParameter to pass arguments to, and return arguments from, a SQL Server stored proc in a tidy and injection-proof manner. The main time I used this I think I was passing tabular data to the stored proc and had generalized the subroutine so I could point it at different Excel tables. Very gratifying.

  13. I don’t like tests. I was recently in the job market, and had to do two tests for a job with pretty average pay…one on verbal reasoning (that I scored very, very highly on) and one on math (that I scored pretty average on, although I reckon the online system that I was doing the test on missed some of my answers). Didn’t get the job. A job involving lots of Excel. I doubt that the people that scored higher in math than me are similarly better users of Excel.

    And then I had to do a home-based exercise for another job. They gave us two weeks to do it, with the brief that “A Govt Minister wants an answer pronto, using this data. Do some graphs, and attach any code that you write”. I used a PivotTable, did it that night, and didn’t attach any code because I didn’t need any code. And I didn’t even make it to an actual interview with an actual person. Partly because I didn’t write code, I’m told. Apparently not writing code is a signal of lack of sophistication. Double unsophisticated points if you use Excel to answer a question that can easily be answered with Excel, rather than write some R code. Heck, I *could* have written some really cool code. But that would have taken like three times as long to come up with the same answer for what was definitely a one-time scenario.

    So I decided “Screw it, I’m not doing any tests unless they damn well pay me for my time. They want to see how I think, I’ll give them something stand-alone I’ve developed, and explain my thought processes that shaped the end result”.

    In the end I got another job based on a mere 15 minute coffee chat with someone who tested me via Google. I didn’t even know I was being interviewed. Nor did the posts I wrote some time back on DDOE. I passed his test pretty much before we met for coffee, and was offered the job at the end of it. And a much more lucrative job than the ones involving ‘live’ tests, at that. Even though I didn’t have the entire skillset that was in the job add. Still don’t. But I’ve automated heaps of stuff that used to take days per week, and delivered heaps of insights that otherwise might have stayed buried. Used lots of code from my library that I’ve developed over the years. Showed tenacity when I was stumped by some technical things that would have screwed me over in a test. And helped others to use Excel much more efficiently. Even more importantly, helped them to understand why their existing use of the tool was a problem.

    I wouldn’t want to do you test, Dick. To the point that I’d simply pass, and go for interviews that have a lower compliance cost. I often wonder if organizations ever miss out on good people that similarly hate paying interview tax, merely for a chance of being offered a job offer of uncertain remuneration. Then again, maybe I’d suck at the job.

    If I’m ever hiring, I’ll do what my now boss did. Ask them for their StackOverflow name, or some other forum. Ask them for blog articles, or blog comments. Ask them to show me some graphs that really nail a problem. Ask them to help me understand how they actually perform under real-world conditions, by drawing on their real-world portfolio of solutions.

  14. Those are good points Jeff. Everyone who’s ever used a headhunter to recruit me has lost out on me due to an interview tax, so I know what you’re saying.

    I’ve never given a test before, but this situation was special. First, I’ve never hired for this job before. In fact, this job didn’t exist until I saw this guy’s resume. Building systems to support our main ERP system is what I do in between regularly scheduled duties. Now I see a guy with the right combination of skills – something I’ve never seen around here before – and I have ideas about culling my todo list.

    But since I made this guy an offer and he’s likely to accept, I feel I can give a few more details. He doesn’t have any online presence that I can see. His interview answers sucked. They were vague and superficial and sounded like someone who knew the right keywords but didn’t really know what he was talking about. However, I suspected that because he’s only been speaking English for a few years that it could be that he knew his stuff, he just wasn’t great at communicating it. His answers might have been vague and superficial because of a lack of language skills, not technical skills. It would be a lost opportunity if I didn’t give this guy every chance to show his skills.

    It was more stress than the guy deserved due to my inability to design a proper test. I recognize that. But it really gave me a good feeling about his level of technical competence. And he gets to work for me – the greatest boss ever – so I’m sure all will be forgiven.

    If you ever move to the American prairie, Jeff, it won’t take me fifteen minutes to make you an offer. :)

  15. I started to write an answer somewhat like Jeff’s. I then re-read the post and figured Dick would only choose this course if necessary, which his answer above confirms. I also assumed he’d give a lot of latitude during the test-taking.

    Along these lines I realized that if I had to take a test like this, I’d want to do it with the tester there. That way I could ask questions as needed and let them know my thinking as I wrote code. That would be the best of both worlds. I’m sure it’s not true for everybody. It’s similar to my attitude towards presentations: I’m much happier if people ask questions during them. That way I get a sense of how it’s going, their level of interest, and the chance to make a corny joke or two (one of my main goals in life).

  16. I was going to make a separate post about what my test was designed to do, but I think I’ll just make it a comment. Here’s what I’d hoped to learn ordered more or less from basic to advanced.

    Question 1
    Knows what SSMS is and how to open it
    Can create a new query window in SSMS
    Understands the USE keyword
    Can write a SELECT statement
    Can join tables in a FROM clause
    Can concatenate two fields and rename the result
    Knows the TOP keyword
    Knows ORDER BY
    Recognizes a LEFT JOIN from SalesOrderHeader to vSalesPerson

    Question 2
    Recognizes that you can’t use Excel because there are two many rows
    Doesn’t ignore the error message if he tries to use Excel
    Knows how to import into SQL Server

    Knows how to create an External Data Query
    Knows how to create a pivot table
    Knows how to change the aggregate pivot table to Average
    Knows how to group a pivot table field
    Knows how to change the external data query type to SQL rather than Table (he ended up making a view, which is a good answer)
    Can figure out that “Bring in the sales order data” means joining multiple tables.

    Knows how to open the VBE
    Knows how to insert a Module
    Knows how to loop
    Knows the Range.Value
    Knows how to read ranges into Arrays and write them out too

    This question was the hardest and the most vague on purpose. The exact steps are not required
    Brings a list of Salesperson onto another sheet and make a data validation cell to choose one
    Make a userform to choose a salesperson and display information
    Make an grid-based form to display information
    Lock down information that shouldn’t be changed
    Recognize when a cell has changed
    Set a reference to ADO
    Write an UPDATE statement and push it to the server
    Write an UPDATE statement in a Command object with proper parameters and push it to the server
    Write a Stored Procedure with parameters that does the updating server side
    Make a functional, attractive user interface

    I wouldn’t expect anyone to hit every one of those. But where you end up would tell me if you’re an eight or a five or a three.

  17. @Dick

    The day you finally download the PowerQuery addin for 2010 will be the day your world changed forever.

    So much of what you are doing now (based on your test questions) can be done so easily in PQ – and the tool keeps getting better every few months (which in of itself is so not like the development world of the past – and this for a product (Excel 2010) that has not changed since it was released)

    Take the plunge – it is worth it!


  18. Thanks for the response, Dick. Thanks for your remote job offer…good to know that I have a job without ever having to pass a personal hygiene test. :-)

    Question 2 can most definitely be done with Excel, using VBA and ADO/SQL. Never say never. Question 5 would probably take me way longer than an hour. Maybe I’ll have a crack if I find time.

    Agree with Charlie and others that PQ significantly raises the bar of what your average analyst can do. Where data is concerned, it makes them non-VBA developers. Of course, VBA is still important if you want to do things with objects other than ranges, and I have workbooks that leverage off PQ, PP, *and* VBA to do some real cool stuff that you can’t do in things like PowerBI. I’ve resisted jumping from the sunshine into the cool depths of PQ and PP for the longest time: partly because I work for Government, meaning I can’t get IT to install it even though it’s free because that’s what IT do best, but mostly because I hate being as clueless as everyone else at something new. It still often takes me much longer to do things with PP and PQ than I could do without code in Excel, but man do I get satisfaction from doing it. In a repeatable fashion. And I’m getting faster. I have a plan to write up some stuff about my foray into this alternative Excel dimension, and am also lining up an awesome guest poster to do a series here around the foundational stuff that Excel users need to get cracking in PP/PQ without feeling like the idiot that they used to be in Excel.

  19. I had a few minutes spare so I thought I would have a go at problem 2. I’m not a big fan of ADO (or PP) so I came up with:

    sub Problem2
    Dim fso As New Scripting.FileSystemObject
    Dim ts As TextStream: Set ts = fso.OpenTextFile(“C:\Dump\Excel\LoyTrans.csv”)
    Dim my_row As String
    Dim row_array() As String
    Dim total_gallons As Double
    Dim test_date As Date: test_date = DateSerial(2017, 4, 3)

    Do Until ts.AtEndOfStream
    my_row = ts.ReadLine
    row_array = Split(my_row, “,”)

    On Error Resume Next
    Dim my_date As Date: my_date = CLng(CDate(Replace(row_array(3), Chr(34), vbNullString)))
    Dim gallons As Double: gallons = CDbl(row_array(5))

    If my_date = test_date Then
    total_gallons = total_gallons + gallons
    End If
    On Error GoTo 0

    Debug.Print “Sum of gallons for the TrxDate of ” & Format(test_date, “yyyy-mm-dd”) & ” = ” & Format(total_gallons, “#,##0.000″) & ” gallons”
    end sub

    Which give me an answer of 268,529.389 gallons.

  20. To Increase all values by 10

    Sub Test()
    Dim Rng As Range, cRng As Range
    Set Rng = Sheet1.UsedRange
    Set cRng = Rng.Offset(, Rng.Columns.Count).Resize(1, 1)

    With Rng
    .Offset(, Rng.Columns.Count).Resize(1, 1).Value2 = 10
    .Offset(, Rng.Columns.Count).Resize(1, 1).Copy
    .PasteSpecial xlPasteValues, xlPasteSpecialOperationMultiply
    .Offset(, Rng.Columns.Count).Resize(1, 1).Clear
    End With

    End Sub

  21. This exercise would probably never come up in real life but for the following bellow I think the quickest way is to place in code to Cells(Range(“A” & &H100000).End(xlUp).Row+2,1).Value=10 and then copy Range(“A1”).CurrentRegion and pastespecial(multiply) by where 10 is.

    Open ReadWriteRange.xlsm. Write a VBA procedure to read in all the data in Sheet1, multiply each number by 10, and write the data back to a new worksheet.

  22. @Jeff – some of my clients ask me to test Excel + VBA + Access skill level of people they want to hire for an “Excel developer” role.

    You would be surprised at just how many people do not know the difference between an Absolute / Relative and Mixed reference and have spent years working with excel and claim to have a high proficiency with Excel and VBA – when the only code that they would have written would be the one that came out of the macro recorder.

    A test needs to test the thought process rather than the correctness of the answer – because in Excel there are so many ways to skin a ….potato (just saved a animal i think….)
    Take Q1 – Today we can achieve the output in so many different ways
    1. Use Power Query ->Join the Fact and the Dim tables – Create a Flat Table – Remove columns – Do a Group by on the required columns and dump the o/p in Excel
    2. User Power Query – Get the tables as it is in to the Data model – Create Relationships in the DM – Create measures for each of the the Output Parameters – then display in a Power Pivot table
    3. Use just Power Pivot – Get the Tables from SQL Server – In to the Data model – create the relationships
    and Build a DAX Query that generates the Output using ADDCOLUMNS and SUMMARIZE
    4. Do what most people would do – Build a view in SQL Server having the columns you require – Build a Query/Pivot table in Excel on that View

    Take Q4 – There are at least 3 ways to do this.
    1. Loop through each cell – add 10 t0 the results
    2.Write the contents of the range.Value2 property to a variant array + loop through the array – modify the contents and write back to the the Range.Value2 property
    3. Write 10 in a cell , Copy the cell, Pastespecial multiply, clear the contents of the cell with 10

    I wish we could do a paste special multiply using data placed on the Clipboard – then it would not require us to write 10 to a cell first – but doesn’t seem to be possible….

    So a Excel test Question should have several solutions and what has to be tested is if the candidate knows the merits and demerits of each approach.

    The problem today is we have a lot of Google engineers – these are people who copy code from a forum /blog – paste – run – have no idea why it worked and hence do not know how to fix it once it stops working and are back on google again.

    A well framed test helps you identify such people…. – A test is definitely required at some point of time – the format and the questions need to be well framed

  23. @sam

    I can add some more:

    Sheets.Add(, Sheets(Sheets.Count)).Range(Sheet1.Cells(1).CurrentRegion.Address) = Evaluate(Sheet1.Cells(1).CurrentRegion.Address & “*10”)

    Sheets.Add(, sheets(Sheets.Count)).Range(Sheet1.Cells(1).CurrentRegion.Address) = “=sheet1!A1*10”

    Sheet1.Cells(1).CurrentRegion.Name = “snb”
    Sheets.Add(, Sheets(Sheets.Count)).Range([snb].Address) = [snb*10]

  24. @snb – nice
    But why are you adding a sheet ?
    Sheet1.Cells(1).CurrentRegion.Value2 = Evaluate(Sheet1.Cells(1).CurrentRegion.Address & “*10”)

Leave a Reply

Your email address will not be published. Required fields are marked *