Ingenuity or Ignorance – The Redundant Features of Excel in the Real World

Over the last few months several clients have been using Excel Model Adapter (“EMA”) to better understand the complexity & associated risks of their end-user developed spreadsheet models. Some of the clients have a desire to simplify or refactor their existing spreadsheet models, while others have a plan to migrate their unmanaged workbooks to fully managed Mo.net projects.

One of the key features of EMA is the ability to digest all the built-in and any custom Excel functions used in a workbook. This includes providing a list of the functions used and the count of how many times each function is used.

Since actuaries are perhaps some of the most demanding users of Excel, you might have thought that the actuarial community would be making use of a high proportion of the 400+ built-in functions available in Excel.

However, following a review of the initial workbook analysis performed by clients, it appears that actuaries manage to develop very sophisticated business critical spreadsheet-based solutions using a very modest set of the functionality on offer in the latest versions of Excel.

The chart below illustrates the top 20 functions used by actuaries and the number of instances of each function taken from a sample of 16 client workbooks. It’s perhaps no surprise which are some of the most popular worksheet functions in use, and of course these numbers are likely to be influenced by the same formulae being down entire columns or across rows.

Based on our analysis, extending the sample size to 50 or 100 spreadsheets doesn’t significantly change the number of distinct functions in use or their distribution. There is perhaps an argument that Excel contains a lot of redundant function that is wasted on even the most demanding user communities.

Ingenuity or Ignorance?

A reasonably obvious question might be whether actuaries and other “power users” are simply using their ingenuity to solve solutions with a relatively limited set of functions, or whether they are ignorant of other functions that may be better suited to the particular use case.

Furthermore, is there a more general problem for actuaries and more humble users of Excel in knowing the best way to solve a particular problem? How are even the most experienced users of Excel supposed to understand when new or better functionality is released? Does Excel need to help users and suggest alternative / better ways of solving a problem rather than just adding more redundant functionality with each new version?

How do people learn what is possible with Excel in today’s business environment? Certainly not from a formal training programme. And probably not from a book. Maybe from colleagues or YouTube, but more likely from experimentation or their efforts in trying to understand a workbook that somebody has asked them to use or update. However, once a solution to a problem is found, regardless of how brute force it might be, there is little incentive to subsequently review & refactor with a more appropriate solution.

Conclusion

There is clearly a challenge of how to increase awareness of existing functionality let alone anything new that might appear in a product which is now evolving more rapidly and seamlessly than ever.

While there is probably little merit in providing a cut down or “lite” version of Excel with stripped-back functionality, there is clearly a wealth of untapped potential in Excel that people simply don’t know about, and which might make their lives easier.

But perhaps using a small repertoire of functions is better because it helps novice users get to grips with pretty much any workbook they might come across.