Purge the Fused Spreadsheets that Undermine Data-Drivenness

 By Richard G. Lamb, PE, CPA, ICBB; 

All system standard reports, business intelligence and advanced analytics as insight deliverables rest upon one thing. Data formatted as tables. There are no exceptions.

The tasks along operational processes simultaneously contribute and draw upon the data captured and stored as tables in operating systems such as the ERP, the systems in its orbit and others outside the ERP. System standard reports are generated by connecting to the tables.

A firm’s intelligence and advanced analytic systems may or may not be integrated with its operating systems. However, the tables of all operating systems, by some means, are always the feedstock to their deliverables.

What about a firm’s many process tasks that are not subjected to the table-based discipline of an operating system and are, instead, conducted with Excel? Do they function on tables as do operating systems? Were the ramifications of data tables ever considered in the design of the process and its Excel-based tasks? If the answer is, “no,” then the firm’s ability to be data-driven is seriously undermined.

It is a strategically important question to ask and answer. You only have to determine if the data and insight deliverables of each process task are layered or fused.

This article will explain what is meant by layered and fused data and insight deliverables—layered being best practice and too often the road not traveled. Before that, the article will define and contrast tables and spreadsheets. Finally, the article will explain how to quickly restructure all Excel-based tasks to be layered, rather than fused.


Table Compared to Spreadsheet

We need to clearly establish what is a table in contrast to what is a spreadsheet. The contrast tells the story.

A table is defined by its format because certain rules must hold. Figure 1 shows data formatted and stored as a table to an operational task conducted with Excel.


The rules, as characteristics of the table, are as follows:

  • Each column is one piece (variable) of numeric or categorical information.
  • Column headers are single-level—no header spans multiple variables.
  • A table is tabular in its variables. For example, rather than a column for each shift, the day and night shifts are categories to the Shift variable.
  • Along the left side there are no row titles for sections and subsections, and totals and subtotals.
  • There are no separation (empty) rows.
  • Only horizontal calculations are allowable and the results are shown as a variable.
  • A table is continuous. Each period’s (e.g., day, month, etc.) data are appended to the table rather than stored in individual tables.

Figure 2 is a spreadsheet to another operational task conducted with Excel. There are no rules to a spreadsheet format other than integrity. The following rules of a table format are broken in the shown spreadsheet:

  • There are multiple levels to the column headers.
  • Each location and order type area variable, rather than two tabular variables.
  • Along the left side, titles are given to every row except separation rows.
  • There are vertical calculations— totals and subtotals.
  • The spreadsheet is not continuous; it only reports a single day.


What we see in the spreadsheet is also defining. Data, calculation, filtering and presentation are fused in a single static object.


Layered Data Objects

Now to define what is a layered structure and why we care. A clue is that all operating systems function on the principle of layers—no exceptions.

The principle of layers is simple. Data and deliverables are separate objects. In contrast, and as already mentioned, data and deliverable are “fused” in a spreadsheet as single static object.

How we move from a fused to a layered structure is easy to see. As the insert demonstrates, we would find that the spreadsheet of Figure 2 entails six variables. Therefore, the “first layer” is a table designed to capture and store the six variables for each record (row). The date variable makes the table continuous.

The six variables of each data event, as a row, are simply placed in the continuous table. In contrast, a fused system is akin to completing a form by tediously inserting each item of the data event in the appropriate cell—a task that often requires task-specific skills and the concentration invites errors.

In the “second layer” of a layered structure are task worksheets, standard reports, intelligence and data analytics. They reach across the separation to the table layer when initially built and subsequently updated as data are added to the table as a row.

The fused spreadsheet of Figure 2 is never built. Instead, the spreadsheet is emulated with Excel Pivot. In fact, Pivot is the most ubiquitous of all intelligence software as the second layer.

Other than mandatory to being data-driven-capable, a layered structure is a gift that keeps giving. First, multiple deliverables, as perspectives to the same operational issue, can be readily created at the speed of click and drag. Second, rather than the static spreadsheet of Figure 2, we can interactively slice-dice, drill down and roll up layer-based deliverables in search of insight. Third, data can be inspected for actionable insights and cleansed to assure the integrity of the deliverables that reach to it.

Fourth, any table can be tapped by whatever operational tasks and insight deliverables to which its data are relevant. If only partially relevant to an insight, any table can be joined with any number of other tables from any source to form fully relevant tables that are not otherwise available from any single source. The article, “Building the Super Tables Behind Data-Driven Operations,” explains the method.

Making the Transformation

The logical personnel to lead the transformation of a firm’s excel-based tasks from a fused to a layered structure are its body of analysts. Their skills with Excel Pivot are typically the greatest in any organization. If not already power-users, they are close enough to immediately become so by self-directed learning.

The analysts will first identify all operational tasks conducted with Excel. With management, they will establish a sequence to transform them. With the task operatives, they will design the first and second layer components of each task. Finally, the operative-analyst teams will redesign the parts of the local and grand process that must be adjusted and can be enhanced because of the redesigned tasks.

The operative-analyst collaboration will organically upskill the engaged operatives. Through them, as front-of-the-curve operatives, the firm’s abilities will continue to evolve long after the transformation. Just as importantly, the organic upskilling will strengthen each operative’s job and work security as a new-age worker able to fill new-age roles.

There are also process operatives and managers who receive insight deliverables rather than being the source. The operative-analyst team’s role is to introduce and make them fully skilled in harvesting the interactive value of insight deliverables.

There is another group of mandatory participants in the transformation—management from the pinnacle down of the transforming organization. Research shows that management, at the pinnacle, must establish the transformation as policy. In turn, leadership from the pinnacle down, must be observed as being supportive and adoptive. Otherwise, the two-layered structure will emerge very slowly, at much pain to its implementers or may not emerge at all.

Sources for self-directed learning: Pivot Tables in Depth for Microsoft Excel 2016, Oeska, 2017.


Related website articles: First Things First: Become Capable of Data-Driven Operations | Building the Super Tables Behind Data-Driven Operations | Keep Your Old Career Hot in the New Age of Data Science