Validation and use of exce spreadsheets in regulated environments. (Part 11)

11/GxP compliance
Validation during development and installation
How to ensure integrity&security of spreadsheets
How the FDA is using spreadsheets
Documentation requirements
Validation example from beginning to the end
Case studies for Part 11 compliance

Q&A's

Q&A's

Part 1

Part 2

– E-Signatures/Records Defines requirements for electronic records, electronic signatures in FDA regulated industries
EU Annex 11 to GMPs Defines requirements for software and computer systems in EU GMP regulated environments
PIC/S Good Practice Guide Has lots of good recommendations on using computers in regulated environments

handling and maintenance
Authenticity
Integrity
Confidentiality
Accuracy
Tools:
Electronic audit trail
Electronic signatures
Digital signatures for system not under direct control
Validation

in GxP environments should be validated
Testing should cover full range
End users should validate any program Macros and other customizations that they prepare

FDA Requirements

Spreadsheet Calculations and Macro Programs should comply with 21 CFR Part 11 (E-records/signatures)

its intended purpose the Microsoft Excel software used in creating and maintaining nonconformance records, product return records, internal audit corrective records, or corrective action records. (189)
In addition, you continued to release products based on assay results generated by the spreadsheet that have not been verified for accuracy (W-237)
Spreadsheets intended to check for outliers and calculate mean, SC, % CV, not validated (W-201)

Ref: www.fdawarningletter.com

Validate Spreadsheets

intended use according to an established protocol. For example; the Microsoft Excel spreadsheet software program was not validated for formulation of reagents and was seen to give incorrect data (W-125)
Microsoft Excel spreadsheet software used manufacturing has not been validated for the purpose of generating a worksheet for formulation of reagents.
No documentation was found to establish or verify corrections made to the program. (W-125)

Ref: www.fdawarningletter.com

Validate Spreadsheets

record of all calculations performed in connection with laboratory tests as required by 21 CFR §211.194(a).
For example, the notebook does not document reference to the spreadsheet calculation used to generate the results.
Your SOP omits instructions to include in the notebook the reference to the spreadsheet calculation used to generate the results, as well as the raw data and calculations."

Ref: www.fdawarningletter.com (W-237)

Record Spreadsheet Calculations

analytical results for in-process and finished product testing [21 CFR 211.165(e)]. For example, the computer spreadsheets used to calculate analytical results for... have not been validated.
QA/QC Spreadsheet Validation, is deficient in that only a small range of values are being used to challenge computerized spreadsheet mathematical calculations.

Ref: www.fdawarningletter.com (W 063)

Validate Spreadsheets over wide range

following types of data: aberrant high findings, aberrant low findings, in-specification findings, zeros, negative numbers, and alphanumeric combinations
Each spreadsheet is product specific and has a separate validation package
Each package contains the initial testing of the information as entered into the Spreadsheet, a blank spreadsheet, and a spreadsheet showing the calculation formulas used in the appropriate cells.

EIR

EIR = Establishment Inspection Report

Ref: www.fdawarningletter.com (W 106)

Validation

tests conducted and the dates they were performed as well as hand calculations of some trial data for comparison
Revised SOP "QA/QC Computer Spreadsheet Validation," contains directions for testing new and existing spreadsheets prior to use in analytical testing.
The spreadsheets are checked monthly by a familiar analyst with previously entered data.
The check results are compared to the originals to make sure that corruption of the file has not occurred

EIR

EIR = Establishment Inspection Report

Ref: www.fdawarningletter.com (W 106)

Validation

read-only form to compact discs, specific to product.
Changes to spreadsheets cannot be saved in this format.
Two sets of CDs were made, one Set for the daily laboratory use and one master copy containing all spreadsheets kept by ….
lf one spreadsheet an a CD is changed, then a new CD is burned and the old one is archived.
The spreadsheet when printed out bears a file path at the bottom to assure it came from the CD
.

EIR

EIR = Establishment Inspection Report

Ref: www.fdawarningletter.com (W 106)

Integrity

11 p 6) are expected for use of spreadsheets?
A: Data integrity should be ensured by suitably implemented and risk assessed controls. The calculations and the files should be secured in such a way that formulations are not accidentally overwritten. Accidental input of an inappropriate data type should be prevented or result in an error message (e.g. text in a numeric field and or a decimal format into integer field) - so called boundary checks are encouraged.

Ref: EMA Website

the validation of spreadsheets?
A: Validation according to paragraph 4 of Annex 11 is required at least for Spread-sheets that contain custom code (e.g. Visual basic for applications). Formulas or other types of algorithms should be verified for correctness..

Ref: EMA Website

GxP validation and computer science) can write programs
Everybody can change ==>Frequent change without control
No validation, no documentation
Many different environments (operating systems, PC hardware)
Many versions in use (local PC, server, inbox, delete folders)
No or insufficient documentation
Typically do not comply with regulations (e.g., Part 11), and QA Unit is not aware of this

perform calculations in secure and validated environments with functions for Part 11, e.g., chromatographic data systems, LIMS etc
Use document management software with built-in Excel support and Part11/GxP functionality, (E.g., Agilent OpenLab)
Develop, implement, and enforce procedures for development and use of Spreadsheets with quality, security and validation in mind.
Evaluate and use add-on software with more security and compliance functionality, e.g., e-audit trail, e-signatures Examples: e-Infotree from Cimcon, www.part11solutions.com, ExcelSafe from Ofnisystems, www.ofnisystems.com/ExcelSafe

Today's focus

with integrity in mind - Don’t enable users to change spreadsheets
Design for security
Validate during development, before and during use
Develop and apply rigorous change control procedures

entry is 50% above average.

Spreadsheet should detect errors during data entry - wrong type, e.g., string characters instead of numbers - wrong sequence of entries - wrong data range - wrong format (e.g., date)
Spreadsheet should prompt the user in case of wrong entries, and not crash

Design for Error Detection

for data entry
Protect use of Excel sheet by passwords
Store and load from write protected directory (e.g., secure server or CD)
Display directory, subdirectories, file name and sheet name at the bottom of the spreadsheet
Display date and time of last data entry
Display&print operator name
Verify file integrity with hash function

With VBA (example 3)

Disable menu and tool bars to limit function to the intended use
Disable ‘save’ and ‘save as’ Disable cut/copy and paste control keys

software from RSA
Used to check accuracy or e-mail transfer
Used for digital signatures
Used to verify proper software installation
Used to verify file transfer accuracy in networks
Calculate hash value (124 bit string)
Store the value
For verification: recalculate and compare with original value

access to authorized users (e.g., through user ID/password)
Configure and use secure operating systems (Windows 7, XP, VISTA)
If available, use secure server for storage of spreadsheets and access to spreadsheets
Maintain user lists with authorized access

software
Standard calculations under normal conditions (GAMP Category 3)
Validate
Everything we customize, e.g., - user interface (input/outputs) - validation functions for data entry - macros (VBA Scripts)
Extreme conditions (at and above/beyond limits)
Security functions (e.g., passwords, cell protection)
Spreadsheet integrity (e.g., hash function)

requirements - user profile (skills) - design specifications
Design and code review
Installation qualification/documentation
Testing during development and after installation
Change control/requalification
Validation report

operation range don’t need to be verified (GAMP category 3)
Verify standard functions if used in extreme ranges (e.g., very small numbers) and if there are indications for problems with Excel
Use commercial calculator for verification for 2.
Regularly check the vendor’s website for errors (need written procedure)

correct calculations have been used, e.g., (a+b)*c can be easily mixed up with a + (b*c)
Document formula
Verify any cell links
Protect the spreadsheet
Strict access controls
Validate data input of critical data
Ensure integrity of electronic data

No need to verify accuracy of native functions Ref: (GAMP 5, Page 294)

validation team
Develop preliminary project plan
Interview anticipated user departments about use of the SS
Update project plan and distribute to QA and operation manager
Interview anticipated users for intended use and requirements
Interview anticipated users about required functions
Draft of requirement specifications
Conduct risk assessment
Qualify developer/supplier
Develop template for design specification and review
Develp and review design specification

Slide

plan
Distribute test plan to test persons (user representatives)
Test functional specifications
Evaluate test results (and correct deficiencies, if there are any)
Develop a list with anticipated users
Distribute spreadsheet to anticipated users
Install and document spreadsheet
Test spreadsheet in the user‘s environment
Add the spreadsheet to the company‘s spreadsheet data base
Write the validation report
Repeat tests from (21) every quarter
Follow documented procedures for any changes and record changes in the change history

Slide

risk systems
Print and sign For low risk systems
Use ‘Track Changes’ function For medium risk systems
Use 3rd party software e.g., ExcelSafe, e-Infotree For high risk systems

Changes -select Highlight Changes
Select which changes you want to track and time range
Result: Changes can be reviewed and printed

and Validation for the Multi-User Application for the Chemistry Laboratory

User ID/Password for secure log-on
MS NTFS to limit access to files
Store spreadsheets on write protected server directories
Validate spreadsheet applications
Control and archive spreadsheets for internal audits
Standardize design of templates (use of colors, cell protection)

input of raw data: verify data entry through second person (only for high risk records)

Source: FDA LIB: Spreadsheet Design, Verification and Validation, Use and Storage of Single-User Workbook Files in the US FDA Laboratories

Single-User Excel Spreadsheets in FDA Laboratories

protocol identifies at which steps a computerized system will be used to create, modify, maintain, archive, retrieve, or transmit data.

Document your business practices especially important: where can users change records (e.g., spreadsheet templates)
Define and document your rational behind part 11 controls (e.g., audit trail, archiving on paper vs. electronic, validation)

data
Approval

A, B

Example – Using Excel Template as Calculator

Records required by predicate rule
No __ Explicit _x Implicit __
Regulated activity relies on e-records Yes _x No__
Impact on product quality High _x Medium __ Low __ No __

Business Practice - Steps
Spreadsheet loaded from write-protected server
Data from paper is typed into an Excel spreadsheet Example: weights of balance
Spreadsheet performs calculations and results are printed and signed
All printed results are archived together with original data
No electronic records stored

Printer

No e-audit trail
Load spreadsheet from write protected server
Print file source with each result
Maintain records in paper form

Recommendations

C

(temperature, pressure, results)
Data is stored temporarily and processed
Authorized access to data
Operators can manipulate data

Computer 2 Excel software Primary data evaluation

B

B, C, D

Records required by predicate rule
No __ Explicit _x Implicit __
Regulated activity relies on e-records Yes _x No__
Impact on product quality High _x Medium __ Low __ No __

Business Practice - Steps
Data generated by equipment (e.g., process parameters, signals)
Data transferred to computer 1 for primary data evaluation (no operator interaction)
Intermediate results transferred to computer 2 for secondary evaluation with operator interaction
Results from secondary evaluation are signed on computer 2 and archived

Printer

A

Automatic Transfer of Intermediate Data to Excel

Remark: Computer I and II can be combined

No e-audit trail on Comp1
E-audit trail on Comp2
E-signature on Comp2
Need Excel remediation software
Comp1 and Comp 2 must be validated

Recommendations

3

what the program does
Description of formulas used
User manual incl. description of color coded cells
Explanation of the relationship of formulas used in procedures to Excel equations
Listing of VBA Macros
Test sheets with anticipated results, acceptance criteria and actual results
Security and password maintenance, user lists
Documentation of operating systems, spreadsheet version, workbook version, date of installation