Understanding the 7 Types of Spreadsheets
The first step of managing Excel Hell
Excel Hell. The age-old problem that will not seem to go away. It’s like a bad penny that always turns up. Over the past 20 years, business computing has seen an unprecedented evolution in analytics technology — everything from the cloud, to visually rich presentation, to vast in-memory high-performance computation. Yet, despite these advancements and the promise of seamless interconnected collaboration, here we are — companies small, mid, and large still haunted by the Excel Hell problem.
The tool Excel, the Microsoft behemoth of the spreadsheet world, is an easy target for scrutiny due to its mass adoption. However, this issue is beyond one tool and is more accurately considered “spreadsheet proliferation.”
Spreadsheet proliferation within organizations can be akin to the Roman Empire dealing with the roving hordes moving in from the Far East. The hordes sowed discord, chaos, and destruction, fueling the ultimate collapse of the realm. How can we capitalize on the positive benefits of spreadsheets without having them sack the kingdom? How can we apply a strategy of spreadsheet management?
Spreadsheet management can be looked at similarly to overseeing people. As people have roles, so it is with spreadsheets. What are the roles of spreadsheets? Which roles are viable, and which ones are detrimental? How do we maximize each to its fullest capacity? Answering these questions can go a long way to determining how to prime your management efforts and engage in a meaningful spreadsheet reduction plan.
Spreadsheet Toxicity
Certain spreadsheet functions are more toxic than others. We can start by prioritizing the harmful use cases that can lead the organization astray and keep the healthier ones in place. Moving away from the toxic functions in small increments counteracts spreadsheet dystopia and proliferation. Like saving pennies, start small, and the results build over time like compounding interest. Focus on being a little bit better today than you were yesterday.
After devoting my career to the field of analytics and working on hundreds of projects across a myriad of organizations, I have seen spreadsheets twisted, turned, contoured, perform numeric handstands, and even proverbial back flips (I am even guilty of using spreadsheets this way myself!). So, take it from someone involved in active spreadsheet combat, like the weathered, hardened war veteran who has done ten tours of duty — I have seen it all.
The Seven Spreadsheet Use Cases
There are at least seven types of spreadsheet use cases across the enterprise:
- Calculator
- Autonomous Model
- Distributed Model
- Distributed Collection
- Tethered BI
- Tethered Performance Management
- ETL Functions
The following diagram illustrates the workflow of each of the seven use cases.
Let us walk through each use case and its function to an organization and determine its impact on Excel Hell and spreadsheet proliferation.
Use Case 1 — Calculator
The calculator use case is utilized as it states — the spreadsheet functioning as an advanced calculator. Simple, quick, ad-hoc calculations, A + B = C. The calculator use case is one of the beneficial aspects of spreadsheets, and an organization should embrace this functionality as an agent of productivity. This use case does not contribute to Excel Hell.
Use Case 2 — Autonomous Model
The ability to model is an essential function of Excel. Modeling is generally defined as the representation of a process, concept, or system to express an idea, gain information, or make predictions. Models can be structured as tabular, hierarchical, visual, or loose text. An autonomous model means the expression of individual ideas is encoded in the spreadsheet. The model is autonomous because the use case is individualistic — meaning the modeling is between the end-user and the machine. It is not shared nor interacted with by other people. The data storage is contained unto itself. Put differently; the sheet acts as a database. The model is being used as a mechanism to explore ideas or gain information.
In the business domain, an example would be if a department head was working through a hypothetical monthly expense budget. The budget may use expense line items as the y-axis, a month for the x-axis, and dollars as the numeric measure. Or perhaps a sales rep is tracking their quarterly commission payouts, with deals listed on the y-axis, time going across the x-axis, and estimated future payouts, optimistic and pessimistic. Or perhaps a project manager is tracing the course of an IT project. A project plan will have activities on the y-axis with time across the x-axis, with hour assignments as the numeric measure and target timelines. All of these are examples of the autonomous model. In every example, they are used to capture ideas, gain information, and model the future. The end-user is interacting with the machine to express this data.
As you will notice, the term “report” is not used here, as a report is usually non-interactive, a pane of glass, such as an invoice, bank statement, or earning release. An autonomous model can act as a report but is also interactive.
Autonomous models are a very healthy use of spreadsheets throughout an organization, allowing for flexible individual analysis of numbers, advanced problem solving, independent thought, the development of sophisticated reasoning, what-if modeling, and self-expression of ideas.
Use Case 3 — Distributed Model
Once a successful idea is formulated, it is a natural human tendency to share and collaborate to help acquire feedback, adaption, and apply rigor. The model needs to be seen by others to gain these collaborative attributes. This ushers in the next type of model — a distributed model. A distributed model is similar to an autonomous model other than the fact it is shared. AKA shared model. What does it mean to be a shared model? In the business domain, models can be used across corporate functions, including sales, accounting, HR, operations, and finance. Interactive sharing requires distribution.
A distributed model could be as simple as an ad-hoc analysis sent to a colleague or, more broadly, a sheet sent in an email distribution list from one department to another. Like the autonomous model, the data storage is contained unto itself. A distributed model could be a formal report sent to management, a quote sent to a customer, or a collaborative working draft shared between project team members on a Windows share drive or the cloud. All of these examples are distributed model spreadsheets.
Unlike the first two types, the distributed model use case starts to tiptoe into slippery Excel Hell territory. This problem is mainly due to the validity of the source data and its disconnected nature. Once data is keyed into a sheet, it becomes a static snapshot in time. This is not much of a problem in situations involving one or two people. However, as the magnitude of involvement increases in large-scale organizations, multiple people keying similar numbers into different spreadsheets becomes an issue. The keying occurs at slightly different time intervals; thus, one person’s figures are slightly more up-to-date. Mistakes and misjudgments further compound this. The reports get distributed in spreadsheets, and the numbers do not match. Data reconciliation issues, thereby creating “multiple versions of the truth,” is one of the quintessential features of Excel Hell.
Once the spreadsheet departs from a local individual’s machine, distributed models can become an organization’s gateway drug of misinformation. If distributed models are used in small dosages, one-off use cases, or limited circulation — no harm, no foul. However, once a distributed model becomes a centerpiece of a significant business process, the road to hell has commenced. The primary symptom to look for is a broad, centralized, recurring distribution of a disconnected distributed spreadsheet.
Use Case 4 — Distributed Collection
The fourth spreadsheet type is a distributed collection model, AKA input template. This model type is similar to a distributed model as it is circulated, although, in this case, there is a feedback loop. Not only is the model shared for reporting purposes, but it is also used to collect information from the disseminated audience. Where the distributed model is read-only, the distributed collection model is read and write. Like the autonomous and distributed model, the data storage is contained in the sheet. In other words, the sheet acts as a portable database. An example would be emailing out an annual budget template. The department heads would type in their numbers and send them back. This situation is a widespread use case of the distributed collection model.
Using spreadsheets as a collection method is the first formal stage of establishing Excel Hell. If the shared distributed model is the gateway, the distributed collection model is two steps into the fire. This situation spans beyond Excel. While Excel is the easy target, Google Sheets is notorious for participating in spreadsheet proliferation due to its out-of-the-box collaboration features. It’s like gas onto a flame. To add insult to injury, many organizations run both Google Sheets and Excel. Furthermore, numerous other spreadsheet technologies are floating around out there, and they can drift past you like jellyfish ruining a perfectly good swim in the ocean. Welcome to a world of pain.
While trying to collect, control, and analyze tons of loosely coupled data points in spreadsheets, mistakes are bound to happen, user audibility is difficult (who changed what cell and when?), complexity can fractal, and the lines of numerical reality can blur. These symptoms equate to mounting frustration for everyone — and the most fiendish of all beasts: strategic company decisions driven by bad data.
Use Case 5 — Tethered BI
A tethered BI model is like the other models, autonomous and distributed, except for an external dependency. This dependency could be in the form of linking-and-embedding, the advanced “get data” feature in Excel, or pivot table features. All the aforementioned examples are tethered in some way to another sheet, file, or connection. The tethered model can act as a receiver or supplier of data. The data source could be relational, OLAP, or unstructured types. The presentation of a tethered BI model is tabular, x-y axis in nature.
Since tethered BI models are connected to a centralized consumable source, they promote reusable and consolidated data points. In addition, they provide a more robust separation of data and presentation. From this perspective, their use case can be helpful to offset and neutralize some of the Excel Hell symptoms, such as user keying mistakes, different versions of the truth, and more organized and integrable access to centralized data. Excel becomes a conduit rather than a storage point, thus minimizing critical information errors, saving users time, and offsetting data source complexity fractals.
Despite the tethered BI model benefits, they are only a partial solution to Excel Hell. Tethered BI models are not conducive to broad distribution due to external dependencies. Their sophisticated nature shrinks the audience to more of a power user community. These factors make sharing difficult and often neutralize the sheets’ collaborative value. Pockets of innovation and emergent third-party cloud-enabled tools are working to solve this problem. Furthermore, while tethered BI models are suitable for reading and analyzing against familiar centralized data sources, they tend to be weak on the write. Thus, not helpful for distributed collection.
Since there is a codependent external relationship within a tethered BI sheet, this can sometimes strengthen their hold within a business process. Per Metcalfe’s law, the more nodes participating in any given system, the stronger the system becomes. Thus, a tethered model can solidify a business process, like cement, good, bad, or indifferent.
Due to the competing positive and negative benefits of the tethered BI model, they need to be evaluated case-by-case to ensure the overall health of an organization and the associated business process.
Use Case 6 — Tethered Performance Management
A tethered performance management model has the qualities of the tethered BI, except it has both read and write to a centralized external data store. In addition, unlike tethered BI, which is bound to a hardened x-y construct, tethered performance management models allow for both x-y axis and cellular level send-retrieve control. In other words, each cell can act as an autonomous agent for independent intersections of information within the same x-y structure.
An Achilles heel limitation of the spreadsheet is its hard binding to two-dimensional x-y axis storage. Tethered performance management tools use Excel-based interfaces and embedded cellular functions to tether the sheet to centralized multi-dimensional OLAP data stores. Thus, the benefits of multi-dimensional in-memory databases can be fully realized, providing more profound levels of analysis, including nested cellular information and dynamic, hierarchical drill down.
Tethered performance management is not a native spreadsheet function of Excel. There are many third-party tools on the market to help facilitate this. Detailed third-party product analysis is out of the scope of this article, but rest assured, a successful adaption of tethered performance management is a SOLUTION to Excel Hell. Tethered performance management is the proverbial silver bullet that allows companies to yield the autonomous model’s positive benefits while offsetting the distributed model and the distributed collection deficiencies.
Use Case 7 — ETL Functions
ETL functions center around moving, transferring, and even transforming information between one system and another. ETL stands for Extraction, Transformation, and Load. CSV files are often used as a shared data transport medium between systems. For those that work with data often, how many system dumps have you been handed in an Excel format?
ETL function sheets are not limited to data dumps but can act as upload files. Supposing I need to upload budget information into the ERP system. Excel is a common mechanism to accomplish this task. ETL functions include intra-sheet data feeding — anything that transfers information from one sheet to another. In summary, data Loading sheets can be broken into four subcategories: import, export, sheet-to-sheet, and intra-sheet.
Data Loading sheets are sometimes a necessary function, especially in distributed platforms. When a Data Loading sheet is identified, one should ask: Are critical business and transformation rules essential to the organization embedded within the sheet? Are they best suited in a spreadsheet? Perhaps better integration approaches between systems that capitalize on databases, system connectors, or more efficient file systems should be leveraged?
Flagrant Misuse
While the seven use cases illustrated are considered “valid” uses of spreadsheets, countless flagrant spreadsheet misuses exist. Have you ever seen people build workflows and schematics in Excel? Even perhaps full-scale presentations? I have. I can’t tell you how often I have seen full-scale writing and documentation embedded into a spreadsheet tab. Yikes, there is more of that than I care to admit — embedded imagery, presentation materials, schematics, maps, workflows, etc. Usually, content that belongs to PowerPoint, Visio, or some presentation-rich visualization software. Misuse of Excel is rife throughout organizations and can impact all seven model types.
The question becomes, are these misuses detrimental to a business process? Well, this depends. What is the cause of the misuse — reduced access to more advanced tools, limited skills, an emergency time crunch, rapid expression of ideas in a single platform, a force of habit? All these reasons and more can drive people to get creative when using spreadsheets and sometimes are a practical necessity.
A tool like Excel has extraordinary capabilities; to me, a flagrant misuse is only detrimental if it negatively impacts the productivity of the individual or the business process. For example, if you cross paths with a person struggling to do a slide presentation in Excel, and as a result, their productivity is being affected, one should ask, perhaps we can get you trained up on PowerPoint?
More broadly speaking, is the misuse integrated into a full-scale business process, where maintenance, knowledge transfer, shared workflow, and collaboration are required? Does this mishandling affect the efficiency of the business process in totality? These questions should all be considered when assessing misuses of Excel within each of the seven use cases to ensure they are not tearing down productivity.
What about Macros?
The business world has a love-hate relationship with Excel macro automation. The three-letter acronym VBA can be a curse word to some and a life saver to others (VBA = Visual Basic for Applications). Macros use VBA programs to automate process steps to help save the end-user time. This automation could be as simple as a series of recorded sequences to apply formatting, move data, and perform calculations. Macro automation is a feature of Excel and is not a sheet use case, as much as a feature than can be exploited across all use cases. Macros can infiltrate each of the seven use cases and should be used cautiously.
In the early stages of business computing before the mature evolution of the RPA market (RPA = Robot Process Automation), macros could be easily rationalized as a quick productivity enhancer. However, with the RPA market’s maturity and tools, macro process automation’s use case is substantially weakened. Macros are to business process like coffee is to your diet. It will give you a seemingly quick jolt of productivity. Also, like coffee, you will get the slump on the backend. A diet of coffee alone is unsustainable.
Excessive process automation worksheets are another gateway into the world of Excel Hell. They can magnify the toxicity of the other harmful types and neutralize the beneficial value of the tethered models.
Workbooks are a Combination
Spreadsheets are multi-tab single workbooks that could contain any one of these different types of models. In the most sophisticated real-world situations, the seven types of use cases can materialize in combination across a single workbook. Thus, when assessing spreadsheet functionality, it should be conducted per tab. Even within the same tab, there are cases where these seven types can be mixed. Tab-by-tab analysis in large-scale enterprise organizations may seem daunting. However, it is the most precise and best method to capture the complete order of Excel Hell within a company.
In Closing
If you made it this far through our journey together, I surmise you have already concluded that you have an Excel Hell problem. Hopefully, you found the information in this article enlightening and, at the very least, gives you a gleam of hope in how to start to tackle your problem. Remember, the first step is always the hardest!
For more articles on Spreadsheet technology, see The Spreadsheet and Cellular Autonomy.
About the Author
Scott Gehring has over 30 years of experience in global enterprise information systems and holds several patents for his work in varying industries. As a pioneer in the field of analytics, he has been an influential industry leader in defining best practices around system design, implementation, integration, and operations. Scott has built hundreds of solutions for companies ranging from small-mid-business to large-scale enterprise organizations, helping to drive process improvement, tighten the link between business and IT, and provide the latest innovations in information technology.
www.scott-gehring.com
www.linkedin.com/in/scott-gehring/
Scott Gehring — Medium
Technology Whiteboard (medium.com)
