excel.Rmd

---
title: "Excel tables"
---

# Supporting summary tables

All of our statistics outputs are published with supporting Excel summary tables that contain the most granular data that we release for each publication. This may also include data that is not part of or not discussed in the main summary narrative.

There are a number of data and formatting checks that we need to carry out on the supporting tables to make sure that they are suitable for release, including meeting GDS accessibility guidelines where possible and that statistical disclosure control (SDC) has been applied where appropriate. Automation of Excel formatting and SDC application is under development to make this task easier and less prone to error, however checks will always be required.

# Data

The most important part of any publication is the data that is based upon; high quality, accurate data is the foundation of all of our statistics. If all data is correct then anything created further downstream including charts, graphics, and analysis has much less room for error. It follows then that the data in the supporting tables should be the first thing to be checked once the publication is ready. 

The data in the excel tables should be checked after a code review of the `SQL` to extract the data from data warehouse has been completed. If the publication has an existing RAP with previously reviewed code then a code review is not necessary unless any code changes have been implemented.

Below is a checklist of the things that should be considered when assuring the data. This list **is not** exhaustive.

__Please note: These checkboxes will not stay marked if you refresh or navigate away from this page__

## Checklist

<input type="checkbox" unchecked> total items and NIC match between tables</input>  
*Patient counts will not match depending on the level that they are taken at.*

<input type="checkbox" unchecked> total items and NIC match other published sources of data</input>  
*This can include ePACT2 and ODP, though certain criteria might mean this step is not always possible*

<input type="checkbox" unchecked> reproducible breakdowns match ePACT2/EDW</input>  
*Not all breakdowns such as those by age are reproducible from ePACT2*

<input type="checkbox" unchecked> are you able to recreate the data by re-running the RAP `SQL` code</input>

<input type="checkbox" unchecked> does the data meet the requirements set out</input>  
*Is the data for England only? Are there any erroneous data points? Are the breakdowns at an appropriate level?*

<input type="checkbox" unchecked> has external data been sourced correctly</input>  
*Is it clear where external data has come from? Is it cited correctly?*

# Formatting

Once the underlying data of the publication has been checked we need to make sure that the formatting of the spreadsheets is correct and accessible as possible. GSS have issued guidance on releasing statistics in spreadsheets that can be found on their website (https://gss.civilservice.gov.uk/policy-store/releasing-statistics-in-spreadsheets/).

__All NHSBSA GSG badged statisticians or those involved with Official Statistics production should familiarise themselves with this guidance__

It should be expected that the responsible statistician of the publication has ensured that this guidance has been followed, however this should be checked and any elements that do not match this guidance should have a valid reason provided for it.

Much of the checklist below comes from [the checklists provided by GSS themselves](https://gss.civilservice.gov.uk/policy-store/releasing-statistics-in-spreadsheets/#section-2). The elements below are those listed as essential for passing accessibility criteria by GSS.

## Checklist

<input type="checkbox" unchecked> are tables marked up as such, including a meaningful name</input>

<input type="checkbox" unchecked> have all merged cells, split cells, and nested tables been removed</input>

<input type="checkbox" unchecked> are there no blank rows or columns within tables</input>

<input type="checkbox" unchecked> do all tables have a tagged header row</input>

<input type="checkbox" unchecked> has 'wrap text' been applied to all cells with overflowing text</input>

<input type="checkbox" unchecked> cells in a table with no data should not be left empty</input>

<input type="checkbox" unchecked> symbols or superscript should not be used to signpost notes</input>

<input type="checkbox" unchecked> links must be accessible</input>

<input type="checkbox" unchecked> have all worksheets been given a descriptive title which are tagged and formatted correctly</input>

<input type="checkbox" unchecked> there should be no headers, footers, floating text boxes, or floating toolbars</input>

<input type="checkbox" unchecked> colour should not be used in any way to convery a message or meaning e.g. RAG status</input>

<input type="checkbox" unchecked> if using colour for emphasis check the contrast</input>

<input type="checkbox" unchecked> do all worksheets have unique names or numbers</input>

<input type="checkbox" unchecked> have all blank worksheets been removed</input>

<input type="checkbox" unchecked> is all information required to understand the data above the table located in column A</input>

<input type="checkbox" unchecked> do all tables start in column A</input>

<input type="checkbox" unchecked> has a spelling and grammar check been ran and passed</input>

<input type="checkbox" unchecked> has document information been completed (title field and document language)</input>

<input type="checkbox" unchecked> is the cursor in cell A1 on sheet 1 when you open the workbook</input>

# Documentation

These checklists can be used to help you keep track of your progress but do not make a permanent record of the checks carried out. Please make sure you complete a QR log with the steps taken to assure supporting excel tables and data.