Ditch the spreadsheet
Microsoft Excel has flooded the world in data. So how do you build an information ark to survive the deluge?
There is a complaint that I hear often in the corporate corridors and meeting rooms that I frequent; “We’re data rich but information poor!” The cry is inevitably from some beleaguered executive attempting to make sense of yet another voluminous spreadsheet comprising hundreds of rows replete with undecipherable formulae and references.
The concerns are always the same: have I got the latest version? How do I know if these formulae are correct? Where did this data come from? How reliable is it? Who else has been editing this document? Which tabs are the ‘live’ ones? Where do I make my changes? Who do I send this to next?
Excel is ubiquitous. It’s used in every organisation that I’ve ever worked for, as a calculator, as a modelling tool, as a notepad, a messaging system, a scheduling app, a planner, a business intelligence tool, and yes, as a database too.
Consultants like me are fond of saying ‘Microsoft Excel is the most popular database on the planet’. Which is an odd thing to admit to, especially as Excel isn’t a database at all, and more especially because Microsoft is also the author of two of the most popular actual database products on the planet: SQL Server and Access. One wonders how frustrated the product architects must be to know that 99 times out of a 100, users will choose to implement Excel rather than Access as their desktop database solution.
Yet in my experience, wherever there is a business processes whose data handling requirements are supplied by an Excel spreadsheet, there is a high probability of waste being generated, or that the process adds less value than it could. Excel – when used as a database - is almost always an indicator of underlying or latent inefficiency.
That’s not to say that Excel doesn’t have a place on the corporate desktop. It does, and I am actually a big fan. When deployed appropriately, Excel adds value and reduces cost; the problem is that it is rarely deployed appropriately. Instead, it’s usually the ‘go to’ solution for every project irrespective of its suitability for the task at hand.
Why is Excel so popular?The reasons for Excel’s emergence as the de-facto solution for data handling are manifold. It is by any measure a superb application, well-engineered and more or less bug free. In the right hands, it provides more functionality per $ than any other piece of software that I can think of. Building a case for its deployment is remarkably straightforward:
There appears to be a compelling case in favour of the spreadsheet-as-database. But the benefits and advantages of Excel camouflage significant problems which inevitably present themselves once their parent projects and processes develop and mature.
The case against:
It’s no coincidence that the pitfalls of deploying Excel as a database mirror the advantages. This is because the problem isn’t Excel per se. The problem is how and when it’s used. The adage that ‘when the only tool you have is a hammer, everything starts to look like a nail’ could have been composed for these business cases.
While the Excel application itself is reliable and robust, the spreadsheets that people create using it are too often fragile. There is no easy way to trace where the data has come from, and with no concept in Excel of a transaction or an audit trail it’s too easy to overtype the contents of a cell without realising it. You may never discover that a colleague was inadvertently typing dates into a value field. Furthermore, reconciling and testing a large spreadsheet is a daunting task that few people are prepared or qualified to undertake. Where you find spreadsheets you’re invariably in the presence of errors – it’s just that you’ve no way of discovering where they lie.
What is the alternative?Over the last two or three years a number of powerful, low-cost, cloud-based tools have emerged which offer businesses the opportunity to build real applications to meet the needs of their departments and workgroups. The solutions that can be created with this new generation of on-line tools are robust, secure and reliable, but they also offer many of the same advantages that spreadsheets do, without the corresponding disadvantages.
Like spreadsheets, these tools are simple to use, low cost and quick to deploy. They empower non-technical users to create sophisticated applications without learning to code. Being cloud-based they can be deployed seamlessly on any device from desktop to tablet to mobile phone. Most importantly, they require little or no input from the IT department because there is no software to install, no servers to configure and no local data to back up. The solutions are genuinely multi-user in that they can support many users editing, creating and viewing the data simultaneously and they are scalable: suitable for one user or a thousand users. The only application a user needs to have installed on their device is a web browser.
Two of the most successful players in this market sector are Knack* and QuickBase. Of the two Knack* is perhaps the most interesting success story: the company was formed just over three years ago in New York by a small team of frustrated web developers. Fed up with constantly writing and re-writing the same application under a different guise they asked themselves a simple question:
“What if we could build a web application that could build web applications?”
This simple idea would result in the creation of a real killer app which within 18 months would be adopted by thousands of businesses around the world. Knack* is now used by blue chips and start-ups alike, including many household-name corporations such as:
A typical Knack* project
The gestation of a Knack* project will start in much the same way as an Excel database project. Someone needs to collect and manipulate data and then communicate the results. No one wants to create a software development request as that’s likely to sit in the IT Department’s pending folder for longer than the project will take to complete. Besides there probably isn't time to scope out everything that is necessary at this stage, the team may not yet be ready or qualified to specify what they need in detail. What is required is something to ‘get started with’, something that can then be developed and fine-tuned as the project progresses. This is usually where Excel enters stage right, and this is where Knack* can provide a superior solution – and by superior, I mean more flexible, more cost effective, less prone to error, easier to deploy, secure and simpler to build and use.
I should declare an interest at this point. I have been using Knack* for over a year now and I am a huge admirer of the product. I have spoken to the founders, Eric and Brandon, via Skype and have found them to be smart, dedicated, customer-focused and passionate about their application. They have been hugely successful and deserve every accolade that they are presented with. Knack* is a game changer, so much so that it has already started to spawn copy-cat and ‘me too’ competitors. The press is referring to this as the emerging ‘age of the citizen developer’ and the beginning of a new era in ‘frictionless data’. It sounds grandiose, but there is real potential in Knack* and products like it to change the way businesses deal with data that doesn’t form part of the corporate data repository.
A typical Knack* project will be concerned with data collection, usually via input forms on a web-based application (see the example below) followed by data presentation and analysis. Like Excel, Knack* provides a suite of standard charts and pivot table views to visualise information, though the key differentiator is that all of the analysis and ‘drilling down’ is done via an intuitive user interface rather than a complex spreadsheet.
A key advantage of using a real database to store information is that it is transactional. This means that any change to the data is audited so that you know who added, deleted or edited a record and when it was done. This permits far greater data integrity than could be achieved with a shared spreadsheet.
The authors of Knack* have also given a good deal of thought to the typical projects that are likely to be supported by a Knack* application and have created useful ‘wizards’ that can import a Spreadsheet and build a basic Knack* database complete with input forms and views completely automatically. This allows new Knack* developers to get up and running quickly.
Mindful of the advantage of customers being able to integrate Knack* into their existing websites the developers have also made it very easy to embed a Knack* application into a standard web form. An embedded Knack* application also ‘inherits’ the style sheet of the host website so that it takes on the appearance of the client’s corporate identity with regard to fonts and colours. There are also powerful integration features to enable Knack* applications to ‘talk with’ other on-line systems. While these capabilities are generally for the more advanced or technically experienced user, they do allow Knack* to operate seamlessly with other hosted systems such as SalesForce, XERO, Google Docs, MailChimp, DropBox, PayPal, Facebook etc. as well as leading commercial databases such as Oracle and Amazon Dynamo.
The rise of ‘frictionless data’ and the ‘citizen developer’
The emergence of simple, cloud-hosted database applications such as Knack* has led to many businesses re-evaluating their approach to data collection and presentation. Excel is no longer the only option, and certainly shouldn’t be considered the default option. In addition to the clear technical, security and productivity advantages there are other benefits to the adoption of cloud technology which shouldn’t be overlooked:
Teams given the creative freedom to design their own solutions, or at least to influence the way in which their applications are built, experience a significant productivity-increase. Part of this comes from the empowerment that using ‘the right tool for the job’ brings (the hammer is no longer the only tool in the box) but much of the morale boosting effect is a result of being liberated from the strength-sapping delays that are part and parcel of having to deal with an over-worked, under-resourced IT department. Many companies allow their ‘citizen developers’ to build non-core solutions entirely independently of their IT function. As cloud-based applications require only a web browser and an internet connection there may be no compelling need to initiate an ‘IT project’ at all.
It seems that the revolution in cloud applications has started and that Excel may be the first casualty. This is a good thing for most businesses. Excel still has its place, but that place is for a niche spreadsheet application; a calculating, analysing, modelling ‘what if’ tool, not a database. Data collection and management is best handled by a real database, one that can enforce integrity, validate inputs effectively and secure corporate information reliably. In the past, systems like this were beyond the reach of most of the business, only the IT Department had the necessary skills and underlying technology to create and support such a solution. Fortunately, things have moved on and there are leaner, more creative and more productive options available.
The Irrational Project Manager