Last week, one of my first events as a member of the IT Faculty at ICAEW was to visit ModelOff, an Excel competition for top financial model builders.
The competition finals were run alongside a series of seminars and training sessions for Excel aficionados, with several members of the Microsoft dev team present to talk about the future of the program with delegates.
As a result of all of that, I ended up talking with one of the ModelOff question designers, Dan Mayoh, who agreed to do a blog series walking through one of their problems for us.
That all came to fruition in the last four weeks – the blog series having finished, I did a live webinar version of it, tackling the problem of building a Snakes and Ladders simulator. As much as being an exercise in showing how to build a Monte Carlo analysis, and how to build IF formulas for some complex conditions, the real aim here was to teach problem solving skills.
How do you break down a problem into understandable chunks? How do you research the bits you don’t know? How do you build the file to be understandable, tested, and controlled? Whilst most Excel tips (and mine are no exception) focus on how to use various tools, it’s just as important to think about these larger strategic concerns. Problem solving is a big topic, and applying some critical thinking to Excel can really help improve your skills.
Also one year on, ModelOff was back on again, this time in Canary Wharf. The various tracks in the training camp covered Power BI, modelling, top-flight Excel skills, and more. As much as ModelOff itself is a celebration of the very best of the Excel elite, the rest of the camp showed how much appetite there is for upskilling in Excel. Speaking with other delegates, I got a real sense of curiosity about what the versatile software could do, and how it could be leveraged to do more than ever before. I came away from the event feeling revitalised about what ICAEW can do to help people start up that ladder themselves – and hopefully avoid a few of the snakes in the grass too.
Tip of the Week highlight – Merging cells sucks
More than 50% of Excel spreadsheets contain a merged cell, but most spreadsheet experts will tell you that they are terrible and should never be used. Why are they so popular? And are they really all that bad?
To answer my second question first: Yes, merged cells really are all that bad. Here’s a list of the problems with them:
- Formulas made by clicking on the merged cell use the top-left most underlying cell’s value; formulas that look at any of the other constituent cells return 0. Formula results can be unpredictable when copy-pasting.
- You can’t select a single row/column where a merged cell is in the way, making insertion/deletion of rows very tricky
- You can’t paste a single row/column into a cell with a merged cell in it either
- You can’t filter a range with a merged cell in it
- If you delete part of the range that has a merged cell in it, the value might remain in a smaller merged cell, or might get deleted – depending on which part of the range you delete
All of this makes working with a workbook with lots of merges pretty tricky, and explains why so many spreadsheet experts hate them. But then why exactly are they so common?
Ultimately merged cells tend to be used for presentational purposes, where having e.g. a common over-header on a table is desirable. And, honestly, if the spreadsheet is 100% for presentational purposes, then merging cells is just fine. But if the issues listed above are likely to cause trouble, then consider instead using Format => Alignment => Center Across Selection to achieve the same goal. You could also use coloured fill or other visual elements to tie together the related columns instead.
Principle of the Month
This month’s selection from the 20 Principles for Good Spreadsheet Practice is #7:
Include an ‘About’ or ‘Welcome’ sheet to document the spreadsheet.
This Principle is particularly relevant given that we’re talking about modelling. Large, complex models – and more modest ones – benefit greatly from usability improvements.
There are several key pieces of information that should be included in any spreadsheet of moderate complexity – such as the title and purpose of the workbook, the creator’s name, the version number, and a basic guide to how to find and update key inputs to the calculations.
A little documentation can go a long way to helping others follow your work – or even help you get back up to speed with your own workbook, if you come back to it months later.
David Lyford-Smith is a 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. Find out more at this link.
