Creating and sharing spreadsheets
Spreadsheets are a common way to share data. Use this information to help you avoid common errors, improve interoperability and create more accessible spreadsheets.
If you plan to publish or share statistics content, you should follow the Government Analysis Function guidance on releasing statistics in spreadsheets.
If your spreadsheet is for personal use, you should still follow best practice as spreadsheets often end up serving a wider audience.
If you are sharing or publishing your spreadsheet, follow this guidance to make your file easy to find, use, and to minimise risk for your organisation.
Using spreadsheets to meet user needs
When sharing or publishing data, consider who needs access and how they will use the information.
General users might view the information for reference only, or they might need the spreadsheet to record or submit information following specific requirements. For example, you might publish a template form for users to complete and return so they can claim back expenses. In most cases, a spreadsheet is a suitable format for these users.
However, spreadsheets might not be suitable for technical users who want to perform detailed analysis on the information or combine it with other data. These users typically need data in machine-readable formats which they can use with programming tools with minimal data manipulation. In these cases, it might be more appropriate to publish your data as a CSV file.
Use a cover page
Adding a cover page to your spreadsheet helps users understand its purpose and content. This is particularly important for large or complex spreadsheets. A cover page is also useful to keep metadata and maintenance information in one place.
Your cover page should include information such as the:
- publisher, for example your name or department
- publication date
- content description
- version number of the spreadsheet
For more information on using metadata in your spreadsheet, read the guidance about recording information about data sets you share with others.
Choose the right software for the task
Spreadsheet tools and software have different capabilities and limitations for handling data. Check the specifications of your software, particularly around row and column limits, to make sure you are using an appropriate tool.
For example, older versions of Microsoft Excel (97-2003) have a row limit of around 65,000 rows. Microsoft has information about Excel specifications and limits.
If you need to share your spreadsheet with people using earlier versions of Excel, Microsoft has guidance on making your file backwards compatible.
If the spreadsheet tool cannot display all the data in the file, this can result in lost or missing information, which can distort statistics and conclusions.
Use open file formats
Open file formats are standardised file types not specific to one particular software package. Using an open format for your spreadsheet makes it easier for people to use with different tools.
Open data formats can also make it easier for your organisation to meet legal or compliance obligations. For example, when responding to requests for information under the Freedom of Information Act (FOIA) or the Data Protection Act 2018 you might need to provide data in a reusable, machine-readable format.
The open file format for spreadsheets is Open Document Spreadsheet (ODS). Most spreadsheet software allows you to save or export as an ODS file.
Read the guidance on sharing or collaborating with government documents.
Use descriptive names
Give your spreadsheet tabs unique names to provide context on the information each tab contains. For example, you could call a tab with HR budget data ‘hr_budget_2019’.
Clear and descriptive filenames help people find files and understand their content. File names should:
- use descriptive terms - for example ‘report’, ‘dashboard’, ‘list’
- include a date or date range if relevant, for example ‘January 2019’ or ‘2011 to 2017’
- avoid abbreviations or acronyms that are not widely understood
- avoid special characters or punctuation other than ‘-’ (dash) or ‘_’ (underscore)
- include the version number if there is one
Use versioning to keep track of changes
If you create, share or maintain different versions of a spreadsheet, you should clearly label each version. It is especially important to do this for business critical spreadsheets, for example those shared with a regulator.
Versioning makes it easier for you and others to:
- find the appropriate version
- keep track of which version has been shared with a particular group
- keep an audit of changes
- archive your spreadsheets
You can version your spreadsheet by:
- adding a version number in the filename
- including the version number in the cover page or metadata
Some cloud-based storage tools automatically version the documents for you. Make sure you understand the versioning of your file system and environment.
Use consistent column names
All column names should follow a consistent naming convention in the file. For example, use ‘start_date_time’ and ‘end_date_time’ rather than ‘start_date_time’ and ‘enddtTime’ in the same file.
If your spreadsheet uses multiple tabs, and the columns in these tabs hold similar or identical data, use the same column names. For example, use ‘department_name’ in all tabs rather than ‘department_name’ in some and ‘name_of_department’ in others. This will make it easier to cross reference, manipulate or merge columns.
Check for personal information
Spreadsheets can contain sensitive or personal information such as names, addresses and health or financial data. Protecting personal information is a legal obligation and important for maintaining credibility and the trust of users. The Information Commissioner’s Office (ICO) has guidance on personal data and the law.
Some simple checks for personal information might include:
- setting up a peer review process - for example, checking it twice, then asking someone else from your team to check it again
- do a keyword search for common column names such as “address”, “postcode”, “name”, “dob”, “email”
- check any columns that contain freeform text such as ‘comments’ or ‘feedback’
- check for hidden tabs, columns and rows - read the next section for more information
If your spreadsheet needs to contain personal or sensitive information, mark the spreadsheet as “Official - Sensitive” in both the filename and first tab. Make sure only those who need the information can access the spreadsheet.
Check for hidden data
People sometimes hide tabs containing background information for cosmetic or usability reasons. Before publishing or sharing spreadsheets, you must unhide all tabs, columns and rows and delete any personal or sensitive information.
To be certain you are not including hidden data, export the spreadsheet to CSV as this will export all the information from the current tab, including hidden rows or columns. You can then import this data into a new spreadsheet to make sure there is no hidden data in the file.
Spreadsheet tools can also store personal information in the document properties (the metadata), such as the name of the document creator. This is useful for internal documents but you might want to remove this information for a public facing document. Microsoft has guidance on how to view or change the properties for an Office file.
Identify any application code held in your spreadsheet
One benefit of spreadsheets is you can use them as an application development environment to provide interactive functionality.
However, development in spreadsheets rarely has the accuracy or structure of larger application development environments. This can be a liability if organisations or departments are dependent on an application that is not well documented or is difficult to maintain.
If your spreadsheet contains code, you should write documentation so developers and other users of the spreadsheet understand:
- the dependence on the code - for example, any systems or processes that could break if the code changes
- how to minimise potential security liabilities
- what they should use the spreadsheet for, and the scale - typically, this is for smaller departmental tools rather than enterprise applications
You might need to assess the security risks of any macros or code in your spreadsheet. Ask your IT or Security team for help if you need support.
You should be aware that spreadsheet macros do not export to the CSV format.
Use formulas and hard coded data appropriately
If you are using spreadsheets over a period of time, such as financial spreadsheets, make use of formulas and cell references so your spreadsheet data is as dynamic as possible. This is useful if your spreadsheet contains calculations or values that are likely to change over time, such as:
- date values - for example, the current year
- a total or sum value - for example, the sum of a column
If you are publishing spreadsheets which capture data at a point in time, such as statistical spreadsheets, using hard coded data might be appropriate. You do not need to use dynamic data where a value is fixed (for example, pi) or unlikely to change.
Optimise for accessibility and machine readability
By law, content published online by the public sector must meet the AA level of the Web Content Accessibility Guidelines (WCAG) 2.2. This includes spreadsheets published to public sector websites.
Apply accessibility principles to your spreadsheets to make them easier to use for everyone.
There is some overlap between accessibility and machine readability. Many of the suggestions in this guidance will also make your spreadsheet easier to use with programming tools, as well as software such as screen readers.
To improve the accessibility of your spreadsheets, you should avoid freezing rows and columns as this can limit how much space a user has to navigate the spreadsheet. For example, this can affect users who are using an increased zoom level, a low resolution or a device with a smaller screen. If your spreadsheet is read-only, users might not be able to adjust frozen row and column sizes to suit their viewing needs.
You should also:
- use the first row on your sheet as the header row containing all the column names
- avoid blank or empty rows - if you need more whitespace, adjust the line spacing, row height and column width
- avoid merging cells - every cell should contain a value
- avoid using colour alone to convey meaning, for example using red to highlight important values or cells - use text labels wherever possible
- use only one header row - if you need to group data into further categories, create new columns or use separate tabs for each category
Example
The following format is not recommended as it introduces a second header row and does not properly link the ‘Continent’ data to the ‘Country’ data:
| Continent | Country |
|---|---|
| Europe | |
| Slovakia | |
| Germany | |
| Italy |
The following format is correct, as it uses the ‘Continent’ column to categorise each row of country data individually:
| Continent | Country |
|---|---|
| Europe | Slovakia |
| Europe | Germany |
| Europe | Italy |
For more guidance on formatting spreadsheets for accessibility and readability, read the Government Analysis Function guidance on releasing statistics in spreadsheets. This is primarily for use with statistics spreadsheets, but you can use it for all types of spreadsheets.
If the data in your spreadsheet will not be read by humans and only needs to be machine readable, consider using CSV file format.
Be aware of archiving requirements
Many organisations need to regularly archive published data sets, either for their own auditing purposes, or to meet legal requirements. For example, The National Archives is legally required to archive published data sets from a range of government departments.
Some of the common challenges when archiving spreadsheets include:
- password protected spreadsheets - you will need to maintain a permanent record of the password
- sophisticated spreadsheets, for example those using macros or programming languages like Visual Basic for Applications (VBA) - there is a risk the macro will not run in future versions of the application
You should try to make sure users can access these files in future by reducing their complexity, particularly if the document will be maintained indefinitely.
Last updated 5 October 2023 + show all updates
-
Updated links so they point to the most recent versions of WCAG and of the Government Analysis Function guidance on releasing statistics in spreadsheets.
-
First published.