Everything you need to know about migrating your informal Excel application to a more robust platform.
Engineers may be the world leaders in misusing Excel, but they love using it all the same. While there are ways to use Excel more effectively, eventually every engineer will have to face the fact that their workbook-based application has outgrown Excel.
The first warning sign is when a workbook has become an essential step in your daily, weekly or monthly production cycle. It may no longer be able to serve its critical business function, especially if any of the following ring true:
- Millions of rows of data stored in multiple workbooks are slowing performance.
- Identifying and resolving data quality lapses is approaching a full-time job.
- Calculations have become so complicated that no one is confident that the results are sufficiently accurate.
- When a crash occurs, resolving the problem takes too much elapsed time.
- The latest wizard assigned to keep the Excel application going has been headhunted, promoted, transferred or laid off.
Now what? It’s time to upgrade your informal Excel workbooks to a robust and reliable production-quality application. Here are several strategies that engineers can use to move beyond Excel.
Develop a custom application
The most radical approach to replacing your informal Excel application is with a custom application developed by IT professionals using a high-level programming language. Example programming languages include Microsoft C#, Oracle Java, or Python from the Python Software Foundation.
This new custom application will provide the following benefits:
- No limitations on the number of concurrent end-users, the number and size of data sources and the complexity of calculations.
- High performance.
- No limitations on the number of concurrent end-users.
- Responsive to your current and future requirements.
- Can be maintained and enhanced by software developers familiar with the chosen programming language.
A custom application has higher development and maintenance costs than the other alternatives. The implementation cost is a function of the data volume that must be migrated.
Develop a low-code application
If a custom application is too costly or overkill, replacing your informal Excel application with a low-code application developed by IT professionals may be a viable solution within various constraints. Examples of vendors offering low-code tools include Appian, Microsoft, OutSystems, Quickbase, and ServiceNow.
This new low-code application will provide the following benefits:
- A low-cost software development project, because developing a custom application using low-code tools is incredibly productive.
- A low-cost implementation, because migrating the data and orienting one or two end-users should be straightforward.
- Clear demarcation between software and data. That will make software maintenance easier, leading to a more reliable application.
- Responsive to your current and future requirements.
- Can be maintained and enhanced by software developers familiar with the chosen low-code tool.
A low-code application does have a few limitations. However, they are much less restrictive than the limitations associated with your informal Excel application. The limitations include:
- A low maximum for the number of concurrent end-users.
- A limit on the number and size of data sources.
- A limit on the complexity of calculations.
A low-code application will require some ongoing development and maintenance costs.
Implement a SaaS solution
The variety and richness of available Software as a Service (SaaS) solutions from various vendors are astonishing. Engineers may discover that many other organizations require similar functionality by investigating possible SaaS solutions to replace their informal Excel application. As a result, a SaaS solution has become a viable software business.
The action is to implement the SaaS solution, migrate the data and decommission the informal Excel application.
The benefit of the SaaS solution is that it:
- Exposes your staff to the thinking of an entire end-user community about this application.
- Outsources application maintenance. You no longer depend on a wizard who understands your informal Excel application.
- You avoid the application development cost, risk and elapsed time of the previous alternatives.
The operating cost of this alternative is largely the SaaS vendor’s monthly fee.
Introduce a DBMS
Excel is not designed to manage a lot of data, even though engineers are sometimes pressured to use Excel for this purpose. Attempting to use Excel for more significant amounts of data in multiple tables with relationships among those tables invites a data integrity disaster.
Once you have a lot of engineering data, an alternative is to separate the calculations or processing from the data and manage the data with a database management system (DBMS). That separation will:
- Improve the ability of engineers to maintain their Excel workbooks by removing data that obscures the processing logic.
- Improve performance because much of the processing can be shifted from the workstation to the DBMS server.
- Dramatically reduce the size of the Excel workbooks because much of the data has been moved to the DBMS.
A DBMS offers these benefits:
- Handles large volumes of data.
- Supports concurrent access for many end-users.
- Supports multiple views of the data.
- Maintains data integrity.
- Ensures consistent, reliable data.
- Can ensure a single, consistent algorithm for calculated columns.
- Enables data sharing.
- Ensures data security.
- Enables sophisticated backup and disaster recovery.
With a DBMS, engineers can continue to use the wonderfully flexible Excel user interface to extract the required subset of the data from the DBMS, perform calculations and create reports and charts.
This alternative is viable only if:
- The problem with your informal Excel application is the data volume and not the complexity of the calculations.
- You can continue relying on a wizard who understands your Excel calculations.
The cost of introducing a DBMS is typically proportional to the number of:
- Data sources.
- Tables in each data source.
- Excel worksheets that need to be revised.
This alternative introduces an ongoing cost to operate the DBMS.
Integrate the functionality into an existing application
Sometimes an existing formal application performs the same or closely similar function as your informal Excel application. This duplication of functionality occurs when:
- The implementation of the formal application ran out of money or resources before all the organization’s divisions were migrated.
- Two organizations were never fully merged after an acquisition.
In any case, the action is to migrate the data to the formal application and decommission the informal Excel application.
The appeal of this alternative is that it eliminates the Excel application and its ongoing operating costs without adding any new systems to the organization’s application portfolio.
—
Information technology offers many feasible solutions to migrating your informal Excel application to a more robust solution that can continue to deliver business value reliably.