Spreadsheets are utilized throughout the Pharmaceutical industry, which is why spreadsheet validation is so important. Well designed spreadsheets increase both efficiency and compliance, while poor spreadsheet validation and design can lead to frustration and in some cases to reporting incorrect results. The process by which spreadsheets are implemented is also critical for compliant laboratories and laboratory data validation. Weak controls around spreadsheet security and management have been the source of many regulatory findings. This article focuses on aspects of spreadsheet design that deliver solutions which increase efficiency and compliance. Design criteria which support compliant spreadsheet implementation are also addressed.

The design principles in this article assume a templated deployment pattern, where copies of a controlled template are created each time the process is run. The term “template” will be used to identify the master spreadsheet and “document” will describe template copies.

Scoping

Before embarking on spreadsheet creation, key aspects of the environment surrounding the eventual use of the spreadsheet should be identified. The key aspects are compliance, target audience, and specificity of functionality.
Obtaining answers to the following questions drives the applicability of specific design components which are described in detail below.

Is Compliance a Factor?

Determining if compliance is a factor is the most critical decision point in spreadsheet design. Compliant templates must adhere to regulatory requirements with careful design to facilitate document review and potentially template validation. Templates not utilized for compliant work can opt out of many design strategies specifically targeted at compliance. There are two strategies to assure accuracy of the information produced by a compliant template/document.

100% Review of each Document

This design option relies on qualified reviewers to verify the outcome of each formula in the spreadsheet. In addition, reviewers need to verify the results of automated outcomes (code which generates content), cell format of numeric results, and conditional formats which can flag outcomes (green/red for pass/fail results). This option is feasible for relatively simple spreadsheets or in situations where use is sporadic. In these cases, the overhead of individual document review can be less burdensome than the template validation strategy defined below. Spreadsheets should be designed to:

• Make sure that formulas are visible and easily understood
• Avoid use of conditional format or cell formats which transform cell values

Template Validation

Validation of the template is a variation on 100% review in which the spreadsheet functionality is specified, tested, and documented once. The major advantage of template validation is avoiding the need to review templated results and reports. For compliant organization, template is all but mandatory for complex spreadsheets and/or high volume usage.

Significant effort is required to validate and maintain validated spreadsheet compliance. Validated spreadsheets (including copies) must be maintained within an automated system or a set of manual processes must be in place which enforce version control and maintain custody.

Spreadsheet design must secure formulas or automated template functionality which generates results. The design should:

• Clearly indicate formulas or sections of the template which are validated and do not require additional review
• Lock and protect validated formulas
• Protect the spreadsheet from user interactions which could invalidate the verification (ie: deletion of a required formula precedent)

Who is Your Target Audience?

A second determinate of spreadsheet design is the target user community. The design criteria are very different as the number of users in the target community grows. The design for a personal spreadsheet can make assumptions regarding security, expertise, training, work process, and physical environment. For instance, a personal template can be designed to work with a specific instrument, target a specific sequence of events, assume knowledge of spreadsheet work arounds, and of course a personal template is a known commodity.

As the expected template audience increases, design must compensate for the different factors described above to deliver a robust solution generating quality results.

A widespread issue is the personal or small group template that becomes wildly successful beyond the initial group. In this situation, the template is often deployed to a wider audience without addressing new design requirements. Lack of a suitable wide audience design can result in a less successful roll out.

 

How Specific is the Functionality?

In many situations, the designer is faced with a choice between a single generic template or multiple specific templates for each use case.

The trade-off between specific and generic should be carefully considered to determine if the upfront effort for a generic template offsets the added maintenance required for multiple specific templates.

Below are a list of some of the Pros and Cons to help in determining how specific You should be in your Template Design:

Specific Functionality

Pros	Cons
Specific to Manufacture Line – Designer can account for all needs of Single Group of Users	Multitude – The need to create many templates
Time – Quicker to get Template into Production	Maintenance – the need to maintain a multitude of Templates

Generic Functionality

Pros	Cons
Creation of fewer Templates	Upfront Effort and Time – Designer needs to accommodate needs of various groups
Maintenance is done on fewer templates	Takes longer to get into production
	Future Requirements are hard to predict

Design Components

Tables

Tables are used extensively in most spreadsheets. The tables may record samples, preparation data, resources, results, etc. Tables in most spreadsheets are predefined meaning that the maximum number of table rows/columns is ascertained during spreadsheet development. For instance, a sample preparation table may be defined to support up to 20 preparations. Each preparation may require cells for weights, volumes, and formulas to calculate concentrations. If each preparation is displayed on a row, there will be a total of 20 rows complete with predefined cells for weights, volumes, and concentrations.

Cell Fill and Font Colors

Consistent use of cell color in spreadsheets is an important design component. A unified color scheme can:

• Designate cells with specific functionality
• Flag potential issues or compliance concerns
• Increase usability for both the Author and Reviewer
• Increase reviewer efficiency

Color schemes are optional for personal spreadsheets, but spreadsheets deployed to a wider audience should implement a consistent color scheme. Be sure the selected color scheme is printable as some fill/font combinations do not translate to grey scale printers (many office printers).

Define Colors for cells containing:

• Formulas:
  Cells containing predefined calculations are typically locked to prevent inadvertent changes. Identifying formula cells alerts authors to the location of cells which are not normally edited. Reviewers are alerted to formulas which do or do not require review depending on the validation strategy
• Expected Entry:
  Cells where entry by the author is expected. By identifying these cells, authors are guided through the process and reviewers can verify that all expected entries are as expected
• Available:
  Cells which are not predefined for a purpose but can be utilized by authors to record
• Status:
  Cells which define the standing of a material, instrument, or other component. Status is often an indicator of a potential compliance issue and should be clearly flagged. Traffic light colors will make this information stand out and quickly identify compliance concerns

Example:

Data Validation

The best path to creating a robust spreadsheet is to assure the initial data entry is recorded correctly. Data validation of numbers allows spreadsheet developers to force data entry values to be within acceptable parameters which also assists with spreadsheet validation since endpoints are defined by the data validation max and mins. This also means that you need to use the right data validation tools. One caution with this technique is that limits may not be defined broadly enough and legitimate values cannot be recorded. This potential drawback can be avoided by using a data validation “Warning” rather than “Stop” when an error condition is encountered.

Conditional Format

A great way to highlight alert situations or pass/fail results is through conditional formats. The format of target ranges can be manipulated based on any input.

Conditional formats are however difficult to maintain since the underlying conditional formula is not easily displayed or obvious. To overcome this issue, place all formulas which evaluate a condition in hidden cells on the spreadsheet and include only simple references in the conditional formula. A simplistic example is a result to be colored red if greater than 7. A hidden cell would contain the evaluation in a formula myResult > 7″. The conditional formula would reference this hidden cell =myCondition=TRUE.