So I have just failed the Basic Excel Certsy test. I’m obviously not as smart as I thought I was. I mean how can an entire organisation allied with one of the biggest job sites in Australia be wrong? Read on and make up your own mind.

History

Skip this History section if you are not interested in understanding why I consider myself an expert.

Project Pricing Tool

In 2010 I built a major utility an application for automatically pricing their projects in Excel which they continue to use today.  It allows only administrators to update material prices and labour rates and allows users to store project quantities.  Each project can then be saved externally to the application and later imported again to amend or reprice against the latest pricings. The priced schedule is produced at the click of a button. Part of the application allows the user to specify how they intend to excavate and bed pipelines. It adjusts the pricing based on the methods selected and chainage depths which can be imported into the application. So I’m not new to Excel.

Action Register

I supply all my smaller certification customers with applications that I built in Excel. These typically include an Action Register which allows my customers to log complaints and non-conformances in Excel, it has access controls so that only Administrators can close items not created by them. Users can attach documents and pictures to both the opening and closing of an item. When a finding is raised and assigned to an individual it automatically creates an Outlook Task containing information about the issue and assigns a deadline based on the assessed risk. So I do appear to know a thing or two about Excel.

For one customer I created a webform in MS Forms which populated a spreadsheet held on in Sharepoint.  Then when they opened their Action Register it accessed the Sharepoint workbook, to see if anything new had been submitted via the Form. It then allowed the user to select the items to automatically import them into the Action Register. Once imported, they disappeared from the list of items which can be imported. Hey, that’s pretty fancy.

Tool for managing Work

Just at the end of 2021 I built a customer an Excel application to manage their sales and their workshop work. The application provided automated management reporting at the push of a button to Administrator level users only.  Administrators could then amend the reports to only report between their chosen dates. My customer also had a problem with people logging jobs against incorrectly named customers (which obviously made management reporting difficult).  So I built them a function that checked the customer names against a table of synonyms. It then provided users with the preferred customer name.  If a user disregarded the proposed customer name, the new name was automatically appended to the synonyms table, allowing Administrators to consider how it should be managed.

What’s wrong with Certsy?

The fact is that you can’t hope to assess someone’s competence in 12 questions in 12 minutes which is what Certsy attempts to do. That’s a ridiculous assumption to make, but even more ridiculous when you consider the quality of Certsy’s questions. I screen grabbed a couple of shots to illustrate my point, which probably contravenes their Terms and Conditions. So sue me! It’s not like I’m giving away the answers. Let’s be clear, I don’t know the answers.

First Example

It’s really not clear how to interpret the answers to the above in my view and I’ll explain why. I am happy to be corrected if someone knows better than I, so feel free to comment.

So my first impression is that the question is oddly worded.

“What is a workbook on Excel?” 

A workbook is not on Excel.  A workbook is a file created in or by Excel, but not on Excel.

Answer 1 “An Excel file with more than one worksheet”? 

• Not necessarily it may have only one.  Create a New workbook in Office 365 and it comes with only one worksheet by default. Older versions came with 3 by default, but that doesn’t stop you deleting 2 of them. Does it magically become something other than an Excel workbook when you delete two worksheets? Is it an Excel workbook if is has 5 worksheets? Absolutely, it is.

Answer 2 “A file containing a single worksheet”? 

• No, it may contain one or more worksheets.  You can even insert an Excel Worksheet into other MS applications like Powerpoint or Word, so a single worksheet definitely does not define an Excel workbook.  Worksheets could appear in other file types. For the smart ones, I believe this is the answer they want you to tick, but this is more of a semantics test than a test of your Excel acumen. It would have been absolutely correct if they had said “A file containing one or more visible worksheets”. You can’t have a workbook without at least one visible worksheet.

Answer 3 “A template on Excel”? 

• A template in Excel is a specific file type (.xlt or xltx).  Templates are used for the purpose of controlling the starting point for document creation.  This is not just a ‘workbook’ in Excel, but is an Excel template a workbook? Yes it is, so this is a potentially valid answer.

Answer 4 “When you downloaded the Excel file (as a pdf, .ods, xlsx)”

• I don’t even understand the grammar of the last option.  Downloading an Excel file as a PDF is not possible.  You can only download the image of an Excel file as a pdf – it will have no Excel functionality.  A .ods file is an alternate to the .xlsx files and produced by a product called Calc (I’ve no experience of this product thankfully). I guess however you throw it up in the air, this can’t be a valid answer.

Second Example

There is huge ambiguity in these potential answers of the next question. So here it is:

When would you use source and destination workbooks?

At least the question’s grammar is correct this time

Answer 1 “When you are sourcing data from another document or program”

OK, I guess this is incorrect purely because it states another document or program whereas the question states workbooks, but does this provide any insight into a persons knowledge and capability? You certainly can scrape data from all manner of places other than workbooks. Wouldn’t it have been more interesting to know if the candidate knew how to use source data in another workbook without replicating it? You could find out if they were aware of the multitude of ways to achieve this (hyperlinks, formulae, array formula, queries, VBA, etc).

Answer 2 “When you are using one workbook for master data and editing data on another”

I don’t really understand the term Master Data as it applies to Excel. I went looking for Microsoft guidance and generally found references to databases. I assume it means data that you read from a source workbook and use in the destination workbook. But the term ‘editing’ is misleading here. You wouldn’t edit Master Data in the destination workbook. You might manipulate it, use it for calculations or chart it, but never edit it. But is this the right answer? It certainly could be. Sadly, your guess is as a good as mine.

Answer 3 “When you are manually copying data from one workbook to another”

Well, yes this is a source file and destination file in its most basic form, but you can’t argue that this could be a correct answer.

Answer 4 “When you are creating a link between workbooks”

Presuming this means linked via formulae, then yes this is certainly a way of capturing data from a source workbook and replicating it in another workbook. However using the term “link between workbooks” is pretty vague. We could link workbooks via a hyperlink or a query data connection. Two completely different methods which produce wildly differing results. Determining whether the candidate knew about the various methods for creating a live connection between workbooks would be more valuable.

Once again these question seems to test your ability to fight through the semantics of a question rather than the candidates knowledge of Excel. They provide no clearly correct answer and in many cases I would argue that multiple answers are correct. They probably are effective at discriminating against people who struggle with the English language. I was brought up with the Queen’s English and I am still struggling.

Conclusion

If I don’t qualify as an expert in Excel and Microsoft Office, I don’t know who would and yet I have just failed the Certsy Basic Excel test. Am I embarrassed about that? I should be, but I’m not. If the test had any real value I may have been, but it clearly doesn’t.

But the real question is why are employers and recruitment specialist websites relying on these tests to filter out weak candidates? If they don’t know how poorly written these questions are, they should do.

Now you’ve read this, you know, so I hope you choose not to rely on them and either set your own test questions or just try talking to candidates. Is that really so hard to do?