Don’t blame the spreadsheets: 3 tips to avoid losing control of your data

D

I recently came across an interesting blog post from Adrian Reed. In his post, he argues that spreadsheets are evil and that they should not be used for any serious analysis in an organization (well, he’s not painting them as bad as this, but this explanation helps me to support my own post 🙂 ).

Although I agree with some of his ideas, I think that spreadsheets, when they are well used, are a really poweful tool to help organizations succeed. When they create problems, they are often only a symptom of a bigger problem. Even if they sometimes create some, why are spreadsheets so common in organizations? And what can we do to prevent these problems?

Why are spreadsheets so popular?

Spreadsheets popularity can be summarized with these two words: control & flexibility.

Spreadsheets are almost as used in organizations as PowerPoint presentations, although they are probably less deadly. In my opinion, people use spreadsheets for one main reason: they give them control over information, and allow them to take decisions based on facts instead of only their intuition. In a world where information overload sounds more and more like a professional sickness, spreadsheets can help people to get their head out of the water.

Moreover, spreadsheets are flexible and allow users to work with the data the way they want. Should anything happen, they can remodel their spreadsheets on-the-go and get the information they need almost in real time. They are not limited by preformatted reports, nor they have to wait for changes to be implemented to their systems before performing their analysis.

A good example of this can be found even in larger organizations with multi-million dollars systems implemented to support data analysis and decision process. In many of these organizations, people indeed rely on these systems to interpret data. However, in many cases, they will also export some of the data into a spreadsheet to get a better grip on it, and perform ad-hoc analysis.

In my mind, this illustrates very well that spreadsheets do answer some users’ needs. Spreadsheet eradication will not be an easy task.

How to make them work?

So much flexibility & control over data can lead to many problems; Adrian described some of the pitfalls associated to spreadsheets in his post. But the problem here aren’t the spreadsheets; spreadsheets are only there because they answer these users’ needs. If we want to prevent these problems, we need to provide some guidelines to spreadsheets users (since we can’t wait for our big systems to answer every ad-hoc needs from every user).

I’m not a business intelligence expert, but from my experience working in both small and large entreprises, these best practices should help you get the best from spreadsheets while minimizing their problems.

1. Learn how to use your spreadsheet application, for real

Everyone knows how to use Microsoft Excel, yet many Excel experts still don’t have a clue how to use pivot tables, one of its most powerful function.

In order to use spreadsheets efficiently, you need to know basic and advanced functions of your application (like Microsoft Excel or OpenOffice Calc). Ask your colleagues about their own spreadsheets, ask them if you want to know how to do something specific, and share your work and knowledge with them. Google your spreadsheets questions, and you’ll get thousands of tips & answers.

In other words, become a spreadsheet ninja.

2. Standardize your data sources and create data dictionaries

One of the major issues with spreadsheets is when people give data a wrong meaning. This can lead to bad decisions, leading to more or less important consequences. Like with any information, context is the key to provide meaningful insight from data.

In order to achieve this, a centralized source of data (or many unique sources) is essential. To make this source even more useful, a good data dictionary describing each piece of data will allow a standardized understanding of the same information by various people.

This will ensure the integrity of data knowledge without reducing the flexibility of your users.

3. Monitor spreadsheet usage and look for recurring trends

By providing a good framework to your now spreadsheet-knowledgeable users, you should prevent problems brought by democratizing access and analysis of data.

However, having so much data lovers around your organization can lead to some inefficiencies. For example, you might find out that many people will perform the same analysis in different departments. In this situation, creating a standardized automated report distributed among these users would increase their productivity.

In order to deal with these inefficiencies, you need to proactively monitor what’s done by spreadsheets users, and capture any common reporting and analysis needs. This can easily be done by business analysts in your organization, through their various contacts with users.


By implementing those tips, you are creating an environment where data-driven decisions can be made easily and where transition to more advanced business intelligence system is facilitated, whether you’re a small enterprise or a multinational company.

Do you like what you’re reading?

Let’s keep in touch! Subscribe to my newsletter and be notified when I publish something new (about once a month). You can unsubscribe at anytime!
By submitting this form, your information will be shared with MailChimp, which I use to manage my newsletter.

Do you use spreadsheets instead of corporate systems to analyze data? Have you seen problems (or successes) related to spreadsheets use? Please share your experience with us in the comments below, or in the social medias (FacebookTwitter, or Google+)!

Image credits: Stepri2003 @ Wikimedia Commons

4 comments

This site uses Akismet to reduce spam. Learn how your comment data is processed.

  • Hi Eric, I’m really pleased that my article spurred some discussion and that you wrote this article. You make some interesting points and I really enjoyed reading your blog. It’s always great to be part of a debate 🙂

    On the whole, I think we are probably in agreement far more than it may seem. I was certainly not arguing wholesale against spreadsheets — there are certainly times when they are useful. I certainly agree with you that where spreadsheets are used, it’s important that we know how to use the spreadsheet software, and that we interpret the data correctly (I could talk for hours about the need for proper data models…. But that’s a whole different story 🙂 ) However, even with this in mind, I would argue that we need to break the pattern in organisations where spreadsheets become the *default* and the *norm*, and ultimately that we ensure we’re using the right tool for each job. Spreadsheets are just fine for some jobs – but there are many other jobs that they really aren’t best for.

    Take an example: A customer services team may find that that the core application that they were provided with didn’t have sufficient ‘notes & tasks’ functionality, so they instinctively choose to create a spreadsheet to capture notes and tasks related to specific customer accounts (although this sounds far fetched, it really does happen….). When a customer rings, they would have their core application open… and they’d access their version of the spreadsheet too. Different teams increment it over time, and there are multiple version of the spreadsheet. …. and if a customer rang a *different* department, they’d have no knowledge of that data at all.

    In that circumstance, the *real* problem is that the core application didn’t meet the end-users needs… so they (understandably) supplemented by building a spreadsheet workaround. Yet a far more scaleable response would have been to understand the real need of all stakeholders, and assess the options. A spreadsheet would be one option — but it may well be better and more effective in the long run to store the comments on the core application. The key is to have the discussion and make the decision consciously.

    Conversely, in other cases, a spreadsheet might be appropriate. To quote my original blog post “When we see our stakeholders reaching out for a new spreadsheet, it’s incredibly important to take a step back to ask “what are we trying to achieve here”, “what are our needs and requirements” and “is a spreadsheet the only (or best) tool”? ” In some cases, a spreadsheet will be best. In others it won’t. The important factor, in my opinion, is to make the decision *consciously* and not just revert to Spreadsheets by default.

    Thanks again for your comments, I really enjoyed reading your blog and it’s great to be part of the discussion 🙂

    Adrian

  • As Keynes said “in the long run we are all dead”. It is all very well to talk about taking a step back and evaluating what is the right approach to a particular technology issue.

    Most people already know that the “right” approach is not to use a spreadsheet. However the reality is that it is usually a choice between a spreadsheet or a 5 year business budgeting process to get your requirements onto and then delivered from the IT resource road map. In 99% of such situations a spreadsheet is the only answer that can do the job….not just for that day but for the next 5 years.

    The main problem with spreadsheet use is the continuous demonizing or paternalistic/demeaning approach to this amazing piece of productive technology. The result is that users are embarrassed to admit what is going on and organizations live in denial about how their operations are really conducted.

    If only spreadsheet technology could be granted something of an amnesty from this continuous onslaught by self-interested professionals then the associated transparency would start to position spreadsheets and systems as the yin and yang of financial processes. i.e. spreadsheets (when properly monitored and maintained) enable a rapid agile approach to new business requirements. If this new need becomes a long term production requirement then it should go on to a prioritization queue for IT to replace and the spreadsheet retired – but this will always be a multi-year process.

    Of course the reality is that no-sooner has one spreadsheet-based process been retired than a new one will emerge (unless the financial world stops changing).

    A version of spreadsheet technology (but not necessarily Excel) is therefore an imperative strategic requirement for many decades to come.

  • Ralph, Adrian,

    Thank you both for your comments, it’s always interesting to confront some of my ideas with other BAs.

    I think we can generalize our thoughts by saying that there are good spreadsheets (ie the ones focused on specific, ad-hoc users needs that wouldn’t justify an IT project, and that have an individual focus) and bad spreadsheets (ie the ones implemented because some users needs aren’t supported by the systems, and that are used by many users for collaboration).

    Therefore, our mission should be to keep the former (because they efficiently fill business needs) and kill the others (because they can reduce business processes performance over time by making information less reliable / available).

    In the end, it all comes down to a good requirements definition at the beginning, which is a excellent thing for us BAs of the world 🙂

    Eric

    • Thanks Eric but by referring to ‘bad’ spreadsheets you perpetuate the myth that somehow these particular business solutions shouldn’t exist.

      If there is no other solution to hand to solve a problem for today, tomorrow and the year after then it is still a good solution – as without it you would have nothing and no solution. It might not be the best possible solution but you are falling victim to the so-called ‘nirvana fallacy’ (the best is the enemy of the good).

      If you had 100 times as many BAs and 100 times the IT resources then certainly many more of the spreadsheets that you dislike would be replaced. The problem is that any organization that ran itself like this would be bust.

      The challenge for an organization is therefore to understand what is really going on with operational spreadsheets and use appropriate risk assessment/efficiency criteria to prioritize the right projects for IT.

      However, if you then suggest that what is left is ‘bad’ then people just won’t tell you about it. And you will reinforce the often-stated view that IT-minded people will never understand business drivers.

By Eric Provost

Your host

Eric Provost

Making sense out of chaos as a BA & UX specialist

Recent Posts

Recent Comments

Archives

Categories

Meta