Many companies have a couple of Excel sheets in their organization that (when they work) really help out internal business processes. While Excel is a very powerful tool it has some weaknesses and one of them is how hard sharing is.
The biggest problems with Excel are:
- It’s hard to keep track of the latest version of an Excel file
- It’s hard to understand where it is safe to input data
- It’s hard to save your work since it requires new files and breaks the versioning
- It’s hard to develop and maintain
However, the formulas and knowledge embedded in the Excel sheets can make an entire business work and be profitable. A web application, internal or public, could be a good solution to solve the sharing and latest version handling while keeping those parametric formulas running behind the scenes.
Keeping a live Excel sheet embedded inside a web application is a recipe for disaster (Excel is not built for that). Therefore we like to provide some guidelines on how to migrate from an Excel sheet to a web application.
We of course recommend our CAD-configurator platform DynaMaker for doing this. But pretty much any web application should work with this approach.
Steps to migrate from Excel
Step 1 – Separate inputs and outputs
Since there is no beginning or end of an Excel sheet, it’s really important to figure out where the inputs and outputs are. Changing the colors of these cells and moving formulas to a summary page could simplify the migration.
The inputs and outputs should be kept as similar as possible in the web application to ensure that validation can be done easily. Keep this in mind when you start the project!
Step 2 – Collect test cases with inputs and expected outputs
When you have a clear definition of your inputs and outputs you can start collecting test cases. It’s very rare to have them for Excel (not very testing friendly) so you will most likely need to create them from scratch. Start with basic cases that have occurred in the organization recently (quotations, orders, etc).
Step 3 – Solve a few basic cases from input to final output
Solving everything at the same time often results in endless projects with a high risk of failure. Start with a few basic scenarios and ensure that it works from the inputs all the way to the computed outputs. You might have to mock some values and wait with the implementation of those formulas.
Example: If you have a size condition somewhere in your Excel sheet (if x < 1000 then ... else..) you start by implementing the formulas connected to the simplest size range only. Do not migrate formulas from the other case(s) until you have the first set working.
Ensure that the test cases from Step 2 work for the basic cases. It could also be a good idea to have a hotline to call the author(s) of the Excel sheet to cry a bit when things do not make sense.
Step 4 – Convince potential users of the application (sales, market, client) to do a test run to give you feedback
An unreleased application is a potential success that will take everyone with awe. In reality, newly developed software is rarely welcomed with enthusiasm and getting people to use it takes hard work. It’s crucial to get feedback early from potential users of the application to help set the priorities straight. Sit down with a potential user as soon as possible and guide them through how things are set up and take note of their thoughts and feedback. Collect everything they say and divide the feedback into “nice to have” and “need to have”.
Step 5 – Work towards a Minimum Viable Product (MVP)
Keep adding cases and make improvements until you have enough formulas and logic migrated so that it could solve some cases for the user, like handling a few of the quotation requests that the company receives. Launch this to a selected group of users with the side note “it’s not perfect, it’s not all, and yes, more is coming”.
Ensure that users still have access to the original Excel sheets, so that they still can go through with cases that the new application does not yet handle.
Step 6 – Migrate and release, migrate and release
With the MVP out, you can keep migrating, creating test cases and releasing.
Since the distribution of the latest version is easy with a web application, you should be able to migrate the rest of the rules step-by-step and make continuous releases to your users. This will ensure gradual adaptation to the new application throughout the organization.
One day you will have migrated the whole Excel sheet!