This month I was struck by some philosophical thoughts about Excel expertise. I have been developing some thinking on this topic in this blog over the last few months, talking about the tendency to do too many things in Excel, or about how to push yourself further, even if it means making your own rules.
This month, I was speaking with the folks over at ModelOff, who run a contest to find the “financial modelling world champion”. Microsoft also started their own contest, and there are a few other things out there with a similar purpose. Sometimes when I talk to other spreadsheet experts, they can be quite dismissive of the skills tested at these kinds of competitions: they tend to focus on speed as a major element, for example, over process and testing. I think that challenges like this are definitely fun – I recorded myself doing a past ModelOff problem for this webinar, for example. But what do they really measure?
Certainly anyone that can solve these kinds of problems well under time constraints is someone that knows their way around Excel. But there are plenty of other skills out there that aren’t tested – teamwork, for example, or the ability to build a meticulously detailed model slowly and with proper documentation and testing.
What’s more, ability in all sorts of things, spreadsheets aside, tends to suffer from the “Dunning-Kruger effect”: less competent individuals lack the knowledge necessary to recognise their own incompetence, while more competent individuals tend to assume their knowledge and ability is more common and accessible than it truly is. So many expert users believe they are less expert due to recognising the true breadth of their subject, or falsely assume that their training content is more accessible than it truly is.
What would it really mean to be the world’s best Excel user? Would it mean total mastery over all 700+ functions, VBA, and the features behind the hundreds of buttons on all the taskbars? Would it mean being the ultimate team player, capable of designing for others’ needs and explaining things to the right level for every learner? Or, more likely, would it mean feeling that you were just starting to get to grips with what the programme could offer, and that what you had achieved was not so uncommon after all?
Sobering to consider, but the lesson is clear to me: your actual ability may not be what you think it is, and you may not be able to win a world championship in Excel. But there’s still plenty to learn, and while you might not be number 1, you can still learn a few new tricks and work on being the champion of your own learning path, and being the best you’ve ever been.
Tip of the Week highlight – Counting items in common
This week’s Tip is drawn from TOTW #204. It’s a useful trick for making a formula to measure how much overlap there is between two ranges of data.
All we want to do is make a single simple formula that can look at two ranges and tell us how many items they have in common. Here’s the generic formula:
=SUMPRODUCT(COUNTIF(range 1, range 2))
This is a type of array formula, although it doesn’t need any special key presses to use. It works best if each range is without repetition; if there are duplicates within one of the ranges, the scores’ meanings will be a little harder to derive. Essentially if there are duplicates, each instance of a name in one range will add x to the score, where x is the number of times that name appears in the other range.
Principle of the Month
This month’s selection from the Twenty Principles for Good Spreadsheet Practice is #20:
Protect parts of the workbook that are not supposed to be changed by users.
Worksheet protection, whether it be achieved by locking cells or through the use of data validation, is essential to keeping users from changing things they aren’t intended to, either on purpose or by accident. Keeping formulas protected, inputs restricted to appropriate types, and so on can help the user to avoid stumbling or making errors, and can also ensure that any formula changes are done only in collaboration with the original author.While no protection is foolproof, no protection at all is risky and at least basic protection should be considered for any workbook exposed to users.
David Lyford-Smith is the technical manager in the ICAEW’s IT Faculty and blogs for the ICAEW Excel Community. The Excel Community is an Excel content hub that encompasses webinars, blogs, member Q&A, fortnightly bulletin updates, and more.