Why do actuaries love Excel so much?

A former colleague once told me she felt trapped by Excel. "They're even called cells!" she cried "tiny little prisons to store my numbers!".

At that point I'd spent half a decade using Excel close to daily and I'd never considered it in this way. To me, the word cell had more of a biological connotation than a penitential one; these tiny little boxes are pretty boring individually but put a load of them together and suddenly you have life! (Or a reserving model).

Another half-decade on, I'm older, wiser (greyer and more cynical) and I realise the truth is somewhere in between. Excel really can create some wonderful things. I've seen it used to create a playable version of Pac-Man using VBA, as well as plenty of complex actuarial models. It can also be a horrifying prison of poor formatting, merged cells and lists of text. Yet on balance it's still the first place I go for a quick piece of analysis or bit of maths or visualisation. Often just to sketch out an idea before moving onto something stronger. Why does it have this draw?

The button for the maths

Like an unwanted U2 album on your iPhone, Excel is, simply, there. It doesn't matter whether better tools exist behind 2 clicks of a button, being the tool that lives at 1 click of a button is a huge advantage. There can be no other reason for the fact that Edge and Internet Explorer still make up about 15% of desktop internet traffic.

I saw this with my own eyes when installing Chrome for my mum and hiding the IE shortcut so she'd have to use it, which led to a panicked phone call that she "couldn't find the button for the internet".

For so many people Excel is "the button" they turn to for maths, graphs, project plans and quick lists. They've never used another because Excel was there and it was enough. For this reason alone, it has a sort of power; I know if I build something in Excel I will never be short of people who can understand it, amend it, and take full ownership of it.

I'm all for using the right tool for the right job, but sometimes the right tool isn't necessarily the objectively best one; it's the one that can be flexible, can be handed over, that people are comfortable seeing and understand how to check things in.

Jack of all trades

This got me thinking about how I'd rate software from scratch. As an actuary, I regularly use or have used four main tools:

1. Excel
2. Bespoke actuarial modelling software
3. Open source tools and statistical packages
4. Visualisation software

Each have advantages and disadvantages in certain situations, and to see why we keep coming back to Excel you only need to think these through. What follows is in no way a recommendation for or against any type of software in any circumstances. It is merely my take on what I look for in the tools I use. In case that caveat wasn't clear enough, I have scored them with my proprietary scoring system: GUESS (Generally Unscientific Estimate of Software Suitability):

• Price: no prizes for guessing who wins here, you're either free or you're not!
• Ease of use: Excel is pretty easy, but tools like Tableau really take intuition to a whole new level; drag and drop all the way. The clear loser here is the likes of R and Python; more power inevitably makes them less user-friendly.
• Flexibility: Other than price, this is the one place I've given Excel the winning score. Rightly or wrongly, Excel can produce anything from full actuarial models, dynamic visualisations, VBA videogames, and project plans.
• Ability to do complex things: Bespoke software and open source tools are clear winners here, but with a lot of expertise and VBA skills Excel isn't too far behind.
• Future development: This is where software such as Mo.Net and Prophet really comes into its own; the support and development behind them is incredible and hugely valuable to larger companies wanting to quickly implement new models, make use of cloud computing, etc.
• Robustness: Again a clear win for bespoke software, where audit trails are built into the very fabric of how they're designed.
• Looks good: If you've ever taken a Tableau dashboard to a Board who is used to Excel outputs, you have no questions about the winner here!
• Shareability with non-actuaries: Everyone understands Excel and everyone can read a dashboard, beyond that it really depends who you're talking to.

Overall, even though Excel is rarely best at anything it seems to come out as best overall. I'll probably continue to use it for another decade, alongside other tools that are better for specific jobs. Excel is the Swiss Army Knife of the actuarial world; the jack of all trades (even if it masters none).

Conclusion

Excel's ease of access belies it's complexity. Fundamentally it is a tool for doing maths, and often the problems people have with Excel are from trying to use it to make lists or plans. It has some genuine problems; it struggles to handle large data sets, and it's insistence on running calculations simultaneously is problematic in an actuarial forecasting context. But its flexibility is its saving grace; there is always a hack to force it to do what you want and for that reason I have no doubt I will keep coming back to it.

How would you rate your experience with Excel? Are you a devotee, a self-styled "power user", or have you moved on and never looked back?

What other points do you consider when deciding which tool to use?

I'd love to hear from actuaries and non-actuaries.

Finally: Not just for Actuaries

In my line of work, I've had the pleasure of sharing spreadsheets with a number of different people. I've learned you can tell the difference between an actuary and an accountant in two ways:

1. An accountant will use VLOOKUP() when every actuary knows they should use INDEX(MATCH())
2. Accountants start all formulae with =+ or =-. Never just =. I've never learned why.