How to design better spreadsheet templates
This post is based on the second half of ”A thousand things at once: bulk transactions and data collection in digital services” – a talk we gave as part of Services Week 2025.
In the previous post we looked at all of the challenges that spreadsheets in services present to users. Spreadsheet templates are often the default solution – but they commonly don’t provide enough guidance, are tricky to manipulate and prioritise service needs over user needs.
Point 4 of the Service Standard requires that you "make services simple to use" and that applies to spreadsheet templates too, so let's start the conversation about how to do spreadsheets in services well. If you're going to use a spreadsheet upload in your service, there are a few design accommodations that you can make to improve the experience for users.
Provide guidance on what data is needed
Spreadsheet users need lots of guidance on what data they need to supply. Before the users download the template, give them a high-level summary of what data they are being asked for. The summary can tell the user what each row on the spreadsheet is going to represent without getting into detail about columns. For example, "vehicles you own that have MOTs" or "employees of yours that have a workplace pension with us" both describe the individual things the user will populate.
Detail on what is needed in each column is best saved for the spreadsheet itself. Put this guidance in an extra header row or rows below the column names, so that it's visible to the user whilst they are working. Include details about both what each column value is expected to mean (e.g. the date a contract was signed) and how it should be formatted (e.g. dd/mm/yyyy). Specify if the column is optional (most users will assume all columns are required). If the column is a reference to some other piece of data like an account or identity number, tell the user where they can get it from. As ever, work with a content designer to make this guidance understandable.
Data practitioners may claim that a "tidy" spreadsheet is one where the headers occupy the first row and all other rows are data. Don't listen to them – spreadsheet templates should contain whatever guidance is needed and in whatever structure works best for the user. As long as a template is consistent in where the data starts and ends, it isn't any less difficult to extract the data when it gets uploaded to a service.
Minimise the data that is needed
Don't collect anything you already know or that can be inferred from other values on the sheet. For example, if you're collecting information about imported goods and the user has identified what product they’re importing, your service shouldn’t be asking the user for the relevant import taxes because that’s available from another data source. It is not the user’s responsibility to tell the service things it could find out for itself.
This also applies to calculated values such as totals. Asking the user to sum some numbers does not improve the reliability of the sheet, it's just confusing and slow. Instead, compute total values in the service and show them to the user as a check step.
If you know that your users are likely to be copying data from similar sources, try and match the formats that they use. For example, if you know that a majority of your users will be exporting data from another system which formats dates in YYYY-MM-DD format, use the same format in your service to minimise transformation. The same applies for the order of columns, which makes it easier to copy multiple columns at once.
Minimise the amount of work done by the user outside of the service
Avoid unique ID numbers where possible.
Whilst unique IDs are great for computers, requiring them in a template pushes the burden of work onto the user to look up ID numbers and remember what they mean between submissions. Instead, collect data that is human-readable and match it in the service to the correct data. If the data can't be matched exactly, offer the user a choice in the service of how to resolve the problem.
Be flexible on details to avoid frustration.
We've talked about date formats a lot in this article and this is how you remove most of that pain. Allow the user to select the date format they want to use when they upload the sheet, or better yet, work out the date format automatically and check it with the user.
Remember the user's choices between sessions.
Their data is often coming from the same source between uploads and so the formats normally won't have changed. If they've selected a date format in a previous upload, remember it and apply it by default, allowing them to change it only if necessary.
Handle ODSs and XLSXs as well as CSVs.
It used to be that parsing spreadsheets in web services was a hard technology problem. No more – free and open-source libraries exist that make it as easy to parse XLSX and ODS files as CSVs (if not in fact easier, as CSVs are infamously unstandardised which causes all sorts of headaches). Supporting these major formats in your service prevents the user having to learn how to export a CSV whenever they make changes – which is not something users automatically know how to do.
What better looks like – a guided template experience
Whilst some design patterns for spreadsheet templates will help, it's clear that there are diminishing returns on trying to design spreadsheet templates to be human friendly. Bulk transactions are a service design problem – we need to discard the assumption that a spreadsheet template is the only acceptable answer and reframe the constraints to find alternatives. So: what would be the ideal way for users to interact with a service to do a thousand things at once? There are perhaps lots of good designs – so we'll talk in general terms of what the service could do, and leave the designing up to the designers.
Our first design principle is that the user should have to do as little work as possible to transform their existing data source into whatever is required by the service. The service should make that heavy lifting as small as possible.
To achieve that, our second design principle is to get the user into the service, and then keep them there. Using a spreadsheet template forces the user to work outside of the service, where the service can't help. Having two-way, personalised communication with the service clearly opens up more help for the user. So a good service will be one where the user is doing more work within the service itself, rather than within their office program. But what sort of help can the service provide?
Allow flexibility in the structures as well as data formats
Instead of asking the user to restructure their spreadsheet offline, instead get them to provide their existing data and help them extract the useful parts in the service.
A user's data source may well be in a completely custom structure and something completely different to any other user. Their spreadsheet may have multiple worksheets, multiple tables of data in one sheet, and multiple titles, headers and footers per table. So a good service could help them drill down through those choices and help them pick the sheets, columns and rows of interest.
A good service can also avoid requiring hard-coded column names. Instead of getting the user to change their column names offline, allow them to pick the columns online and identify what each column contains for the service. In more advanced services, the service might be able to guess which columns are which based on the contained data and check with the user, rather than needing them to map every column from scratch.
Fix errors within the service
Instead of providing text-based error messages, a good service can show the user what is wrong and allow them to fix it. At the very least, the service can show the user the invalid data in context, surrounded by the other rows or columns nearby. Once the user can visually see the data, it might be immediately clear what is wrong (extra blank rows, for example, although the service should perhaps just warn the user about these and ignore them).
For the fixing, the example above included offering users a choice of nearest correct options if the service wasn't able to validate their data. Other ways to help the user make their data understandable by the service are possible, depending on context and common user problems found during testing. Any fixes need to be communicated clearly.
At the very least, the service can offer a way for the user to correct their data manually. It could for example use the same form used for a single transaction but pre-populated with errored data, allowing the user to fix it. It could also provide a spreadsheet-like interface, and ask the user to fix their data inline – but this time with help.
Use statistics instead of previewing data as a quality check
It's tempting to show the user a preview of their data as a way of getting them to check that it's correct. In our testing, this is not a reliable way to spot errors. For tables with more than a few cells, the user simply won't engage with the amount of data onscreen – and of course this doesn't scale to a thousand things at once. Instead, show the user summary statistics that help them spot possible issues.
A simple check that is normally generally applicable is just to show the number of transactions that the service is going to process. Other aggregate values may also make sense in context: for example, if your service will involve a payment depending on the number of submitted records, showing the user the final payment amount is often a useful check.
If you are asking the users to submit similar data regularly (like every week or month) then it can make sense to show them how their current submission differs from their previous submissions. It is easier to engage users with things that have changed rather than absolute values. For example, if the records they submit represent some real world asset (like a vehicle with an MOT, or a product they are selling) show them how many new or removed records are in this submission – if they've not added or removed any new real world things this month, it's a clear indication that something is wrong.
And of course – show them the summary before any permanent action has been taken and allow them to go back and fix any issues. There's no point showing summary statistics after the records have already been processed – that's a one way ticket to an expensive and slow support request to fix the submission manually.
Our free Data Upload Design Kit helps design human-friendly services
Register Dynamics has made its reputation in delivering services that mix highly complex data with the very best of user-centred design. We've seen multiple service teams solving the same data problems over and over again, which is why we're now building tools and design kits that make it easier to do better with data in Government services.
We've recognised that what's outlined above is a lot of work for a service team just trying to pass assessment for their single service. We've created the Data Upload Design Kit to do the hard work to make things simple for designers building services with bulk transactions. With the Design Kit, you can easily achieve what we've set out above – helping users to select, filter and check their data with help from your service. It's free, open-source, accessible and compatible with the Design System and Prototype Kit. Give it a try and let us know how you get on – we'd love to hear about what features you need or help you fix and problems you find.
So next time you find yourself designing a service to do a thousand things at once, don't reach straight away for a spreadsheet template – give the Data Upload Design Kit a try and help us push new ground in making data-centric services friendlier for humans!