Convert Excel to a web application: replacing a shared spreadsheet with custom software

The file started as a simple table, kept by one person. Then the whole team started filling it in. Today it tracks stock, orders or customer records. It has become indispensable.

At this stage, the spreadsheet does the work of an application, without an application’s safeguards. Turning it into an application does not mean reinventing everything: the file is the starting point.

An Excel file shared between several users: when the spreadsheet becomes an application

To tell whether your spreadsheet already does the job of an application, you first need to know what an application is. The clearest definition we have found is Bob Baxley’s, in an article published in 2003 by Boxes and Arrows, an online magazine about interface design. According to him, an application has two observable properties. It lets its users create, modify and store data permanently. And it sets up a one-to-one relationship with each of its users.

An Excel file shared by several users already meets the first condition: each of them creates, modifies and stores data in it. It often lacks the second. In a file sent around by email or dropped into a shared folder, nothing limits what each person can change, and nothing says who changed what.

Your file has reached that point when:

• Several people edit it every day, sometimes at the same time.
• Copies circulate by email, and nobody knows which one is right.
• Only one person understands its formulas and macros.
• It is used to run a whole area of the business: stock management, sales management, tracking call-outs.

The limits of Excel: errors, and who sees them

A spreadsheet filled in by hand has its errors too. In 2000, Raymond Panko, a researcher at the University of Hawaii, brought together audits of real spreadsheets carried out in companies. The most recent audits, which used the most rigorous methods, found errors in at least 86% of the files examined. Most of them counted only serious errors. A wrong figure in a spreadsheet then finds its way into the decisions based on that file.

This figure is disputed. In 2009, three researchers at Dartmouth College, Stephen Powell, Kenneth Baker and Barry Lawson, judged that the idea of widespread spreadsheet errors rested more on anecdotal observations than on solid research. They then audited 50 spreadsheets used in organisations. They found errors in 0.9% to 1.8% of the cells containing a formula, depending on how they were counted. The rate varied widely from one file to another.

The two measurements do not say the same thing: one counts the files that contain at least one error, the other the wrong formulas. In both cases, it took an audit to find these errors: a spreadsheet does not flag a wrong result.

The spreadsheet has technical limits too. In October 2020, in England, 15,841 positive Covid-19 cases were left out of the national figures, from 25 September to 2 October. The test results went through files in .xls format, limited to 65,536 rows, and the extra rows disappeared on import. The case concerns a large public organisation. Yet it shows what lies in wait for any file that grows: beyond its limit, data can disappear without anyone being alerted.

If your business depends on a file, ask yourself who would spot an error, and when.

Excel or a database: where your data lives

In an Excel file, data and calculations are mixed in the same cells. In our view, a formula overwritten by a value typed by hand goes unnoticed.

An application separates the two. The data is stored in a database, in the same place for every user. The calculation rules are written in the application’s code: nobody can bypass them, or replace a calculation with a value typed by hand. Each user signs in with their own account, and the application knows what they are allowed to see and change.

A business application is used to track your orders, your call-outs, your case records and everyone’s access rights. It often replaces a spreadsheet, or several tools into which the same information is entered several times.

Turning Excel into a web application: what changes

There is only a single version of the data. Nobody works on a copy of the data any more. When an order is entered, anyone who is allowed to view it sees it.

The calculation rules no longer depend on a person who knows the file.

Your application can also connect to the software you will not replace, such as your accounting or invoicing software. The same information is then entered only once and stays consistent from one piece of software to the next.

And when a task consists of copying an invoice or a purchase order into the file, AI can read these documents inside the application.

Moving off Excel to other software: off the shelf or bespoke

If off-the-shelf software already covers your need, and its vendor makes the adjustments you ask for, it remains the simplest choice. For an activity that does not set you apart from your competitors, adapting to the tool is often the right answer: Martin Fowler’s distinction helps you decide.

Sometimes your file came about because no off-the-shelf software fitted the way you work. Another standard package then brings back the same problem: the team keeps the spreadsheet on the side, for what the tool does not cover.

That spreadsheet kept on the side, outside the tools the company has chosen, is a case of shadow IT.

Developing custom business software: start from the file

The file already describes much of what you need: the columns list the data tracked, the formulas the rules applied, the tabs how the work is divided.

Development therefore starts from the file, and from the people who use it. We begin with the few features they need most, so that they can stop using the file for that part. This minimum viable product checks that the application meets the need before going further.

The rest of the file stays in use in the meantime. Each feature that goes into production replaces part of it, until nobody needs it any more.

DEVEDANOS: your file becomes a business application, for a fixed monthly budget

At DEVEDANOS, we develop your custom software, usable within a month, for a fixed monthly budget, and we adjust it continuously as your business evolves. The budget is built with you, from your figures and your goals: together, we set the amount and the problem your application has to solve.

We start by talking with the users of your application, to understand their needs. From these needs, we specify each feature, then we develop it. Your application’s users work with it from the first month of development, and how they use it determines the next feature or features.

What has been accepted goes into production. Every day, you see the application progress on your acceptance environment.

And so that your file’s calculations stay correct in the application, we apply a zero-regression guarantee: every regression is fixed and covered by an automated test so that it does not come back, at no extra cost.

The source code, infrastructure, deployment pipeline and documentation are your intellectual property once the first monthly invoice is paid. A trial period of up to 15 working days in the first month: no invoice if you do not want the deliverables.

Our page on custom business software development shows what we build: back offices, dashboards, client portals, monitoring tools.

Further reading

How much should you plan for this application? Our article on budgeting an application explains why you should aim for a goal rather than a list of features.

Does your team run a whole part of the business from an Excel file? Shall we talk?

Sources

• B. Baxley, “What is a Web Application?”, Boxes and Arrows, 20 January 2003 (the two properties that define a web application: modifying and storing data, a one-to-one relationship with each user).
• R. R. Panko, “Spreadsheet Errors: What We Know. What We Think We Can Do”, proceedings of the EuSpRIG symposium, July 2000, arXiv 0802.3457 (errors in at least 86% of the spreadsheets audited).
• S. G. Powell, K. R. Baker and B. Lawson, “Errors in Operational Spreadsheets”, Journal of Organizational and End User Computing, 21(3), 2009, pp. 24-36 (50 spreadsheets audited, errors in 0.9% to 1.8% of the cells containing a formula).
• The Register, “Excel spreadsheet blunder blamed after England under-reports 16,000 COVID-19 cases”, 5 October 2020 (15,841 cases left out from 25 September to 2 October 2020, .xls files limited to 65,536 rows).