16. October 2018

The 3 types of spreadsheets

Through this, we’ve come to the conclusion that pretty much every spreadsheet in the world is used for one of three things:

  1. Small databases

  2. Models

  3. Business Processes

Let’s take a little bit deeper dive into each of these categories.

Small databases

Spreadsheet software is the first thing a regular user fires up whenever any data needs to be collected: Contact lists, inventory, logging custom things — the spreadsheet is the natural choice.

These files will usually not have so much as a single formula in them, and the content is often text heavy compared to other spreadsheets. These files are usually small (less than 1,000 rows of data) and contain 2-dimensional data.

Occasionally however — you will see much more complicated data structures in user-created spreadsheets :

  • multi-dimensional: multiple headers in a table usually add a dimension although obviously still represented in the 2-dimensional grid(a simple intro to multi-dimensional data can be found)

  • hierarchical: some rows in a table are of a lower level and roll up to higher level rows

  • even relational: data on one sheet refers to details on another, or additional rows list children (sometimes literally) of a parent.

But you can still assume that the creators of these sheets will rarely have any of that vocabulary. In other words: Many people are capable of fairly complicated data modeling, without knowing any of the data-modeling jargon. We believe this is an important observation.

A separate — and very common —sub-category of spreadsheet databases are files that are exported from other systems. These can in fact get quite big (often >100,000 rows) and are put in Excel files either to do analysis on them (see next category) or to transport them from one system to another, e.g. from a transactional system to a Business Intelligence system.

Models

The second category is what spreadsheets were built for: models. These files are usually number-heavy and their defining characteristic is that they have formulas in them. Users are calculating something.

We have furthermore identified three sub-categories here:

  • Projections: Where a lot of data is generated from a relatively small set of input parameters. A common example would be a financial projection based on a set of assumptions (often 1000s of numbers generated from <20 assumptions).

  • Analysis: Where a small set of data is calculated from a large set of data, often exported some other system. A typical example would be to calculate average customer spend based on age-group on data exported from a retail system. In this case the spreadsheet is competing directly against purpose-built BI systems, and — in many respects — winning.

  • Calculators: This is the most diverse sub-category. Here you’ll find spreadsheets where people are performing some sort of calculations but the balance between the amount of input and output variables is better. An example here might be a mortgage calculator.

Business Processes

This third and final category is the one that was the most invisible to us when we started our research. But it is incredibly common, and most of us recognize these types of spreadsheets when we hear about them. These are business processes that are run partially or entirely on spreadsheets.

A simple example might be a workplace lunch-order: Fill in the spreadsheet before 11:30 and the order will go out.

A higher level example might be that every department in the organization has to fill in a spreadsheet template and submit it to HR or Finance before the end of the month. There some poor back-office person aggregates the data into another spreadsheet, enters the results into the payroll system and sends it on to whoever creates the slide-deck for the upcoming board meeting.

And everything between: I’ve seen a spreadsheet that serves as the UI for a large IT company’s billing process. I’ve seen spreadsheets that generate custom portfolio reports for high net-worth individuals in a bank. I’ve seen spreadsheets that are effectively sophisticated CRM-systems.

These spreadsheets are usually accompanied by formalized or not-so-formalized workflows that rely on emailing spreadsheets around, notifying people of changes in a network drive or multiple people working on a spreadsheet stored in the cloud at the same time.

Somewhat tongue-in-cheek, I’ve theorized that every new process within an organization starts out as a spreadsheet before it is (sometimes) formalized in a purpose-built system. That formalization often comes at a great cost, and surprisingly often leads to a solution that is not only much more reliant on IT for operating and maintaining, but also a lot less flexible than the original spreadsheet creation.

Millions upon millions of dollars are spent on reprogramming workflows and processes that exist in spreadsheets into more formal systems, often only to see a roll-back to the spreadsheet system not too much later.

The reason: The spreadsheet systems are written and maintained by the domain experts — the business users that intrinsically understand the problem space. The more formalized IT systems are written by IT experts that have limited understanding of the problem space. Even when the domain expert and the IT expert work together on a new solution there is a lack of a common language and too much of a gap in the fundamental understanding of the problem for such a project to be successful.

But more about that later…

This article has also been published on Medium.

Written by Hjalmar Gislason.

More blog posts

30. September 2021

Build a price quote calculator in 5 easy steps 

Embed interactive features like calculators on a website, and voilà! They become super-engaging conversion rate boosters 🚀

25. August 2021

Embedding 101: Entrepreneur Edition

As GRID grows and evolves, so do interesting new use cases and patterns of GRID-authorship. These patterns help us learn...

23. March 2021

GRID launches empowering spreadsheet-to-web software 

It’s launch time, and we couldn’t be more thrilled. Something special happens when a team this passionate unites behind a...

21. October 2017

3 things you don’t understand about spreadsheets (Part 1)

The world has a love/hate relationship with spreadsheets.

27. October 2017

3 things you don’t understand about spreadsheets (Part 2)

This is part 2 of 3 in a series of posts about the nature and history of spreadsheets.

28. August 2018

Excel vs. Google Sheets usage — nature and numbers

One of the most common questions I get is if Excel is still important, implying that “everybody” must be using...

03. November 2017

3 things you don’t understand about spreadsheets (part 3)

This is the third and final part of a series of blog posts on the origin and nature of sspreadsheets.

14. December 2018

The 20 Best Spreadsheet Quotes

In GRID’s deep dive into the world of spreadsheets, we’ve come across a lot of insightful, witty and sometimes somewhat distressing quotes....

08. February 2019

Excel — a Domain Specific Language for Finance?

One of my biggest inspirations when doing background research before starting GRID was Felienne Hermans. I met Felienne first at Strata in London...