Eventually, the demands of successful Excel applications outgrow the capabilities of Excel. From a business perspective, you can recognize the need to upgrade the Excel workbook for improved reliability if the workbook has become an essential step in your daily, weekly or monthly production cycle. Even though Excel is a wonderfully productive tool, one or more of the following technical events indicate the need to improve reliability:
- Millions of rows of data stored in multiple workbooks slow 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 evaluate alternative strategies for stabilizing your informal Excel applications.
Rationalize the Excel workbooks
The fastest and cheapest way to improve the performance and reliability of informal Excel applications is to rationalize them. By asking these questions, your Excel wizard can determine if parts of the applications can be deleted:
- Dormant data sources? These are data sources that an Excel workbook accesses but no longer uses the data.
- Inactive workbooks, worksheets, reports and charts? These are Excel components that exist but are not distributed to anyone.
- No longer used columns?
- No longer used rows copied from data sources or calculated?
- Unused calculated cells?
- Experimental worksheets that have never been incorporated into reports and charts?
The appeal of this strategy is its low cost and absence of an externally visible change. Often, there’s a reluctance to take these pruning actions because no one is highly confident they know how all the worksheets interact. Mitigate this risk by copying the Excel application into another directory, exploring various possibilities, and carefully testing the results.
Confirm Excel best practices
Over time, Excel wizards introduce poor practices into workbooks due to time pressure, lack of experience and simple sloppiness. These poor practices lead to calculation errors that undermine our confidence in the calculation results.
Correcting these common issues in informal Excel applications can significantly improve reliability:
- Use one or more available Excel auditing tools to identify potential issues that Excel can’t help you with.
- Resolve all Excel errors such as #CALC!, #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, #SPILL! and #VALUE!
- Resolve Excel warnings such as possible range issues, inconsistent formulas, circular formulas, and VBA alerts. Excel displays a triangle in the cell’s top-left corner to highlight these warnings.
- Resolve Excel warnings that are displayed as pop-up text.
- Never turn off warning messages.
- Unhide error values and error indicators in cells.
- Address security alerts and Policy Tips in the Message Bar.
For an expanded list of Excel best practices, click on this link or search the web to browse many excellent resources.
Introduce a DBMS
Excel is not designed to manage a lot of data, even though many professionals are sometimes pressured to use Excel for this purpose. Attempting to use Excel for significant amounts of data in multiple worksheets with relationships among those worksheets invites a data integrity disaster.
Once you have a lot of data, an alternative is to separate the calculations from the data and manage the data with a database management system (DBMS). That separation will:
- Improve the ability of analysts to maintain their Excel workbooks by removing data that obscures the processing logic.
- Improve performance because much of the data selection and summarization 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, analysts 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.
Introducing a DBMS 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.
- Your organization supports a DBMS.
The development 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.
Introducing a DBMS introduces an ongoing cost to operate the DBMS.
Information technology offers many feasible solutions to maintaining reliability in informal Excel applications so that they can continue to deliver business value.
Yogi Schulz has over 40 years of information technology experience in various industries. Yogi works extensively in the petroleum industry. He manages projects that arise from changes in business requirements, the need to leverage technology opportunities, and mergers. His specialties include IT strategy, web strategy and project management.
The commentaries offered on SaskToday.ca are intended to provide thought-provoking material for our readers. The opinions expressed are those of the authors. Contributors' articles or letters do not necessarily reflect the opinion of any SaskToday.ca staff.
