| Department | Validation/Technical Services | Document no | VAL-170 | ||
| Prepared by: | Date: | Supersedes: | |||
| Checked by: | Date: | Date Issued: | |||
| Approved by: | Date: | Review Date: | |||
1 Purpose
The purpose of this document is to provide guidelines for a suitable approach for the qualification of Excel spreadsheets used in direct, or in-direct, GxP related activities at all GMP facilities.
2 Scope
The procedure applies to the initial qualification and re-qualification of Excel spreadsheets, designed and used for making quality based decisions in GxP environments at the GMP facility. These areas include but are not limited to:
– Quality Operations
– Manufacturing Operations
– Development
– Planning
– Regulatory Affairs
The scope does not include those spreadsheets which are single use only (prepared for a specific, one off tasks) or those which are used for organisation of personal, or departmental work plans. These should be verified at each time of use as appropriate.
3 References
– Good Automated manufacturing Practice Guide (GAMP), Version 4, ISPE
– David Harrison and David A Howard, A Pragmatic Approach to the Validation of Excel Spreadsheets, Pharma IT Journal, Vol1 No.4 October 2007
4 Introduction
Spreadsheets can be used to record and manipulate (change, delete, add) GxP data and as such need to be managed to ensure the continued integrity and security of that data. While some are no more than fancy calculators others are embedded with special features and use intricate logic in the form of Macros that are in effect computer programs.
Spreadsheets can be classified into GAMP categories depending on use and contents of the spreadsheet. This will impact the level of validation effort that is applicable just as with other computerized systems. Spreadsheets which fall into GAMP Category 3 guidelines (Table 1) do not require full validation activities but should be held under appropriate security conditions.
Table 1 GAMP Categories for Spreadsheets
| GAMP Category | Description |
| 3 | used solely to produce paper documents |
| 4 | more complex such as those that incorporate templates |
| 5 | uses macros, lookup tables &/or sophisticated logic |
Spreadsheets which have calculations that are fully explained on the spreadsheet and which undergo verification with each use do not need to be fully validated, however where there are templates for the spreadsheets appropriate security conditions, including password protection should be provided.
This guidance outlines steps that shall be followed to request, approve, develop, test, implement, maintain and support a controlled spreadsheet application.
5 Validation of a Spreadsheet Application
5.1 Functional Requirements Development
Spreadsheet applications may be developed by individuals (e.g., laboratory analysts) who have been qualified by training or experience as a Spreadsheet Application Developer.
The Functional Requirements Document incorporating user requirements and functional specifications shall be written by the developer. These should be approved prior to initial design but may be developed retrospectively after a prototype spreadsheet has been prepared.
The Functional Requirements Document should contain applicable sections to describe:
– The intended purpose of the spreadsheet application (such as to calculate results, to re-order results for Management Reports, etc.).
– The formulas that will be included in the spreadsheet application, including logical formulas used for branching or for calculations.
– A description of macros that will be included.
– A description of any column or row rearrangements/sorting that will be required to format a new report from included data.
– A description of the report that will be generated.
– A statement defining electronic storage requirements for the executed spreadsheet application (results).
– Where applicable, issues such as data formats and numerical precision, numeric rules such as rounding or floating points, flagging of limits, boundary conditions, filters or outliers.
– Performance requirements for the application and program (i.e., calculations or reports within X seconds, applications/program availability, etc.).
– Workflow needs such as the source of the data, a statement regarding raw data, and “who will perform what actions and make what decisions based on what data and criteria” may be required.
5.1.1 Review and Approval of Functional Requirements
The functional requirements should be submitted to the Validation Manager for technical approval, to Quality Assurance for regulatory compliance, the Local Area Network Manager (if applicable) and to the Area Manager for overall approval.
5.2 Spreadsheet Application Development
The Spreadsheet Application Developer will devise the spreadsheet application parameters, such as the way the table looks, data formats, data sorting parameters, the addition of fields to the spreadsheet application, reports, formulas and interfaces with other programs and instrumentation.
5.2.1 Raw Data Considerations
The Spreadsheet Application Developer must define how data will be entered.
Data can be inputted directly from instruments (or data files) into a spreadsheet application or data can be entered manually. For automated operations, raw data will be input directly from the instrument to the spreadsheet application.
If data sets are entered manually, all entered data must be reviewed by a second individual to verify the correctness of the entered (transcribed) data. The initials of the reviewer and the date of review should be indicated.
If data sets are entered automatically through an electronic interface, then the transfer link between the spreadsheet application and the source of the data must be validated.
Similarly, data from a spreadsheet must not be exported to a validated computer system/application unless the transfer link has been validated and the importation approved via change control for the receiving system.
5.2.2 Formula Development
Each formula described in the Requirements Documents must be embedded in the spreadsheet application.
The formulas must be checked for errors during development.
Diagnostic tools that allow error checking to be carried out during development are sometimes available with spreadsheet programs.
Examples of error detection tools include:
– Cell errors – error messages that are created when cell rules are violated
– Auditing tools – Cells which are referred to by a formula in another cell may be audited for alignment, with the spreadsheet displaying tracer arrows between cells
– Error tracer – when a formula returns an error, another auditing tool can be used to track it back to its source
5.2.3 Rearranging Columns and Rows
Spreadsheets are also used to sort and rearrange data for reports and presentations. The source of the data may be from another program/database, or an instrument system such as report data from a chromatographic computer system. The procedures to be used for subsequent sorting or rearranging of the data via the spreadsheet application should be described.
5.2.4 Macro Programming
Frequently performed tasks may be automated by recording them as macros and playing them back whenever necessary. Macros may be associated with toolbar buttons, keystrokes and menu entries. Manual sorting and rearranging of data are actions that might be automated, but if the macro is recorded, the actions must be carefully documented during development and tested during the qualification process.
5.2.5 Report Creation
It is the responsibility of the developer to create/modify the spreadsheet application to report the results of calculations performed by the spreadsheet application.
The Spreadsheet Application Report shall contain:
– The method (analytical if for a lab) used during creation of data
– A unique identifier for the analyst and the checker who are responsible for the correctness of entries into the spreadsheet application
– The date the spreadsheet application was executed
– The version of the spreadsheet application program
– Version control for the report
– The unique name of the electronic version of the file
If data is received through an electronic interface, the report must identify the instrument system or software program communicating the data.
5.3 Qualification Procedure
When the spreadsheet has been developed the file should be transferred to the Validation team
The validation engineer will prepare a suitable test protocol using appropriate template
5.3.1 Installation
The protocol will describe the name, version number, location, control and ensure that appropriate security restrictions are implemented at the time of initial installation to the secure directory.
The Validation Department shall store a master copy of the spreadsheet application in a secure environment. Each time the spreadsheet application is required for use on a computer in the user area, it must be opened from the (read only) master copy.
5.3.1.1 Establishment of Version and Security Control:
The Validation Manager will add the version number and effective date to the spreadsheet application, and lock cells containing formulas to protect the contents of the cells. The spreadsheet application shall be protected by a password.
5.3.1.2 Security Settings:
The Validation Department shall maintain the security information (including the passwords for the spreadsheet application) in accordance with site procedures.
5.3.2 Operation/ Perfomance
The Operational/ Performance Qualification shall be written to document the instructions for the testing. The Operation Qualification shall include the following tests:
– Calculation Test to verify that correct formulas are entered
– Logical Values Test to verify Boolean algebra formulas.
– Range/Precision Test to verify calculations will still be accurate when extremely large or small values are entered
– “Challenge condition” testing for the program and application (e.g., invalid inputs, stress testing)
– Boundary and limit checking, failure analysis and confirmation of appropriate error trapping.
Acceptance criteria shall be established for each test and written into the Operation Qualification Protocol. Typical tests with associated acceptance criteria are:
5.3.2.1 Calculation Test
These are performed to verify that correct formulas are entered. Enter sample values in all fields that are included in the input range of all mathematical functions. Restrict the input to whole numbers at this time for aggregate functions (sum, max, etc.) use different values for each field. Document the step, include a printout of the spreadsheet application indicating the test values, and compare the printout to that obtained from a calculator.
Acceptance Criteria
Calculated values from the spreadsheet application must be identical to those obtained from a calculator (preferably with printout capabilities).
5.3.2.2 Logical Values Test
These are performed to verify Boolean algebra formulas. Print the spreadsheet application cells containing Boolean (logical values) and calculate the results manually. Print the results from the spreadsheet application calculation.
Acceptance Criteria
For logical values (e.g. Pass/Fail or True/False) verify the displayed value is the same as the expected value, calculated manually.
5.3.2.3 Range/Precision Test
These are performed to verify calculations will still be accurate when extremely large or small values are entered. Enter extremely large values at the correct precision (e.g. enter 99.99 for 0.01 precision). For aggregate functions, enter 0 in one or more fields with non-zero values in other fields. Duplicate the formula calculations using a calculator and compare the results/printouts. Repeat this test with extremely small values, and report the results.
Acceptance Criteria:
Calculated values from the spreadsheet application must be identical to those calculated manually using a calculator (with correct precision and consistent with agreed Rounding principle).
5.3.3 Execution of Qualification Protocol
Each step of the Operation Qualification Protocol must be appropriately executed and the results recorded.
– Testing results and deviations from the protocol will be recorded in an approved documentation system by the Spreadsheet Application Developer.
– The testing will be performed by a Spreadsheet Application Developer (or designee).
– Test results will be reviewed and approved by the Validation Manager.
5.3.4 Evaluation Requirements
After each step of the qualification process is completed, the following evaluations should occur:
5.3.4.1 Deviations from Protocol:
If the written validation method has not been followed, a protocol deviation must be recorded on the protocol deviation/variance report. An evaluation of the deviation must be completed, and corrective actions listed.
If protocol changes are required, the Protocol Revision Page must be updated as required by the appropriate corporate policy. The revised protocol shall be appropriately approved, e.g., by Quality Assurance and user area Supervisor.
5.3.4.2 Comparison of Results to Acceptance Criteria:
If any of the tests fail, the results must be documented on a protocol deviation/variance report and attached to the executed protocol.
Error analysis shall be conducted and any required changes shall be made to the spreadsheet application to resolve the failure. Typically, all tests must then be repeated.
5.3.4.3 Error Analysis:
If an error results from an incorrect formula, the spread-sheet tools should be used for error analysis to determine the cause of the error. (For example, Excel has error messages for cells and has auditing tools for determining precedents and dependent tracers).
An assessment must be made regarding the impact of the error. Then, if required, the template appropriately modified and all tests repeated.
5.3.5 Final Report
A final report shall be written to summarize the testing results, explain any deviations that occurred during the testing, and to give the rationale for resolution of any deviations.
5.3.5.1 Preparation of Final Report:
The Spreadsheet Application Developer shall complete the Summary of Results Section of the Qualification Protocol, attach all of the associated data and submit the final report for review and approval.
5.3.5.2 Review and Approval:
The Spreadsheet Application Developer should submit the completed Operation Qualification Protocol and associated results for review and approval. The User department manager and the validation manager will review it for technical completeness and the Head of Quality Operations will review for compliance and give overall approval.
5.3.6 Addition to Computer System Inventory:
The Validation Manager shall update the site Computer Systems Inventory List to include the spreadsheet application and validation status.
6 Usage of Spreadsheet Applications
6.1 Availability of Spreadsheet Application:
The Spreadsheet applications shall be protected from unauthorized modification of the master copy. For example, the spreadsheet application shall be available to authorized users as “read only”.
Authorized users will be given access after they have been trained on the use of the spreadsheet application (which shall include proper GMP handling of the files and documentation practices).
Where networks are employed for spreadsheet application use, spreadsheet applications will typically be stored in a network directory that allows access by authorized users. In this instance, the storage of a copy of the spreadsheet application on the hard drive of the local computer shall be forbidden by policy.
6.2 Training of Users
Users shall be trained to use the spreadsheet application. As appropriate, training objectives shall include learning:
– How to use the spreadsheet application, including how to print the Spreadsheet Application Report
– The procedure for storing Spreadsheet Application Reports (if applicable)
Training may be conducted earlier based on a preliminary review of the Final Report.
6.3 Storage of Completed Electronic Files:
When data have been entered into the spreadsheet application, the calculated results become a new electronic file.
If site policy requires storage of an electronic copy of the spreadsheet application file, the file should be stored under a new name in a designated area of the LAN server.
– Files should be named using a convention agreed between the user and site Quality Assurance
– The file name of the completed spreadsheet application electronic report should be recorded in notebook or other similar documentation system as a cross-reference.
– During review of notebook or other similar documentation system data, the reviewer should assure that the electronic file has been stored correctly.
7 Re-qualification of Spreadsheet Application Formulas
Spreadsheet applications in the library will be reviewed / verified every 3 years to address the validity of the validation documentation which includes formula integrity. Reviews will be managed via the Validation Manager program.
Re-qualification may also be triggered by the installation of an upgrade of the spreadsheet program (Change Control System), or by changes in the LAN (e.g., system crashes).
8 Change Control
If changes are required in a spreadsheet application, it is the responsibility of the Department Manager to manage the change using the site Change Control procedures.
Appendix 1 –Definitions
The following definitions may be used when reading these guidelines and interpreting protocols and reports:
| Acceptance Criteria – | A predetermined numerical value or an all-or-none qualitative endpoint that determines the acceptability of test results. |
| Aggregate Function – | A spreadsheet function that performs an action based on a collection of cells (sum, max, etc.). |
| Audit Trail | In reference to computing, a means of tracing all activities affecting a piece of information, such as a data record, from the time it is entered into a system to the time it is removed. An audit trail makes it possible to document, for example, who made changes to a particular record and when. |
| Boolean Algebra | An algebraic system with two binary operations and an identity element that operates in symbolic logic and in logic circuits in computer science. |
| Functional Requirements | A description of the scope, objectives, and types of operations that are to be considered in the development of an information-handling system. |
| LAN (Local Area Network) | A system that links together electronic office equipment, such as computers and word processors, and forms a network within an office or a building. |
| Legacy Spreadsheet | A spreadsheet that was developed prior to the establishment of Validation principles and thus has undergone no or little validation at its inception |
| Macro | Short for macro instruction. A single instruction in programming language that results in a series of instructions in machine language. It may be a series of instructions that are carried out manually and “recorded” by the spreadsheet program. The macro may be replayed to execute the same instructions on a new set of data, producing new results. |
| Management Approval | Approval with respect to consideration of alignment with business objectives, scientific soundness, and current GMP requirements. |
| Operation Qualification | A written plan that is prepared prior to the qualification testing. The Qualification Protocol is generally prepared by the user department, reviewed by department supervision and appropriate support units and approved by the Quality Assurance Department. |
| Protocol Deviation | Significant differences between expected results or acceptance criteria identified in the protocol and actual test result or conditions. |
| Repository | A place where spreadsheet applications may be put for safekeeping, e.g., a data warehouse, or storage area. |
| Spreadsheet Application | A specific instance of a spreadsheet that has been developed to perform calculations for a method. |
| Spreadsheet Application Report | A printed results report arising from calculations completed by a spreadsheet application. |
| Spreadsheet Application Developer | An individual, who is sufficiently familiar with spreadsheet software and the task(s) to be automated, to carry out the development of the spreadsheet application. This individual may be qualified by means of either work experience or training, or both. |
| Spreadsheet Application Specialist | A person with the skills in spreadsheet development, the knowledge of the spreadsheet program, and experience in developing spreadsheet applications for use in their department. |
| Spreadsheet Program | Application Software, typically Microsoft Excel. |
| Version Number | A number assigned by a software developer to identify a particular program at a particular stage, both before and after public release. Successive public releases of a program are assigned increasingly higher numbers. Version numbers usually include decimal fractions. Major changes are usually marked by a change in the whole number, whereas, with minor changes only the number after the decimal increases. |
Appendix 2 – Responsibilities
| Role | Responsibilities |
| User | Use only spreadsheets that have been validated and approved for handling of data associated with the testing, trending and disposition of raw materials and finished products. Store completed, reviewed and approved (i.e., second user review) spreadsheets in spreadsheet archives consistent with site procedures for storage of electronic raw data. Records any errors that occur during the use of a spreadsheet application and reports the error(s) to the supervisor. |
| Spreadsheet Application Developer | Work with users, prepares Functional Requirements Documents for review and approval by Spreadsheet Specialist, Users, QA and Supervisors. Write test scripts for the Performance Qualification (PQ) Protocol. Develop new spreadsheet applications to meet needs using the Spreadsheet Application process; this includes creating/modifying the spreadsheet application for new or revised systems. Verify all calculations included in the spreadsheet, including a test of the range of operating parameters. Perform testing for Operational Qualification (OQ). Be responsible for the preparation of the “Final Report” Participate in legacy system validation/qualification as required by management. |
| Validation Department | Review spreadsheet application Functional Requirements for technical content. Review and approve spreadsheet application development, format and calculations submitted by Spreadsheet Application Developer for technical content during Qualification. Assures that formulas/macros are protected (protected fields) from unauthorized changes. Maintain approved spreadsheet applications including version control and archiving. Develop and maintain a “template report” that will be used for printed information that contains version control and other critical identifiers. Perform and document periodic spreadsheet application verifications. Prepare protocols for legacy system validation/qualification of spreadsheet applications. Update the list of approved spreadsheets and remove any previous spreadsheet(s) that the new one replaces. Also, ensure that a reference to this list is included in the site VMP system inventory list. |
| Area Supervisor | Ensure that Users are trained in and follow the procedures for spreadsheet application use as defined in this guidance and related procedures. Ensure periodic verification of the integrity of the “spreadsheet application”. Assure that Spreadsheet Application Developers have been appropriately trained. Participate in legacy system validation/qualification of spreadsheet application. Review and approve spreadsheet application proposals/functional requirements descriptions prepared by the Spreadsheet Application Developer |
| Quality Assurance Function | Review and approve qualification protocols. Review and approve “Final Report” for the qualification. Review and approve Protocols for legacy system validation/qualification. Review and approve, from a regulatory compliance perspective, spreadsheet application proposals/functional requirements descriptions prepared by the Spreadsheet Application Developer. |
| IT Department | Upgrading the application software when patches are approved for installation. Notifying user and QA Management when upgrades are installed. |