Microsoft Excel gets a bad rap! Is it warranted? All too often I hear negative comments about Excel in the work place. Statements like:
- Someone keeps messing up the formulas!
- The data is out of date!
- There are too many copies of the same workbook all with different names!
- Who has the latest “good” copy?
Excel has a long, strong history as an excellent decision support tool that provides figures that translate to meaningful information. But, nothing can be more disruptive to the decision process than the presence of multiple versions of the same workbook and everyone in the meeting believing their copy is the most current. Thus, the bad rap!
Like in the field of medicine—where following proven guidelines to administer the correct drug in the right dosage can work wonders and yet prove fatal when the guidelines are ignored—Excel, when deployed for business on its own, can be frail and is susceptible to failure. Excel needs the support of Company-directed Standard Operating Procedures (SOPs) to be a truly effective business tool.
The first step in developing effective SOPs is assessing the strengths, weakness, and current workflows of the specific resource, as well as cataloging related circumstances contributing to any undesirable results. Armed with a clearer perspective of the various components, the next step is establishing a sound regiment of checks and balances to leverage the strengths, mitigate weaknesses, and reduce the probability of errors and omissions associated to the human factor with the objective of delivering repeatable, sustainable, and desirable results.
What makes Microsoft Excel so popular?
Excel is truly an amazing product. It is one of a few applications on the market that:
- Is tailored for use by individuals with little to no professional development knowledge.
- Provides access and control to the User Interface Layer for clearer visualization of the message (Objective specific presentation),
- Facilitates defining the Business Logic Layer to manage the data appropriately (Predictive Results)
- Offers a full suite of tried and tested functionality within the Program Logic Layer to aid in rapid deployment (Formula, Graphs, Pivot Tables, Output & Sharing options, … etc.)
- Includes a Configurable Data Storage Layer, defining multiple Data attributes (Add/Edit/Delete/Save/Re-call)
All of this and more in one self-contained application!
Excel’s strengths are providing these levels of flexibility and control. There is justification to state that they also contribute to Excel’s weaknesses.
Kudos to the Excel development team for acknowledging and then providing an abundance of tools to address perceived weaknesses. Weaknesses that are within the control of the application. These include: (not a complete list)
- Data entry validation at the cell level,
- Cell, range of cells, worksheet, workbook – Add/Edit/Delete protection,
- Formula – Visibility/Add/Edit/Delete protection,
- Track Changes,
- Share and distribution protection,
- Trust Center – Macros, Add-ins, Active-X, Trusted Publishers, Locations, Documents
- Privacy Protection
- Version Control
However, none of the Microsoft development activity can address the real problem, which is “related circumstances contributing to undesirable results.”
Microsoft Excel + SOPs
I purposely used “related circumstances” as a big umbrella term that engulfs any number of events, actions and activities that influence a desired outcome. The list below is specific to the management of workbooks within the business environment. When developing your SOPs, equal consideration should be given to each related circumstance catalogued in the discovery process.
Examples of SOPs to deal with management of workbooks within the business environment are:
- Discourage copying existing workbook containing data as the starting point for a new workbook. Instead use structured Excel templates with formats and formulas but no data.
- Mandate the use of a formalize, structured naming convention.
- Mandate the visibility of Workbook attributes (title, file location, date/who last modified, … etc.) on all printed output with proper worksheet header and footer discipline.
- Mandate a centralized repository for all distributed or collaborative work.
- Invoke strict Version Control management.
- Restrict ad-hoc distribution and sharing of workbooks. Deploy distribution groups to ensure one-version-of-the-truth is sent to all project partners.
- Destroy (or at least archive) outdated workbooks and remove them from visibility to avoid confusion.
Even without further discussion the list above provides ample justification that Excel needs the support of Standard Operating Procedures to be a truly effective Business tool.
In my opinion “related circumstances” should be the recipient of most of the bad rap and not Excel!
The Evolution of Managed Code
Microsoft Excel workbooks are so ubiquitous they frequently become heavily entwined with a company’s enterprise software that is used for financial management and day-to-day operations.
Most modern enterprise solutions provide Excel import and export functionality. To many providers this represents just another check box on the functionality list with no additional thought required. Other providers actively advocate reducing Excel dependency to a minimum.
The more progressive enterprise solution providers acknowledge and embrace, rather than shun, the important role Excel plays as an effective decision support tool. These providers invest development resources to deliver the infrastructure to properly manage Excel workbooks and dramatically increase their value to the company.
Instead of being islands of information, Excel workbooks transition into a theater of operations referred to as “managed code”. Below are two examples of how managed code can incorporate the flexibility and strength of Excel while delivering repeatable, sustainable, and desirable results.
Example 1
In this model, structured workbooks are stored as BLOBs (Binary Large Objects) in the database and benefit from the routine database maintenance activities like backup and archiving. The term “structured workbook” simply means that all the presentation and business logic needed to properly present data has been completed in prior steps. The sample data used as a proof of concept has been removed leaving just the shell or structured workbook. When used, a copy of the structured workbook is populated with the appropriate data either manually or automatically. This can be repeated multiple times with different data sets without affecting the original template workbook. The resulting functional workbook, when saved, is stored with the associated data as a BLOB in the database. When recalled the entire workbook with the same data as at the time of the save is returned. This model is popular in the Spitfire Project Management System when preservation of the source data for a specific time is required.
Example 2
In this model, as above, the structured workbooks are stored in the database and benefit from routine database maintenance. However, in this model the data is NEVER stored within the workbook. The data is written to and recalled from the database. Each time the workbook is called into action the data is current. This ability to electronically populate the workbook with any system data dramatically increases the workbook’s reach and usability. Add to this the ability to Add/Edit/Delete/Drill-into the database data from within the workbook and this model of manage code reaches a new tier of usability. This model is used in the Spitfire Project Management System for applications like Budgeting, Forecasting, and Schedule of Values billing where the data set is current and the user makes changes to update the database.
Win-Win-Win
The combination of Microsoft Excel’s strengths with the security and strength derived from a relational database and the structure protocols of managed code is a decisive win-win-win in any discussion related to Excel’s “bad rap”.
In conclusion, Microsoft Excel is like any tool. How the tool is used and what protocols are in place dictate the outcome. What works at a home-DIY site would most likely fail minimum requirements at a commercial job site where management and/or OSHA has oversight. Yet the tool could be as simple as a hammer.
Tweet
If you have been following the recent blogs of my colleagues, the collection portrays an interesting tapestry of middle-class USA. They write about people, specifically about traits like experience and wisdom, effort versus skill, and the status of modern day written communication.
