How to Excel

I often marvel at the sheer quality of Excel.  It is unquestionably a package so clever, useful and refined that many organisations could not function without it.  Has any business ever cornered a market quite as effectively as Microsoft with spreadsheets?  The most useful function the Apple equivalent has, in my opinion, is the easy conversion of the sheet you are working on back to the real thing.

One Corporate Solutions employee is such a fan he has a mug for his tea that simply states “I love spreadsheets”.   Although geekery of the first order that I would not wish to be associated with, I do ‘feel’ the enthusiasm for it.

I have the pleasure of seeing a vast array of spreadsheets in my working week, from a vast array of different organisations.  The sheer variety is often a sight to behold, from the violently coloured, to the old school black and white, to the understated artisan.  I always get a sense of great anticipation when waiting for a sheet to open; and there is always an inevitable rush when you see a beautiful sheet, no formula errors, easy to view and comprehend what it is telling you.  An orchestral performance of Carmen compared to the latest offering of Little Mix.

Then there is that sense of disappointment, or comedic effect, when you open a chaotic masterpiece that will take at least 14 hours of forensic deduction to fully appreciate what it is telling you.  I have recently been allocated a new computer at work that is the equivalent of a Ferrari to my last, a much loved clapped out Robin Reliant.  This is clearly a good thing, but, it does open spreadsheets at a comparative 0-60 of 1.2 seconds as opposed to 4 minutes; so I lose a little bit of the building anticipation.

The most staggering sheet I have ever seen from a customer contained a bewildering array of links to multiple sheets, Lookups, Data validation, Macros, Conditional Formatting, complex Ifs.  There was a formula error with almost every click, a warning message highlighting Excel’s all-encompassing confusion on what it was being asked to compute with every save.  An impressive 190,000 rows with 145 columns and 14 worksheets, all talking to each through over a million formulas.  I attribute the demise of my Robin Reliant directly to it.

It had a stunning complexity, a testament to the ingenuity of the people involved and the software development that facilitated it.  The sheet had been the working lives of 6 people, a combined 12,240 hours of data entry a year.  No doubt extremely innovative but could never hope to pass the keep it simple test and extremely difficult to get any workable data from it.  Some operations are of course furiously complex and pressured for time, resources and contain challenges that on first view appear insurmountable.  In order to make any progress though, you need to understand, analyse and then devise a method of improvement to get past any problem.

Good spreadsheet design is an art.  When they are used as control documents for important business functions there should be an excel wizard that advises operators to re-consider, do you really need to do this?  Is this really the best way to achieve this end? Perhaps a complexity warning alarm?  As sheets grow organically, and the requirements on the operator become more onerous, the inverse correlation between complexity and quality of information becomes more and more marked.

For any logistics operation you need to know where and what your product is, when and where you need to deliver it, how you can get it there, and how long that will take.  If you can understand how much that whole process will cost to achieve then you are on the right path.  If you have a team of 6 people all inevitably making entry errors onto the most complex spreadsheet ever devised it is unlikely that you can ever hope to understand whether you are doing all you can to excel.

Leave your reply

Your e-mail address will not be published. Required fields are marked *