Taming The Beast

(stackoverflow rep: 7963, Project Euler 83/261 complete – slacker!)

It’s a little while back now (I’m a slow thinker) but Simon Murphy, developing on a topic found in a post by Dick Moffat, discussed some observations of Excel usage within organisations. He concluded that general user skill levels are largely unchanged but that the uses to which Excel is being put are becoming more complex and at the same time training quality is declining. As a result, it’s increasingly commonplace that such organisations are seeking to reduce or limit the degree to which user-written spreadsheets are used.

The way the future was

The way the future was

It’s a tricky one. We have this enabling technology that makes it possible for end-users to manipulate information in ways unheard-of before 1979 (the year Visicalc was launched). Since the introduction of the computer spreadsheet, the power and complexity (yes, ease of use too, but see below) of the products available has increased steadily, necessarily, if you think about it: if Multiplan, Lotus 1-2-3, Quattro Pro, Wingz, Excel et al didn’t offer something more then we’d still be using Visicalc.

Putting a spreadsheet program on every desk-top computer, as is commonplace nowadays, due to Microsoft’s bundling policy: most will need Word and Outlook, they’ll probably expect Powerpoint, God help us, so Excel is almost certainly going to come along for the ride. Once it’s there, all but the most dedicated non-fiddlers will find it, even if it’s only because they’re drawn in by their peers.

I’d say a not-untypical progression starts with using the electronic squared paper to make lists or tables, moving on to the occasional diagram and then one day there’s the discovery of arithmetic, at which point Pandora (which wouldn’t be a bad name for a spreadsheet program) has opened the box and we’re heading downhill on a slippery slope into the land of overused metaphors, not to mention end-user spreadsheets.

There, I’ve said it: “end-user spreadsheets”. Three words (or two, depending on how you count hyphens) to strike fear into the giblets of any spreadsheet professional. Why? Here’s a selection of risks to which an organisation may be exposed:

  • the spreadsheet uses the wrong function(s): anyone know the difference between STDEV() and STDEVP(), for example? Which should you use and when? What happens when the wrong one is still not accurate enough? What other areas might be dangerous? How about the entire “Financial” category?
  • dynamically-growing data ranges and fixed-size references: not all data is included in calculations
  • the user spends more time playing with his spreadsheet creation than he1 should – losing focus on the job for which s/he is actually being paid
  • the spreadsheet becomes so useful that users other than the creator start to use it: the creator is now part of application support. Was that what you had in mind?
  • because protecting spreadsheets can be tricky, and changing cell contents to perform “what-if” calculations is easy, formulae may be overwritten with new un-noticed and un-wanted constants.
  • the spreadsheet acquires a noxious sludge of user-written VBA (shudder). You know, pages of global variables, no classes, 1500-line Subs, that sort of joy.

That’s the 5-minute, off-the-top-of-my-head list. This is the sort of stuff that has IT people waking up screaming in the middle of the night and contract support people (been there, done that) reassured of continuing employment for the remainder of their working lives.

How to address the problem without flushing both baby and bathwater?plughole

First of all, if no-one is prepared to accept that there may be some real risk here, then then whole question is moot and you’ve already irrevocably lost a few minutes of your life getting this far. Sorry about that.

A review process needs to be put in place: what XLS files are being stored on the network? Can you see who is referencing them? Reading or writing? Are there patterns that might indicate that any of the problems above are being experienced?

Can you write a “spider” that looks around the user spreadsheet “corpus”  for frequent updates, evidence of VBA, all-round horribleness? Remember you’re not spying, you’re auditing.

As candidate files are identified, review them with the relevant user representatives to determine if the workbook is an signal for action to be taken. Such actions may include

  • doing nothing – proactively chossing to do nothing is a perfectly reasonable option;
  • adding the workbook to the portfolio of maintained and supported spreadsheet applications;
  • setting up a project to address the business need in a more rigorous way;
  • a bit of business process re-engineering to address any procedural deficiency that may have driven the need a an ad hoc spreadsheet.

Again, that’s the five-minute list. What other courses of action might one take to mitigate the risks of the enthusiastic amateur running wild with a spreadsheet program in one’s business?


1 Let’s face it, folks, it’s usually a “he”


3 Responses to Taming The Beast

  1. Julian says:

    A very interesting post, thank you.

    For me, as a user (I author long legal documents, and create VBA macros to help me), aspects of the question are:-

    A. To what extent you allow individual users to create micro-apps to ease their own daily lives. To give users some sense of control over their daily work flow (therefore happiness), I would say yes, as long as the user can demonstrate a reasonable level of competence so they are not likely to do any damage, and as long as their micro-app is not likely to become important to or conflict with a business process (e.g. its only purpose should be to help the user to be a more efficient, given their own working style : to grease the cog, not replace the cog).

    B. To what extent you allow them to be shared with other users / other PCs. I think that this should not be the case, without IT department adoption. (To continue the metaphor, the grease needs to be suitably generic to work for all).

    For eaxample, my IT department know that I have re-implemented / augmented a number of built in MS Word functions. E.g. I re-engineered the cross-reference dialog in my own UserForm to make it more efficient to use when inserting lots of cross references and managing the start and end points of lots of associated hidden bookmarks, including functions to heuristically guess the paragraph a manual cross reference is pointing to and replace it with a field and appropriate bookmark. It saves a few thousand clicks, and therefore time . They freely let me do it and use it, after I demonstrated it to them, but on the understanding that I do not share it with any other member of our team without their agreement.

  2. Julian says:

    PS. I would also add, that our firm did not permit our IT team to create the macros for me (i.e. they wouldn’t even consider my ideas), and even if they had done so, it would have been 2 years before the work was started (given resources and priorities). So I had little choice but to do it myself and show it in practice. That is possibly a third aspect: C. How active is the IT team in engaging with user ideas?

  3. Julian says:

    PPS. Sorry, last comment (call me Grumpy Old User).

    I think your comment “the user spends more time playing with his spreadsheet creation than he should – losing focus on the job for which s/he is actually being paid” is spot on. I have to discipline myself strongly to writing only at home, in the evenings and at weekends.

    I also think the comment “the spreadsheet acquires a noxious sludge of user-written VBA (shudder).” is again spot on. As a non-professional programmer, I wrote a suite of macros to auto-generate consumer credit agreements (including calculating amortization schedules etc) as my clients were getting them badly wrong. My first effort (whilst accurate) was such a mess of code, that I had no chance of maintaining it myself. I spent some long evenings re-writing to a more maintainable level.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: