Structuring data in spreadsheets – for humans and computers

Structuring data in spreadsheets is really important for producing good financial models and reports.

However, data designed for humans to read should look different from data designed for computers to read.

Structuring data for humans and computers

Humans prefer to look at something that looks like the example at the top of this post.

Spaces help us read data better, so there’s a lot of whitespace in this example.

We can read in two dimensions, so splitting the data between rows and columns helps us understand it in different ways.

However, this data is impossible to pivot or do further data analytics with, without doing a lot of data cleansing first.

Let’s contrast this with this example:

This appears ugly and unreadable to us, but it’s perfectly laid out for computers to do further work on it.

No spaces, no merged columns, and each column represents a separate variable.  We sometimes call this “unpivoted” data.

Different tools for different readers

When you’re designing a spreadsheet solution for something, it’s important to think about whether you want the computer to read it or a human. This will influence the choices of tools you use. 

For example, I’m usually banging on about how you should use Tables to structure your data. As well as helping you to keep data consistent, there’s just a lot more data analytical functionality available in Excel (and Power Query) if your data is in a Table format to start.

So you might be surprised if you look at my financial models to see that there appear to be hardly any Tables. This is because I use them at the input stage (for computers) and not at the output stage (for humans).

Lots of spreadsheets are a mess because they mix up inputs, workings and outputs. And as a result of this, the data structure is usually an uneasy compromise that doesn’t serve human or computer well.