Ensuring Compliance: The Crucial Role of FDA Regulations for ELNs and LIMS in the Pharmaceutical Industry In the rapidly evolving landscape of scientific research and development, Electronic Laboratory Notebooks (ELNs) and Laboratory Information Management Systems (LIMS) have become essential tools. They offer a convenient and efficient way to store, manage, and analyze data, thereby enhancing productivity and accuracy in laboratories. However, in the pharmaceutical industry, the utility of these systems extends beyond mere data management; compliance with regulations set by the Food and Drug Administration (FDA) is paramount. I. The Importance of Compliance The FDA enforces strict regulations to ensure the safety, efficacy, and quality of pharmaceutical products. Compliance with these regulations is critical for several reasons: 1. Ensuring Data Integrity and Traceability:

• Data Integrity: Accurate and reliable data is the cornerstone of pharmaceutical research and development. Compliance with FDA regulations ensures that data is recorded, processed, and maintained in a manner that preserves its integrity.
• Traceability: Regulatory compliance mandates detailed audit trails, which are essential for tracking the history of data and ensuring traceability. This is crucial for identifying any deviations and implementing corrective actions.

2. Meeting Regulatory Requirements:

• 21 CFR Part 11: This FDA regulation outlines the criteria under which electronic records and electronic signatures are considered trustworthy, reliable, and equivalent to paper records. Compliance with 21 CFR Part 11 is mandatory for ELNs and LIMS used in regulated environments.
• Good Laboratory Practice (GLP) and Good Manufacturing Practice (GMP): Adhering to GLP and GMP guidelines ensures that laboratory practices and manufacturing processes meet the required quality standards.

3. Facilitating Regulatory Inspections and Audits:

• Readiness for Audits: FDA inspections and audits are routine in the pharmaceutical industry. Compliance with regulatory standards ensures that laboratories are always prepared for inspections, thereby reducing the risk of non-compliance penalties.
• Transparency: Maintaining detailed and accurate records in ELNs and LIMS enhances transparency and facilitates smoother audits.

II. Key Features of Compliant ELNs and LIMS To meet FDA regulations, ELNs and LIMS must incorporate several key features: 1. Secure Data Storage and Access Control:

• User Authentication: Robust user authentication mechanisms are essential to ensure that only authorized personnel can access the system.
• Access Controls: Role-based access controls help in defining user permissions, ensuring that data access is restricted based on the user’s role and responsibilities.

2. Audit Trails:

• Detailed Logs: Comprehensive audit trails that record all data entries, modifications, and deletions are critical. These logs must be immutable and easily retrievable for audit purposes.
• Time Stamps: Accurate time stamps are necessary for all electronic records to ensure traceability and accountability.

3. Electronic Signatures:

• Compliance with 21 CFR Part 11: Electronic signatures must be implemented in accordance with FDA guidelines, ensuring that they are as reliable and trustworthy as handwritten signatures.
• Signature Records: Systems must maintain records of electronic signatures, including the name of the signer, the date and time of the signature, and the purpose of the signature.

4. Data Encryption and Backup:

• Encryption: Sensitive data must be encrypted both in transit and at rest to protect against unauthorized access and data breaches.
• Regular Backups: Regular data backups are essential to prevent data loss and ensure data recovery in case of system failures.

5. Validation and Verification:

• System Validation: ELNs and LIMS must undergo rigorous validation to demonstrate that they function correctly and meet all regulatory requirements.
• Regular Reviews: Continuous monitoring and regular reviews of the systems are necessary to ensure ongoing compliance.

III. The Path Forward As the pharmaceutical industry continues to embrace digital transformation, the adoption of ELNs and LIMS will only increase. However, with this technological advancement comes the responsibility of ensuring that these systems comply with FDA regulations. By doing so, laboratories can not only enhance their efficiency and productivity but also safeguard the integrity and reliability of their data. IV. Conclusion Compliance with FDA regulations is not just a legal requirement but a fundamental aspect of maintaining high standards in pharmaceutical research and manufacturing. ELNs and LIMS that meet these regulatory standards provide a robust framework for data management, ensuring data integrity, traceability, and security. As such, they are indispensable tools for any pharmaceutical laboratory committed to achieving excellence in their scientific endeavors. By prioritizing compliance, laboratories can confidently navigate the complexities of the pharmaceutical industry and contribute to the development of safe and effective medical products.

Does Your ELN or LIMS Help Your Lab Be FDA 21 CFR Part 11 Compliant?

Whether you’re selecting your first ELN/LIMS or evaluating the effectiveness of your current digital solution, you need to verify that it meets the United States Code of Federal Regulations (CFR) Part 11 as it applies to a pharmaceutical lab that produces and utilizes electronic documents and signatures. Since 1997, Part 11 of Title 21 of the Code, has been the primary authority governing the standard of practice for electronic records. Pharmaceutical laboratories that plan on adopting digital solutions for documentation should ensure that the ELN or LIMS solution they are selecting is one that is FDA 21 CFR part 11 compliant. In this article, we explain several key provisions of FDA 21 CFR Part 11 and answer commonly-asked questions.

What made 21 CFR Part 11 a necessity?

What are the operational purposes of 21 CFR Part 11?

The operational purposes of 21 CFR Part 11 focus, first, on granting access to documents only to authorized individuals who are properly identified and who meet access requirements. The second purpose of 21 CFR Part 11 is to safeguard data integrity by creating strong security measures that include strict password, login, and electronic signature protocols as well as a clear audit trail that monitors all changes to a record and enhances a lab’s ability to catch errors or detect unauthorized access to any document.

Which industries need to comply with 21 CFR Part 11?

There are a few exceptions, but generally, US companies that manufacture pharmaceuticals, food products, or medical devices must adhere to 21 CFR Part 11.

What are the 3 main parts of 21 CFR part 11?

1. General provisions. This section lays out the overall scope of the regulation and explains the types of documents to which it pertains. 2. Electronic records.

• Data Integrity: Ensuring that electronic records are accurate, complete, and reliable.
• Traceability: Maintaining a traceable link between records and their respective electronic signatures.
• Secure Storage: Implementing robust security measures to protect records from unauthorized access, alteration, or deletion.
• Audit Trails: Maintaining detailed logs of all changes to records, including who made the change, what the change was, and when it occurred.
• System Validation: Conducting rigorous validation to demonstrate that the informatics solution meets all regulatory requirements.

3. Electronic signatures.

• Verification: Ensuring each person’s identity is verified and assigning unique electronic signatures.
• Signature Linking: Associating electronic signatures clearly with their respective records, including date and time stamps and the role connected to each signature.
• Non-repudiation: Implementing measures to prevent electronic signatures from being repudiated.

What qualifies as an “electronic document”?

• The whole “life” of the document. Any file created with a computer system falls under 21 CFR purview. So does every iteration of the file, regardless of how many times it is “modified, maintained, archived, retrieved or distributed.”
• Any record–even a single file. Even if your company creates only one electronic document, you’re obligated to comply with 21 CFR Part 11.

How does the SciCord Informatics platform help you be 21 CFR Part 11 compliant?

The SciCord Informatics platform features a spreadsheet-based hybrid ELN/LIMS that combines the compliance and structured aspects of a Laboratory Information Management System (LIMS) with the flexibility of an Electronic Laboratory Notebook (ELN). The ELN and LIMS functions integrate seamlessly and enable your company to quickly reap the benefits of enhanced data and time efficiencies as you continue to meet compliance standards and follow GLP.

Summary

Complying with all the requirements of 21 CFR title 11 can be tedious unless you have a robust ELN or LIMS solution in place. The specifications regarding the safety and integrity of electronic records, including electronic signatures and an audit trail, can be onerous. SciCord Informatics platform meets all the 21 CFR Part 11 compliance requirements and provides a hybrid solution for data integrity and safety. By ensuring compliance with these regulations, laboratories can enhance their efficiency, accuracy, and security in managing electronic records and signatures.

SciCord Benefits within the Lab

SciCord Informatics provides laboratories with a hybrid ELN and LIMS solution that offers the flexibility of an ELN with the structure of a LIMS. When asked, scientists that operate at the bench like to use SciCord because of the user-friendly interface, the integration of ELN and LIMS functionality, collaboration with colleagues, compliance aspects, data visualizations, customizability, and the fact it’s a cloud-based service. User-Friendly Interface: SciCord has a user-friendly interface that allows scientists to easily manage and organize their experiments and data. The platform is designed to be intuitive and straightforward, making it easy for users to get started and quickly become productive. Integration of ELN and LIMS: SciCord Informatics provides a single platform that features electronic laboratory notebooks (ELN) and laboratory information management systems (LIMS), which can help streamline the workflow of scientists. This integration enables researchers to manage their data and experiments in one place, eliminating the need for multiple tools and reducing the risk of data loss or inconsistencies. Collaboration: SciCord provides a range of collaboration features that make it easy for scientists to work together on experiments and projects. Researchers can share their data and results with colleagues, and use the platforms audit and commenting features to track changes and discuss findings. Compliance: SciCord has built-in security and compliance features to help ensure that sensitive research data is stored and shared securely. The platform is designed to meet industry regulations and standards (FDA 21 CFR Part 11 / GAMP), and can be customized to meet the specific needs of individual organizations or projects. Data Visualization: SciCord provides tools for data visualization, which can help scientists quickly identify patterns and trends in their data. This can save time and improve the accuracy of research results, as well as make it easier to communicate findings to others. Customizability: SciCord provides a range of customization options, allowing scientists to tailor the platform to meet the specific needs of their organization or project. This can include customizing the layout and functionality of templates that simplifies the experiments into easy-to-follow processes. Cloud-Based: SciCord is a cloud-based platform, which allows scientists to access their data and experiments from anywhere with an internet connection. This can be especially useful for researchers who need to work remotely or travel frequently. Real-Time Tracking and Automated Data Entry: SciCord offers real-time tracking and automated data entry features, ensuring that all sample information is always up-to-date and accurate. This automation reduces manual input errors and saves time. Advanced Search Functionalities: SciCord’s advanced search functionalities enable scientists to quickly locate specific data or samples using various attributes, such as sample ID, date, type, or storage location. This capability greatly enhances efficiency and productivity. Mobile Accessibility and Barcode Scanning: SciCord’s mobile-friendly platform allows researchers to access and update data from anywhere, enhancing flexibility and responsiveness. The ability to use cell phone cameras for barcode scanning simplifies sample tracking and data entry. Seamless Integration with Other Systems: SciCord integrates seamlessly with analytical instruments and systems, creating a cohesive data ecosystem and improving overall laboratory efficiency. Scalability: SciCord is designed to scale with the needs of the laboratory, accommodating growing data volumes and more complex workflows, making it adaptable for various research and manufacturing environments. Cost Efficiency: By optimizing resource use and reducing waste through efficient sample and inventory management, SciCord helps laboratories achieve significant cost savings and improve operational efficiency.   In conclusion, SciCord offers a comprehensive solution for scientific data management and collaboration that can help bench scientists streamline their work and improve the accuracy and security of their research results. With advanced features like real-time tracking, automated data entry, advanced search functionalities, and seamless integration, SciCord enhances the efficiency and effectiveness of laboratory operations. With its user-friendly interface, integration of ELN and LIMS, collaboration features, compliance options, data visualization tools, customizability and cloud-based architecture, SciCord can be an excellent choice for researchers looking to improve the efficiency and effectiveness of their work.

Introduction

By all accounts, “Cloud Computing” lowers the total cost of ownership of IT for companies in industries of any type and of any size.  In marked contrast to other industries, the Pharmaceutical industry has lagged in adopting cloud computing solutions, retaining costly on-premise solutions or implementing hybrid Cloud solutions which deliver only minimal benefit. Data integrity, among other barriers to implementation, remains the major hurdle for the Pharmaceutical Industry in attempting to adopt Cloud technology. In 2014 the Food and Drug Administration published an article in the FDA Voice praising how leveraging Cloud Computing allowed the Agency to “continue to protect and promote the public health”. In early 2016, the FDA followed-up by publishing “Guidance on Data Integrity”, which, for the first time, clearly categorized a computer system: “Computer or related systems can refer to computer hardware, software, peripheral 142 devices, networks, cloud infrastructure, operators, and associated documents (e.g., 143 user manuals and standard operating procedures)”. About the same time the FDA was publishing its “Guidance on Data Integrity”, the Internal society for Pharmaceutical Engineering (ISPE) formed a new GAMP (Good Automation Manufacturing Practices) Special Interest Group with the intent to provide guidance on the usage of Cloud technologies in the regulated (GxP) environments and to help accelerate the adoption of this technology within the industry.

Implement a Cloud solution

There are 3 main delivery models for Cloud solutions, which can be implemented using virtual online products instead of physical on-site hardware: Infrastructure as a Service (Iaas), Platform as a Service (Paas) or Software as a Service (SaaS). The responsibilities for using any of these delivery models as GxP solutions are comparable to utilizing any traditional on-site IT model and they all apply similarly. Depending on the used cloud model – Infrastructure as a Service (Iaas), Platform as a Service (Paas) or Software as a Service (SaaS) – the nature of some of these responsibilities, however, might change. The true benefits of Cloud computing are specialization and focus.  Scientific organizations want to focus on science and view IT as necessary to supporting infrastructure.  By contrast, vendors focus and specialize exclusively on IT solutions, spreading the development and support costs across their entire customer base. From the diagram, it is evident that there is a transfer of effort from the scientific organizations onto vendors as the SaaS Cloud delivery model is more fully implemented.  SaaS describes a delivery model in which the IT effort is almost completely transferred to the solution vendor and describes the most beneficial end-state for scientific organizations. Under this well-established model, customers who intend to use Cloud products as part of their GxP systems must assess responsibilities covered by Quality Systems, System Development Life Cycle and Regulatory Affairs areas. Management Responsibility Involves tasks such as creating and managing user accounts within the system, ensuring access controls are appropriately managed and maintained. Personnel Focuses on ensuring that all personnel interacting with GxP systems have the necessary qualifications, training, and experience to perform their assigned roles effectively and compliantly. Audits Regular assessments conducted to evaluate the effectiveness of security measures, data integrity controls, and overall compliance with regulatory standards. Audits ensure that systems are operating as intended and that any issues are promptly identified and addressed. Product Assessment Ensures that all products and components used within GxP systems meet predefined specifications and quality standards. This assessment is crucial to maintaining the integrity and reliability of data generated by these systems. Supplier Evaluation Involves assessing and selecting suppliers, contractors, and consultants based on their ability to meet specific GxP requirements. This process ensures that external partners contribute to maintaining compliance and system integrity. Supplier Agreement Establishes clear responsibilities and commitments between organizations and their IT suppliers. This includes obligations to notify about any significant changes to IT products or services that could impact GxP operations. Records & Logs Identifying and maintaining necessary records and logs as evidence of GxP compliance throughout their required retention periods. This ensures data integrity and traceability for audits and regulatory inspections. System Development Life Cycle Each GxP system must have certain features and a controlled SDLC process for delivering them. The specific features and SDLC controls that apply to each system depend on a variety of factors and are derived from regulations like 21 CFR Parts 11 and 820 in the US, Annex 11 and 93/42/EEC in the EU, and their international equivalents. The overall intent of these regulatory guidelines is to ensure that the GxP system fulfills its intended use and that the data is trustworthy and reliable. Develop GxP systems need to be developed following documented procedures that ensure the systems meet their specified requirements. Development activities include: planning, coding, building, configuring, testing, and deployment. Validate GxP applications need to be validated to ensure that software specifications conform to user-needs requirements, and the software infrastructure that the GxP applications runs on needs to be qualified to ensure that it meets the system requirements for the application. Operate Developing, conducting, controlling and monitoring GxP systems in production operations is important to ensure that they continue to conform to specifications. When end-user issues or system deviations occur, organizations with GxP systems also need to maintain a process for responding, correcting and preventing those issues.

• Change Control
• Service Level Agreement (SLA)
• End User Support
• Back and recovery
• Incident Response
• Corrective and Preventive Action (CAPA)

Conclusion

With the Cloud, organizations of any size can take advantage of solutions that will minimize the total cost of ownership, ensure pharmaceutical data integrity, and remove the need to plan and procure physical devices and IT infrastructure weeks or months in advance. Cloud Validation is certainly a challenge, however following FDA and GAMP guidance many organizations are demonstrating that it is possible to utilize the Cloud in GxP regulated environment and thus secure the benefits that this innovative technology offers.

Choice of Solutions

Once you have decided that you’re ready to transition to a new ELN or LIMS you will need to decide whether to implement a

1. Traditional solution
2. A Cloud hosted solution
3. A Cloud solution provided as a service (Software as a Service or SaaS)

Traditional ELN/LIMS vendors have historically controlled the market, however SaaS providers have made recent advances. As you consider a solution for your lab, you will need to assess the risk/benefits associated with each type of solution.

Progression to the Cloud

Cloud computing concepts are not as new as you would think. In fact, since the 1950s the computer revolution was driven by hugely expensive mainframes shared among “dumb” terminals. This configuration shares similarities with the Cloud only limited within a company. Introduction of affordable personal computing in the 80s and 90s revolutionized home computing but companies continued to keep big, expensive, server farms inside their corporate walls. The advent of the Internet introduced the “Cloud” era.  Products like Salesforce and services like Amazon Web Services gave Cloud access to any size company providing scalable, secure and inexpensive remote computing.  This revolution is not (only) driven by the research of computing power but, rather, by the infinite possibilities of having a system that can be used anywhere and by anyone. Some of the benefits derived from Cloud solutions:

• Ability to access data remotely
• Minimize Total Cost of Ownership – IT footprint (Hardware, Maintenance, Personnel)
• Scalability – easily scale hardware to support user load.

Recognize the Total Cost of Ownership of a traditional solution

Actually, “out of the box” traditional solutions entail a big effort to install, setup, and configure. These are complex systems that require dedicated Servers, Databases, File Shares and Integration with other systems.  Once those items have been accounted for there will still be a need to allocate a team to support the various systems and take care of all the periodic activities like monitoring and patch installations. Even if your Company already has a strong IT footprint, ask yourself, “How efficient can our IT department be when supporting a tool that’s not developed by them? How many hours, days or months would they need to invest in keeping themselves up to date with the latest releases or investigating every issue that will arise? Is this necessary for our core business?”.

This article will focus on the Pharmaceutical industry and the transition these labs are making from paper to digital documentation for a paperless lab. For years, investment in digital solutions to manage, record, and make sense of lab data was mainly limited to major pharmaceutical institutions. These companies initially deployed Laboratory Information Management systems (LIMs) and more recently Electronic Laboratory Notebooks (ELNs). Now, even smaller laboratories can afford and benefit from the transition from physical notebooks to a digital environment.

Why Go Paperless?

1. Data is stored in one easy to find location. The data isn’t in multiple notebooks stuffed into the bottom of someone’s desk or in off-site storage but accessed via a database or cloud solution.
2. The capability of sharing documents. Through managed access, users can share documents, review documents, and approve documents, increasing the laboratories efficiency.
3. Search Tools. Users can search for documents, resources, methods, etc. when they need them. Instead of having to leave the lab and look for a specific item, the user is able to digitally find, review and, if desired, reference or attach supporting documentation.
4. Cost Savings. The laboratory will be able to complete more tasks using the same resources, with documented efficiency increases of 30% over paper systems. In addition, the lab also avoids the costs of off-site storage for physical notebooks.
5. Compliance. Some ELN and LIM systems are Regulatory Compliant.

What does ELN and LIMS mean and Why It Matters?

The strength of an ELN solution is its ability to efficiently document laboratory work processes, enhancing collaboration and document sharing among colleagues. However, typical ELN solutions often lack robust support for defining samples, conducting detailed analyses, and structuring results—essential for advanced developmental stages. They also fall short in workflow management, including document and sample review cycles, and adhering to Good Manufacturing Practice (GMP) regulations. Consequently, ELNs are primarily suited for early-stage development or must be supplemented with a LIMS for comprehensive support in pharmaceutical development. The strength of a LIMS lies in its comprehensive management capabilities, effectively overseeing samples, users, instruments, resources (such as solutions and standards), structured data, and integrating applications seamlessly. Positioned at the core of information management, LIMS enables processing and reporting of data essential for later stages of drug development. However, its highly structured framework can impede capturing real-time bench data effectively. Moreover, implementing and maintaining a LIMS poses challenges, especially for smaller companies lacking dedicated IT resources. Combining ELN and LIMS offers significant advantages for companies involved in advanced drug development stages. ELN serves as a practical solution for recording bench activities efficiently, interfacing seamlessly with LIMS to access managed resources, while LIMS acts as the central control and reporting hub. Previously, integrating ELN and LIMS required substantial corporate commitment and resources. Recent advancements, exemplified by SciCord, highlight a shift towards hybrid solutions that integrate the best features of both ELN and LIMS into a single application. This approach eliminates the need for complex integration processes, thereby minimizing implementation and maintenance efforts required to achieve a fully paperless laboratory environment.

How to decide on an ELN and LIMS?

Selecting an ELN or LIMS system depends largely on a laboratory’s specific needs and developmental stage. Considerations include the types of work planned for the future and whether an ELN alone suffices or if integrating a LIMS is necessary. For non-GMP laboratories, investing in expensive GMP-compliant digital documentation systems may not be necessary. Additionally, the size of the laboratory plays a crucial role in determining the feasibility of implementing and maintaining these systems. While larger companies may prefer integrated ELN and LIMS solutions for comprehensive data management, smaller organizations lacking extensive implementation and maintenance resources might opt for hybrid models. It is essential to assess where and how the ELN/LIMS will be deployed, its integration with existing IT infrastructure, user interface friendliness, instrumentation compatibility, licensing models, and compliance requirements. Understanding these factors ensures that the chosen ELN and LIMS system effectively meets the laboratory’s operational needs and facilitates a smooth transition towards a paperless environment. The continuous evolution of ELN and LIMS technologies provides laboratories with increasingly sophisticated tools to streamline workflows, enhance data integrity, and meet regulatory requirements. Advancements such as cloud-based solutions and integrated platforms offer flexibility and scalability, allowing laboratories of all sizes to adopt tailored solutions that optimize efficiency and support innovation in scientific research and development. By leveraging these technologies, laboratories can achieve greater operational agility, reduce costs, and accelerate the pace of discovery and development in the pharmaceutical and life sciences sectors. Here are other items to consider:

• Where will the ELN/LIMS be in use?
• What is the Main Purpose?
• How will it be integrated with the current IT Infrastructure?
• Is the User Interface easily understood? ‘Friendly’?
• Instrumentation Connection?
• What type of Licensing Model?
• Compliant or Not Compliant?

Understand your needs before jumping-in to buy an ELN or LIMS. Too many times companies have sunk large amounts of money into Laboratory IT programs that sputter along and eventually fail. End-user adoption is important, but if the ELN or LIMS doesn’t integrate well with other systems, the end-user will never have a chance to utilize the ‘friendliness’. SciCord offers a GMP-compliant product that can be used for Analytical QC, Developmental Studies, Synthetic Chemistry, and Manufacture Batch Records. It combines the compliance and structured aspects of the LIMS with the flexibility of an ELN. As SciCord is a cloud solution, no hardware will need to be purchased and integration with any current IT infrastructure is simple. The program can either be accessed via the web or through a Client portal downloaded onto the desktop. Also, because SciCord utilizes the spreadsheet paradigm the user interface is highly intuitive making the solution an easy transition for users.

Going Paperless

Choosing an ELN, LIMS, or Hybrid system is the first step towards transitioning to the paperless environment. The remaining way isn’t always easy. Road bumps can be found with IT integration, end-user adoption, customization and validation issues. Nonetheless, the benefits of such a system far outweigh any of these road blocks and the best systems find a way to minimize these issues as laboratories make the transition.

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.