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

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…

## 35 thoughts on “Test Your Excel, VBA, and SQL Skills”

1. Jayson says:

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. Dick Kusleika says:

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. Elias says:

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

Thanks

5. GMF says:

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

#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. Dick Kusleika says:

@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. Dick Kusleika says:

@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. Dick Kusleika says:

@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. GMF says:

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. Dick Kusleika says:

@Doug. Right. Fixed.

12. Dick Kusleika says:

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.

13. @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.

14. Jeff Weir says:

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.

15. Dick Kusleika says:

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

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

17. Elias says:

@Doug, now the answer of point #5 using the stored procedure make sense to me.

Thanks

18. Elias says:

@Dick, I believe PQ add-in works with Office 2010 too.

Regards

19. Dick Kusleika says:

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
Knows COUNT DISTINCT

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

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

Question4
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

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

20. @Dick

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!

Charlie

21. Jeff Weir says:

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.

22. snb says:

problem 4

4 minutes, 2 lines of VBA code

23. James Brown says:

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:

[VB]
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
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
Loop

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

Which give me an answer of 268,529.389 gallons.

24. sam says:

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

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

26. sam says:

@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

27. snb says:

@sam

Sheet1.Cells(1).CurrentRegion.Name = “snb”

28. sam says:

@snb – nice
But why are you adding a sheet ?

29. snb says:

@sam

30. Wilson Brodbeck says:

Hey Dick,
This is an unrelated question (maybe for a different daily dose), but since your level of Excel use and expertise seems to be above the fray it should not hurt to ask.
I run a macro several times a day to sort my consulting time, format and hide duplicate tasks (multiple time entries for same task), add new rows with formulas. As time went by it was taken more and more time to run, so I limited the number of rows to process (only the last 50 or so), added some speedup practices, and added a log for the various blocks on the code.
The processing time came to about 6-10 sec, and on occasion would jump to 15 or so.
On March 22, without any change in code or data, processing time jumped to 21 to 60 seconds. I then cut about 1600 rows from the sheet with minimal change. I searched a lot but could not find anything on the net related to my issue.
Now on April 21 also without any change in code or data, processing time went down to 4-6 seconds, which is excellent.
The question is, has Microsoft done anything recently that crapped out VBA processing and just fixed it? Would you know what might have happened or know where could I find answers?
Best,
Wilson

31. Would it be possible to post the VBA code as well as a text file of the structure one can easily paste into an Excel sheet to run the code? It is highly unlikely that such code could be affected by anything Microsoft has done in the last month or for that matter ever. Such code should run in the very first version of VBA.

32. Raj says:

@Dick Kusleika

Thanks for the test. I am applying for entry level positions and I think question 1 is a good resource to get some practice in SQL.

Would anyone be able to guide me with number 1?

Here is what I have so far, but that is not giving me the output desired in SSMS.

SELECT TOP 5 FirstName + LastName AS [SalesPerson], TotalDue, Count(Distinct ProductID)
Left Join Sales.vSalesPerson s2
Inner Join Sales.SalesOrderDetail s3
ON s1.SalesOrderID = s3.SalesOrderID
Order by TotalDue;

33. The trick with interview questions – whether verbal or a formal screen-based exam – is designing them with *grade boundaries*.

That is to say: there are questions with a ‘banana’ grade – if they get that wrong, they really shouldn’t be in the room.

There are questions with a ‘basic competence’ grade: they do know the subject, and you proceed to questions which gauge how much supervision they will need.

There are questions which require expert knowledge and – more importantly – an expert’s ability to communicate that knowledge effectively.

Sometimes you can’t get all three grade boundaries into a single question, and you have to yuse a spread of increasingly-difficult ones. But it’s not impossible: ‘Where do you go for information?’ reveals a lot: the ‘deer-in-the-headlights’ look is a clear ‘banana’, and “I go to Microsoft’s documentation” shows competence, but needs a follow-up quesion: ‘What are the limitations of that approach?’ or ‘Have you ever found an answer that you believe to be wrong, or missing something important?’. “I ask my colleagues” is always a good answer and the experts will, of course, mention Daily Dose of Excel and the web pages of your regular contributors.

Finally, you need to interview for operational competence as well as technical expertise.

The classic question for operational competence is: ‘A trader tells you he can’t open his pricing spreadsheet: what do you do?’

The wrong aswer is a technical digression on the causes of spreadsheet errors on open – you didn’t ask for that, the sheet isn’t opening at all! – and you should stop them if they start with an explanation of the causes of Excel file corruption. The correct answer is: “He’s got a backup, right?” and then a short list of things that’s get him working again, fast, while you look at the underlying problem at leisure.

I can expand on this – and send you the interview script we use here – despite increasing the risk of that embarrassment I get when I’m interviewed using my own interview questions.

I would make a point of saying that Excel is a huge knowledge domain and you’re not going to get a meaningful measure of a candidate’s competence in an hour unless your questions are very carefully structured. And almost all interview tests are crude snapshots that end up screening-out the best candidates.

34. Very interesting reading. Thanks to everyone who contributed.

Jeff wrote:

“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.”

I for one would welcome that. I’m fairly proficient in VBA, but have only just started looking at PP/PQ. I recently tried using MS Query for a project, and found it so difficult and annoying that I went back to using VBA instead. I’m hoping PQ will be a better alternative.

Posting code? Use <pre> tags for VBA and <code> tags for inline.