Teleworking: Beyond Spreadsheets

Emergency Mode

A few weeks ago it was business as usual here in the US. A vague sense of something going on in China but this was just another SARS thing right? Whatever it was, it was happening "over there". Nothing that we would actually affect us.

Now suddenly we are working from home, juggling questions about hastily prepared homework packets from our kids along with questions about just as hastily prepared google and office 365 docs from our colleagues. (And, hopefully, over-tipping our food and package delivery people and other service workers: it's not just medical workers who are keeping us alive).

We've figured out the magic formula for getting Microsoft Teams to work (unplug from the docking stations, mute your damn microphone when you're not actually talking) and we've recovered to at least 40% of our productivity. We can start moving out of triage into a second pass to make our teleworking infrastructure more robust: now that basic communication has been set up, we can revisit that sea of spreadsheets that we've all created.

If you're on google docs, you're probably okay for the time being. But, sorry Microsoft, Office may be 365 but it's still not quite 24/7. Checking excel files into and out of sharepoint is not really cutting it.

Spreadsheets are stop-gaps

So just when you think it's safe, there is another issue building up over time that is going to bite you eventually. If you haven't encountered it yet, you will: data quality is not-existent with spreadsheets. Which is just a fancy way to say that mistakes are easy to make but hard to find making your data unreliable and planning impossible.

And even if your data entry is super disciplined it's well nigh impossible to support anything other than the most basic workflow with just spreadsheets. Your team will spend just as much time trying to figure out what needs to be done as actually doing it. No security or roles to modulate who can see and update what.

Spreadsheets are exactly the right tool to start with and can often bootstrap you out of emergency mode but don't fool yourself that you've truly launched your teleworking infrastructure yet. If you stick to spreadsheets you'll run out of runway before you get your plane into the air.

Cells, Not Rows

The problem with spreadsheets is that their fundamental unit of data is a cell.

We as humans know that generally there is relationship among the cells in a column and a different relationship between the cells in a row but that is not the way the spreadsheet views it.

The rows and columns impose a visual order for organizing and manipulating cells but the spreadsheet itself treats each cell as a peer to every other cell. A spreadsheet is a sea of individual cells that we fool ourselves into thinking is something more structured.

You've encountered this many times: whenever you are typing data for the same 'record' across columns in a row and accidentally hit enter to go the row below before you are finished. Or have to expand long column headings so that you can see which one you are in.

Records, Not Cells

This is exactly the reason a database was invented. Whether it is your bog standard relational database (eg Access, MySql, SQL Server, Oracle) or something more cutting edge like a noSQL database (mongoDB, couchbase), the reason that a database is needed is to store data in a structured way.

Or a more accurate way to say that is that a database allows you to find data in a structured way. Meaning that both the method you use to find data is more structured as well as the data itself is delivered in a more structured format that gives more context to each element

The first point implies more than simply finding a single record. It also means that we can run metrics and statistics on the data. Grouping data by different elements (number of items per order, number of orders per item), reporting on trends over time, etc.

Your data becomes a driver of decisions rather than just tracking the result of decisions.

The second point refers to the fact that the fundamental unit of data for a database is a "record", that is a set of attributes of the particular thing you are tracking. The individual examples of the thing that you are tracking (the different records) correspond to the rows of your spreadsheet while the attributes of the thing (the fields of each record) correspond to the columns.

The difference is that the database stores these all as an associated group so that when you store data in a record rather than a cell, you are able to pull all the attributes for a particular entity at once rather than a mass of individual cells that you hope have not been misaligned during data entry.

In addition, you can also create a "data model", ie a web of relationships between different entities that you are tracking. These could be transient, like the items in a shopping cart, or more permanent like the members of a particular family. Either way, you are "modeling" in a simple way the complexity of your business.

Some no-code really is

But "database". That sounds complicated, no? You don't have time for that when you are struggling to stay afloat. The good thing is that if you are getting away with using spreadsheets to support your work then at this level you are not talking about super-complicated data models.

After a week or two of the spreadsheet "infrastructure" you likely will already know what you need, where the spreadsheets are starting to fall short. In fact, you've likely already done the first pass of designing the structure of what you are looking to track by creating the spreadsheet. That is likely the first table you would directly create and also likely contains data elements for other tables.

Lucky for you, there are some platforms out there that can take you through the next step of actually creating a database from these simple data models.

Among the better ones are ServiceNow, Mendix, and Quickbase. All are good examples of effective no-code platforms. Salesforce is ubiquitous but is a little too complicated and expensive for the average smart but non-technical user and generally requires a developer to really make it useful.

Most of these platforms have a free trial period for a month or so and some are even offering a much longer trail period of a few months for certain "front-line" organizations (ie government agencies, community non-profits, health organizations, etc) due to the pandemic shutdowns.

In a related article, I will take you through the steps of creating a tracker in Quickbase but the approach should hold for other platforms as well.

Fundamentally, get out from under the false promise of spreadsheets. Duct tape and bubblegum won't get you into space.