The UK Government Statistical Service recently released its good practice guidance for releasing statistics in spreadsheets. While this advice is clearly well-intentioned*, and parts of it are good, the overall effect is to encourage the release of data in formats that are difficult to process by computer. This is a disappointing retrograde step.
The following spreadsheet is shown in Annex A: 
If you work with data, I apologise for making you look at that. If you don’t, I should explain it is the sort of spreadsheet that, if you had to work with it, would cause you to wail in anguish and despair and demand to know what you had done to deserve such a fate. To enumerate the most serious problems briefly:
- Data and metadata are mixed together willy-nilly in the same sheet.
- The meaning of the columns – the ones that have a meaning, and aren’t just blank for layout purposes – is specified by four different rows, two of which use merged cells.
- Worst of all, background colour is used to convey the reliability of each estimate: information that is not provided in any other form. If the spreadsheet is converted to CSV – which is usually the first step when doing any serious work – this vital information is lost.
The use of minus signs to denote missing data, which might be irritating in an otherwise well-designed spreadsheet, is in this context so insignificant a problem as barely to register.
This spreadsheet is presented as a “good practice example”.
It isn’t as though the authors don’t know how to make a useful spreadsheet. Their example of a “spreadsheet focused on reusability”† is pretty much spot-on. But their advice on presentation ignores usability, where it doesn’t actively sabotage it. They recognise that there is a tension when they write:
“Providing an output which reconciles the requirement for clarity of presentation with reusable data can be hard.”
But that isn’t good enough. All data should be provided in a usable form, and ‘clarity of presentation’ shouldn’t be an excuse for poor usability.
We need an alternative, real good practice guide to releasing statistics in spreadsheets. I don’t see why it should be more than a page or so. Our attempt is online at clean-sheet.org. What have we got wrong, or missed out? Leave your suggestions in the comments below or submit a pull request to the git repository.
* Getting behind the GSS spreadsheet guidance describes the well-intentioned process that led to this unfortunate outcome.
† The guidance uses the word “reusability” for the quality we’re calling usability.
Bosker Blog 
Most of this could be covered by a simple test: is the sheet invariant under the operation of saving as .csv and reimporting into the same spreadsheet program?
Most of it? I think of my bullet points only “Don’t use colour or other stylistic cues…” is implied by your test.
I think you could then add something of this sort:
“If you also want to create a human-friendly presentation of this data, do so by creating another sheet in the same workbook and referencing the appropriate cells in the canonical data sheet.”
Everyone wins.
Robin, thanks for the feedback – you raise some interesting point …
1. For the benefit of your readers, the example you’ve used above was cited in our guidance as an example of using shading to reflect uncertainty, not as an all-round example of good practice (as there are certainly other aspects of that example we would not recommend). Communicating uncertainty is a current hot topic for the Government Statistical Service and we wanted a real example to demonstrate this point. They have: visually presented uncertainty, provided information on sample size and explained confidence intervals. Unfortunately, GSS spreadsheets do not typically include this sort of information as standard.
2. Its clear that your primary consideration is data provided in a usable form (and that clarity of presentation isn’t an excuse for poor usability). When we work with producers to help implement this guidance, I will remind them of your frustrations.
3. It seems that much of the much of the tension here can be removed when producers provide two spreadsheets – one for data re-use and one for presentation, as suggested above in the comments section. This is also suggested in our section on releasing data in open formats, together with an example from the Department for Education.
4. Looking at your checklist, you will see that we do address most of these points, albeit with a different emphasis for presentation and re-use spreadsheets. There was a certain amount of discussion in development on whether the metadata should ride closely with the statistics to help avoid misinterpretation or be separated as you suggest. We advised against including pivot tables, as these do not work in the OpenDocument format.
5. We will be looking to update this guidance next year – to take on feedback and to keep up to date regarding the open data / open statistics agenda. The suggestions on what you would put in a new good practice guide is helpful.
Thanks for taking the time to read this. Yes, I agree that the difference is essentially one of emphasis and that your good practice guide contains much good advice. As you say, my real concern is that it doesn’t – in my view – place sufficient emphasis on the importance of not sacrificing machine-readability when improving the presentation. The fact that using cell shading to express uncertainty is presented as good practice is symptomatic of that failure, I think.
Pingback: Who is the intended audience for open data? | Lost Boy
Pingback: Weeknote 8, 2023 – Librarian of Things